Reading from an excel file with C#

Ok so I have found a need to read data from an excel spreadsheet in a C# program. After looking over using COM and other things I found actually OleDB is the best way. This ensures you don't have to have excel/office installed on the target system. It may mean a bit more work but for most purposes I think it is good enough. All of this code is contained in the System.Data.OleDb namespace. This code reads from a known file with a known sheet name. If you want to be more flexible and don't know the sheet name(s) you have to do a bit more magic. That will be covered in another post.

From the posts below I have decided a bit more information is necessary for this post to be more informative. When dealing with any office component you have to deal with the different versions (2000,2003,2007) etc. This example is connecting to an excel 2003 file. You will need to use the right excel version number based on what you have installed or what your customers will have installed. Because of this I recommend taking a look at the links at the end of the post for more reading.

// create a connection to your excel file
// the 8.0 denotes excel 2003
// you will need to use the right number for your version of excel

OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=InsertYourFile.xls"; Extended Properties=Excel 8.0" );

// create a new blank datatable
DataTable dtSheets = new DataTable();

// create a data adapter to select everything from the worksheet you want
OleDbDataAdapter da =
new OleDbDataAdapter( "select * from [YourWorksheetName$]", con );

// use the data adapter to fill the datatable
da.Fill( dtSheets );


HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases

How To Transfer Data from ADO Data Source to Excel with ADO

Comments

Anonymous said…
Thanks Justin,

It solved my problem of accessing Excel 12 data in C#. I am bringing my connection string from app.config though as in:

key="ExcelTestDBConnection" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Work\Test.xlsx;Extended Properties='Excel 12.0;HDR=YES'"
Justin said…
Hey glad it worked out for you. The issue with reading from excel no matter what you're using (COM, OleDB, etc) you still have to deal with the different versions. It's been a while since I used the code but I recall it being used to access excel 2003.

Popular Posts