R语言使用openxlsx导出带条件行格式的DataFrame问题求助

我有一个从Excel导入并处理后的DataFrame df1，需要将其导出为带有特定行颜色格式的Excel文件，格式规则如下：

• 当Subproject列的值为NA1时，整行设置为黄色（#FFFF00）；
• 当Subproject列的值以SE加任意数字开头（如SE235、SE062）时，整行设置为红色（#FF0000）；
• 当Sample_Thaws列的值不为0（1-10）且Subproject列的值以RE开头（非SE）时，整行设置为蓝色（#0099FF）。

我能导出无格式的df1，但不知如何添加格式，目前尝试使用openxlsx库。另外，是否可以在读取Excel时读取行颜色并添加标注列，以便后续用Excel条件格式还原颜色？

我尝试了以下代码，但使用saveWorkbook()时未生成文件，且未完善蓝色行的Subproject以RE开头的条件逻辑：

# #read in the original data
df1 <- read.xlsx("1946_P2_master.xlsx")  
# #(I then did the data manipulation and assigned the dataframe back to df1)

# Create a workbook
wb <- createWorkbook()

# #we need colors such that SE in Subproject column gives a red colour, or NA1 in the same column gives yellow, and  any number but 0 in samplethaws gives blue,
yellow_rows <- which(df1$Subproject == "NA1")
red_rows <- which(grepl("^SE\\d+", df1$Subproject))
blue_rows <- which(df1$Sample_Thaws != 0)

# Add a worksheet
addWorksheet(wb, "Sheet1")

# Write data to the worksheet
writeData(wb, "Sheet1", df1)

# Create styles for yellow, red, and blue
yellow_style <- createStyle(fgFill = "#FFFF00")
red_style <- createStyle(fgFill = "#FF0000")
blue_style <- createStyle(fgFill = "#0099FF")

# Apply styles to the respective rows
styles_and_rows <- list(
      list(style = yellow_style, rows = yellow_rows),
      list(style = red_style, rows = red_rows),
      list(style = blue_style, rows = blue_rows)
    )

# Loop through the list of styles and rows
for (style_row_pair in styles_and_rows) {
      style <- style_row_pair$style
      rows <- style_row_pair$rows

# Check if rows are not empty, Apply style to each row
if (length(rows) > 0) {
        for (row in rows) {
          addStyle(wb, sheet = "Sheet1", style = style, rows = row + 1, cols = 1:ncol(df1))
        }
      }
    }

# Write the dataframe with applied styles to an Excel file
saveWorkbook(wb, "formatted_data.xlsx")

df1的示例数据：

structure(list(Sample_ID = c("3330_534-20210403 RE277.4", "3330_534-20210403 RE278.2 1 of 15", 
"3330_534-20210403 RE278.2 2 of 15", "3330_534-20210403 RE278.2 3 of 15", 
"3330_534-20210403 RE278.2 4 of 15"), Sample_Project_Code = c("3330_", 
"3330_", "3330_", "3330_", "3330_"), Sample_Original_ID = c("534-20210403", 
"534-20210403", "534-20210403", "534-20210403", "534-20210403"
), Sample_Part = c(NA, "1 of 15", "2 of 15", "3 of 15", "4 of 15"
), Original_Batch_Code = c("RE277.4", "RE278.2", "RE278.2", "RE278.2", 
"RE278.2"), Subproject = c("RE277.4", "RE278.2", "RE278.2", "RE278.2", 
"RE278.2"), LastAction = c(NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), Date_Of_Import = structure(c(18720, 18720, 18720, 
18720, 18720), class = "Date"), ItemType = c("A", "B", "B", "B", 
"B"), Sample_Container_Type = c("card", "2ml tube ", "2ml tube ", 
"2ml tube ", "2ml tube "), Sample_Thaws = c(0, 0, 0, 0, 0), Age = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_), Subject_Sex = c("M", 
"M", "M", "M", "M"), named_person = c("Kay", "Kay", "Kay", "Kay", 
"Kay"), Researcher = c("Bee", "Bee", "Bee", "Bee", "Bee"), Technician = c("Jay", 
"Jay", "Jay", "Jay", "Jay"), Identifier = c("ACR", "ACR", "ACR", 
"ACR", "ACR"), PPL_Sender = c("Bee", "Bee", "Bee", "Bee", "Bee"
), Extraction_Date = structure(c(18720, 18720, 18720, 18720, 
18720), class = "Date"), Sample_PLPI = c(340, 65, 65, 65, 65), 
    Date_Sent = structure(c(18720, 18720, 18720, 18720, 18720
    ), class = "Date"), Date_Received = structure(c(18720, 18720, 
    18720, 18720, 18720), class = "Date"), D = c(NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_), Ethics_Code = c("33/333/3", 
    "33/333/4", "33/333/5", "33/333/6", "33/333/7"), Sample_Volume = c(NA, 
    500, 500, 500, 500), Method = c(NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_), Comments = c(NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_), Row = c(20, 34, 34, 35, 35), Col = c("A", 
    "K", "L", "A", "B"), Level5Name = c("Book 18", "Shelf 14", 
    "Shelf 14", "Shelf 14", "Shelf 14"), Level4Name = c("Compartment B", 
    "Compartment E", "Compartment E", "Compartment E", "Compartment E"
    ), Level3Name = c("Freezer 8", "Freezer 7", "Freezer 7", 
    "Freezer 7", "Freezer 7"), Level2Name = c("Ground Floor", 
    "Ground Floor", "Ground Floor", "Ground Floor", "Ground Floor"
    ), Level1Name = c("AH", "AH", "AH", "AH", "AH")), row.names = c(NA, 
5L), class = "data.frame")

