0

I want to use the R-package 'openxlsx' to do conditional formatting of a .xlsx file, but I can't figure out how to format a cell if the value does NOT contain a certain textstring. In the following example (which works perfectly) i am formatting the cells which contains the string "OK" (but my goal is to format everything else):

library(openxlsx)

style <- createStyle(fontColour = '#9C0006', bgFill = '#FFC7CE') 
wb    <- openxlsx::loadWorkbook('myFile.xlsx')
openxlsx::conditionalFormatting(wb, 'Sheet1', cols = 3:7, rows=1:100, type = 'contains', rule= 'OK', style = style)
openxlsx::saveWorkbook(wb, 'myFile.xlsx')

When looking at the documentation for openxlsx i see that operators are allowed in the rule-argument, so i tried the following:

openxlsx::conditionalFormatting(wb, 'Sheet1', cols = 3:7, rows=1:100, type = 'contains', rule= '!=OK', style = style)

This still does not work, as it seems the operators are only usable when type is set to 'expression'.

It seems to me that I might be missing something very small and easy, but I have had no luck figurering out what that might be, so hopefully someone has an idea (or can tell me that it just isn't possible to do it the way I intend to)

Sjakalen
  • 1
  • 2
  • You define `style` but use `style=Style`? – r2evans Aug 18 '20 at 13:57
  • I just cloned `openxlsx` and checked its history, I can find no reference to `fontColor`, it introduced `fontColour` in 2014. Does the code you've provided generally work for you? – r2evans Aug 18 '20 at 14:01
  • @r2evans Those are both excellent points, I may have been a bit too fast writing the example-code.I have style=style and use fontColour. I apologize for my sloppiness. – Sjakalen Aug 18 '20 at 14:16
  • `xlsx` supports `"containsText"` (mapped to `"contains"` in `openxlsx`), `"notContains"`, `"beginsWith"`, and `"endsWith"`. `openxlsx` has implemented the first only. There does not appear to be an immediate workaround. I've submitted a FR [openxlsx#94](https://github.com/ycphs/openxlsx/issues/94) for this, and since the code is relatively straight-forward, I would think that it would be good for a developing programming to submit a PR for it. (There are two places to add code: `conditional_formatting.R` and `WorkbookClass.R`, plus perhaps tests and docs.) – r2evans Aug 18 '20 at 14:51
  • @r2evans. Wow, thank you so much for looking into it, it honestly gives me peace of mind knowing i'm not just using the package wrong since it seemed like maybe a simple syntax error. I made a (crude) workaround which I am now convinced is not as stupid as I first thought. – Sjakalen Aug 18 '20 at 18:54

2 Answers2

1

The new development version has the listed features implemented. I hope it works for you. Added features for conditionalFormatting to support also 'contains not', 'begins with' and 'ends with'

YcPhS
  • 11
  • 1
  • Sjakalen, are you able to load the development version ([instructions](https://github.com/ycphs/openxlsx/issues/94#issuecomment-676483429)) and test on your data? – r2evans Aug 19 '20 at 15:53
0

Since r2evans so kindly explained to me that the function I was seeking was not available I created a workaround. It might not be pretty but it did the simple job I was trying to do. What I did was I formatted the entire workbook the way I wanted the cells NOT containing "OK" to be formatted, and then "unformatted" (formatted back to the standard format) all the cells containing "OK".

Sjakalen
  • 1
  • 2