C# - Retrieve Excel Workbook Sheet Names.

This article provides a way to retrieve the worksheet names of an Excel Workbook. This can be used to query a workbook using ADO.NET if you do not know the names of the worksheets.

Introduction

There are many examples using ADO.NET to query an Excel Workbook but they all have a limitation, you must know the worksheet name. This might work if you know the name of the worksheet, but what if you don't? If your program is dynamic and your sheet names differ for each Excel workbook, you need a way to extract the names of the sheets. The code example provides you with a way to retrieve the work sheet names.

The following method returns a string array containing the names of the sheets. The method also shows how to loop through the array. The method has one input parameter excelFile which is the location of the Excel file.

Code Example

///<spanclass="code-SummaryComment"><summary></span>

Enjoy...

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Share

About the Author

Ken currently works as the Director of Software Engineering at the Pediatrics Epidemiology Center at the University of South Florida. He is involved in the architecture and design on numerous clinical trial projects. Some of the project include:

Hi Kenny,
I have used the code given above in a C# .Net Windows Excel project. It is working in about 5 computers perfectly well. Today when I was installing it on a users machine it gave the error "no error message available, result code:E_UNEXPECTED (0x8000FFFF) while I think it is trying to open the OLEDb connection.
Actually it is returning null in place of the worksheet name. I tried to figure out the problem but could not succeed. Please help me.

hi,
I have a request
Can you give me a simple and complete example
in C# about access to MS excel 2003 sheets and cells
and reading or writing to cells and also drawing
graph in accordance with excel cells
(i.e. for example I have an integer table in an excel
sheet and now I want to draw a chart in accordance with
this integer table)
very thanks
yours sincerely hamed

I have an excel file downloaded from a web site that I need to get the worksheet names from. It's in Excel 5.0 format and the code doesn't work. If I load it into Excel 2003 and then save it in that format, the code works fine.

When I run the code on an Excel 5.0 file (even if I change the Extended Properties = Excel 5.0), the SchemaTable returns zero rows (no information).

i found a bug and im still figuring out how to fix it.
can you help?
it reads an extra sheet.

sample
newExcel.xls
sheet1 - somename

when it retrieves the sheets, it displays 2 sheets as
1 - oldsheetname
2 - sheet1$

i trace the problem here
dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
dt returns 2 rows when it should only retrive 1 row.

hmm it does not happen all the time. i tried creating a sheet and it works ok, but when i use my own excel sheets, the bug appears.
hmm. maybe it has something to do with renaming my sheets and deleting some sheets. i will try to replicate

someone is generating excelfile for me, i open it using this code to retrieve the excelsheet names.
im getting 2 sheets
1 - oldsheetname
2 - oldsheetname$

i use this sample apphttp://www.codeproject.com/csharp/Excel_using_OLEDB.asp[^]
its using the code with you retrieving sheetnames.
im still having trouble on how to duplicate it.
im getting these error on these excel files.
one more thing, im also getting error if there are spaces on sheetnames - like "sheet1 (2)"

Hi All , this concept of reading the ExcelSheet has really provided gr8 help to me as far as the issues of returning extra excelsheets with $ symbols appended to it I have come up with the following approach , the code is in VB.NET but its simple enough to be convertable to C#

Dim index As Integer
Dim excelsheetName As String = row("TABLE_NAME").ToString()
index = excelsheetName.LastIndexOf("$")
'Check wheather a sheet or not
If Not index < 0 Then
excelsheetName = excelsheetName.Remove(index, excelsheetName.Length - index)
If i = 0 Then
'To get rid of this "'"
If excelsheetName.Chars(0) = "'" Then
excelsheetName = excelsheetName.Remove(0, 1)
End If
excelSheets(i) = excelsheetName
i += 1
Else
If excelsheetName.Chars(0) = "'" Then
excelsheetName = excelsheetName.Remove(0, 1)
End If
If Not excelSheets(i - 1) = excelsheetName Then
excelSheets(i) = excelsheetName
i += 1
End If

I think the point of the tip was to use ADO.NET -- in the context of reading an Excel spreadsheet as an ADO.NET data source. With the information from this article, one would not need Excel installed on the machine.

What you propose is fine too - in the context of already having Excel installed on the machine, and using the Excel object library. But in the context of reading data from Excel sheets, probably without Excel installed, the author's tip is very useful.

I get certain sheets with their names ending with a underscore (for example Details_). but no such sheet is present in excel file. I think these are added when data is added to excel through another application. Is there a way to avoid reading these sheet names!!??