Read all worksheets in an Excel workbook into an R list with data.frames
Solution 1
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.
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")
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)})
}
Thus, it could be called with:
importWorksheets('test.xls')
Solution 2
Note that most of XLConnect's functions are already vectorized. This means that you can read in all worksheets with one function call without having to do explicit vectorization:
require(XLConnect)
wb <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect"))
lst = readWorksheet(wb, sheet = getSheets(wb))
With XLConnect 0.2-0 lst will already be a named list.
Solution 3
I stumbled across this old question and I think the easiest approach is still missing.
You can use rio
to import all excel sheets with just one line of code.
library(rio)
data_list <- import_list("test.xls")
If you're a fan of the tidyverse
, you can easily import them as tibbles by adding the setclass
argument to the function call.
data_list <- import_list("test.xls", setclass = "tbl")
Suppose they have the same format, you could easily row bind them by setting the rbind
argument to TRUE
.
data_list <- import_list("test.xls", setclass = "tbl", rbind = TRUE)
Solution 4
From official readxl
(tidyverse) documentation (changing first line):
path <- "data/datasets.xlsx"
path %>%
excel_sheets() %>%
set_names() %>%
map(read_excel, path = path)
Solution 5
Since this is the number one hit to the question: Read multi sheet excel to list:
here is the openxlsx
solution:
filename <-"myFilePath"
sheets <- openxlsx::getSheetNames(filename)
SheetList <- lapply(sheets,openxlsx::read.xlsx,xlsxFile=filename)
names(SheetList) <- sheets
Jeromy Anglim
I am a Senior Lecturer in the School of Psychology at Deakin University bridging I/O psychology and statistics. I'm quite active on the Cognitive Sciences and Statistics Stack Exchanges. You can find me also on: Twitter: @JeromyAnglim My blog on psychology and statistics: http://jeromyanglim.blogspot.com
Updated on October 29, 2021Comments
-
Jeromy Anglim over 2 years
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 calledtest.xls
into R.library(XLConnect) readWorksheetFromFile('test.xls', sheet = 1)
I have an Excel Workbook with multiple worksheets.
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?