如何用R遍历多Sheet的XLSX文件并获取单元格样式信息？

针对你处理多Sheet Excel文件、按字体颜色移除手动插入均值的需求，以下是两种可行的R包实现方案：

方法一：用tidyxl遍历所有Sheet并识别样式 #

tidyxl支持指定Sheet读取格式信息，之前你可能没指定sheet参数导致只获取了第一个Sheet的样式。下面是完整的遍历处理代码：

library(tidyxl)
library(dplyr)
library(purrr)
library(writexl)

# 替换为你的文件路径
file_path <- "your_target_file.xlsx"

# 获取所有Sheet名称
sheet_names <- excel_sheets(file_path)

# 定义单个Sheet的处理函数
process_single_sheet <- function(sheet_name) {
  # 读取当前Sheet的单元格数据和对应格式
  cell_data <- xlsx_cells(file_path, sheet = sheet_name)
  format_data <- xlsx_formats(file_path, sheet = sheet_name)
  
  # 提取所有格式对应的字体RGB颜色（tidyxl返回的RGB无#，为大写）
  font_rgb_list <- format_data$local$font$color$rgb
  
  # 筛选掉字体颜色为指定值的单元格（这里假设手动均值是红色，对应RGB为FF0000，需根据实际调整）
  cleaned_cells <- cell_data %>%
    mutate(is_manual_mean = map_lgl(local_format_id, ~ font_rgb_list[.x] == "FF0000")) %>%
    filter(!is_manual_mean)
  
  # 转换为结构化数据框（根据你的数据结构调整行/列映射逻辑）
  cleaned_df <- cleaned_cells %>%
    select(row, col, numeric, character) %>%
    pivot_wider(
      names_from = col,
      values_from = c(numeric, character),
      names_glue = "col_{col}_{.value}"
    )
  
  return(cleaned_df)
}

# 批量处理所有Sheet
cleaned_sheets <- map(sheet_names, process_single_sheet)
names(cleaned_sheets) <- sheet_names

# 保存清理后的文件
write_xlsx(cleaned_sheets, "cleaned_result.xlsx")

关键说明 #

• 先通过xlsx_formats指定Sheet名称，就能获取对应Sheet的样式信息
• 需提前确认手动均值对应的字体RGB值：可以先读取一个包含目标值的Sheet，打印font_rgb_list查看对应颜色代码

方法二：用openxlsx遍历Sheet处理样式 #

openxlsx加载工作簿后，可直接访问每个Sheet的单元格样式信息，适合结构化较强的表格：

library(openxlsx)

file_path <- "your_target_file.xlsx"
wb <- loadWorkbook(file_path)
sheet_names <- sheets(wb)

cleaned_data <- list()

# 遍历每个Sheet
for (sheet in sheet_names) {
  # 读取当前Sheet的原始数据
  raw_df <- read.xlsx(wb, sheet = sheet)
  
  # 获取当前Sheet的样式对象
  target_ws <- wb$worksheets[[which(sheet_names == sheet)]]
  all_styles <- target_ws$cellStyles
  
  # 遍历每个单元格检查字体颜色
  for (row_idx in 1:nrow(raw_df)) {
    for (col_idx in 1:ncol(raw_df)) {
      # 获取单元格的样式ID
      style_id <- target_ws$cells[[row_idx]][[col_idx]]$style
      if (!is.null(style_id)) {
        # 获取对应样式的字体颜色
        font_color <- all_styles[[style_id]]$font$colour
        # 匹配目标颜色则替换为NA（或直接删除行，根据需求调整）
        if (font_color == "#FF0000") {
          raw_df[row_idx, col_idx] <- NA
        }
      }
    }
  }
  
  cleaned_data[[sheet]] <- raw_df
}

# 保存结果
write.xlsx(cleaned_data, "openxlsx_cleaned_result.xlsx")

注意事项 #

• 处理400+Sheet时，建议在循环中加入print(sheet)查看进度，避免程序假死
• 如果表格有固定表头，可跳过表头行的检查，提升处理速度

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