Apache POI生成1000条数据Excel耗时超10分钟，求优化方案

我用Java的Apache POI库实现Excel报表功能，处理1008条记录的对象列表时，生成报表耗时超过10分钟，调试发现耗时主要集中在writeDataLines()方法中，以下是可运行代码：

控制器类 #

注意：列表report包含1008条记录，最终报表生成耗时10分钟
调试发现writeDataLines()方法占用了大部分时间

List<PricingCalculationReport> report = pricingRuleServiceImpl.applyPricingOnAirshopReport(pricingRequest, agentId);

log.info("logging pricing report: ");
log.debug(objectMapper.writeValueAsString(report));

if (report != null) {
    response.setContentType("application/octet-stream");
    DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
    String currentDateTime = dateFormatter.format(new Date());

    String headerKey = "Content-Disposition";
    String headerValue = "attachment; filename=PricingReport_" + currentDateTime + ".xlsx";
    response.setHeader(headerKey, headerValue);

    PricingReporter excelExporter = new PricingReporter(report);

    excelExporter.export(response, new PricingCalculationReport());
}

Report类的export方法 #

public void export(HttpServletResponse response, PricingCalculationReport report) throws IOException {

    try {
        writeHeaderLine("Pricing Report", report);
        writeDataLines();

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();

        outputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

Report类的writeDataLines方法 #

private void writeDataLines() throws Exception {
    int rowCount = 2;

    CellStyle style = workbook.createCellStyle();
    XSSFFont font = workbook.createFont();
    font.setFontHeight(10);
    style.setFont(font);

    for (PricingCalculationReport report : objects) {
        Row row = sheet.createRow(rowCount++);
        int columnCount = 0;

        createCell(row, columnCount++, report.getOfferId(), style);
        createCell(row, columnCount++, report.getOfferItemId(), style);
        createCell(row, columnCount++, report.getRuleName(), style);
        createCell(row, columnCount++, report.getFareType().name(), style);
        createCell(row, columnCount++, report.getAdjustmentType().name(), style);
        createCell(row, columnCount++, report.getAdjustmentValue(), style);
        createCell(row, columnCount++, report.getIsVisible(), style);
        createCell(row, columnCount++, report.getOfferItemBaseAmount(), style);
        createCell(row, columnCount++, report.getOfferItemTaxAmount(), style);
        createCell(row, columnCount++, report.getOfferItemTotalAmount(), style);
        createCell(row, columnCount++, report.getNewOfferItemBaseAmount(), style);
        createCell(row, columnCount++, report.getNewOfferItemTotalAmount(), style);
        createCell(row, columnCount++, report.getOfferBaseAmount(), style);
        createCell(row, columnCount++, report.getOfferTaxAmount(), style);
        createCell(row, columnCount++, report.getOfferTotalAmount(), style);
        createCell(row, columnCount++, report.getNewOfferBaseAmount(), style);
        createCell(row, columnCount++, report.getNewOfferTotalAmount(), style);
        createCell(row, columnCount++, report.getOfferItemMarkupCalculated(), style);
        createCell(row, columnCount++, report.getOfferMarkupCalculated(), style);

    }
}

createCell方法 #

private void createCell(Row row, int columnCount, Object value, CellStyle style) {
    sheet.autoSizeColumn(columnCount);
    Cell cell = row.createCell(columnCount);
    if (value instanceof Integer) {
        cell.setCellValue((Integer) value);
    } else if (value instanceof Double) {
        cell.setCellValue((Double) value);
    } else if (value instanceof Boolean) {
        cell.setCellValue((Boolean) value);
    } else if (value instanceof BigDecimal) {
        cell.setCellValue(((BigDecimal) value).doubleValue());
    } else {
        cell.setCellValue((String) value);
    }
    cell.setCellStyle(style);
}

Reporter类 #

public class PricingReporter {

   private XSSFWorkbook workbook;
   private XSSFSheet sheet;
   private List<PricingCalculationReport> objects;

   public PricingReporter(List<PricingCalculationReport> objects) {
       this.objects = objects;
       workbook = new XSSFWorkbook();
   }
}

优化建议 #

• 移除循环内的autoSizeColumn调用：createCell中每次调用sheet.autoSizeColumn(columnCount)是性能瓶颈核心。自动列宽计算需要遍历列内所有单元格内容，循环内重复调用会导致O(n²)级别的耗时。应在所有数据写入完成后，统一遍历所有列调用一次autoSizeColumn。
• 切换为SXSSF流式处理：替换XSSFWorkbook为SXSSFWorkbook，XSSFSheet为SXSSFSheet。SXSSF会将超出内存阈值的行刷入临时文件，大幅降低内存占用并提升写入速度，适合处理中等规模数据。注意使用后需调用dispose()清理临时文件。
• 关闭debug级别的JSON序列化日志：控制器中log.debug(objectMapper.writeValueAsString(report))会序列化1008条记录为JSON，这会消耗大量CPU和IO资源，生产环境建议关闭该日志输出。
• 优化资源释放逻辑：使用try-with-resources自动管理流资源，避免手动关闭可能出现的泄漏，同时处理SXSSF的临时文件清理：

  try (ServletOutputStream outputStream = response.getOutputStream()) {
      workbook.write(outputStream);
  } finally {
      if (workbook instanceof SXSSFWorkbook) {
          ((SXSSFWorkbook) workbook).dispose();
      }
      workbook.close();
  }
  

• 复用单元格样式：当前代码已在循环外创建CellStyle和Font，保持此做法，避免在循环内重复创建样式（POI对样式数量有限制，且重复创建会消耗资源）。
• 提前处理BigDecimal转换：如果PricingCalculationReport中的BigDecimal字段频繁转换为Double，可在数据预处理阶段提前转换并缓存结果，减少循环内的重复计算。

内容的提问来源于stack exchange，提问作者Sikandar Sahab