openxlsx conditionalFormatting doesn't work as expected when certain characters are present in-CodePudding

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.

CodePudding user response：

Following