使用Apache POI写入大Excel致CPU过高，求低资源占用方案

问题描述 #

需写入约50万条、25列的数据至Excel文件，采用Apache POI的SXSSFWorkbook流式工作簿实现，但执行workbook.write(fileOutputStream)时出现CPU峰值，本地及Kubernetes部署的应用均受影响，K8s应用因触达CPU资源限制（配置2042Mi内存、1024m CPU）重启。业务要求必须使用Excel格式，无法改用CSV，现寻求低资源占用的高效写入方案。

当前使用代码如下：

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import com.king.medicalcollege.model.Medico;

@Service
public class ExcelWriterService {

    // file is an empty file already created
    // Large List around 500K records of medico data [Medico is POJO]

    public File writeData(File file, List<Medico> medicos) {

        SXSSFWorkbook sxssfWorkbook = null;
        try (SXSSFWorkbook workbook = sxssfWorkbook = new SXSSFWorkbook(1);
                FileOutputStream fileOutputStream = new FileOutputStream(file)) {

            Sheet sheet = workbook.createSheet();
            CellStyle cellStyle = workbook.createCellStyle();
            int rowNum = 0;
            for (Medico medico : medicos) {
                Row row = sheet.createRow(rowNum);
                //just adding POJO values (25 fields)  into ROW 
                addDataInRow(medico, row, cellStyle);
                rowNum++;
            }

            //workbook.write causing CPU spike
            workbook.write(fileOutputStream);

            workbook.dispose();

        } catch (Exception exception) {
            return null;
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.dispose();
            }
        }

        return file;
    }

    private void addDataInRow(Medico medico, Row row, CellStyle cellStyle) {
        Cell cell_0 = row.createCell(0);
        cell_0.setCellValue(medico.getFirstName());
        cell_0.setCellStyle(cellStyle);
        
        Cell cell_1 = row.createCell(1);
        cell_1.setCellValue(medico.getMiddleName());
        cell_1.setCellStyle(cellStyle);
        
        Cell cell_2 = row.createCell(2);
        cell_2.setCellValue(medico.getLastName());
        cell_2.setCellStyle(cellStyle);
        
        Cell cell_3 = row.createCell(2);
        cell_3.setCellValue(medico.getFirstName());
        cell_3.setCellStyle(cellStyle);
        
        //...... around 25 columns will be added like this
    }
}

优化方案 #

1. 调整SXSSFWorkbook内存窗口大小 #

当前代码设置new SXSSFWorkbook(1)，仅在内存保留1行，频繁磁盘刷写会大幅增加CPU开销。建议调整为合理的批量值（如1000或5000），平衡内存占用与磁盘IO频率：

SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // 内存保留1000行，达到阈值后刷写到磁盘

2. 修复代码错误并简化单元格创建逻辑 #

原addDataInRow方法中存在列索引重复（cell_3使用索引2覆盖cell_2）的错误，需先修正索引。同时可通过预定义字段数组循环创建单元格，减少重复代码提升效率：

private void addDataInRow(Medico medico, Row row, CellStyle cellStyle) {
    Object[] values = {
        medico.getFirstName(),
        medico.getMiddleName(),
        medico.getLastName(),
        // ... 补充剩余22个字段
    };
    
    for (int i = 0; i < values.length; i++) {
        Cell cell = row.createCell(i);
        Object value = values[i];
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        }
        cell.setCellStyle(cellStyle);
    }
}

3. 减少CellStyle重复设置开销 #

若所有单元格样式一致，可直接为Sheet设置默认列样式，避免逐个单元格调用setCellStyle：

// 创建Sheet后统一设置默认样式
Sheet sheet = workbook.createSheet();
CellStyle cellStyle = workbook.createCellStyle();
for (int i = 0; i < 25; i++) {
    sheet.setDefaultColumnStyle(i, cellStyle);
}
// 后续创建单元格无需再单独设置样式

4. 使用缓冲流优化写入效率 #

直接使用FileOutputStream写入磁盘效率较低，改用BufferedOutputStream减少磁盘IO次数：

try (SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
     BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file))) {
    // ... 数据写入逻辑
    workbook.write(bos);
}

5. 异步执行写入任务 #

将Excel写入操作放到异步线程中执行，避免阻塞主线程。利用Spring的@Async注解实现异步：

@Service
public class ExcelWriterService {
    @Async
    public CompletableFuture<File> writeDataAsync(File file, List<Medico> medicos) {
        try {
            // ... 原写入逻辑
            return CompletableFuture.completedFuture(file);
        } catch (Exception e) {
            return CompletableFuture.failedFuture(e);
        }
    }
}

6. 替换为更高效的Excel库 #

若POI的SXSSF性能仍无法满足需求，可替换为阿里的EasyExcel——它基于POI做了深度优化，流式写入时内存与CPU占用更低：

// EasyExcel示例代码
public File writeDataWithEasyExcel(File file, List<Medico> medicos) {
    EasyExcel.write(file, Medico.class)
             .sheet("Medico Data")
             .doWrite(medicos);
    return file;
}

需添加EasyExcel依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

关键优化点总结 #

• 调整SXSSF内存窗口，平衡内存占用与磁盘IO
• 修复代码错误，简化单元格创建逻辑
• 减少样式设置的重复开销
• 使用缓冲流提升磁盘写入效率
• 异步执行避免阻塞业务线程
• 考虑替换为轻量高效的EasyExcel库

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