R语言批量读取非结构化xlsx 按区块拆分生成规整数据框

核心思路 #

不用读取Excel单元格加粗样式（该方法效率极低且容易因格式兼容报错），基于表单固定值特征定位区块位置，先写单文件解析逻辑，再批量套用到所有文件，248个文件数秒即可处理完成。

第一步：优化现有文件读取逻辑 #

原有的循环读取存在路径错位、误读非xlsx文件的风险，替换为以下更稳妥的读取方式：

library(tidyverse)
library(readxl)

# 自动识别当前目录下所有xlsx文件，获取全路径避免读取错误
file_paths <- list.files(pattern = "\\.xlsx$", full.names = TRUE)
# 批量读入所有文件存入列表
excel_list <- map(file_paths, ~read_excel(.x, col_names = FALSE))

第二步：编写单文件解析函数 #

该函数自动提取固定元信息、识别所有分块、规整为标准数据框：

parse_bid_form <- function(raw_df) {
  # 提取前5行固定元信息
  meta_df <- tibble(
    field_name = raw_df[[1]][1:5],
    val = map2_chr(raw_df[[2]][1:5], raw_df[[3]][1:5], ~na.omit(c(.x,.y))[1])
  ) |> 
    pivot_wider(names_from = field_name, values_from = val)
  # 单独留存需要的作业名称字段
  current_job_name <- meta_df$Name

  # 定位所有明细区块起始行：判定规则为第1列非空、同行第5列为"Quantity"，和样例特征完全匹配
  block_start <- which(!is.na(raw_df[[1]]) & raw_df[[5]] == "Quantity")
  # 计算每个区块的结束行：下一个区块前空行位置，最后一个区块取到表尾
  block_end <- c(tail(block_start, -1) - 2, nrow(raw_df))
  block_titles <- raw_df[[1]][block_start]

  # 逐个提取区块并规整
  block_output <- map2(block_start, block_end, function(s, e) {
    # 提取区块明细行（起始行下一行到结束行），取需要的4列
    part_df <- raw_df[(s+1):e, c(1,5,6,7)]
    # 删除全空行
    part_df <- part_df |> filter(!if_all(everything(), is.na))
    # 重命名列
    colnames(part_df) <- c(raw_df[[1]][s], "Quantity", "Price", "Totals")
    # 数值列转类型
    part_df <- part_df |> mutate(across(c(Quantity, Price, Totals), as.numeric))
    return(part_df)
  }) |> set_names(block_titles)

  # 返回单文件解析结果
  return(list(
    meta = meta_df,
    job_name = current_job_name,
    detail_blocks = block_output
  ))
}

第三步：批量处理所有文件 #

# 批量解析所有Excel，得到需要的嵌套列表结构
all_result <- map(excel_list, parse_bid_form)

输出结构说明 #

• all_result为嵌套列表，每个元素对应1个投标文件
• 每个文件节点下包含3部分：meta存全量元信息、job_name存作业名称、detail_blocks是分块的规整数据框列表，每个数据框第一列为对应品类名称，后三列依次为数量、单价、合计金额，完全匹配需求。

如果后续需要做跨文件汇总，可以直接用以下代码把所有分块合并成一张长表：

all_detail_long <- map_dfr(all_result, function(file_res) {
  map_dfr(file_res$detail_blocks, ~.x |> mutate(job_name = file_res$job_name), .id = "category")
})

效果验证 #

用提供的样例数据x测试，解析出的Trees区块输出如下：

DG区块输出如下：

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