如何用openxlsx实现Excel多规则条件格式？

问题说明 #

使用openxlsx包为Excel单元格设置条件格式时，需求是：将result列单元格与对应criteria列单元格比较，仅当result大于criteria且criteria不为NA时高亮。当前仅设置规则"> $B2"，会导致criteria为NA的result=3被错误高亮，需要修正规则实现多条件判断。

现有问题代码：

library(openxlsx)
library(tidyverse)

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

# 定义高亮样式
yellow <- createStyle(fontColour= "#000000", bgFill = "#FFBF3F")

# 创建工作簿并写入数据
wb <-createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", df) 

# 条件格式设置（存在问题）
conditionalFormatting(wb, "Sheet 1",
                      cols = 1,
                      rows = 2:(nrow(df)+1),
                      type = "expression",
                      rule = "> $B2", 
                      style = yellow)

# 打开查看
openXL(wb)

期望效果：

解决方案 #

利用Excel条件格式的AND()函数组合两个判断条件：

1. $A2>$B2：判断当前result单元格值大于对应criteria单元格值
2. NOT(ISNA($B2))：判断对应criteria单元格不为NA

将条件格式规则修改为"AND($A2>$B2, NOT(ISNA($B2)))"即可实现需求。

修改后的完整代码 #

library(openxlsx)
library(tidyverse)

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

# 定义高亮样式
yellow <- createStyle(fontColour= "#000000", bgFill = "#FFBF3F")

# 创建工作簿并写入数据
wb <-createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", df) 

# 修正后的条件格式设置
conditionalFormatting(wb, "Sheet 1",
                      cols = 1,
                      rows = 2:(nrow(df)+1),
                      type = "expression",
                      rule = "AND($A2>$B2, NOT(ISNA($B2)))", 
                      style = yellow)

# 打开查看
openXL(wb)

效果验证 #

运行上述代码后，Excel中仅会高亮同时满足以下两个条件的result单元格：

• 单元格值大于同一行criteria列的值
• 同一行criteria列的值不为NA

完全符合期望的格式要求。

内容的提问来源于stack exchange，提问作者william3031