Import multiple excel sheets using openxlsx

Question

I am trying to import a large xlsx file into R that has many sheets of data. I was attempting to do this through XLConnect, but java memory problems (such as those described in this thread have prevented this technique from being successful.)

Instead, I am trying to use the openxlsx package, which I have read works much faster and avoids Java altogether. But is there a way to use its read.xlsx function within a loop to read in all of the sheets into separate dataframes? The technique I was using with the other package is no longer valid bc commands like loadWorkbook() and getSheets() can no longer be used.

Thank you for your help.

Answer 1

I think the getSheetNames() function is the right function to use. It will give you a vector of the worksheet names in a file. Then you can loop over this list to read in a list of data.frames.

read_all_sheets = function(xlsxFile, ...) {
  sheet_names = openxlsx::getSheetNames(xlsxFile)
  sheet_list = as.list(rep(NA, length(sheet_names)))
  names(sheet_list) = sheet_names
  for (sn in sheet_names) {
    sheet_list[[sn]] = openxlsx::read.xlsx(xlsxFile, sheet=sn, ...)
  }
  return(sheet_list)
}

read_all_sheets(myxlsxFile)