How to Efficiently Import and Export Millions of Rows with POI and EasyExcel

Background

Java projects often need to import data from Excel to a database or export data from a database to Excel. The classic Apache POI approach suffers from row‑count limits and can cause out‑of‑memory errors when handling large volumes.

POI Workbook Implementations

HSSFWorkbook

Supports the legacy .xls format (Excel 97‑2003).

Maximum of 65,535 rows per sheet.

All data is kept in memory, which is acceptable for small datasets.

XSSFWorkbook

Handles the newer .xlsx format (Excel 2007+).

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

All data, styles and formulas are stored in memory, which may lead to out‑of‑memory errors for very large exports.

SXSSFWorkbook

Introduced in POI 3.8, based on XSSF but writes data to temporary files on disk.

Low memory footprint, suitable for exporting millions of rows.

Limitations: flushed rows cannot be accessed, sheet.clone() is unsupported, formula evaluation is disabled, and sheet headers cannot be modified after flushing.

Choosing the Right Workbook

If rows ≤ 70,000 and no complex styling, use HSSFWorkbook or XSSFWorkbook.

If rows > 70,000 and no styling/formulas are required, prefer SXSSFWorkbook for its low memory usage.

If rows > 70,000 and styling/formulas are needed, use XSSFWorkbook with batch writing to avoid memory spikes.

Problems When Handling Millions of Rows

Memory overflow with traditional POI.

Full‑table queries (e.g., SELECT * FROM table) become very slow.

Single‑sheet Excel files become unwieldy and slow to open.

Frequent I/O during row‑by‑row writes degrades performance.

Inserting rows one‑by‑one via MyBatis is extremely slow.

Solution Overview

Adopt Alibaba's EasyExcel library for fast, low‑memory Excel read/write.

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

Write to multiple sheets, each limited to 1 M rows.

For imports, read Excel in batches and batch‑insert into the database using plain JDBC with manual transaction control.

EasyExcel Repository

https://github.com/alibaba/easyexcel

Export Implementation (EasyExcel)

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<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("onlineseqid", "businessid", "becifno", "ivisresult", "createdby", "createddate", "updateby", "updateddate", "risklevel"));
        table.setHead(titles);
        int totalCount = actResultLogMapper.findActResultLogByCondations(3000001);
        int sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT; // 1,000,000
        int writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT; // 200,000
        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<List<String>> dataList = new ArrayList<>();
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = new Sheet(i, 0);
            sheet.setSheetName("TestSheet" + i);
            int writeCount = (i != sheetNum - 1) ? oneSheetWriteCount : lastSheetWriteCount;
            for (int j = 0; j < writeCount; j++) {
                dataList.clear();
                PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                List<ActResultLog> resultList = actResultLogMapper.findByPage100w();
                if (!CollectionUtils.isEmpty(resultList)) {
                    for (ActResultLog item : resultList) {
                        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 duration: " + (endTime - startTime) / 1000 + " seconds");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (outputStream != null) {
            try { outputStream.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}

Export Test Results

Exporting 3 million rows to an .xlsx file took about 2 minutes 15 seconds. The generated file size was approximately 163 MB. No cell styles or formulas were applied.

Import Implementation (EasyExcel + JDBC)

// Listener for reading Excel rows
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    private ActResultLogService2 actResultLogService2;
    private List<Map<Integer, String>> dataList = new ArrayList<>();
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        dataList.add(data);
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            saveData();
            dataList.clear();
        }
    }
    private void saveData() {
        actResultLogService2.import2DBFromExcel10w(dataList);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

// JDBC batch insert service
public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
    Map<String, Object> result = new HashMap<>();
    if (dataList.isEmpty()) {
        result.put("empty", "0000");
        return result;
    }
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long startTime = 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<Integer, String> 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 endTime = System.currentTimeMillis();
        System.out.println(dataList.size() + " rows imported in " + (endTime - startTime) + " ms");
        result.put("success", "1111");
    } catch (Exception e) {
        result.put("exception", "0000");
        e.printStackTrace();
    } finally {
        JDBCDruidUtils.close(conn, ps);
    }
    return result;
}

Import Test Results

Reading 3 million rows with EasyExcel took approximately 82.9 seconds. Inserting the same amount using JDBC batch + manual transaction required about 8.2 seconds (average 200 k rows per batch).

Conclusion

Combining EasyExcel for low‑memory Excel processing with batch database queries and JDBC batch inserts provides a practical, high‑performance solution for massive data import/export tasks in Java backend applications.