Problem while reading excel file in C# with ADO.Net

Question

Hello everyone,

I just started C# today, and i have trouble reading an excel file.

Here's what i did :

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended Properties=""Excel 12.0;HDR=YES;""");

OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet$]", connection);

connection.Open();

OleDbDataReader reader = command.ExecuteReader();

And i have an exception saying it can't find "Sheet$" (i can't copy/paste the exception, because the message is in french, and i don't know yet how to have generic messages in english)

Could someone tell me what did i do wrong ?

I followed what they say in tutorials or like here http://stackoverflow.com/questions/15828/reading-excel-files-from-c

Thanks, really !

Answer 1

You can get a list of "Tables" (worksheets or named ranges) from your Excel file:

DataTable schema = connection.GetSchema(OleDbMetaDataCollectionNames.Tables);

The TABLE_NAME field is the one you should use in your query. If the value has single quotes around it, you'll need to include those. For example, my file has worksheets named "GP" and "Kimberly Clark". In the schema table, they appear as "GP$" and "'Kimberly Clark$'". In a query, I would reference them as "[GP$]" or "['Kimberly Clark$']".

Answer 2

You can use code mentioned here http://chetanwarade.wordpress.com/2010/08/18/read-excel-with-ado-net/ to read code from perticular sheet.