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

Solution 1

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)

Solution 2

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.

Solution 3

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
}

Solution 4

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.

View more solutions