Store contents of workbook to separate data.frames in a list

Question

I am trying to read in an Excel workbook with an unknown number of sheets, and store each sheet as part of a variable (result[1] gives sheet 1, result[2] gives sheet 2, etc). I started trying to find a way to do it using the XLConnect package (which I could get to work correctly on Linux). I stopped when I realized I had broken almost every R convention there is.... Anyone have a better solution, using the XLConnect package?

require(XLConnect)

demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect")

endloop<<-F
x<<-1
result<<-NULL
while(!endloop){
  result[x] <<- tryCatch({
    readWorksheetFromFile(demoExcelFile,sheet=x)
    x<<-x+1
  }, error = function(e) {
    endloop<<-T
  })
}

Note: I'm open to using other packages, I just haven't been able to find another one that works reliably on 64 bit Linux Mint

Answer 1

Use the readxl package which has a function to list sheet names.

library(readxl)
library(purrr)

# get the sheet names
sheetnames  <- excel_sheets("path/to/myfile.xlsx")

# loop through them and read each sheet into an item in a list.
# alternatively, use lapply() instead of map()
listofsheets <- map(sheetnames, ~ read_excel("path/to/myfile.xlsx", sheet = .x))

Answer 2

I would recommend using readxl from tidyverse. You could write something like:

library(readxl)
sheets <- excel_sheets("insert_filepath/workbook.xlsx")

data <- list()
for (i in 1:length(sheets)) {
  data[[i]] <- read_excel("insert_filepath/workbook.xlsx", sheet = sheets[i])
}

Because I don't have your Excel file, I can't reproduce your data exactly. But this should be a general solution that finds all the sheet names in your Excel file and then loops through each sheet and reads them into a list called 'data'

Answer 3

require(XLConnect)

# Load workbook
wb <- loadWorkbook(system.file("demoFiles/multiregion.xlsx", package = "XLConnect"))

# Read all worksheets into a list of data.frames
listOfDfs <- readWorksheet(wb, sheet = getSheets(wb))