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

1. Comparison of Traditional POI Versions

Describes HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook, their supported Excel versions, and their advantages and disadvantages regarding row limits and memory usage.

HSSFWorkbook

Supports .xls files up to Excel 2003, maximum 65,535 rows.

Advantage: does not cause memory overflow for small datasets.

XSSFWorkbook

Supports .xlsx files from Excel 2007 onward, up to 1,048,576 rows.

Disadvantage: keeps all data in memory, which can lead to out‑of‑memory errors for large datasets.

SXSSFWorkbook

Streaming version introduced in POI 3.8 that writes data to disk to avoid OOM.

Advantages: can handle millions of rows with low memory consumption.

Disadvantages: persisted data cannot be accessed, sheet.clone is unsupported, formula evaluation is disabled, and header modifications are not allowed.

2. Choosing the Appropriate Workbook

Guidelines based on data volume and required Excel features.

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

If rows exceed 70,000 and no styling or formulas are needed, use SXSSFWorkbook.

If rows exceed 70,000 and styling/formulas are required, use XSSFWorkbook with batch processing.

3. Exporting and Importing Hundreds of Thousands of Records

Problem analysis and solution steps for massive data handling.

Traditional POI causes memory overflow and low efficiency for huge data.

Use batch queries and batch writes to Excel.

Split data across multiple sheets (1 M rows per sheet).

Avoid line‑by‑line I/O operations.

Batch insert into the database using JDBC with manual transaction control.

3.1 Export Example with EasyExcel

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

CREATE TABLE dept(
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT "",
    loc VARCHAR(13) NOT NULL DEFAULT ""
);

CREATE TABLE emp(
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    ename VARCHAR(20) NOT NULL DEFAULT "",
    job VARCHAR(9) NOT NULL DEFAULT "",
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2) NOT NULL,
    comm DECIMAL(7,2) NOT NULL,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);
... (additional test data and functions) ...

@Resource
private EmpService empService;

@GetMapping("/export")
public void export() throws IOException {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    empService.export();
    stopWatch.stop();
    System.out.println("Total time: " + stopWatch.getTotalTimeSeconds() + "s");
}

public void export() throws IOException {
    OutputStream outputStream = null;
    try {
        Integer totalCount = empMapper.selectCount(null);
        Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT; // 1,000,000
        Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT; // 200,000
        Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
        Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
        Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        outputStream = response.getOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        for (int i = 0; i < sheetNum; i++) {
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetName("TestSheet" + i);
            sheet.setSheetNo(i);
            for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);
                List<Emp> empList = page.getRecords();
                List<EmpVo> empVoList = new ArrayList<>();
                for (Emp emp : empList) {
                    EmpVo empVo = new EmpVo();
                    BeanUtils.copyProperties(emp, empVo);
                    empVoList.add(empVo);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "Employee" + (i + 1)).head(EmpVo.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(empVoList, writeSheet);
            }
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("EmployeeInfo", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
    } catch (IOException | BeansException e) {
        e.printStackTrace();
    } finally {
        if (outputStream != null) {
            outputStream.close();
        }
    }
}

3.2 Import Example with EasyExcel

@Resource
private EmpService empService;

@GetMapping("/importData")
public void importData() {
    String fileName = "C:\\Users\\asus\\Desktop\\员工信息.xlsx";
    long startReadTime = System.currentTimeMillis();
    System.out.println("Start reading Excel: " + startReadTime + "ms");
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("End reading Excel: " + endReadTime + "ms");
    System.out.println("Total read time: " + (endReadTime - startReadTime) + "ms");
}

public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    private EmpService empService;
    private List<Map<Integer, String>> dataList = new ArrayList<>();
    public EasyExceGeneralDatalListener() {}
    public EasyExceGeneralDatalListener(EmpService empService) { this.empService = empService; }
    @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() { empService.importData(dataList); dataList.clear(); }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) { saveData(); dataList.clear(); }
}

public interface EmpService {
    void export() throws IOException;
    void importData(List<Map<Integer, String>> dataList);
}

public void importData(List<Map<Integer, String>> dataList) {
    if (dataList.isEmpty()) return;
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long startTime = System.currentTimeMillis();
        System.out.println(dataList.size() + " rows start import at " + startTime + "ms");
        conn = JDBCDruidUtils.getConnection();
        conn.setAutoCommit(false);
        String sql = "insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (?,?,?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < dataList.size(); i++) {
            Map<Integer, String> item = dataList.get(i);
            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.setString(6, item.get(5));
            ps.setString(7, item.get(6));
            ps.setString(8, item.get(7));
            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();
        long endTime = System.currentTimeMillis();
        System.out.println(dataList.size() + " rows end import at " + endTime + "ms");
        System.out.println(dataList.size() + " rows duration: " + (endTime - startTime) + "ms");
    } catch (Exception e) { e.printStackTrace(); }
    finally { JDBCDruidUtils.close(conn, ps); }
}

public class JDBCDruidUtils {
    private static DataSource dataSource;
    static {
        Properties pro = new Properties();
        try {
            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) { e.printStackTrace(); }
    }
    public static Connection getConnection() throws SQLException { return dataSource.getConnection(); }
    public static void close(Connection connection, Statement statement) {
        if (connection != null) try { connection.close(); } catch (SQLException e) { e.printStackTrace(); }
        if (statement != null) try { statement.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    public static DataSource getDataSource() { return dataSource; }
}

# druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/llp?autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username=root
password=root
initialSize=10
maxActive=50
maxWait=60000

Performance tests on a local machine show exporting 5 million rows takes about 400 seconds, while importing the same amount takes roughly 200 seconds.

4. Summary

Large‑scale import/export consumes significant memory; limit concurrent users in production.

When importing massive data, use JDBC with manual transaction and batch commit for optimal speed.