logo标签列表

使用Java编写大型Excel文件的API

javaexcelapache-poi
16

我想用Java程序编写到一个Excel (.xls MS Excel 2003 格式) 文件中。Excel 输出文件可能包含 ~200,000 行数据，我计划将其分成多个工作表 (每个工作表 64k 行，由于 Excel 的限制)。

我尝试使用 Apache POI API，但由于 API 对象模型的缘故，它似乎是一个内存占用量很大的程序。我被迫将单元格/工作表添加到内存中的工作簿对象中，只有当所有数据都添加完毕后，才能将工作簿写入文件！以下是 Apache 建议使用他们的 API 编写 Excel 文件的示例：

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

//Create a row and put some cells in it
Row row = sheet.createRow((short)0);

// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

很明显，写入约20k行数据（每行有10-20列）会导致可怕的“java.lang.OutOfMemoryError：Java堆空间”。

我已经尝试使用Xms和Xmx参数将JVM初始堆大小和最大堆大小增加为Xms512m和Xmx1024。仍然无法将超过150k行的内容写入文件。

我正在寻找一种流式传输到Excel文件的方法，而不是在将其写入磁盘之前在内存中构建整个文件，这有望节省大量内存使用。任何替代API或解决方案都将不胜感激，但我受限于使用Java。谢谢！ ：）

你可以查看以下链接：https://dev59.com/HlfUa4cB1Zd3GeqPFjNl - ParagJ
只有1024兆？试试4086（4GB）。我们在工作中有时运行8GB虚拟机。电子表格就是这样设计的，即使Excel也可以一次只处理部分电子表格。 - Bill K
9个回答
10

尝试使用SXSSF工作簿，这对于处理大型xls文档非常有用。它构建文档而不占用RAM，因为它使用nio。

SXSSF不支持与XLS文件一起使用。https://stackoverflow.com/questions/20678164/saving-sxssf-as-xls-file - Samy Omar
7

所有现有的Java API都试图一次性将整个文档构建在RAM中。相反，尝试编写符合新的xslx文件格式的XML文件。为了让您开始，建议在Excel中构建一个所需形式的小文件并保存它。然后打开它并检查结构并替换您想要的部分。

维基百科有一篇关于整体格式的好文章。

谢谢！我确实考虑过xslx，但客户使用的是Office 2003，xslx会出现问题。而且他们无法安装MS插件来将xslx转换为xls。两个词 - “企业IT” :| - Jaskirat
创建文件，用Excel打开并以旧格式保存。 - Aaron Digulla
报告将每日/每周生成，使用Excel打开并保存为旧格式并不是很可行。 - Jaskirat
请向Excel专家请求编写此宏。将宏放入Excel工作表中，只需双击“转换工作表”即可进行转换。当这个过程正常运行后，您可以编写批处理文件以自动执行此操作。 - Aaron Digulla
2
@Jass： 你使用的是Office 2003，但需要写入15万行？我认为只有从2007年开始的Office版本才能使用超过65536行... http://en.wikipedia.org/wiki/Excel_2003 - HerdplattenToni
1
@Toni已经说明了：“输出文件可能包含约200,000行，我计划将其分成多个工作表（每个工作表64k行，因为Excel的限制）。” - Jaskirat
4
我不得不将文件分成几个Excel文件，以克服堆空间异常。我发现每个文件约有5k行和22列，所以我只需使逻辑如此，每5k行我就结束文件，开始新文件，并相应编号。
在需要写入20k+行的情况下，我将有4个以上不同的文件代表数据。
是的，我也有这个想法，使用多个Excel文件，然后将它们压缩成一个存档文件。但这还不够好。无论如何，感谢您的建议。 - Jaskirat
如果您找到了解决方案，我会很感激您更新您的问题 =) 这是一个非常烦人的问题。 - Chris Dale
我现在也面临着同样的问题。我计划拆分数据。但是，你能告诉我你是如何做到的吗？ - Akshar A K
3

请看来自Cocoon项目的HSSF序列化器。

HSSF序列化器捕获SAX事件并创建一个使用Microsoft Excel使用的XLS格式电子表格。

