Read Excel 2007 file into C#

[Pho3nix]

Hi all,

Need to validate that the data in certain columns follows a certain datatype.
Tried using the Filehelper dll's but no joy as it reads the date field as a string (ie. 2013-03-21 as 41354) even when
Some help would be appreciated in getting this up. Will post some code if needed.
Hopefully someone has done this before.

 

[froot]

Try using Interop or perhaps EPPlus (awesome Excel Wrapper).
It allows you to read a cell into c# as an object, which you can parse as whatever you want.
Pm me if you need help with that.

 

[biena]

You need to remember that what is stored in the file is different from what you see, internally excel stores a date as a serial number

http://answers.oreilly.com/topic/1694-how-excel-stores-date-and-time-values/

 

[Beachless]

Have a look at this post:

http://social.msdn.microsoft.com/Fo...2eee0cbc9b/readingwriting-excel-without-excel

 

[Pho3nix]

Try using Interop or perhaps EPPlus (awesome Excel Wrapper).
It allows you to read a cell into c# as an object, which you can parse as whatever you want.
Pm me if you need help with that.

Looking into this. Thanks.

You need to remember that what is stored in the file is different from what you see, internally excel stores a date as a serial number

http://answers.oreilly.com/topic/1694-how-excel-stores-date-and-time-values/

That I didn't know. Explains a bit.. Lemme try fix a couple things.

Have a look at this post:
http://social.msdn.microsoft.com/Fo...2eee0cbc9b/readingwriting-excel-without-excel

Thanks for the link. Will read through it.

 

[Pho3nix]

Just some further info, the validation is needed to prevent the users from uploading a file which could break a SSIS package which uses said Excel files.

 

[initroot]

PM me, can share some code did some heavy interop work while ago.

 

[Nod]

Can't help with C#, but Python have a very nice library: https://openpyxl.readthedocs.io/en/default/

 

[GoB]

I tested a few and decided on ExcelDataReader to read entire XLS and XLSX files:
https://github.com/ExcelDataReader/ExcelDataReader

I load the entire workbook into simple WorkSheet objects which have some column/row info and the row data in object arrays.

Just to mention because no one I know is interested in this ... I did this recently is part of a generic Xls2Csv converter which takes an XML config. That let's me do things like searching regions for matching headers, unpivoting, reading repeated ranges. Just needs a GUI to make it look cool.

 

[Pho3nix]

I tested a few and decided on ExcelDataReader to read entire XLS and XLSX files:
https://github.com/ExcelDataReader/ExcelDataReader

I load the entire workbook into simple WorkSheet objects which have some column/row info and the row data in object arrays.

Just to mention because no one I know is interested in this ... I did this recently is part of a generic Xls2Csv converter which takes an XML config. That let's me do things like searching regions for matching headers, unpivoting, reading repeated ranges. Just needs a GUI to make it look cool.

Thanks! Think I ended up using this.. If I just remembered which project needed this I could check.
Thanks!!!

 

[GoB]

Geez 2014! lolisuck