Selecting from the first sheet in an XLS file

Selecting from the first sheet in an XLS file

July 21, 2004 | Life

Using the OLEDB .NET Data Provider and Jet, I am able to select from the various worksheets inside an Excel spreadsheet. This is great, and has saved me from automating Excel many times. The catch is that you have to know the name of the first sheet in the XLS file, or use automation or DAO (yuck) to determine it. I found a posting on DonXML’s blog that shows how to do this, using the OleDbConnection and it’s GetSchemaTable method. Here’s the goods:

conExcel.Open();
DataTable xlsSheets = conExcel.GetOleDbSchemaTable
  (System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]
  {null, null, null, “TABLE”});
string SheetName =
  “[“+xlsSheets.Rows[workSheetNumber][“TABLE_NAME”].ToString()+”]”;