2

I want to create a color scale based on the input values from an excel spreadsheet.The required output is a color scaled cells in the excel sheet based on the values which is programmed in R.I tried through XLConnect but haven't yet found a suitable solution.

thanks & Regards

Abinas Mishra
  • 21
  • 1
  • 3
  • 1
    Welcome to SO. You have to be much more specific, including self contained example code of what you did, what did not work, what the errors where that you received. – Paul Hiemstra Aug 21 '13 at 12:33
  • You're almost certainly doing things the hard way, but at the very least tell us whether you need to know the actual values generated in `R` to set the ranges of the Excel cells' conditional formatting. – Carl Witthoft Aug 21 '13 at 13:51
  • @CarlWitthoft , Sorry for the late reply. Yes your understanding is correct , i need to know the exact values in R to set the ranges of the excel cells. – Abinas Mishra Aug 26 '13 at 06:07
  • @PaulHiemstra Thanks. I am actually new to using R and have been exploring all the function and packages. What i tried was loading an xls file and putting a heatmap based on the ranges. But the problem is that this is device output which i will not be able to save in an excel sheet. – Abinas Mishra Aug 26 '13 at 06:12
  • Time to back up: what is your desired result? It's easy to generate a heatmap, with or without the numbers displayed, in `R` . Unless someones aiming a gun at your head, you shouldn't use Excel in the first place. – Carl Witthoft Aug 27 '13 at 11:47

2 Answers2

2

The xlsx package allows using RGB-colors for Fill(). E.g.

fg <- rgb(100, 50, 50, max = 100)
bg <- "black"
style <- CellStyle(wb) + Fill(foregroundColor = fg, backgroundColor = bg)
setCellStyle(c, style)

Setting the max value for rgb() makes it easier to scale the color with respect to the value's range.

The xlsx package has some IMHO rough edges, though.

lith
  • 929
  • 8
  • 23
0

I think you need to write a macro inside your Excel workbook to allow dynamic changes based on the data you load into cells. Take a look at the last answer at http://social.msdn.microsoft.com/Forums/office/en-US/4998f97b-44c2-431a-bc91-138a94b12519/how-to-dynamically-set-a-cells-background-color-based-on-value-from-another-worksheet-in-the-same for one possibility. Here's another: http://www.mrexcel.com/forum/excel-questions/459520-dynamically-assigning-cell-rgb-background-color-using-contents-3-other-cells.html

I haven't tried these out, so you'll have to play with them to see if they meet your needs.

Edit: tripped across this SO question: Conditional Formatting using Excel VBA code

Edit to respond to comment: @AbinasMishra I don't understand your comment. R cannot change the values inside Excel's Conditional Formatting dialog box, so far as I know, in any way other than having Excel run a macro. And your claims about "additional memory" to run a macro are wrong. If you open an Excel sheet it's going to allocate memory for all its internal objects. The time and RAM associated with running a small macro are miniscule.

Now, are you thinking of static cell coloring, rather than Conditional Formatting? If you wanted to do that from R, your best bet is to have R write some integer values to a 'reference' worksheet in your workbook, and, yes, run a macro which looks up those integer values and assigns a fill color to the cells of interest.

Community
  • 1
  • 1
Carl Witthoft
  • 20,573
  • 9
  • 43
  • 73
  • May be i had missed something.This can be done using macro i understand, but then the conditional formatting happens every time we open the excel sheet, which consumes additional memory in our system even if we don't change any value as it runs a check.But suppose the conditional formatting is done through R and we just get the output in excel then there would be no additional check as there would be no background codes running. Hence, we only would be running the R code the time the values are changed and the excel sheet would only give the output. – Abinas Mishra Aug 27 '13 at 06:39
  • ok. so if we want to have a static color bar, in which we just give the low and high value with the color grades say yellow(#FFD801) for 'low' and red(#FF0000) for 'high'.The values inbetween is to be automatically colored in the range between #FF0000 to #FFD801. The static color bar output has to be in the reference excel sheet.So is it possible? – Abinas Mishra Aug 28 '13 at 11:59