'List xlsx sheetnames with R

Is it possible to generate a list of sheetnames within an xlsx file? Or perhaps, can I check if a sheet name exists, and if not, proceed with some designated function?

r xlsx

Solution 1:^[1]

With xlsx library you can get the list of the sheets in an existing workbook with getSheets():

wb <- loadWorkbook(your_xlsx_file)
sheets <- getSheets(wb)

Solution 2:^[2]

Yes, I have done that with the xlsx package which (just like the XLConnect package) uses a Java backend with the Apache POI code -- so it is cross-platform.

Solution 3:^[3]

You can also do this with the RODBC package:

h <- odbcConnectExcel2007("file.xlsx")
sqlTables(h)

Solution 4:^[4]

A oneline solution using openxlsx would be

openxlsx::getSheetNames('your/file.xlsx')

Solution 5:^[5]

Only this worked in my case:

library(openxlsx)
sheetNames <- getSheetNames("filename.xlsx")

None of the above solutions worked for my big xlsx (>300 sheets):

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : java.lang.OutOfMemoryError: Java heap space

Solution 6:^[6]

To get excel or workbook file sheet names using R xlsx package, load your workbook or excel file, in my case e.g. name of excel file is "input_4_r.xlsx"

> wb<-loadWorkbook("input_4_r.xlsx")

See the list of files, here it shows 2 sheets in my example case in my example, I have not named first sheet and kept the default but 2nd sheet, I named as "name city" and hence the output below.

> getSheets(wb)
$Sheet1
[1] "Java-Object{Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml}"

$`name city`
[1] "Java-Object{Name: /xl/worksheets/sheet2.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml}"

You can see the names of sheetnames as below.

> names(getSheets(wb))
[1] "Sheet1"    "name city"

To get the name of specific index of sheet, e.g. passing [2] in my case for 2nd sheet.

> names(getSheets(wb))[2]
[1] "name city"

Assumption for above is xlsx package is installed and loaded in R

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Geza
Solution 2	Dirk Eddelbuettel
Solution 3	Hong Ooi
Solution 4	loki
Solution 5	stallingOne
Solution 6	jay.sf

'List xlsx sheetnames with R

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]