如何使用R筛选并保留Excel文件中的带填充色单元格并生成数据框

依赖包准备 #

首先安装所需的工具包：

install.packages(c("tidyxl", "dplyr", "tidyr"))

加载包：

library(tidyxl)
library(dplyr)
library(tidyr)

完整实现代码 #

# 替换为你本地存储的Excel文件路径
excel_path <- "SpeciesByYear_Colored.xlsx"

# 读取Excel内所有单元格的内容、位置、格式关联信息
cell_data <- xlsx_cells(excel_path)
# 读取Excel内所有单元格格式规则
format_rules <- xlsx_formats(excel_path)

# 筛选出带有填充色的数值单元格
filled_cells <- cell_data %>%
  # 关联单元格对应的填充色信息，判断是否存在非默认白色填充
  mutate(has_fill_color = format_rules$local$fill$patternFgColor$rgb[local_format_id] != "FFFFFFFF") %>%
  filter(has_fill_color, !is.na(numeric))

# 关联表头信息，整理为要求的长表结构
result_df <- filled_cells %>%
  # 匹配每行对应的物种名称（第一列为物种行表头）
  left_join(
    cell_data %>% filter(col == 1, !is.na(character)) %>% select(row, Species = character),
    by = "row"
  ) %>%
  # 匹配每列对应的年份（第一行为年份列表头）
  left_join(
    cell_data %>% filter(row == 1, !is.na(numeric)) %>% select(col, Year = as.character(numeric)),
    by = "col"
  ) %>%
  # 保留目标列并排序
  select(Species, Year, value = numeric) %>%
  arrange(Species, Year)

# 查看输出结果
head(result_df)

适配说明 #

如果你的Excel表格表头位置和示例不一致，只需调整关联行表头、列表头的过滤条件即可适配。

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