R语言按条件设置Excel单元格背景色并导出xlsx文件问题咨询

问题原因 #

• 样式参数使用错误：createStyle中设置单元格纯色背景需使用fgFill参数，你代码中使用的bgFill是单元格填充图案的背景色，多余的边框默认参数调用也会增加不必要的计算开销。
• 条件格式作用范围不合理：你将纯数字的序号列、表头行都纳入了条件格式检索范围，大量永远无法匹配规则的无效检索触发了程序死锁。
• 规则写法存在兼容性问题：type = "contains"搭配字符串规则在部分版本的openxlsx中存在解析bug，会触发循环计算。

修正后可运行代码（openxlsx原生条件格式方案） #

library(openxlsx)

# 你原有测试数据生成代码无需改动
lot.name <- c("LotA","LotB","LotC")
set.seed(569336)
save.output <- vector()
for (i in 1:50){
  new.rw <-  sample(lot.name)
  save.output <- rbind(save.output, new.rw)
}
save.output <- as.data.frame(save.output)
row.names(save.output) <- NULL
colnames(save.output) <- c("Test1", "Test2", "Test3")
save.output$iter <- 1:dim(save.output)[1]
save.output2 <- save.output[, c(4,1:3)]

# 导出加格式设置部分
wb <- createWorkbook()
addWorksheet(wb, "Sheet", gridLines = TRUE)
writeData(wb, "Sheet", save.output2)

# 定义三种样式
red_style <- createStyle(fontColour = "black", fgFill = "red")
blue_style <- createStyle(fontColour = "black", fgFill = "lightblue")
green_style <- createStyle(fontColour = "black", fgFill = "lightgreen")

# 条件格式仅作用于Test1~Test3列（第2到第4列），仅覆盖数据行（第2行到第51行，第一行是表头）
conditionalFormatting(wb, "Sheet", 
                      cols = 2:4, 
                      rows = 2:(nrow(save.output2)+1),
                      rule = '== "LotA"', 
                      style = red_style,
                      type = "expression")
conditionalFormatting(wb, "Sheet", 
                      cols = 2:4, 
                      rows = 2:(nrow(save.output2)+1),
                      rule = '== "LotB"', 
                      style = blue_style,
                      type = "expression")
conditionalFormatting(wb, "Sheet", 
                      cols = 2:4, 
                      rows = 2:(nrow(save.output2)+1),
                      rule = '== "LotC"', 
                      style = green_style,
                      type = "expression")

saveWorkbook(wb, file = "RamdomizationExample.xlsx", overwrite = TRUE)

更稳定的替代方案（直接指定单元格样式，无依赖条件格式） #

如果条件格式方案依然存在兼容问题，可以直接遍历匹配单元格赋值样式，运行更稳定：

library(openxlsx)
# 测试数据生成代码同上，此处省略

wb <- createWorkbook()
addWorksheet(wb, "Sheet", gridLines = TRUE)
writeData(wb, "Sheet", save.output2)

# 预定义样式映射
style_map <- list(
  LotA = createStyle(fgFill = "red", fontColour = "black"),
  LotB = createStyle(fgFill = "lightblue", fontColour = "black"),
  LotC = createStyle(fgFill = "lightgreen", fontColour = "black")
)

# 遍历需要设置格式的列和行
for (col in 2:4) {
  for (row in 2:(nrow(save.output2)+1)) {
    cell_val <- save.output2[row-1, col]
    addStyle(wb, "Sheet", style = style_map[[cell_val]], rows = row, cols = col)
  }
}

saveWorkbook(wb, file = "RamdomizationExample_stable.xlsx", overwrite = TRUE)

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