Handling Large-Scale Excel Import/Export with POI and EasyExcel in Java

In many projects data needs to be imported from Excel to a database or exported from a database to Excel. Traditional Apache POI approaches can cause memory overflow or performance bottlenecks when handling large volumes.

1. Comparison of Traditional POI Versions

POI provides three main workbook implementations:

HSSFWorkbook : works with Excel 2003 (.xls) files, limited to 65,535 rows; low memory usage but cannot handle more rows.

XSSFWorkbook : works with Excel 2007+ (.xlsx) files, supports up to 1,048,576 rows; higher memory consumption.

SXSSFWorkbook : introduced in POI 3.8, streams data to disk to keep memory low, suitable for very large files but does not support some features such as sheet.clone(), formula evaluation, or modifying headers after writing.

Choosing the right implementation

If the data size is under 70,000 rows, HSSFWorkbook or XSSFWorkbook is sufficient. For data larger than 70,000 rows without needing complex styling or formulas, SXSSFWorkbook is recommended. When styling, formulas, or header manipulation are required for large data, use XSSFWorkbook together with batch writes.

2. Large‑Scale Export (300 W rows)

The author uses EasyExcel, an Alibaba library that simplifies POI usage. The export logic performs the following steps:

Query the database in batches (e.g., 200 k rows per batch).

Write each batch to a sheet; when a sheet reaches 1 M rows, start a new sheet.

Use EasyExcel's ExcelWriter to stream data directly to the HTTP response.

Key export code (kept unchanged):

public void dataExport300w(HttpServletResponse response) {
    OutputStream outputStream = null;
    try {
        long startTime = System.currentTimeMillis();
        outputStream = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
        // ... set table head ...
        int totalCount = actResultLogMapper.findActResultLogByCondations(3000001);
        int sheetNum = totalCount % ExcelConstants.PER_SHEET_ROW_COUNT == 0 ?
                totalCount / ExcelConstants.PER_SHEET_ROW_COUNT :
                totalCount / ExcelConstants.PER_SHEET_ROW_COUNT + 1;
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = new Sheet(i, 0);
            sheet.setSheetName("Sheet" + i);
            int writeCount = (i != sheetNum - 1) ?
                    ExcelConstants.PER_SHEET_ROW_COUNT / ExcelConstants.PER_WRITE_ROW_COUNT :
                    // calculate last sheet write count
                    ...;
            for (int j = 0; j < writeCount; j++) {
                List
> dataList = new ArrayList<>();
                PageHelper.startPage(j + 1 + ExcelConstants.PER_WRITE_ROW_COUNT * i, ExcelConstants.PER_WRITE_ROW_COUNT);
                List
resultList = actResultLogMapper.findByPage100w();
                for (ActResultLog item : resultList) {
                    dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), ...));
                }
                writer.write0(dataList, sheet, table);
            }
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
        response.setContentType("multipart/form-data");
        writer.finish();
        outputStream.flush();
        long endTime = System.currentTimeMillis();
        System.out.println("Export time:" + (endTime - startTime) / 1000 + "s");
    } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream != null) outputStream.close(); }
}

Testing on a local machine showed that exporting 300 W rows took about 2 minutes 15 seconds, and the generated file was ~163 MB.

3. Large‑Scale Import (300 W rows)

Import uses EasyExcel's read API with a custom AnalysisEventListener that batches rows into a list and inserts them into the database via JDBC batch statements inside a manual transaction.

Key import listener code:

public class EasyExceGeneralDatalListener extends AnalysisEventListener
> {
    private ActResultLogService2 service;
    private List
> dataList = new ArrayList<>();
    public EasyExceGeneralDatalListener(ActResultLogService2 service) { this.service = service; }
    @Override
    public void invoke(Map
data, AnalysisContext context) {
        dataList.add(data);
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            saveData();
            dataList.clear();
        }
    }
    private void saveData() { service.import2DBFromExcel10w(dataList); }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) { saveData(); }
}

The service method builds a JDBC PreparedStatement , adds each row to the batch, executes the batch, and commits the transaction:

public Map
import2DBFromExcel10w(List
> dataList) {
    Connection conn = JDBCDruidUtils.getConnection();
    conn.setAutoCommit(false);
    String sql = "INSERT INTO ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) VALUES (?,?,?,?,?,?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    for (Map
row : dataList) {
        ps.setString(1, row.get(0));
        ps.setString(2, row.get(1));
        // ... set other parameters ...
        ps.addBatch();
    }
    ps.executeBatch();
    conn.commit();
    JDBCDruidUtils.close(conn, ps);
    return Collections.singletonMap("success", "1111");
}

Performance results: reading 300 W rows took ~82.9 seconds, while batch inserting the same amount took only ~8.2 seconds, demonstrating that the combination of EasyExcel streaming and JDBC batch inserts can handle massive data efficiently.

4. Conclusion

The article shows that for data import/export tasks involving hundreds of thousands to millions of rows, using POI's streaming workbook ( SXSSFWorkbook ) or the higher‑level EasyExcel library together with batch database operations avoids memory overflow and dramatically reduces execution time. The presented code can be adapted to other backend services that require high‑throughput Excel processing.