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

Prospects

In project development data import from Excel to DB and export from DB to Excel are common. The author faced large‑scale data import/export and decided to solve it.

Traditional POI version comparison

POI provides three Workbook implementations:

HSSFWorkbook – works with .xls (Excel 2003 and earlier), limited to 65,535 rows, but does not cause memory overflow for small data.

XSSFWorkbook – works with .xlsx (Excel 2007+), supports up to 1,048,576 rows, but stores the whole workbook in memory, leading to possible OutOfMemoryError.

SXSSFWorkbook – introduced in POI 3.8, writes data to temporary files, supports large files with low memory consumption, but cannot access persisted rows, does not support sheet.clone(), formula evaluation, or header modification.

Choosing the appropriate Workbook

Guidelines:

If data is under 70,000 rows, HSSFWorkbook or XSSFWorkbook works.

If data exceeds 70,000 rows and no Excel styling or formulas are needed, use SXSSFWorkbook.

If styling or formulas are required for large data, use XSSFWorkbook with batch processing.

Million‑row import/export (the real challenge)

The author needed to export and import 3 million rows without running out of memory.

Problems

Traditional POI would cause memory overflow and low efficiency.

Full‑table SELECT is too slow.

Writing all rows into a single sheet is inefficient.

Row‑by‑row I/O is unacceptable.

Inserting rows one by one into DB is too slow.

MyBatis batch insert is essentially a loop and also slow.

Solution

Use Alibaba’s EasyExcel library together with batch query, sheet splitting, and JDBC batch insert with manual transaction control.

Export: query 200 k rows per batch, write to a sheet until 1 million rows are reached, then create a new sheet; repeat until all data are written.

Import: read Excel in batches (e.g., 200 k rows), store rows in a collection, and insert the collection into DB using JDBC batch and a transaction.

Key code snippets

public void dataExport300w(HttpServletResponse response) { ... }

// EasyExcel read listener
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer,String>> { ... }

// JDBC batch insert example
String sql = "insert into ACT_RESULT_LOG (...) values (?,?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (Map<Integer,String> item : dataList) {
    ps.setString(1, item.get(0));
    // set other parameters …
    ps.addBatch();
}
ps.executeBatch();
conn.commit();

Performance results

Exporting 3 million rows took about 2 minutes 15 seconds (≈135 s) with EasyExcel. Importing the same amount took roughly 8 seconds for the JDBC batch part and about 83 seconds for reading, totaling ~91 seconds.

Conclusion

EasyExcel simplifies large‑scale Excel import/export, avoids memory overflow, and combined with JDBC batch operations provides acceptable performance for millions of records.