一、修正导出带格式Excel的代码 #

你的代码存在两个核心问题：蓝色行的条件逻辑缺失，以及样式应用效率低下。以下是修正后的完整代码：

library(openxlsx)

# 读取并处理数据（假设已完成数据处理得到df1）
df1 <- read.xlsx("1946_P2_master.xlsx")

# 创建工作簿
wb <- createWorkbook()

# 定义行筛选条件
yellow_rows <- which(df1$Subproject == "NA1")
red_rows <- which(grepl("^SE\\d+", df1$Subproject))
# 完善蓝色行条件：Sample_Thaws≠0 且 Subproject以RE开头
blue_rows <- which(df1$Sample_Thaws != 0 & grepl("^RE", df1$Subproject))

# 添加工作表并写入数据
addWorksheet(wb, "Sheet1")
writeData(wb, "Sheet1", df1)

# 创建样式
yellow_style <- createStyle(fgFill = "#FFFF00")
red_style <- createStyle(fgFill = "#FF0000")
blue_style <- createStyle(fgFill = "#0099FF")

# 批量应用样式（Excel行号从1开始，数据行从第2行开始，需+1）
if(length(yellow_rows) > 0){
  addStyle(wb, sheet = "Sheet1", style = yellow_style, rows = yellow_rows + 1, cols = 1:ncol(df1), gridExpand = TRUE)
}
if(length(red_rows) > 0){
  addStyle(wb, sheet = "Sheet1", style = red_style, rows = red_rows + 1, cols = 1:ncol(df1), gridExpand = TRUE)
}
if(length(blue_rows) > 0){
  addStyle(wb, sheet = "Sheet1", style = blue_style, rows = blue_rows + 1, cols = 1:ncol(df1), gridExpand = TRUE)
}

# 保存工作簿（overwrite参数避免文件已存在导致失败）
saveWorkbook(wb, "formatted_data.xlsx", overwrite = TRUE)

关键修正点 #

1. 补充了蓝色行的完整条件：同时满足Sample_Thaws != 0和Subproject以RE开头；
2. 使用gridExpand = TRUE参数，一次性将样式应用到整行所有列，无需循环每行；
3. 添加overwrite = TRUE，避免因目标文件已存在导致保存失败；
4. 确保行号映射正确：writeData默认将表头放在第1行，数据从第2行开始，所以which返回的索引（从1开始）需要加1对应Excel行号。

二、读取Excel行颜色并添加标注列 #

可以通过openxlsx的样式读取函数提取行填充颜色，生成标注列用于后续还原格式：

library(openxlsx)

# 读取目标工作簿
wb_read <- loadWorkbook("1946_P2_master.xlsx")
# 获取工作表的样式集合
sheet_styles <- getStyles(wb_read, sheet = 1)
# 获取每行对应的样式索引（第1行是表头）
row_style_indices <- getRowStyles(wb_read, sheet = 1)

# 定义颜色与标签的映射（根据实际Excel中的颜色码调整）
color_map <- list(
  "#FFFF00" = "yellow",
  "#FF0000" = "red",
  "#0099FF" = "blue"
)

# 提取每行的填充颜色标签
row_colors <- sapply(row_style_indices[-1], function(idx){
  if(idx == 0) return(NA)  # 0表示无自定义样式
  style_fg <- sheet_styles[[idx]]$fgFill
  color_map[[style_fg]]
})

# 将标签列添加到df1
df1$Row_Color_Label <- row_colors

说明 #

• getRowStyles返回的索引为0时，表示该行无自定义样式；
• 需要根据原始Excel中实际使用的填充颜色码调整color_map；
• 生成的Row_Color_Label列可直接用于Excel条件格式：比如设置条件为Row_Color_Label等于yellow时，整行填充黄色。

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