在R中将多个表格导入多个数据框架

I have an Excel file with a lot of sheets and I need a code to import each sheet in a separate data frame which will be named in the same convention as the sheet name in Excel.

我有一个包含大量工作表的Excel文件,我需要一个代码来在单独的数据框中导入每个工作表,该数据框的命名方式与Excel中的工作表名称相同。

Example, tabs A, B, C will be imported as data frame A, B, and C respectively.

例如,选项卡A,B,C将分别作为数据框A,B和C导入。

From other threads, I saw codes like: length(excel_sheets(filename)) to get the number of sheets in the file

从其他线程,我看到代码如:length(excel_sheets(filename))来获取文件中的工作表数

Then create a list that would contain each tab:

然后创建一个包含每个选项卡的列表:

read_excel_allsheets <- function(filename) {
  sheets <- readxl::excel_sheets(filename)
  x <-    lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  names(x) <- sheets
  x
}

But I do not know how the tabs gets imported into R from there.

但我不知道标签是如何从那里导入R的。

Would greatly appreciate the help. Thanks in advance!

非常感谢帮助。提前致谢!

3 个解决方案

#1

Here's one way to do it:

这是一种方法:

# write test data
tf <- writexl::write_xlsx(
  list("the mtcars" = mtcars, "iris data" = iris), 
  tempfile(fileext = ".xlsx")
)

# read excel sheets
sheets <- readxl::excel_sheets(tf)
lst <- lapply(sheets, function(sheet) 
  readxl::read_excel(tf, sheet = sheet)
)
names(lst) <- sheets

# shove them into global environment
list2env(lst, envir = .GlobalEnv)

#2

Your function reads in all the tabs and saves them as elements of a single list (because of lapply()). You can take the elements out of the list with list2env:

您的函数读入所有选项卡并将它们保存为单个列表的元素(因为lapply())。您可以使用list2env从列表中取出元素:

your_excel_list <- read_excel_allsheets("test.xlsx")
list2env(your_excel_list, .GlobalEnv)

You'll see that the named elements of your list are now data frames (or actually tbl_df) in your global environment

您将看到列表中的命名元素现在是全局环境中的数据框(或实际上是tbl_df)

#3

could read in one line. should load magrittr and dplyr packages.

可以读一行。应该加载magrittr和dplyr包。

data <- lapply(list.files(pattern = "*.xlsx"),function(x) x=read_excel(x,sheet = "(sheetname)")) %>%  bind_rows

#1