EPPlus，处理大型ExcelWorksheet [英] EPPlus, handling big ExcelWorksheet
问题描述
我正在使用EPPlus每天和每周创建xls报告。但是有时时间表会超过一年，而在我的上一个案例中，时间表是三年，因此行的总数约为18万。
在这种情况下，我我已经优化了针对SQL Server DB的查询，但是我认为我的瓶颈是 ExcelWorksheet对象：我猜测是否有机会每次将其保存在文件50k行中而不是完全保存在文件中，因此内存使用量可以比现在少。
有人对实现有想法吗？
不幸的是，我没有最大的消息。看看这些：
简单的回答是，如您所发现的，EPPlus将用尽RAM。即使使用最新版本的4，该版本修改了存储过程以缩小单元格对象的大小，我仍然达到极限。
在第一个链接中，我谈到编写代码来进行XML操作，但还没有时间尝试。
但是如果要批量导出5万张工作表，则必须单独进行操作。
文件，然后将它们与EPPlus之外的VBA之类的文件合并，就像我在其中一个步骤中提到的那样。我在此处显示了一个类似的示例，其中在加载工作表时VBA自动触发：
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?
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
这篇关于EPPlus，处理大型ExcelWorksheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
