2

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!

Rory
  • 40,559
  • 52
  • 175
  • 261

9 Answers9

2

SpreadsheetGear for .NET will do it.

You can see ASP.NET samples with C# and VB source here and download the free trial here if you want to try it out.

Disclaimer: I own SpreadsheetGear LLC

Joe Erickson
  • 7,077
  • 1
  • 31
  • 31
1

Using ASPOSE Tools

Using ADO.NET

Do you need graphic support?

Srikar Doddi
  • 15,499
  • 15
  • 65
  • 106
1

Im using the ComponentOne XLS component right now. It is pretty stable and does it job. Im using it for data integration too.

I also wanted to do some xls reports, however the API does not support the full excel stack (not even launching an excel instance) so i had to go over Mirosoft.Office.Interop

Johannes Rudolph
  • 35,298
  • 14
  • 114
  • 172
1

One of alternatives: MS Office Wrapper for .NET

TcKs
  • 25,849
  • 11
  • 66
  • 104
1

you can use LINQ to XML also, see the link below...

Video Tutorial

shahjapan
  • 13,637
  • 22
  • 74
  • 104
1

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".

Clinton Pierce
  • 12,859
  • 15
  • 62
  • 90
0

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"

g t
  • 7,287
  • 7
  • 50
  • 85
Mark Redman
  • 24,079
  • 20
  • 92
  • 147
  • 1
    Once you have the dataset (ie all rows in the Excel worksheet) Rows can be skipped/ignored/rejected/logged through counts (if files are known/consistent) and through validation on each row/column. – Mark Redman Aug 07 '09 at 07:58
  • I agree that ADO is a great way to get data from Excel in some situations but I've found it to be a pain in some cases, eg if the data isn't very sanitized. Also requires annoying client changes to ensure data typing is done well. So in this case I want a component that provides a nicer API. – Rory Aug 10 '09 at 14:01
0

Read this blog post on Reading and Writing Excel Spreadsheets.

Reading and Writing Excel Spreadsheets

Allensb
  • 260
  • 3
  • 13
  • This suggests a straight ADO approach but I'd prefer a component to help with some of the complexities. – Rory Aug 10 '09 at 14:03
0

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

Thanks for the other suggestions too.

Rory
  • 40,559
  • 52
  • 175
  • 261