Introduction
This tip describes one way to programmatically get the list of sheets in an Excel file. I do it this way because I like ADO.NET and this basic technique (with some minor changes) can be used with pretty much any database that has an ADO.NET provider.
Background
A few days ago, someone asked about getting a list of sheets in an Excel file, but seemed unwilling to actually write any code to do it.
Using the Code
I'm providing only a simple static method that can be called from other code that actually does something with the list.
The user of this method has to know what OleDb Providers that can access Excel (usually the ACE engine or the JET engine) are installed on the system. The user can then create an appropriate connection string to pass in (connectionstrings.com is a good resource for finding details).
The Method
The method is actually an enumerator, and I hope that makes it easy to use.
For more information, look into the System.Data.Common.DbConnection.GetSchema methods.
public static System.Collections.Generic.IEnumerable<string>
EnumerateSheets
(
string ConnectionString
)
{
System.Data.DataTable dt = null ;
using
(
System.Data.Common.DbConnection db
=
new System.Data.OleDb.OleDbConnection ( ConnectionString )
)
{
db.Open() ;
try
{
dt = db.GetSchema ( "TABLES" ) ;
}
finally
{
db.Close() ;
}
}
if ( dt != null )
{
dt.DefaultView.Sort = "TABLE_NAME" ;
dt.DefaultView.RowFilter = "TABLE_NAME LIKE '*$'" ;
for ( int i = 0 ; i < dt.DefaultView.Count ; i++ )
{
yield return ( dt.DefaultView [ i ] [ "TABLE_NAME" ] as string ) ;
}
}
yield break ;
}
History
- 2018-02-21 -- First version
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
