CODEWITHSUNDEEP
rexcelfor-loopr-xlsx
Daniel Shestakov
Daniel Shestakov

Reputation: 73

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?

Upvotes: 3

Views: 4435

Answers (3)

David S
David S

Reputation: 193

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

gdata::sheetCount("myworkbook.xlsx")

Upvotes: 2

ydaetskcoR
ydaetskcoR

Reputation: 56967

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.

Upvotes: 4

jlhoward
jlhoward

Reputation: 59395

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

Upvotes: 1

Related Questions