Search code examples
rexcelfor-loopr-xlsx

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

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?

Solution

  • 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.