Read all worksheets in an Excel workbook into an R list with data.frames

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')

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.

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)