Apache POI 需要非常长的时间才能写入工作簿

Question

我正在使用Apache POI创建记录并将其保存到工作簿中。 我有近5000 +新记录要写入并保存到工作簿中。 但是在将 fileOutputStream 写入工作簿时，执行基本上会停止并减慢速度。

我的意思是，在执行这一行时：

workbook.write(fileOutputStream);

它几乎停止处理 5000+ 条记录。我验证了在工作簿中写入需要将近1 小时(！) 的时间。

如何提高性能并克服此缺点？请建议...

**注意：其余代码是正常的Apache POI相关代码，它们执行正常，没有问题，因此我没有提到所有这些代码。只有我卡在上面的行上。

我在这里找到了一个讨论： FileOutputStream (Apachhe POI) 需要很长时间才能保存

但是，它对我没有帮助。我需要保存整个文件。

Answer 1

我理解的另一个解决方案，比如，在迭代 Row 并创建单元格时，不要在循环内不断声明CellStyle和sheet.autoSizeColumn(colNumber)，而是只在循环外部声明这 2 次，并且仅在循环内设置值和样式，即cell.setCellStyle和cell.setCellValue.

在迭代时每次声明上述 2 个，基本上会从根本上降低 POI 的性能。

Answer 2

让我们有一个具体的例子，我们可以谈谈：

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.util.GregorianCalendar;
class CreateExcel100000Rows {
public static void main(String[] args) throws Exception {
System.out.println("whole program starts " + java.time.LocalDateTime.now());
try (
//Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
int rows = 100000;
if (workbook instanceof HSSFWorkbook) rows = 65536;
Object[][] data = new Object[rows][4];
data[0] = new Object[] {"Value", "Date", "Formatted value", "Formula"};
for (int i = 1; i < rows; i++) {
data[i] = new Object[] {1.23456789*i, new GregorianCalendar(2000, 0, i), 1.23456789*i, "ROUND(A" + (i+1) + ",2)"};
}
DataFormat dataFormat = workbook.createDataFormat();
CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
CellStyle numberStyle = workbook.createCellStyle();
numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 " Coins""));
Sheet sheet = workbook.createSheet(); 
sheet.setColumnWidth(0, 12*256);
sheet.setColumnWidth(1, 35*256);
sheet.setColumnWidth(2, 17*256);
sheet.setColumnWidth(3, 10*256);
for (int r = 0; r < data.length; r++) {
Row row = sheet.createRow(r);
for (int c = 0; c < data[0].length; c++) {
Cell cell = row.createCell(c);
if (r == 0) cell.setCellValue((String)data[r][c]);
if (r > 0 && c == 0) {
cell.setCellValue((Double)data[r][c]);
} else if (r > 0 && c == 1) {
cell.setCellValue((GregorianCalendar)data[r][c]);
cell.setCellStyle(dateStyle);
} else if (r > 0 && c == 2) {
cell.setCellValue((Double)data[r][c]);
cell.setCellStyle(numberStyle);
} else if (r > 0 && c == 3) {
cell.setCellFormula((String)data[r][c]);
}
}
}
System.out.println("write starts " + java.time.LocalDateTime.now());
workbook.write(fileout);
System.out.println("write ends " + java.time.LocalDateTime.now());
if (workbook instanceof SXSSFWorkbook) ((SXSSFWorkbook)workbook).dispose();
}
System.out.println("whole program ends " + java.time.LocalDateTime.now());
}
}

此代码创建一个HSSFWorkbook，其中第一张工作表从第 1 行填充到第 65,536 行，在第A:D列中具有不同类型的单元格值。

使用java -Xms256M -Xmx512M，即从 256 到 512 MB 的堆空间，这总共需要 2 秒。HSSFWorkbook.write只需不到一秒钟的时间。

如果你这样做

...
try (
Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
...

此代码创建一个XSSFWorkbook，其中第一张工作表从第 1 行填充到第 100,000 行，在第A:D列中具有不同类型的单元格值。

使用java -Xms256M -Xmx512M，即从 256 到 512 MB 的堆空间，这总共需要 7 秒。XSSFWorkbook.write需要 2 秒。这可以通过提供更多可用堆空间来改进。

如果你这样做

...
try (
//Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
...

此代码创建一个SXSSFWorkbook，其中从第 1 行填充到第 100,000 行的第一张工作表在第A:D列中具有不同类型的单元格值。

使用java -Xms256M -Xmx512M，即从 256 到 512 MB 的堆空间，这总共需要 2 秒。SXSSFWorkbook.write只需不到一秒钟的时间。

注意：使用SXSSFWorkbook，((SXSSFWorkbook)workbook).dispose()是删除使用的临时文件所必需的。

Answer 3

如果您使用的是合并的单元格，则此答案可能会有所帮助。

我曾经有 3000+ 条记录，生成输出 xlsx 需要 10 分钟。

使用Java分析器后，我发现org.apache.poi.xssf.usermodel.XSSFSheet#getMergedRegion花了大部分时间。

根据我的数据集，我发现这种方法在 O(n^2) 中增长(n 是记录计数)，这解释了为什么它适用于小记录集(小于 1K)，但对于大型记录集需要大量时间。

我检查了模板和输出，它有很多由jx:each生成的合并单元格：

Excel headers
| A | B | C |
|   headers |
`jx:each` cells
| a |   b   | <- merged 
| a |   b   |
...
|   footers |

所以我在模板中取消合并jx:each单元格，现在只需要不到 1 秒的时间。