POI读取现有Excel和大型数据编辑 [英] poi read existing excel and edit with large data

问题描述

我有模板，其中列标题有色一些被合并（​​我在一张纸上谈100至150列）这个我有大约10张左右。
现在，我读了数据库中的数据并填充数据到这个Excel。
现在对于小型数据集，我能够做的，但对于大数据集，它抛出内存不足。
虽然我像1000行数据的小块从数据库获取数据一次proccess了1000，并再次获得另一套。数据Maxiumn行将以每张50,000行和minumun行将在每个表一行

I using apache poi to read and write excel .I am able to do that without any problem.But i am facing memory issues when writing large excels on existing excel.To Explained in more detail below I have template where column headers are colored some are merged (i am talking about 100 to 150 columns in one sheet) this i have around 10 sheets approx. Now i read data from database and populate the data into this excel. Now for small sets of data i am able to do but for large data sets it throws out of memory. Although i get the data from db in small chunks like 1000 rows of data at once proccess that 1000 and again get another set. Maxiumn rows of data will be 50,000 rows per sheet and minumun rows will be one row in each sheet

我看了一下SXSSF但问题是它不能在现有的过人之处编辑。

I have read about SXSSF but the issue is it does not edit on existing excels.

请让我知道你这是如何可以解决的想法。

Please let me know your thoughts on how this can be solved.

推荐答案

有是一个银弹简单的答案可使用几个选项，但没有。遗憾的是，Excel文件格式确实需要的内存公平位的工作。

There are a few options available, but none that are a "silver bullet" simple answer. Unfortunately, the Excel file formats do require a fair bit of memory to work with.

1. 增加提供给Java的堆大小，因此它可以同时容纳更多的内存


2. 购买一些更多的内存，为您的服务器，然后增加堆的大小。内存为pretty便宜这些天，它并不需要一个体面的程序员非常多小时工资标准支付费用


3. 使用不同的文件格式 - CSV例如是内存占用
非常轻巧

4. 使用href=\"http://poi.apache.org/s$p$padsheet/how-to.html#low_level_api\" rel=\"nofollow\">低水平SAX API 以阅读 SXSSF 写它同样没有太多缓冲。需要相当多的自定义编码的，虽然，因为你更多的工作比只使用HSSF / XSSF


5. /参与赞助的内存改进到Apache POI的贡献。 POI是开源的，免费的，并且完全由志愿者维护，因此如果事情对你很重要，你是经常最好关闭帮助！

1. Increase the heap size given to Java, so it can hold more in memory at a time
2. Buy some more memory for your server, then increase the heap size. Memory is pretty cheap these days, and it doesn't take very many hours wage of a decent programmer to cover the cost
3. Use a different file format - CSV for example is very lightweight on the memory footprint
4. Use the low level SAX API to read in the .xlsx file, then use SXSSF to write it out again without much buffering. Needs quite a lot of custom coding though, much more work for you than just using HSSF/XSSF
5. Contribute / sponsor the contribution of memory improvements to Apache POI. POI is open source, freely available, and maintained entirely by volunteers, so if something matters to you you're often best off helping!

这篇关于POI读取现有Excel和大型数据编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！