Import excel workbook with multiple sheets

Question

I am looking to import an excel workbook into R with multiple sheets. However, I can't seem to quite make this work. The code I have been using is the following:

library(XLConnect)
# Read Excel Sheet
excel <- loadWorkbook("C:/Users/rawlingsd/Downloads/17-18 Prem Stats.xlsx")
# get sheet names
sheet_names <- getSheets(excel)
names(sheet_names) <- sheet_names
# put sheets into a list of data frames
sheet_list <- lapply(sheet_names, function(.sheet){readWorksheet(object=excel, .sheet)})
# limit sheet_list to sheets with at least 1 dimension 
# sheet_list2 <- sheet_list[sapply(sheet_list, function(x) dim(x)[1]) > 0]
# code to read in each excel worksheet as individual dataframes
for (i in 1:length(sheet_list)){assign(paste0("2018df", i), as.data.frame(sheet_list[i]))}
# define function to clean data in each data frame (updated based on your data)

If anyone could help me with my code or share a code that works for them, it would be greatly appreciated

You don't describe the way in which this doesn't work. Can you add any error messages you get? What output do you expect and how is it different to what you are currently getting? — r.bot, Mar 19 '18 at 09:49

score 10 · Accepted Answer · answered Mar 19 '18 at 09:58

You can use readxl package. See the following example.

library(readxl)
path <- readxl_example("datasets.xls")
sheetnames <- excel_sheets(path)
mylist <- lapply(excel_sheets(path), read_excel, path = path)

# name the dataframes
names(mylist) <- sheetnames

The spreadsheet will be captured in a list with the sheetname as the name of the dataframe in the list.

If you want to bring the dataframes out of the list use the next bit of code.

# Bring the dataframes to the global environment
list2env(mylist ,.GlobalEnv)

Thank you very much! That is perfect – Daniel Rawlings Mar 19 '18 at 10:27 — Daniel Rawlings, Mar 19 '18 at 10:27

score 8 · Answer 2 · answered Mar 19 '18 at 10:40

Please look into openxlsx package, which allows you to do loads of stuff with excel workbooks. Here is a code script to read all the sheets from a given workbook.

library(openxlsx)
a <- loadWorkbook('~/filename.xlsx')
sheetNames <- sheets(a)
for(i in 1:length(sheetNames))
{
  assign(sheetNames[i],readWorkbook(a,sheet = i))
}

You can verify the data is loaded in R and can view in your workSpace.

Thanks.

score 3 · Answer 3 · answered Mar 19 '18 at 09:55

What I use:

full_excel_read<-function(fpath,v=TRUE){



 sheetnames <- excel_sheets(fpath)
  workbook <- sapply(sheetnames,function (x){readxl::read_excel(fpath,sheet = x)})
  for (sh in sheetnames) {
    workbook[[sh]]<-as.data.table(workbook[[sh]])
  }
  if (v){
    lapply(sheetnames, function(x){View(workbook[[x]],x)})
  }


  workbook
}

score 2 · Answer 4 · answered Mar 20 '18 at 07:44

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

require(XLConnect)
wb <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect"))
lst <- readWorksheet(wb, sheet = getSheets(wb))

lst is a named list whose names correspond to the sheet names. Note that readWorksheet is vectorized and therefore you can read multiple worksheets with a single readWorksheet call.

Import excel workbook with multiple sheets

4 Answers4