使用 data.frames 将 Excel 工作簿中的所有工作表读入 R 列表 [英] Read all worksheets in an Excel workbook into an R list with data.frames

问题描述

我知道 XLConnect 可用于将 Excel 工作表读入 R.例如，这会将名为 test.xls 的工作簿中的第一个工作表读入 R.

I understand that XLConnect can be used to read an Excel worksheet into R. For example, this would read the first worksheet in a workbook called test.xls into R.

library(XLConnect)
readWorksheetFromFile('test.xls', sheet = 1)

我有一个包含多个工作表的 Excel 工作簿.

I have an Excel Workbook with multiple worksheets.

如何将工作簿中的所有工作表导入到 R 中的列表中，其中列表的每个元素都是给定工作表的 data.frame，并且每个元素的名称对应于 Excel 中的工作表名称?

How can all worksheets in a workbook be imported into a list in R where each element of the list is a data.frame for a given sheet, and where the name of each element corresponds to the name of the worksheet in Excel?

推荐答案

使用 readxl 更新答案(2015 年 6 月 22 日)

自从发布这个问题以来，readxl 包已经发布.它支持 xls 和 xlsx 格式.重要的是，与其他 excel 导入包相比，它适用于 Windows、Mac 和 Linux，无需安装其他软件.

Updated answer using readxl (22nd June 2015)

Since posting this question the readxl package has been released. It supports both xls and xlsx format. Importantly, in contrast to other excel import packages, it works on Windows, Mac, and Linux without requiring installation of additional software.

因此，用于导入 Excel 工作簿中所有工作表的函数是:

So a function for importing all sheets in an Excel workbook would be:

library(readxl)    
read_excel_allsheets <- function(filename, tibble = FALSE) {
    # I prefer straight data.frames
    # but if you like tidyverse tibbles (the default with read_excel)
    # then just pass tibble = TRUE
    sheets <- readxl::excel_sheets(filename)
    x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
    if(!tibble) x <- lapply(x, as.data.frame)
    names(x) <- sheets
    x
}

这可以通过以下方式调用:

This could be called with:

mysheets <- read_excel_allsheets("foo.xls")

旧答案

基于@mnel 提供的答案，这里有一个简单的函数，它将 Excel 文件作为参数，并将每个工作表作为命名列表中的 data.frame 返回.

Old Answer

Building on the answer provided by @mnel, here is a simple function that takes an Excel file as an argument and returns each sheet as a data.frame in a named list.

library(XLConnect)

importWorksheets <- function(filename) {
    # filename: name of Excel file
    workbook <- loadWorkbook(filename)
    sheet_names <- getSheets(workbook)
    names(sheet_names) <- sheet_names
    sheet_list <- lapply(sheet_names, function(.sheet){
        readWorksheet(object=workbook, .sheet)})
}

因此，它可以被调用:

importWorksheets('test.xls')

这篇关于使用 data.frames 将 Excel 工作簿中的所有工作表读入 R 列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！