如何在R中为Excel不同分组设置差异化条件格式配色

需求说明 #

将数据框导出为Excel文件，需满足：

• 当Category1与Category2不相等时，高亮该行所有列
• 不同Group分组使用不同的高亮背景色

示例数据 #

示例数据结构如下：

# A tibble: 9 × 3
  Group Category1 Category2
  <chr> <chr>     <chr>    
1 A     1C        1C       
2 A     1C        1C       
3 A     1C        1C       
4 B     2D        3B       
5 B     2D        3B       
6 B     2D        3B       
7 C     3E        3E       
8 C     3E        3E       
9 C     3E        1A 

可通过以下代码生成：

data <- structure(list(Group = c("A", "A", "A", "B", "B", "B", "C", "C", 
"C"), Category1 = c("1C", "1C", "1C", "2D", "2D", "2D", "3E", 
"3E", "3E"), Category2 = c("1C", "1C", "1C", "3B", "3B", "3B", "3E", "3E", "1A")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-9L))

修改后的完整代码 #

原代码统一使用黄色高亮，修改后实现分组差异化配色：

library(openxlsx)

# 生成数据（如果已存在可跳过）
data <- structure(list(Group = c("A", "A", "A", "B", "B", "B", "C", "C", 
"C"), Category1 = c("1C", "1C", "1C", "2D", "2D", "2D", "3E", 
"3E", "3E"), Category2 = c("1C", "1C", "1C", "3B", "3B", "3B", "3E", "3E", "1A")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-9L))

# 创建工作簿与工作表
wb <- createWorkbook()
addWorksheet(wb, sheetName = 'Changes')
writeData(wb, sheet = 'Changes', x = data)

# 定义各Group对应的高亮背景色（可自行调整）
group_colors <- c(
  "A" = "#FFE663",
  "B" = "#63FFE6",
  "C" = "#E663FF"
)

# 遍历每个Group，应用对应条件格式
for (group in names(group_colors)) {
  # 创建当前Group专属样式
  highlight_style <- createStyle(fontColour = 'black', bgFill = group_colors[group])
  
  # 筛选目标行：当前Group下Category1≠Category2的行，转换为Excel行号（+1是因为表头占第1行）
  target_rows <- which(data$Group == group & data$Category1 != data$Category2) + 1
  
  # 仅当存在目标行时应用格式
  if (length(target_rows) > 0) {
    conditionalFormatting(
      wb,
      sheet = 'Changes',
      cols = 1:ncol(data),  # 对所有列生效
      rows = target_rows,
      # 规则：当前行属于目标Group，且Category1≠Category2
      rule = sprintf('$A%d="%s" AND $B%d<>$C%d', target_rows[1], group, target_rows[1], target_rows[1]),
      style = highlight_style
    )
  }
}

# 保存Excel文件
saveWorkbook(wb, 'Changes.xlsx', overwrite = TRUE)

关键说明 #

• 颜色映射：group_colors命名向量可自由扩展，新增Group时只需添加对应颜色代码
• 目标行筛选：通过which()精准定位符合条件的行，转换为Excel行号时需加1（表头占用第一行）
• 批量列处理：cols = 1:ncol(data)一次性处理所有列，避免重复编写多段格式代码
• 条件规则：结合分组判断与列值不等判断，确保仅目标分组下的差异行被高亮

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