excel 2010 - EPPlus, handling big ExcelWorksheet

Question

I'm using EPPlus to create xls report on daily and weekly basis. But sometimes the timeline is greater than one year and, in my last case it was three year, so the total amount of rows are around 180k.

In this case I've optimized the query against a SQL Server DB but I think that my bottle neck is the "ExcelWorksheet" object: I'm guessing if there's chance to save it on file 50k rows per times instead of save it totally, so the memory usage could be less than now.

Anyone has an idea about an implementation?

Answer 1

Unfortunately, I dont have the greatest news. Take a look at these:

EPPlus Large Dataset Issue with Out of Memory Exception

Load large amount of excel data with EPPlus

Short answer is EPPlus will run out of RAM as you discovered already. Even with the latest version of 4 which the revamped the storage process to shrink the sizes of the cell object I am still hitting the limit.

In the first link I talk about writing code to do XML manipulation but haven't had the time to try it. If you get anywhere with it I would love to see it :).

But if you want to export the sheets in batches of 50K you should have to do it in separate files and then merge them with something like VBA outside of EPPlus as I mentioned in one of the treads. I show an example of something similar here where the VBA fires automatically when the sheet is loaded:

EPPlus Pivot Table - Copy Values to New Sheet