Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

excel 2010 - EPPlus, handling big ExcelWorksheet

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...