-1

I need to work with data which is stored in Excel workbooks of sometimes up to 50 sheets each, and would like to read them in all at once (or read, say, sheets 3:47 in at once). I've read in other discussions that XLConnect can do this. However, XLConnect won't load on my computer because rJava won't load. The most likely reason for this is (I think, after having researched the issue a bit and found it's quite common) that my version of Java is 32 bit, and my version of R (3.3.3) is 64 bit. However, I can't install a different version of Java on my work computer myself, and getting it done through our IT systems will take a while.

Therefore, I was wondering if anyone knows of a way to read in multiple Excel sheets at once using openxlsx (or any package that doesn't require rJava)? I've made a few attempts at writing a loop but that didn't work, when it didn't throw out an error it only read in one of the sheets.

EDIT: I've also found a method using a package called xlsReadWrite, but that package is not available for newer versions of R and has in fact been removed from the CRAN repository.

Pia
  • 77
  • 7

3 Answers3

0

I can't post a comment as I don't have enough rep but the readxl package is actively maintained and does not require java or Perl, you can read about it here

Michael Bird
  • 771
  • 8
  • 21
  • Great, I'll try that! Thank you :) – Pia Jul 21 '17 at 10:27
  • I've tried `read_excel("path", range=3:47)` but got an error: `Error in UseMethod("as.cell_limits") : no applicable method for 'as.cell_limits' applied to an object of class "c('integer', 'numeric')"` I don't know what to make of that? – Pia Jul 21 '17 at 10:31
  • By looking at the documentation `?read_excel` , I think you want the `sheet` parameter for specifying which sheet to read, not `range` – Michael Bird Jul 21 '17 at 10:37
  • That doesn't work unfortunately... "sheet must have length 1". I've played around with "range" a bit more and it seems it's supposed to be used to select a range of cells within a sheet, but not several sheets at once. For example, `read_excel("path", range="sheet1!A1:F5")` works but `range="sheet1A1:sheet10:F5"` doesn't work. – Pia Jul 21 '17 at 10:50
  • If `read_excel()` can only read one sheet at a time, i'd suggest to start thinking about either using a `for` loop or look at the `apply` function family. There are a lot of helpful tutorials on the web for these though. – Michael Bird Jul 21 '17 at 10:59
  • Okay, after searching for readxl functions I've found an answer here: https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames that worked. Thank you! – Pia Jul 21 '17 at 11:00
0

you can try this code xlsReadWrite with lapply function

library(xlsReadWrite)
sheets <- c("Sheet 1","Sheet 2", "Sheet 3")
sheet_list <- lapply(sheets, function(x) 
                   read.xls("filename.xls",sheets=x))
PritamJ
  • 337
  • 4
  • 10
0

Here is a solution using readxl.

library(readxl)
inputWB <- "C:/This/Is/A/Path/To/Your/Excel/Workbook.xlsx"
sheetsToRead <- 3:47
listOfSheets <- lapply(sheetsToRead, function(x,inputWB) read_excel(inputWB, sheet=x), inputWB)
Fish11
  • 453
  • 3
  • 12