Search code examples
rconditional-formattingopenxlsx

Conditional formatting cells with exact text using openxlsx

I am exporting data into an xlsx file using the openxlsx R library, and wish to format cells conditionally based on the exact text they contain. To make the example simpler, let's pretend I want cells containing "A" to be blue, and cells with "B" to be red.

In Excel, I would do this using "Conditional Formatting" -> "Format only cells that contain" -> "Format only cells with:", "Cell Value", "equal to", which matches text exactly. The "specific text" options (containing, starts with, ends with) are not suitable here, as other cells may contain the text I wish to match as part of a longer text string (e.g. a cell with "BANANA" contains both A and B).

I have tried the contains and expression conditionalFormatting type options in openxlsx:

wb <- createWorkbook()
addWorksheet(wb, sheetName = "Test Sheet")

data_to_write <- data.frame(column_1 = c("Apples", "BANANAS", "A", "B"),
                            column_2 = c("C", "D", "B", "Y"))

writeData(wb, "Test Sheet", data_to_write, startCol = 1, startRow = 1, colNames = T)

red_style   <- createStyle(bgFill = "red")
blue_style  <- createStyle(bgFill = "blue")


conditionalFormatting(wb, "Test Sheet", cols = 1:4, rows = 1:5, type = "contains", rule = "A", style = red_style)
conditionalFormatting(wb, "Test Sheet", cols = 1:4, rows = 1:5, type = "expression", rule = "==B", style = blue_style)


saveWorkbook(wb, "Test workbook 1.xlsx", overwrite = TRUE)

contains successfully formats the "A" cells red, but also formats "Apples" and "BANANAS" red (which I do not want).

expression does not format anything blue (although I can see the conditional formatting rule is in place when I open using Excel)

Opening the workbook exported by openxlsx in Excel, the expression rule appears as type formula:

Screenshot of the Conditional Formatting Rules Manager dialog box in Excel, showing Formula: =B and Cell Value contains 'A'

But I wish it to be Cell Value = :

Screenshot of the Conditional Formatting Rules Manager dialog box in Excel, showing Cell Value = 'B' and Cell Value = 'A'

Is there a way I can do this using openxlsx?

Solution

  • The trick lies in the argument rule when using type = "expression". When the rule = "==A" you aren't telling it to search for character "A". By changing this to rule = '=="A"' the conditional formatting will work. So in the provided code you could implement this as follows:

    conditionalFormatting(wb, "Test Sheet", cols = 1:4, rows = 1:5, type = "expression", rule = '=="A"', style = red_style)
conditionalFormatting(wb, "Test Sheet", cols = 1:4, rows = 1:5, type = "expression", rule = '=="B"', style = blue_style)