2
考虑使用CSV格式。这样，您就不再受内存限制的限制了 - 好吧，也许只有在预填充CSV数据时才会受到限制，但是这也可以有效地完成，例如使用例如LIMIT/OFFSET从DB查询行子集并立即将其写入文件，而不是在写入任何行之前将整个DB表内容运到Java的内存中。 Excel中一张“工作表”中的行数限制将增加到约一百万。

话虽如此，如果数据实际上来自数据库，那么我会高度重新考虑Java是否是正确的工具。大多数体面的数据库都有导出为CSV的功能，可以无疑更高效地完成此任务。例如，在MySQL的情况下，您可以使用LOAD DATA INFILE命令完成此操作。

2

还有JExcelApi，但它使用更多的内存。我认为你应该创建一个.csv文件并在Excel中打开它。这允许您传递大量数据，但您将无法进行任何“Excel魔法”。

分隔符文件当然非常轻便！但不幸的是这不是一个选项。没有 Excel 魔法，也没有数据格式化等等。我需要编写 .xls 文件。 :( - Jaskirat
1
我认为你应该试着说服你的老板使用 .csv 文件，因为后来你会遇到很多慢速工作的 Excel 的问题，整个 Web 应用程序可能无法正常工作，因为有 10 个人正在生成 Excel 报告。 - IAdapter
1
我们开发了一个用于此目的的Java库，目前它作为开源项目https://github.com/jbaliuka/x4j-analytic可用。我们将其用于运营报告。我们生成巨大的Excel文件，大约200,000个没有问题，Excel也能打开这样的文件。我们的代码使用POI来加载模板，但生成的内容直接流式传输到文件中，而不需要在内存中使用XML或对象模型层。
有没有关于如何在我们的项目中使用这个库的文档？ - undefined
0

在插入数据到单元格时，还是在执行数据计算/生成时，是否会出现内存问题？

如果您要加载包含预定义静态模板格式的文件到Excel中，则最好保存模板并多次重复使用。通常，在您将生成每日销售报告等情况下会发生模板案例...

否则，每次您需要从头开始创建新行、边框、列等。

到目前为止，我发现仅有Apache POI是唯一的选择。

“显然，写入约20k行（每行大约有10-20列）会给我带来可怕的“java.lang.OutOfMemoryError: Java堆空间”错误。”

“企业级IT”

您可以执行批量数据插入。创建一个队列任务表，在生成1页后，休息几秒钟，然后继续第二部分。如果您担心队列任务期间的动态数据更改，您可以首先将主键获取到Excel中（通过隐藏和锁定用户视图中的列）。第一次运行将插入主键，从第二个队列运行开始，将从记事本中读取并逐部分执行任务。

为什么我们要谈论任务队列？ :-S 我不太明白你想表达什么。当你说要使用模板时，是要我使用jxls api或类似的东西吗？ - Jaskirat
0

我们做了类似的事情，数据量相同，但由于POI资源消耗非常大，我们不得不转向JExcelapi。尝试使用JexcelApi，当您需要操作大型Excel文件时，您将不会后悔！

谢谢，我将使用JExcelAPI进行概念验证，并查看它与POI相比如何表现。但是结构似乎相似，所以我真的不知道会有多大的区别。如果可能的话，您能给我一些比率和数字吗？ - Jaskirat
我们很久以前就放弃了基于POI的代码，因为出现了和你们一样的问题，所以很抱歉我不能提供帮助。不过，我刚刚查看了Glassfish服务器，它除了其他应用程序之外还托管生成.xls文件的应用程序。它有-Xmx768m，我们从来没有遇到过Excel生成引起oom错误的情况。我刚刚生成了10个xls文件，每个文件有45000行*8列，堆上有574,423,040字节。希望这可以帮到你们。 - fvu
多个xls文件不会成为问题，因为一旦它们被刷新到文件中，对象就可以被发送到垃圾回收。所以，一个包含45k行x 8列的Excel文件可以在768Mb下运行，但是150,000 x 20列需要超过1GB的内存，我不能指数级地分配内存，那将导致糟糕的设计。 :D 无论如何，感谢您的所有帮助！ :) - Jaskirat
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接