Java 兴趣点表现
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2498536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
POI performance
提问by The Machine
I am using POI in my J2EE web application to generate a workbook. However, i find that POI takes around 3 mins to create a workbook with 25K rows(with around 15 columns each). Is this a POI performance issue , or is it justified to take that much of time? Are there other APIs known for better performance ?
我在 J2EE Web 应用程序中使用 POI 来生成工作簿。但是，我发现 POI 需要大约 3 分钟才能创建一个具有 25K 行（每行大约 15 列）的工作簿。这是一个 POI 性能问题，还是花那么多时间是合理的？是否有其他以更好的性能着称的 API？
采纳答案by Tomislav Nakic-Alfirevic
I would be very surprised to see POI take that much time to generate such a file. I just generated a sheet with 30000 rows x 10 cells in about 18s (no formatting, to be fair). The cause might be one of the following:
看到 POI 花费这么多时间来生成这样的文件，我会感到非常惊讶。我刚刚在大约 18 秒内生成了一个包含 30000 行 x 10 个单元格的工作表（公平地说，没有格式化）。原因可能是以下之一：
- POI logging might be turned on, as described here
- you are running from swap memory
- your VM available heap might be very low
- POI记录可能被打开，如所描述这里
- 您正在从交换内存运行
- 您的 VM 可用堆可能非常低
回答by pitpod
We also use POI in our web app and do not have any performance issue with it - although our generated documents are far smaller than yours. I would first check if POI is the real issue here. Try to generate those documents without the J2EE-overhead (Unit-Test) and measure the performance. You could also monitor the load and memory usage on your J2EE server to see if the problems come from some suboptimal system settings.
我们还在我们的 Web 应用程序中使用 POI，并且没有任何性能问题 - 尽管我们生成的文档远小于您的文档。我会首先检查 POI 是否是这里的真正问题。尝试在没有 J2EE 开销（单元测试）的情况下生成这些文档并测量性能。您还可以监视 J2EE 服务器上的负载和内存使用情况，以查看问题是否来自某些次优系统设置。
回答by duffymo
If none of the other answers work out, see if Andy Khan's JExcel will be better. I've found it to be far superior to POI for dealing with Excel in Java.
如果其他答案都不起作用，看看安迪汗的 JExcel 是否会更好。我发现它在用 Java 处理 Excel 方面远优于 POI。
回答by andi
I've compared Apache POI with JExcel library. It seems that JExcel is about up to 4x faster than Apache POI but memory consumption seems to be more or less the same:
我将 Apache POI 与 JExcel 库进行了比较。JExcel 似乎比 Apache POI 快 4 倍，但内存消耗似乎或多或少相同：
@Test
public void createJExcelWorkbook() throws Exception {
WritableWorkbook workbook = Workbook.createWorkbook(new File("jexcel_workbook.xls"));
WritableSheet sheet = workbook.createSheet("sheet", 0);
for ( int i=0; i < 65535; i++) {
for ( int j=0; j < 10; j++) {
Label label = new Label(j, i, "some text " + i + " " + j);
sheet.addCell(label);
}
}
workbook.write();
workbook.close();
}
@Test
public void createPoiWorkbook() throws Exception {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet");
for ( int i=0; i < 65535; i++) {
Row row = sheet.createRow(i);
for ( int j=0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue("some text " + i + " " + j);
}
}
FileOutputStream fileOut = new FileOutputStream("poi_workbook.xls");
wb.write(fileOut);
fileOut.close();
}
I've tested it with JExcel version 2.6.12 and Apache POI version 3.7. You need to download the latest library versions yourself and run the simple tests above to get more accurate numbers.
我已经用 JExcel 2.6.12 版和 Apache POI 3.7 版对其进行了测试。您需要自己下载最新的库版本并运行上面的简单测试以获得更准确的数字。
<dependency org="org.apache.poi" name="poi" rev="3.7"/>
<dependency org="net.sourceforge.jexcelapi" name="jxl" rev="2.6.12"/>
Note:there is a limit in Apache POI of 65535 rows per sheet.
注意：Apache POI 限制为每张纸 65535 行。
回答by Eric Nicolas
The performance of writing large files with POI can be heavily reduced if you used the 'streaming' POI API instead of the standard one. Indeed by default POI will keep all your data in memory before writing all in one go at the end. The memory footprint of this can be ridiculously large for big files. Instead using the streaming API you can control how memory is used and data is written to disk progressively.
如果您使用“流式”POI API 而不是标准 API，则使用 POI 写入大文件的性能会大大降低。事实上，POI 会默认将您的所有数据保存在内存中，然后在最后一口气写入所有数据。对于大文件来说，它的内存占用可能大得离谱。而不是使用流 API，您可以控制如何使用内存以及如何逐步将数据写入磁盘。
In order to create a streaming workbook, use something like :
要创建流式工作簿，请使用以下内容：
SXSSFWorkbook book = new SXSSFWorkbook();
book.setCompressTempFiles(true);
SXSSFSheet sheet = (SXSSFSheet) book.createSheet();
sheet.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk
// ...
