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-ins开发者_Go百科talled, is simple (since the input excel file will only have one worksheet in each file).
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.
I heart NPOI.
http://npoi.codeplex.com/
精彩评论