如何用R提取文件夹中所有Excel文件的工作簿名和工作表名并生成数据集

需要生成包含指定文件夹内所有Excel文件的工作簿名和工作表名的数据集，目标格式如下：

workbook    sheetname
a           aphid1
a           aphid2
b           melon1
b           melon2
b           melon3
b           melon4

现有代码仅能获取工作表名的列表结构，且无法显示实际工作簿名（仅显示[[1]]、[[2]]这类索引）：

all_files <- list.files(path="filepath", pattern = "*.xlsx", full.names = T)

sheet_names <- lapply(all_files, excel_sheets)

# 输出示例
print(sheet_names)
[[1]]
[1] "About"

[[2]]
 [1] "About"                          "General"   
 [3] "Data"

期望输出格式：

workbook    sheetname
a           About
b           About
b           General
b           Data

可以通过以下两种方式（base R 或 tidyverse 风格）实现需求：

方式一：Base R 实现 #

library(readxl)

# 获取所有Excel文件路径
all_files <- list.files(path = "filepath", pattern = "\\.xlsx$", full.names = TRUE)

# 提取纯工作簿名（去掉路径和后缀）
workbook_names <- tools::file_path_sans_ext(basename(all_files))

# 将工作簿名与对应工作表名配对，生成数据框
result_df <- do.call(rbind, mapply(function(wb, sheets) {
  data.frame(workbook = wb, sheetname = sheets, stringsAsFactors = FALSE)
}, workbook_names, sheet_names, SIMPLIFY = FALSE))

# 查看结果
print(result_df)

方式二：Tidyverse 风格实现（更简洁） #

library(readxl)
library(tidyverse)

all_files <- list.files(path = "filepath", pattern = "\\.xlsx$", full.names = TRUE)

result_df <- tibble(
  workbook = tools::file_path_sans_ext(basename(all_files)),
  sheetname = lapply(all_files, excel_sheets)
) %>%
  unnest(sheetname)

# 查看结果
print(result_df)

代码说明： #

1. 提取工作簿名：用basename()去掉文件路径，再用tools::file_path_sans_ext()去掉.xlsx后缀，得到纯净的工作簿名（如a、b）。
2. 配对并转换格式：
   • Base R 中用mapply逐个将工作簿名与对应的工作表名列表绑定成小数据框，再用do.call(rbind, ...)合并成完整数据集。
   • Tidyverse 中先创建包含工作簿名和工作表名列表的 tibble，再用unnest()将列表展开成多行，自动对应工作簿名。

输出示例： #

workbook sheetname
1        a      About
2        b      About
3        b    General
4        b       Data

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