如何用R批量处理带合并单元格双表头的.xlsx文件

刚好我处理过不少这类带合并单元格多表头的Excel数据转换需求，用R的tidyverse生态就能完美实现全自动化，完全不用手动调整列对应关系，适配你说的“不同文件列数不固定”的场景。下面是一套完整的可落地方案：

1. 先搞定依赖包 #

首先得安装并加载两个核心包：

• readxl：专门用来读取Excel，能自动识别合并单元格的填充信息，不用咱们手动处理合并单元格的范围
• tidyverse：包含dplyr（数据清洗）和tidyr（宽转长），是处理这类结构化转换的主力工具

安装加载代码直接复制运行就行：

install.packages(c("readxl", "tidyverse"))
library(readxl)
library(tidyverse)

2. 读取Excel并解析双表头 #

因为第一行是合并单元格的大类（CATA/CATB），readxl读取时会自动把合并单元格的内容填充到对应列的第一行，这正好帮我们省了手动标记列归属的步骤。咱们分两步读：先读表头，再读数据：

# 先拿单个文件做示例，后面再扩展到批量处理
file_path <- "你的目标文件路径.xlsx"

# 读取前两行的表头信息
header_raw <- read_excel(file_path, n_max = 2)

# 提取第一行的大类标签和第二行的子列标签
cat_labels <- as.character(header_raw[1, ])
sub_col_labels <- as.character(header_raw[2, ])

# 处理rowid列：它不属于任何Cat，所以把对应的大类标签设为NA
rowid_pos <- which(sub_col_labels == "rowid")
cat_labels[rowid_pos] <- NA

# 读取实际数据（从第三行开始，用第二行的子列名当列名）
data_raw <- read_excel(file_path, skip = 2, col_names = sub_col_labels)

3. 构建子列与大类的映射关系 #

现在需要把每个子列（A1/B1等）对应到它所属的大类，这里用fill函数就能自动填充合并单元格对应的所有子列：

# 生成表头映射表，自动填充每个子列对应的大类
header_map <- tibble(Cat = cat_labels, Col = sub_col_labels) %>%
  fill(Cat, .direction = "right") %>%  # 向右填充，把左边的大类对应到所有同组子列
  filter(!is.na(Col))  # 过滤掉可能存在的空列

4. 把宽格式转成目标长格式 #

用pivot_longer把宽表转成长表，再关联刚才的映射表，就能得到你要的结构化格式：

# 重塑数据并整理成目标格式
final_data <- data_raw %>%
  # 把除了rowid的所有列转成Col和val两列
  pivot_longer(cols = -rowid, names_to = "Col", values_to = "val") %>%
  # 关联大类信息
  left_join(header_map, by = "Col") %>%
  # 调整列顺序和列名，匹配你要的格式
  select(Rowid = rowid, Cat, Col, val) %>%
  # 按Rowid和Cat排序，让结果更整齐
  arrange(Rowid, Cat, Col)

5. 批量处理上百个Excel文件 #

如果要处理文件夹里的所有xlsx文件，用purrr的map_dfr就能批量读取并合并所有结果，一行代码搞定批量处理：

# 获取目标文件夹下所有xlsx文件的完整路径
file_list <- list.files(path = "你的文件夹路径", pattern = "\\.xlsx$", full.names = TRUE)

# 批量处理每个文件，自动合并成一个大的数据框
all_processed_data <- map_dfr(file_list, function(current_file) {
  # 重复单个文件的处理逻辑
  header_raw <- read_excel(current_file, n_max = 2)
  cat_labels <- as.character(header_raw[1, ])
  sub_col_labels <- as.character(header_raw[2, ])
  
  rowid_pos <- which(sub_col_labels == "rowid")
  cat_labels[rowid_pos] <- NA
  
  data_raw <- read_excel(current_file, skip = 2, col_names = sub_col_labels)
  
  header_map <- tibble(Cat = cat_labels, Col = sub_col_labels) %>%
    fill(Cat, .direction = "right") %>%
    filter(!is.na(Col))
  
  data_raw %>%
    pivot_longer(cols = -rowid, names_to = "Col", values_to = "val") %>%
    left_join(header_map, by = "Col") %>%
    select(Rowid = rowid, Cat, Col, val) %>%
    arrange(Rowid, Cat, Col)
})

几个实用的小提示 #

• 如果你的rowid列名不是严格的"rowid"，可以改成模糊匹配，比如rowid_pos <- which(str_detect(sub_col_labels, "rowid"))，适配不同的命名
• 如果Excel里有空列，filter(!is.na(Col))会自动过滤掉，不会影响最终结果
• fill函数的方向是"right"，刚好对应合并单元格从左到右的覆盖范围，要是你的合并单元格是其他方向，调整.direction参数就行

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