BestHub
Sign in
Backend Development 21 min read

How to Efficiently Export Millions of Records to Excel with Alibaba EasyExcel

This article explains why traditional Excel export methods cause memory overflow, introduces Alibaba's EasyExcel library that keeps memory usage in kilobytes, and provides step‑by‑step Maven setup, POJO definitions, and code examples for exporting small, medium, and massive data sets in a Spring Boot environment.

Java Architect Essentials
Java Architect Essentials
Java Architect Essentials
How to Efficiently Export Millions of Records to Excel with Alibaba EasyExcel

Introduction

Exporting large datasets from backend management systems often leads to memory overflow and page freezes. A custom export utility that used batch queries and SXSSFWorkbook still suffered from high memory consumption, empty loops, and data loss. Alibaba's open‑source EasyExcel library limits Excel parsing memory to the kilobyte level, guarantees no out‑of‑memory errors, and can export about 1 000 000 rows with a dozen columns in roughly 70 seconds.

EasyExcel repository: https://github.com/alibaba/easyexcel

Example

Maven Dependency

<!-- Alibaba EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.1</version>
</dependency>

POJO Definition

package com.authorization.privilege.excel;

import java.util.Date;

public class User {
    private String uid;
    private String name;
    private Integer age;
    private Date birthday;

    public User() {}

    public User(String uid, String name, Integer age, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    // getters and setters omitted for brevity
}

Test Cases for Different Data Volumes

Small dataset (≤200 000 rows) – single sheet, one‑time query

@Test
public void writeExcelOneSheetOnceWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:/temp/withoutHead1.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);

    List<List<String>> userList = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
    }
    writer.write0(userList, sheet, table);
    writer.finish();
}

Medium dataset (≤1 000 000 rows) – single sheet, batch queries

@Test
public void writeExcelOneSheetMoreWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:/temp/withoutHead2.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);

    int totalRowCount = 50; // example total rows
    int pageSize = 20;
    int writeCount = totalRowCount % pageSize == 0 ? totalRowCount / pageSize : totalRowCount / pageSize + 1;
    for (int i = 0; i < writeCount; i++) {
        List<List<String>> userList = new ArrayList<>();
        if (i < writeCount - 1) {
            for (int j = 0; j < pageSize; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
        } else {
            int lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
            for (int j = 0; j < lastWriteRowCount; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
        }
        writer.write0(userList, sheet, table);
    }
    writer.finish();
}

Large dataset (several million rows) – multiple sheets, batch queries

@Test
public void writeExcelMoreSheetMoreWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:/temp/withoutHead3.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    String sheetName = "测试SHEET";
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);

    int totalRowCount = 250;
    int perSheetRowCount = 100;
    int pageSize = 20;
    int sheetCount = totalRowCount % perSheetRowCount == 0 ? totalRowCount / perSheetRowCount : totalRowCount / perSheetRowCount + 1;
    int previousSheetWriteCount = perSheetRowCount / pageSize;
    int lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : totalRowCount % perSheetRowCount / pageSize + 1);

    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = new Sheet(i, 0);
        sheet.setSheetName(sheetName + i);
        if (i < sheetCount - 1) {
            for (int j = 0; j < previousSheetWriteCount; j++) {
                List<List<String>> userList = new ArrayList<>();
                for (int k = 0; k < 20; k++) {
                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                }
                writer.write0(userList, sheet, table);
            }
        } else {
            for (int j = 0; j < lastSheetWriteCount; j++) {
                List<List<String>> userList = new ArrayList<>();
                if (j < lastSheetWriteCount - 1) {
                    for (int k = 0; k < 20; k++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                    }
                } else {
                    int lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
                    for (int k = 0; k < lastWriteRowCount; k++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
                    }
                }
                writer.write0(userList, sheet, table);
            }
        }
    }
    writer.finish();
}

Production Code

Excel Constant Class

package com.authorization.privilege.constant;

