CODEWITHSUNDEEP
rexcelxlconnect
userk
userk

Reputation: 951

How to extract sheet names from Excel file in R

I have loaded a workbook into R and read in the worksheets using xlConnect, but I was wondering if there was a way of extracting the names of the sheets perhaps in a vector?

So far my code is:

dataIn<-loadWorkbook(file.path(filenames[1],sep=""))
lst = readWorksheet(dataIn, sheet = getSheets(dataIn), startRow=1, startCol=1, header=TRUE)

...and I want to extract the sheet names of the sheets in lst.

Upvotes: 42

Views: 63588

Answers (5)

tanmayg
tanmayg

Reputation: 11

Easiest way to do this after using loadWorkbook, using code from the user's example:

dataIn<-loadWorkbook(file.path(filenames[1],sep=""))
names(dataIn)

This will print out all the sheets your current workbook object has.

Upvotes: 0

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2213

Here is another approach that can be considered :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlApp[["DisplayAlerts"]] <- FALSE
xlApp[["Visible"]] <- TRUE

path_To_Excel_File <- "D:/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()
}

Upvotes: 0

Luker
Luker

Reputation: 301

In the "openxlsx" package it would be a command "getSheetNames": 

library(openxlsx)
path <- "path/to/your/file.xlsx"
getSheetNames(path)

Upvotes: 9

Matt Dancho
Matt Dancho

Reputation: 7288

Another really nice package developed by the folks at RStudio is readxl. It's easy to get the excel sheet names with the excel_sheets() function.

library(readxl)
path <- "path/to/your/file.xlsx"
excel_sheets(path = path)

Upvotes: 79

agstudy
agstudy

Reputation: 121568

You are looking for getSheets 

Returns all worksheet names in a workbook.

Upvotes: 27

Related Questions