r - read.xlsx from .xlsx with unknown number of sheets

Question

Suppose I have an excel file, which I would like to read to R with read.xlsx function. File consists of spreadsheets, number of which I do not know (there is like 200 of such files so manually checking number of sheets would be huge pain). Each spreadsheet is organized like a proper data frame. I would like to have those spreadsheets one on top of another. I write something like:

    columnsILike <- c(1,40)
    for(i in 1:numberOfSheets){
        dfInd <- read.xlsx("myfile.xlsx", i, # number of sheet
                           colIndex=columnsILike, endRow=201, startRow=2, 
                           header=F)
        PreviousEmptyDataFrame <- rbind(PreviousEmptyDataFrame, dfInd)
    }
    write.csv(PreviousEmptyDataFrame, "data.csv")

Question is, how do I know number of sheets in advance?

score 4 · Accepted Answer · edited Nov 06 '15 at 09:06

4

getSheets(loadWorkbook("file_path")) in the XLSX package should return a list of the sheets in the workbook so you can get the length of the list to find the amount of sheets.

edited Nov 06 '15 at 09:06

pe-perry

2,591
2
22
33

answered May 14 '14 at 17:24

ydaetskcoR

53,225
8
158
177

Just my opinion, but I like this solution less, as xlsx requires a java installation. I like the lightest solution possible, and thus prefer the gdata answer below. – lrthistlethwaite Apr 05 '21 at 18:17

score 2 · Answer 2 · answered Jun 01 '16 at 15:26

2

This answer is rather late, but wouldn't this be simpler?

gdata::sheetCount("myworkbook.xlsx")

answered Jun 01 '16 at 15:26

David S

193
11

score 1 · Answer 3 · answered May 14 '14 at 17:53

1

You can also use package XLConnect if the workbook isn't too large.

library(XLConnect)
wb <- loadWorkbook("myworkbook.xlsx")
result <- do.call(rbind,lapply(getSheets(wb),
                               function(sheet)readWorksheet(wb,sheet)))

answered May 14 '14 at 17:53

jlhoward

58,004
7
97
140

r - read.xlsx from .xlsx with unknown number of sheets

3 Answers3