Read all worksheets in an Excel workbook into an R

Question

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 1

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)

Answer 2

You can load the work book and then use lapply, getSheets and readWorksheet and do something like this.

wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", 
                          package = "XLConnect"))
sheet_names <- getSheets(wb.mtcars)
names(sheet_names) <- sheet_names

sheet_list <- lapply(sheet_names, function(.sheet){
    readWorksheet(object=wb.mtcars, .sheet)})

Answer 3

To read multiple sheets from a workbook, use readxl package as follows:

library(readxl)
library(dplyr)

final_dataFrame <- bind_row(path_to_workbook %>%
                              excel_sheets() %>%
                              set_names() %>%
                              map(read_excel, path = path_to_workbook))

Here, bind_row (dplyr) will put all data rows from all sheets into one data frame, and path_to_workbook is "dir/of/the/data/workbook".

Answer 4

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