java - 编写复杂的 Excel 文件时出现 GC Overhead limit Exceeded Error

Question

在 Java 中，我试图编写包含 27 张工作表的 Excel 工作簿，每张工作表中有大约 500 到 600 列，但是当我运行该程序时，它会给我 GC 开销错误。

这是我得到的异常。

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
  at org.apache.xmlbeans.impl.store.Cur$Locations.<init>(Cur.java:495)
  at org.apache.xmlbeans.impl.store.Locale.<init>(Locale.java:168)
  at org.apache.xmlbeans.impl.store.Locale.getLocale(Locale.java:235)
  at org.apache.xmlbeans.impl.store.Locale.newInstance(Locale.java:586)
  at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.newInstance(SchemaTypeLoaderBase.java:198)
  at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell$Factory.newInstance(Unknown Source)
  at org.apache.poi.xssf.usermodel.XSSFCell.setBlank(XSSFCell.java:696)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellType(XSSFCell.java:737)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:328)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:315)

Answer 1

您应该使用 SXSSFWorkbook。SXSSF 通过限制对滑动窗口内的行的访问来实现其低内存占用，而 XSSF 允许访问文档中的所有行。不再位于窗口中的较旧行在写入磁盘时变得不可访问。

public static void main(String[] args) throws Throwable {
    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }

    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
      Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
    wb.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    wb.dispose();
}

示例:https://poi.apache.org/spreadsheet/how-to.html#sxssf