List xlsx sheetnames with R

Question

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?

score 17 · Answer 1 · answered Jun 25 '14 at 14:53

17

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)

answered Jun 25 '14 at 14:53

Geza

464
6
16

4

I think you want names(getSheets(wb)) – Gerry Nov 13 '16 at 02:56

score 7 · Answer 2 · answered Sep 12 '12 at 02:19

7

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.

answered Sep 12 '12 at 02:19

Dirk Eddelbuettel

360,940
56
644
725

2

I glossed right over the getSheets function, exactly what I needed. – user1664455 Sep 12 '12 at 02:33

stallingOne · Answer 3 · 2023-05-04T19:52:44.407

3

None of the above solutions worked for a big xlsx of more than 300 sheets.

Only this worked in my case:

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

edited May 04 '23 at 19:52

answered Nov 28 '21 at 05:44

stallingOne

3,633
3
41
63

score 2 · Answer 4 · answered Sep 12 '12 at 05:04

2

You can also do this with the RODBC package:

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

answered Sep 12 '12 at 05:04

Hong Ooi

56,353
13
134
187

score 0 · Answer 5 · edited Apr 19 '22 at 14:11

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

score 0 · Answer 6 · answered Apr 15 '20 at 15:34

0

A oneline solution using openxlsx would be

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

answered Apr 15 '20 at 15:34

loki

9,816
7
56
82

You would have to install the package. `install.packages("openxlsx")` – loki Nov 28 '21 at 15:56

score 0 · Answer 7 · answered May 20 '23 at 23:51

The following approach could also be considered with the RDOMClient R package :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
path_To_Excel_File <- "D:/my_Excel_File.xlsx"
xlWbk <- xlApp$Workbooks()$Open(path_To_Excel_File)
nb_Sheets <- xlWbk$Sheets()$Count()
sheets_Names <- character(nb_Sheets)

for(i in 1 : nb_Sheets)
{
  sheets_Names[i] <- xlWbk$Sheets(i)$Name()
}

List xlsx sheetnames with R

7 Answers7

Linked