Efficient Massive Excel Import/Export with POI and EasyExcel in Java

Introduction

The author encountered the need to import and export very large data sets (hundreds of thousands to millions of rows) between Excel files and a relational database, and shares a solution that avoids memory overflow and improves performance.

Traditional POI Versions and Their Pros/Cons

Apache POI provides three main workbook implementations, each targeting different Excel versions and having distinct limitations.

HSSFWorkbook

Supports Excel 2003 (xls) format.

Maximum of 65,535 rows per sheet.

Does not cause memory overflow for small data sets because data is kept in memory.

XSSFWorkbook

Supports Excel 2007+ (xlsx) format.

Allows up to 1,048,576 rows per sheet.

Memory consumption grows quickly; large data sets may cause OutOfMemory errors.

SXSSFWorkbook

Introduced in POI 3.8, based on XSSF with low‑memory buffering.

Writes data to temporary files on disk, preventing memory overflow.

Supports large files (millions of rows) but cannot use certain features such as sheet.clone(), formula evaluation, or dynamic header changes.

Choosing the Appropriate Workbook

Guidelines based on data volume and required Excel features:

If rows ≤ 70,000, use HSSFWorkbook or XSSFWorkbook .

If rows > 70,000 and no styling/formula needs, use SXSSFWorkbook .

If rows > 70,000 and styling/formula is required, use XSSFWorkbook with batch writes.

Exporting Millions of Rows

The solution splits the export into three steps: batch database queries, batch writes to Excel, and sheet rotation when a sheet reaches a predefined row limit (e.g., 1,000,000 rows). EasyExcel is used for the actual file generation.

// Export logic example
public void dataExport300w(HttpServletResponse response) {
    OutputStream outputStream = null;
    try {
        long startTime = System.currentTimeMillis();
        outputStream = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
        String fileName = new String("excel100w".getBytes(), "UTF-8");
        Table table = new Table(1);
        List
> titles = new ArrayList<>();
        titles.add(Arrays.asList("onlineseqid"));
        // ... other column titles ...
        table.setHead(titles);
        int totalCount = actResultLogMapper.findActResultLogByCondations(3000001);
        int sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
        int writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
        int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
        int oneSheetWriteCount = sheetDataRows / writeDataRows;
        int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount :
                (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
        List
> dataList = new ArrayList<>();
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = new Sheet(i, 0);
            sheet.setSheetName("测试Sheet1" + i);
            for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                dataList.clear();
                PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                List
resultList = actResultLogMapper.findByPage100w();
                if (!CollectionUtils.isEmpty(resultList)) {
                    resultList.forEach(item -> dataList.add(Arrays.asList(
                        item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(),
                        item.getIvisresult(), item.getCreatedby(),
                        Calendar.getInstance().getTime().toString(),
                        item.getUpdateby(), Calendar.getInstance().getTime().toString(),
                        item.getRisklevel())));
                }
                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");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        outputStream.flush();
        long endTime = System.currentTimeMillis();
        System.out.println("Export time:" + (endTime - startTime) / 1000 + " seconds");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (outputStream != null) {
            try { outputStream.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}

Importing Millions of Rows

For import, the author uses EasyExcel's read listener to batch rows into a list, then inserts them into the database with JDBC batch statements inside a manual transaction.

// Import logic example
public void import2DBFromExcel10w(List
> dataList) {
    if (dataList.isEmpty()) return;
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCDruidUtils.getConnection();
        conn.setAutoCommit(false);
        String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values (?,?,?,?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        for (Map
item : dataList) {
            ps.setString(1, item.get(0));
            ps.setString(2, item.get(1));
            ps.setString(3, item.get(2));
            ps.setString(4, item.get(3));
            ps.setString(5, item.get(4));
            ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            ps.setString(7, item.get(6));
            ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
            ps.setString(9, item.get(8));
            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("Inserted " + dataList.size() + " rows in " + (end - start) + " ms");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCDruidUtils.close(conn, ps);
    }
}

// EasyExcel listener example
public class EasyExceGeneralDatalListener extends AnalysisEventListener
> {
    private ActResultLogService2 service;
    private List
> buffer = new ArrayList<>();
    @Override
    public void invoke(Map
data, AnalysisContext context) {
        buffer.add(data);
        if (buffer.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            service.import2DBFromExcel10w(buffer);
            buffer.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!buffer.isEmpty()) {
            service.import2DBFromExcel10w(buffer);
            buffer.clear();
        }
    }
}

Performance Results

Exporting 3,000,000 rows to Excel took about 2 minutes 15 seconds (≈135 seconds) using the described batch approach. Importing the same amount of data with JDBC batch + transaction took roughly 8.2 seconds, while EasyExcel read time was about 82.9 seconds.

Conclusion

The combination of POI/SXSSF for low‑memory writing and EasyExcel for fast reading, together with proper paging, batch processing, and manual transaction control, enables reliable handling of massive Excel‑DB import/export tasks in Java backend applications.