Listing Sheets in an Excel File

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