多Excel文件指定单元格提取与文件名关联问题求助

核心思路 #

在遍历每个Excel文件时，同步提取文件名中的日期、F4单元格内容和J9:Q10区域数据，将三者合并为单条记录，最后统一合并成完整数据框。以下是基于openxlsx包的实现代码（该包读取Excel效率更高，若你使用xlsx包，文末附适配代码）：

library(openxlsx)
library(stringr)

# 重命名文件名列表，避免与内置函数冲突
file_list <- c(list2022, list2020, list2021, list2019)

# 遍历每个文件，提取并整合数据
lst1 <- lapply(file_list, function(x) {
  # 从文件名中提取日期（匹配yyyy-mm-dd格式）
  report_date <- str_extract(x, "\\d{4}-\\d{2}-\\d{2}")
  
  # 读取F4单元格内容（第4行第6列）
  f4_value <- read.xlsx(x, sheet = "1", rows = 4, cols = 6, colNames = FALSE)[1,1]
  
  # 读取J9:Q10区域：第9行作为表头，第10行作为数据行
  table_data <- read.xlsx(x, sheet = "1", rows = 9:10, cols = 11:17, colNames = TRUE)
  
  # 合并所有信息为单行数据框
  data.frame(
    report_date = report_date,
    f4_column = f4_value,
    table_data,
    stringsAsFactors = FALSE
  )
})

# 合并所有文件的数据为最终数据框
perc <- do.call(rbind, lst1)

关键细节说明 #

1. 日期提取：使用stringr::str_extract匹配文件名中的yyyy-mm-dd格式日期，若文件名格式不同，可调整正则表达式适配。
2. F4单元格读取：指定rows=4, cols=6精准定位单元格，colNames=FALSE避免将单元格内容误识别为列名。
3. J9:Q10区域读取：通过rows=9:10, cols=11:17定位目标区域，colNames=TRUE将第9行设为列名，最终仅保留第10行的数值数据。

适配xlsx包的代码 #

若你使用的是xlsx包，代码调整如下：

library(xlsx)
library(stringr)

file_list <- c(list2022, list2020, list2021, list2019)

lst1 <- lapply(file_list, function(x) {
  report_date <- str_extract(x, "\\d{4}-\\d{2}-\\d{2}")
  
  # xlsx包读取单个单元格
  f4_value <- read.xlsx(x, sheetIndex = 1, startRow = 4, endRow = 4, colIndex = 6, header = FALSE)[1,1]
  
  # 读取J9:Q10区域，header=TRUE指定第9行为表头
  table_data <- read.xlsx(x, sheetIndex = 1, startRow = 9, endRow = 10, colIndex = 11:17, header = TRUE)
  # 仅保留第10行的数值数据
  table_data <- table_data[2, , drop = FALSE]
  
  data.frame(
    report_date = report_date,
    f4_column = f4_value,
    table_data,
    stringsAsFactors = FALSE
  )
})

perc <- do.call(rbind, lst1)

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