openxlsx conditional formatting with multiple rules-CodePudding

I would like to format some cells in Excel using openxlsx using multiple rules. For example, here the result 3 should not be highlighted as the criteria is NA. I am comparing the result cell to the criteria cell. Is there a way of using two rules to format cells? I tried with different combinations of & and quotation marks but couldn't get it to work.

library(openxlsx)
library(tidyverse)

# data -----------
df <- tibble(result = c(1, 2, 3, 4, 5),
             criteria = c(0.5, 5, NA, 2, 6))

# formatting Style
yellow <- createStyle(fontColour= "#000000", bgFill = "#FFBF3F")

# create Excel workbook
wb <-createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", df) 

# format
conditionalFormatting(wb, "Sheet 1",
                      cols = 1,
                      rows = 2:(nrow(df) 1),
                      type = "expression",
                      rule = "> $B2", 
                      style = yellow)

# view
openXL(wb)

This is the desired output:

tibble::tribble(
  ~result, ~criteria,                         ~desired,
        1,       0.5, "Highlighted as is greater than",
        2,         5,   "Less than so not highlighted",
        3,        NA,          "NA so not highlighted",
        4,         2, "Highlighted as is greater than",
        5,         6,   "Less than so not highlighted"
  )

CodePudding user response：

by keeping NA true we can get the desired result

library(openxlsx)
library(tidyverse)

# data -----------
df <- tibble(result = c(1, 2, 3, 4, 5),
             criteria = c(0.5, 5, NA, 2, 6),)

# formatting Style
yellow <- createStyle(fontColour= "#000000", bgFill = "#FFBF3F")

# create Excel workbook
wb <-createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", df,keepNA = TRUE) 

# format
conditionalFormatting(wb, "Sheet 1",
                      cols = 1,
                      rows = 2:(nrow(df) 1),
                      type = "expression",
                      rule = ">$B2", 
                      style = yellow)

# view
openXL(wb)