openxlsx conditionalFormatting doesn't work as expected when certain characters are present in the rule

user_8864619 0
Viewed 188

I'm using openxlsx to write data from a data frame to an excel file. I need the data to be coloured given some rules (eg if the value of a cell is A1 & B1 colour the cell with red). The problem is that this doesn't seem to work when the string representing the rule contains the following characters: &, *. I assume that there might be more characters having an unusual behaviour, but for these two I've experienced the following:

  • &: if & is in the rule then the Excel file gets corrupt and it doesn't even open properly
  • *: if * is in the rule then all the cells are getting coloured regardless of their content, which is not the expected outcome

Here's the code I'm using:

library(openxlsx)

df <- data.frame(type=c("A1 & B1","A1","B2*","A & B2","C&","C!*"))

wb <- createWorkbook("test.xlsx")
addWorksheet(wb, "Sheet1")
writeData(wb, sheet = 1, df)

style1 <- createStyle(fontColour = "white", bgFill = "red")
style2 <- createStyle(fontColour = "black", bgFill = "yellow")

conditionalFormatting(
  wb,
  "Sheet1",
  cols = 1,
  rows = 2:nrow(df)+1,
  type = "contains",
  rule = "A1 & B1",
  style = style1
  
)

conditionalFormatting(
  wb,
  "Sheet1",
  cols = 1,
  rows = 2:nrow(df)+1,
  type = "contains",
  rule = "*",
  style = style2
  
)

saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

When using rules that do not include & or *, eg if we use rule = "A1" the behaviour is the expected and A1 & B1andA1 will be coloured. To clarify using multiple rules works fine, but when a rule including & or * is used then happens what I've explained above.

openxlsx r
1 Answers

Following this post you have to "escape" a literal * using a tilde. The & is a bit more tricky. But I remembered that I already had a similar issue when creating a docx and voila same solution applies here: You could replace a & by its html entity &amp;:

Note: Additionally, as I guess that you want to start the conditional formatting in row 2 ... : 2:nrow(df)+1 will add +1 to each element of the sequence 2:nrow(df). To fix that use parantheses or do 1:nrow(df)+1

library(openxlsx)

df <- data.frame(type=c("A1 & B1","A1","B2*","A & B2","C&","C!*"))

wb <- createWorkbook("test.xlsx")
addWorksheet(wb, "Sheet1")
writeData(wb, sheet = 1, df)

style1 <- createStyle(fontColour = "white", bgFill = "red")
style2 <- createStyle(fontColour = "black", bgFill = "yellow")

conditionalFormatting(
  wb,
  "Sheet1",
  cols = 1,
  rows = 1:nrow(df)+1,
  type = "contains",
  rule = "A1 &amp; B1",
  style = style1
  
)

conditionalFormatting(
  wb,
  "Sheet1",
  cols = 1,
  rows = 1:nrow(df)+1,
  type = "contains",
  rule = "~*",
  style = style2
  
)

saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

enter image description here

user_129938610
Related