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))