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