C# Reference Excel Worksheet index in a select statement

Question

Task: I need to load the contents of an Excel spreadsheet worksheet into a datagridview using C#.

Progress: I've connected to an Excel spreadsheet through the OLEdbConnection and I need to reference the first worksheet by worksheet index in my select statement. (I will not know what the name of the worksheet will be, but we will ALWAYS load the first worksheet.) Below is the code I have so far...

tbFileName.Text = openFileDialog1.FileName;

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbFileName.Text + ";Extended Properties=Excel 12.0;");
//MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [FlexAccountView$]", MyConnection); <-- accessing worksheet by name...

**MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from " + [need worksheet index reference here], MyConnection);**

I know this is easy, but for some reason I'm drawing a blank today. I've searched sOf, but haven't been able to spot what I'm looking for. Any help would be greatly appreciated. Thank you for your assistance.

http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order — Sorceri, Jan 06 '15 at 21:07

score 3 · Accepted Answer · answered Jan 07 '15 at 20:59

This may not be the best way to do it, but this is what I was able to pull from the links Sorceri provided..

OleDbConnection MyConnection;
DataSet DtSet;
OleDbDataAdapter MyCommand;
MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbFileName.Text + ";Extended Properties=Excel 12.0;");

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); 

//get the workbook
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(tbFileName.Text); 

//get the first worksheet
Microsoft.Office.Interop.Excel.Worksheet wSheet = excelBook.Sheets.Item[1]; 

//reference the name of the worksheet from the identified spreadsheet item
MyCommand = new OleDbDataAdapter("select * from [" + wSheet.Name + "$]", MyConnection);

Hopefully this will help someone find it a little quicker.

score 0 · Answer 2 · answered Dec 03 '17 at 01:24

The simplest way to select a worksheet by index is ...

Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook WorkBook = ExcelApp.Workbooks.Open(inputFile);
Excel.Worksheet WorkSheet = WorkBook.Worksheets[1]; // This is the simplest call

C# Reference Excel Worksheet index in a select statement

2 Answers2