Opening a huge .csv file with Excel Interop

Question

I have an application that write huge .csv files about the size ranging from 1 GB to 2 GB.

I need to color code the file and save it as .xlsx.

So I have tried using Excel Interop and it works great for small files, but when I try to open a 1.3 GB .csv file with Excel, I get an Hresult error.

Any ideas as to how I could accomplish this task either with using Excel, or if there is any other way of doing it.

Answer 1

There are ways to write and read excel files without using the excel interop. I'm pretty sure I remember microsoft themself have open specifications on the excel fileformat.

Answer 2

Are you exceeding 1M rows ? Maybe thats the reason for the HRESULT error.

64K rows max before Excel 2007. 1M rows for 2007

Answer 3

Thanks for the responses guys, after thinking about it, I have decided to simply use the .csv file.

Answer 4

If using Excel is your problem. I have used a component from Syncfusion in the past called "Essential XlsIO". It can be used to read/write and create Excel files from scratch. I used this to avoid issues with having to use Excel interop in a server environment.

I used their component to add colour-coding and comments to spreadsheets to indicate validation errors as part of a spreadsheet upload process.

It can be used to create spreadsheets from scratch. Very nice product it was too, although I did not try it with the data sizes you mention.

http://www.syncfusion.com/products/reporting-edition/xlsio