将 SXSSF 保存为 .xls 文件 [英] Saving SXSSF as .xls file

问题描述

我正在使用 Apache POI 库将大量数据导入 Excel 文件.我不能使用 HSSF 模型，因为它需要太多内存并且 Java 抛出内存堆异常.我发现的方法是 SXSSF 工作簿模型，它将每 N 行写入文件，而不是将所有工作簿存储在内存中.但是我找不到方法，如何将其保存为 .xls(Excel 2003 及更低格式)而不是 .xlsx.

File f = new File("file.xlsx");f.createNewFile();SXSSFWorkbook wb = 新的 SXSSFWorkbook(200);//这里输入值FileOutputStream fileOut = new FileOutputStream(f);wb.write(fileOut);fileOut.close();

解决方案

你不能.

正如 在 POI 组件页面上所述，HSSF 用于处理 .xls 文件(OLE2基于)，而 XSSF/SXSSF 用于处理 .xlsx 文件 (.xlsx)

如果要生成 .xls 文件，则必须使用 HSSF UserModel.HSSF 没有流式写入支持，只有流式读取.>

.xls 文件格式不像 .xlsx 那样适合流式写入(更多后退/前进引用、偏移量等)，因此 HSSF 中没有流式写入支持.只需增加您的 Java 堆大小，或切换到 .xlsx 的 SXSSF，或仅使用诸如 CSV 之类的简单内容！

.

更新如果您需要测试应该为系统分配多少内存以使用 HSSF 甚至 XSSF 进行写入，我建议您尝试使用 SSPerformanceTest 示例，可以在命令行运行.用所有 jars 和给定的堆大小启动它，看看它是否完成.它接受行数和列数以及要创建的文件类型的参数.使用每晚构建并尝试 40k 行/10 列，我可以让它在 90mb 堆中快速完成:

$ java -Xmx90m -classpath poi-3.10-beta3-20131219.jar:poi-examples-3.10-beta3-20131219.jar:poi-ooxml-3.10-beta3-20131219.jar \org.apache.poi.ss.examples.SSPerformanceTest HSSF 40000 10 1经过 1 秒

I'm using Apache POI library to import a large amount of data to Excel files. I can't use HSSF model, because of it needs too much memory and Java throws memory heap exception. The way I found is SXSSF workbook model, that writes every N rows to the file instead of storing all workbook in the memory. But I can't find the way, how to save it to .xls (Excel 2003 and lower format) instead of .xlsx.

File f = new File("file.xlsx");
f.createNewFile();
SXSSFWorkbook wb = new SXSSFWorkbook(200);
//inputing values here
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();

解决方案

You can't.

As explained on the POI Components page, HSSF is for working with .xls files (OLE2 based), and XSSF/SXSSF is for working with .xlsx files (.xlsx)

If you want to generate a .xls file, you'll have to use the HSSF UserModel. There is no streaming write support for HSSF, only streaming read.

The .xls file format isn't so well suited to streaming write as .xlsx (more back/forwards references, offsets etc), so there's no streaming write support in HSSF. Just bump up your Java heap size, or switch to SXSSF for .xlsx, or just use something simple like CSV!

.

Update If you need to test how much memory you ought to be giving to your system for writing with HSSF, or even XSSF, I'd suggest you try using the SSPerformanceTest example, which can be run on the command line. Fire that up with all the jars, and a given heap size, and see if it completes. It takes arguments of the number of rows and columns, and the type of file to create. Using a nightly build and trying for a 40k row / 10 columns, I can get it to complete quickly in a 90mb heap:

$ java -Xmx90m -classpath poi-3.10-beta3-20131219.jar:poi-examples-3.10-beta3-20131219.jar:poi-ooxml-3.10-beta3-20131219.jar \
     org.apache.poi.ss.examples.SSPerformanceTest HSSF 40000 10 1
Elapsed 1 seconds

这篇关于将 SXSSF 保存为 .xls 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！