[R] XLSX package + Excel creation question
Keith Jewell
keith.jewell at campdenbri.co.uk
Wed Sep 4 13:22:41 CEST 2013
I'll skip over the courtesy implications of double posting/pointing to
stackoverflow.
The stackoverflow thread makes it look as if you need to learn more
Excel. Do you really not know what an Excel template is?
It sounds as if you want what Excel calls "conditional formatting" which
you can specify as custom number formats, see
http://www.ozgrid.com/Excel/CustomFormats.htm.
Excel's help on custom number formats says:
----------------
To specify number formats that will be applied only if a number meets a
condition that you specify, enclose the condition in square brackets.
The condition consists of a comparison operator (comparison operator: A
sign that is used in comparison criteria to compare two values.
Operators include: = Equal to, > Greater than, < Less than, >= Greater
than or equal to, <= Less than or equal to, and <> Not equal to.) and a
value. For example, the following format displays numbers that are less
than or equal to 100 in a red font and numbers that are greater than 100
in a blue font.
[Red][<=100];[Blue][>100]
------------------
R package xlsx allows such formats (?DataFormat) as does R package
XLConnect (?setDataFormat).
HTH
Keith J
On 04/09/2013 09:57, Zsurzsa Laszlo wrote:
> http://stackoverflow.com/questions/18511249/excel-cell-coloring-using-xlsx
>
> This is the initial post on stackoverflow. Please look at this maybe I'm
> clearer here.
>
> Thank you in advance,
>
> -------------------------------------------------------------------------------------
> - László-András Zsurzsa, -
> - Msc. Infromatics, Technical University Munich, Germany -
> - Scientific Employee, TUM -
> -------------------------------------------------------------------------------------
>
>
> On Fri, Aug 30, 2013 at 3:48 PM, jim holtman<jholtman at gmail.com> wrote:
>
>> You can also look at the XLConnect package.
>> Jim Holtman
>> Data Munger Guru
>>
>> What is the problem that you are trying to solve?
>> Tell me what you want to do, not how you want to do it.
>>
>>
>> On Thu, Aug 29, 2013 at 9:40 AM, Zsurzsa Laszlo<zsurzsalaszlo at gmail.com>
>> wrote:
>>> I understand you response but it does not solve the problem. I'am aware
>>> that one can simply color every cell in an excel file by using his own
>>> algorithm.
>>>
>>> The question was if I can write my data to a *single* cells and use
>>> different formatting for every piece of data.
>>>
>>>
>> -------------------------------------------------------------------------------------
>>> - László-András Zsurzsa, -
>>> - Msc. Infromatics, Technical University Munich, Germany -
>>> - Scientific Employee, TUM -
>>>
>> -------------------------------------------------------------------------------------
>>>
>>>
>>> On Thu, Aug 29, 2013 at 3:36 PM, Rainer Hurling<rhurlin at gwdg.de> wrote:
>>>
>>>> Am 29.08.2013 15:03 (UTC+1) schrieb Zsurzsa Laszlo:
>>>>> First of all thank you for the quick resposen.
>>>>>
>>>>> I know I can color and set up every cell. I will take a look again *
>>>>> CellStyle* but is it possbile for example to write an array to a
>> single
>>>>> cell that has different colors for some data. Basically the color
>> depends
>>>>> on the data.
>>>>
>>>> As far as I know there is no ready to use functionality to mask groups
>>>> of selected cells. You have to write your own function, which selects
>>>> the right cells and changes their style with setCellStyle(cell,
>> cellStyle).
>>>>
>>>> Some hints are given in the examples section of ?CellStyle.
>>>>
>>>>>
>>>>>
>>>>
>> -------------------------------------------------------------------------------------
>>>>> - László-András Zsurzsa,
>> -
>>>>> - Msc. Infromatics, Technical University Munich, Germany -
>>>>> - Scientific Employee, TUM
>> -
>>>>>
>>>>
>> -------------------------------------------------------------------------------------
>>>>>
>>>>>
>>>>> On Thu, Aug 29, 2013 at 2:55 PM, Rainer Hurling<rhurlin at gwdg.de>
>> wrote:
>>>>>
>>>>>> Am 29.08.2013 12:08 (UTC+1) schrieb Zsurzsa Laszlo:
>>>>>>> Dear R users,
>>>>>>>
>>>>>>> I have a question about the xlsx package. It's possible to create
>> excel
>>>>>>> files and color cells and etc.
>>>>>>
>>>>>> yes, with package xlsx you can colourize you data sheets, even the
>>>>>> fonts. See for example ?CellStyle .
>>>>>>
>>>>>> A good demonstration of the capabilities is on
>>>>>>
>>>>>>
>>>>
>> http://tradeblotter.wordpress.com/2013/05/02/writing-from-r-to-excel-with-xlsx/
>>>>>>
>>>>>>>
>>>>>>> My question would be that is it possible to color only some part of
>> the
>>>>>>> data hold in a cell. Let's assume I've got the following data :
>>>>>>> 167,153,120,100 and I want to color to red everything that is bigger
>>>> then
>>>>>>> 120. How can I achive this using R.
>>>>>>>
>>>>>>> Example file setup with a few lines in attachment. (SEL_MASS column
>> can
>>>>>> be
>>>>>>> used for example)
>>>>>>
>>>>>> Attachment missing ...
>>>>>>
>>>>>> HTH,
>>>>>> Rainer
>>>>>>
>>>>>>>
>>>>>>> Thank you in advance,
>>>>>>>
>>>>>>
>>>>
>> -------------------------------------------------------------------------------------
>>>>>>> - László-András Zsurzsa,
>>>> -
>>>>>>> - Msc. Infromatics, Technical University Munich, Germany -
>>>>>>> - Scientific Employee, TUM
>>>> -
>>>>>>>
>>>>>>
>>>>
>> -------------------------------------------------------------------------------------
>>>>>>
>>>>>
>>>>> [[alternative HTML version deleted]]
>>>>>
>>>>>
>>>>>
>>>>> ______________________________________________
>>>>> R-help at r-project.org mailing list
>>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>>> PLEASE do read the posting guide
>>>> http://www.R-project.org/posting-guide.html
>>>>> and provide commented, minimal, self-contained, reproducible code.
More information about the R-help
mailing list