public class ExcelConstant {
    /** Each sheet stores up to 1,000,000 rows */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    /** Number of rows written per batch (query page size) */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

Export for Small Datasets (≤200 000 rows)

@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        String fileName = new String("SystemExcel".getBytes(), "UTF-8");
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("系统列表sheet1");
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("系统名称"));
        titles.add(Arrays.asList("系统标识"));
        titles.add(Arrays.asList("描述"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创建时间"));
        table.setHead(titles);

        List<List<String>> dataList = new ArrayList<>();
        List<SysSystemVO> sysSystemVOList = sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
        if (!CollectionUtils.isEmpty(sysSystemVOList)) {
            sysSystemVOList.forEach(each -> dataList.add(Arrays.asList(
                each.getSystemName(),
                each.getSystemKey(),
                each.getDescription(),
                each.getState().toString(),
                each.getCreateUid(),
                each.getCreateTime().toString()
            )));
        }
        writer.write0(dataList, sheet, table);
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();
    } finally {
        if (out != null) {
            try { out.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
    return ResultVO.getSuccess("导出系统列表EXCEL成功");
}

Export for Medium Datasets (≤1 000 000 rows) – single sheet, paged queries

@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        String fileName = new String("SystemExcel".getBytes(), "UTF-8");
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("系统列表sheet1");
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("系统名称"));
        titles.add(Arrays.asList("系统标识"));
        titles.add(Arrays.asList("描述"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创建时间"));
        table.setHead(titles);

        int totalRowCount = sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        int writeCount = totalRowCount % pageSize == 0 ? totalRowCount / pageSize : totalRowCount / pageSize + 1;
        for (int i = 0; i < writeCount; i++) {
            List<List<String>> dataList = new ArrayList<>();
            PageHelper.startPage(i + 1, pageSize);
            List<SysSystemVO> list = sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
            if (!CollectionUtils.isEmpty(list)) {
                list.forEach(each -> dataList.add(Arrays.asList(
                    each.getSystemName(),
                    each.getSystemKey(),
                    each.getDescription(),
                    each.getState().toString(),
                    each.getCreateUid(),
                    each.getCreateTime().toString()
                )));
            }
            writer.write0(dataList, sheet, table);
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();
    } finally {
        if (out != null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); } }
    }
    return ResultVO.getSuccess("导出系统列表EXCEL成功");
}

Export for Large Datasets (several million rows) – multiple sheets, paged queries

@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        String fileName = new String("SystemExcel".getBytes(), "UTF-8");
        String sheetName = "系统列表sheet";
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("系统名称"));
        titles.add(Arrays.asList("系统标识"));
        titles.add(Arrays.asList("描述"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创建时间"));
        table.setHead(titles);

        int totalRowCount = sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        int perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;
        int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        int sheetCount = totalRowCount % perSheetRowCount == 0 ? totalRowCount / perSheetRowCount : totalRowCount / perSheetRowCount + 1;
        int previousSheetWriteCount = perSheetRowCount / pageSize;
        int lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : totalRowCount % perSheetRowCount / pageSize + 1);

        for (int i = 0; i < sheetCount; i++) {
            Sheet sheet = new Sheet(i, 0);
            sheet.setSheetName(sheetName + i);
            int currentWriteCount = (i < sheetCount - 1) ? previousSheetWriteCount : lastSheetWriteCount;
            for (int j = 0; j < currentWriteCount; j++) {
                List<List<String>> dataList = new ArrayList<>();
                PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);
                List<SysSystemVO> list = sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                if (!CollectionUtils.isEmpty(list)) {
                    list.forEach(each -> dataList.add(Arrays.asList(
                        each.getSystemName(),
                        each.getSystemKey(),
                        each.getDescription(),
                        each.getState().toString(),
                        each.getCreateUid(),
                        each.getCreateTime().toString()
                    )));
                }
                writer.write0(dataList, sheet, table);
            }
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();
    } finally {
        if (out != null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); } }
    }
    return ResultVO.getSuccess("导出系统列表EXCEL成功");
}

Summary

In a synthetic test with 1 000 000 rows and 18 columns, the export completed in about 70 seconds. Real‑world performance depends on the efficiency of the underlying SQL queries; faster queries lead to quicker exports.

Key principle: Reduce the number of SQL queries to achieve faster export.

Counting rows with SELECT COUNT(1) on very large tables can be slow; adjusting MySQL buffer pool parameters may improve performance. When using PageHelper, the execution time of different LIMIT offsets (e.g., limit 0,200000, limit 200000,400000, …) varies and may require further investigation.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

JavaperformanceSpring Booteasyexcelexcel-exportLarge Data
Java Architect Essentials
Written by

Java Architect Essentials

Committed to sharing quality articles and tutorials to help Java programmers progress from junior to mid-level to senior architect. We curate high-quality learning resources, interview questions, videos, and projects from across the internet to help you systematically improve your Java architecture skills. Follow and reply '1024' to get Java programming resources. Learn together, grow together.

0 followers
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.

Sign in to comment