2

I am applying conditional formatting to an excel sheet using OPENXLSX package in R. My code is something like this :

posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")

conditionalFormatting(wb=wb1, sheet='sheet1', cols=1:10, rows=1:100, rule=" > 4", type = "expression",style = posStyle)

The problem I am facing is that in between columns 1 and 10, if any column is non-numeric then the conditional formatting is applied to all cells in that column. This ideally shouldn't happen because my rule states apply formatting wherever cell value is greater than 4. Am I missing something? If not, is there any solution for this? Thanks!

Prathamesh
  • 97
  • 2
  • 8

1 Answers1

1

to answer your first question: yes, you are missing something. R compares strings in a lexicographic way, and obviously it casts the "4" in your example to a string before it compares it to another string - just type "a">4 in your R console and see that the result is TRUE. For more details on this see for example http://stat.ethz.ch/R-manual/R-devel/library/base/html/Comparison.html.

To solve your problem, it would be easiest if you could change your rule to " >4 & <"a" or so, but the &-operator seems not to be valid according to the conditionalFormatting help. So to me it looks like you have to know the columns with numeric input and just give these to the formatter, like

conditionalFormatting(wb=wb1, sheet='sheet1', cols=numeric_cols, rows=1:100, rule=" > 4", type = "expression",style = posStyle).

Philip Brinkmann
  • 11
  • 2