What is an alternative to ADO.NET, but elegant way in C# to read Excel files

Question

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:

http://stackoverflow.com/questions/2968111/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).

Answer 1

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.

Answer 2

You can read a XLS file with Myxls library, is free and open source, you can see an example in the this url: Myxls example

Answer 3

I heart NPOI.

http://npoi.codeplex.com/