How can we create a color scales for excel using R

Question

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

Answer 1

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.

Answer 2

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.