13

I'm relatively new to R (and programming). I have an Excel workbook with 36 sheets, but suppose that I don't know how many sheets there are and I want my code to find that out for me. I have tried something like:

options(java.parameters = "-Xmx6g")
library(XLConnect)
myWorkbook <- loadWorkbook(filename)
numberofsheets <- length(getSheets(myWorkbook))

But even though I set my memory to 6GB I still run into memory errors with XLConnect, so I would like to use other packages (e.g. xlsx, openxlsx). Is there a way to find out the number of sheets in an Excel workbook without using XLConnect? Thanks for your help.

Kaveh Dianati
  • 145
  • 1
  • 1
  • 6
  • 1
    @rosscova's solution is useful because you don't have to load the file to get count. With `XLConnect` you could do `wbObj=xlsx::loadWorkbook("test.xlsx");numSheets = wbObj$getNumberOfSheets()` – Silence Dogood Nov 11 '16 at 12:04
  • just to round out with `library(xlsx)`. Almost identical syntax to your current: `wb <- loadWorkbook(file); names(getSheets(wb)` I skip the Java object list and just use names for indexing and passing into `read.xlsx(sheetName = sheet)`. – Nate Nov 11 '16 at 12:11

2 Answers2

27

Maybe try:

library( readxl )
length( excel_sheets( filename ) )
rosscova
  • 5,430
  • 1
  • 22
  • 35
3

This should do exactly what you want.

gdata::sheetCount("your_path_here.xlsx")

Also, to list the sheet names as an array.

library(purrr)
library(readxl)

file <- 'your_path_here.xlsx'

sheets <- excel_sheets(file)