Suggestions for reading data from excel in .net c#

Question

I need to read in data from excel files in my c# winforms app. Any recommendations on good components for this? I've used syncfusion some years ago and that seemed to do the trick.

There'll be a bunch of header lines I need to skip (so a straight ADO approach won't work easily) and then a table of data with standard columns but variable number of rows.

I'll be pumping the data into SQL Server db once it's read, but probably need to do validation etc on it before that.

thanks!

Answer 1

Ultimately we opted for Syncfusion's XLSIO which works well.

Thanks for the other suggestions too.

Answer 2

You can do something like this:

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";

OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("Select * FROM [Sheet1$]", excelConnectionString);

DataSet dataSet = new DataSet();

oleDbDataAdapter.Fill(dataSet);

Also see "SqlBulkCopy"

Answer 3

We're currently using Flexcel. It has some nice features including a tool for reading a spreadsheet and generating the necessary C# (or VB or Delphi) code to generate that sheet using their toolkit -- it makes designing a sheet a snap. The licensing wasn't expensive (site license for developers, redistribution free).

The only thing against it is that XLSX (Excel 2007 native format) compatibility is "real soon now".