Jeromy Anglim Jeromy Anglim - 14 days ago 16
R Question

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

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)


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?

Answer

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.