1

ADO.NET just fails miserably when it's used to read Excel files where a column contains mixed data types. It appears that it tries to determine the data type of a column, and then assume the rest of the entire column is of the same data type. Here are some backgrounds:

How to prevent ADO.NET from altering double values when it reads from Excel files

http://blog.lab49.com/archives/196

What is an alternative approach that doesn't require automation, Excel to be co-installed, is simple (since the input excel file will only have one worksheet in each file).

Community
  • 1
  • 1
Kevin Le - Khnle
  • 10,579
  • 11
  • 54
  • 80
  • depending on your Excel file format, you could to use an OpenXML library to get your data, or to extract that data as XML and consume it directly – Rubens Farias Jun 03 '10 at 19:27
  • I suppose you could upload it to Google Docs and use their API. At the very least it should avoid the structuring problems you describe with ADO. – ladenedge Jun 03 '10 at 19:30
  • Exactly which format(s) are the files in? (csv, xls, xlsx, etc) – James Manning Jun 03 '10 at 19:31
  • @James Manning - I think the answer to your question is **xls**. The Excel input file is to be created by non-technical users. They launch Excel, and with an empty file sitting there, they start typing 2 columns of data, hit Save and give it a file name when prompted. – Kevin Le - Khnle Jun 03 '10 at 19:36
  • @ladenedge - Unfortunately, that's not possible. We can't go outside the firewall with company's information like that. – Kevin Le - Khnle Jun 03 '10 at 19:37
  • I'm pretty sure that there is a setting that you can specify in the JET provider's connection string to prevent it from trying to infer the data type of a column. Check out the IMEX=1 setting or the RowsToScan setting: http://support.microsoft.com/kb/257819. It's frustrating to get it working, but I think that once you do ADO is probably a pretty good approach to reading Excel files, considering the alternatives. – Dr. Wily's Apprentice Jun 03 '10 at 21:05

2 Answers2

3

It depends a little on the Excel versions you need to support. I've used ExcelPackage successfully in the past for reading and creating Excel 2007 files. It is no longer under development, which is a bit of a shame. You'd have to manually loop though all the cells, which I wouldn't call elegant, but it's flexible ;)

Leniel has suggested using EPPlus over ExcelPackage. I haven't used it myself, but it looks very promising.

There are several other suggestions in this question, but I have no experience with the other libraries.

Community
  • 1
  • 1
Thorarin
  • 47,289
  • 11
  • 75
  • 111
2

I heart NPOI.

http://npoi.codeplex.com/

Jay
  • 56,361
  • 10
  • 99
  • 123