还在用 POI？试试 EasyExcel，轻松导出 100W 数据，不卡死，好用到爆！-阿里云开发者社区

一. 简介

导出是后台管理系统的常用功能，当数据量特别大的时候会内存溢出和卡顿页面，曾经自己封装过一个导出，采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。

不过一是存在封装不太友好使用不方便的问题，二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题，以及存在内存溢出的隐患。

无意间查询到阿里开源的EasyExcel框架，发现可以将解析的EXCEL的内存占用控制在KB级别，并且绝对不会内存溢出（内部实现待研究）,还有就是速度极快，大概100W条记录，十几个字段，只需要70秒即可完成下载。

遂抛弃自己封装的，转战研究阿里开源的EasyExcel. 不过说实话，当时自己封装的那个还是有些技术含量的，例如：外观模式，模板方法模式，以及委托思想，组合思想，可以看看。另外，微信搜索关注Java技术栈，发送：设计模式，可以获取我整理的 Java 设计模式实战教程。

EasyExcel的github地址是：https://github.com/alibaba/easyexcel

二. 案例

2.1 POM依赖

<!-- 阿里开源EXCEL -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.1</version>
</dependency>

2.2 POJO对象

package com.authorization.privilege.excel;
import java.util.Date;
/**
 * @author qjwyss
 * @description
 */
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;
    }
    public String getUid() {
        return uid;
    }
    public void setUid(String uid) {
        this.uid = uid;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

2.3 测试环境

2.3.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

/**
 * 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中
 * 注意： 一次性查询出来的记录数量不宜过大，不会内存溢出即可。
 *
 * @throws IOException
 */
@Test
public void writeExcelOneSheetOnceWrite() throws IOException {
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    // 设置SHEET
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    // 查询数据导出即可 比如说一次性总共查询出100条数据
    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();
}

2.3.2.数据量适中（100W以内）：一个SHEET分批查询导出

/**
 * 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
 * 注意：
 * 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
 * 数据量不能超过一个SHEET存储的最大数据量105W
 *
 * @throws IOException
 */
@Test
public void writeExcelOneSheetMoreWrite() throws IOException {
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    // 设置SHEET
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    // 模拟分批查询：总记录数50条，每次查询20条，  分三次查询 最后一次查询记录数是10
    Integer totalRowCount = 50;
    Integer pageSize = 20;
    Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
    // 注： 此处仅仅为了模拟数据，实用环境不需要将最后一次分开，合成一个即可， 参数为：currentPage = i+1;  pageSize = pageSize
    for (int i = 0; i < writeCount; i++) {
        // 前两次查询 每次查20条数据
        if (i < writeCount - 1) {
            List<List<String>> userList = new ArrayList<>();
            for (int j = 0; j < pageSize; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
            writer.write0(userList, sheet, table);
        } else if (i == writeCount - 1) {
            // 最后一次查询 查多余的10条记录
            List<List<String>> userList = new ArrayList<>();
            Integer 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();
}

2.3.3.数据量很大（几百万都行）：多个SHEET分批查询导出

/**
 * 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中
 * 注意：
 * perSheetRowCount % pageSize要能整除  为了简洁，非整除这块不做处理
 * 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
 *
 * @throws IOException
 */
@Test
public void writeExcelMoreSheetMoreWrite() throws IOException {
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    // 设置SHEET名称
    String sheetName = "测试SHEET";
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    // 模拟分批查询：总记录数250条，每个SHEET存100条，每次查询20条  则生成3个SHEET，前俩个SHEET查询次数为5， 最后一个SHEET查询次数为3 最后一次写的记录数是10
    // 注：该版本为了较少数据判断的复杂度，暂时perSheetRowCount要能够整除pageSize， 不去做过多处理  合理分配查询数据量大小不会内存溢出即可。
    Integer totalRowCount = 250;
    Integer perSheetRowCount = 100;
    Integer pageSize = 20;
    Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
    Integer previousSheetWriteCount = perSheetRowCount / pageSize;
    Integer 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 sheet = new Sheet(i, 0);
        sheet.setSheetName(sheetName + i);
        if (i < sheetCount - 1) {
            // 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行  2个SHEET合计200行  实用环境：参数：currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize
            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 if (i == sheetCount - 1) {
            // 最后一个SHEET 实用环境不需要将最后一次分开，合成一个即可， 参数为：currentPage = i+1;  pageSize = pageSize
            for (int j = 0; j < lastSheetWriteCount; j++) {
                // 前俩次查询 每次查询20条
                if (j < lastSheetWriteCount - 1) {
                    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 if (j == lastSheetWriteCount - 1) {
                    // 最后一次查询 将剩余的10条查询出来
                    List<List<String>> userList = new ArrayList<>();
                    Integer 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();
}

2.4 生产环境

2.4.0.Excel常量类

package com.authorization.privilege.constant;
/**
 * @author qjwyss
 * @description EXCEL常量类
 */
public class ExcelConstant {
    /**
     * 每个sheet存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

注：为了书写方便，此处俩个必须要整除，可以省去很多不必要的判断。另外如果自己测试，可以改为100,20。

分享给你：Spring Boot 学习笔记。

2.4.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

@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);
        // 设置EXCEL名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
        // 设置SHEET名称
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("系统列表sheet1");
        // 设置标题
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<List<String>>();
        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);
        // 查数据写EXCEL
        List<List<String>> dataList = new ArrayList<>();
        List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
        if (!CollectionUtils.isEmpty(sysSystemVOList)) {
            sysSystemVOList.forEach(eachSysSystemVO -> {
                dataList.add(Arrays.asList(
                        eachSysSystemVO.getSystemName(),
                        eachSysSystemVO.getSystemKey(),
                        eachSysSystemVO.getDescription(),
                        eachSysSystemVO.getState().toString(),
                        eachSysSystemVO.getCreateUid(),
                        eachSysSystemVO.getCreateTime().toString()
                ));
            });
        }
        writer.write0(dataList, sheet, table);
        // 下载EXCEL
        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成功");
}

2.4.2.数据量适中（100W以内）：一个SHEET分批查询导出

@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);
        // 设置EXCEL名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
        // 设置SHEET名称
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("系统列表sheet1");
        // 设置标题
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<List<String>>();
        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);
        // 查询总数并 【封装相关变量 这块直接拷贝就行 不要改动】
        Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
        // 写数据 这个i的最大值直接拷贝就行了 不要改
        for (int i = 0; i < writeCount; i++) {
            List<List<String>> dataList = new ArrayList<>();
            // 此处查询并封装数据即可 currentPage, pageSize这个变量封装好的 不要改动
            PageHelper.startPage(i + 1, pageSize);
            List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
            if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                sysSystemVOList.forEach(eachSysSystemVO -> {
                    dataList.add(Arrays.asList(
                            eachSysSystemVO.getSystemName(),
                            eachSysSystemVO.getSystemKey(),
                            eachSysSystemVO.getDescription(),
                            eachSysSystemVO.getState().toString(),
                            eachSysSystemVO.getCreateUid(),
                            eachSysSystemVO.getCreateTime().toString()
                    ));
                });
            }
            writer.write0(dataList, sheet, table);
        }
        // 下载EXCEL
        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成功");
}

2.4.3.数据里很大（几百万都行）：多个SHEET分批查询导出

@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);
        // 设置EXCEL名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
        // 设置SHEET名称
        String sheetName = "系统列表sheet";
        // 设置标题
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<List<String>>();
        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);
        // 查询总数并封装相关变量(这块直接拷贝就行了不要改)
        Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;
        Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
        Integer previousSheetWriteCount = perSheetRowCount / pageSize;
        Integer 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 sheet = new Sheet(i, 0);
            sheet.setSheetName(sheetName + i);
            // 写数据 这个j的最大值判断直接拷贝就行了，不要改动
            for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {
                List<List<String>> dataList = new ArrayList<>();
                // 此处查询并封装数据即可 currentPage, pageSize这俩个变量封装好的 不要改动
                PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);
                List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                    sysSystemVOList.forEach(eachSysSystemVO -> {
                        dataList.add(Arrays.asList(
                                eachSysSystemVO.getSystemName(),
                                eachSysSystemVO.getSystemKey(),
                                eachSysSystemVO.getDescription(),
                                eachSysSystemVO.getState().toString(),
                                eachSysSystemVO.getCreateUid(),
                                eachSysSystemVO.getCreateTime().toString()
                        ));
                    });
                }
                writer.write0(dataList, sheet, table);
            }
        }
        // 下载EXCEL
        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成功");
}

三、总结

造的假数据，100W条记录，18个字段，测试导出是70s。在实际上产环境使用的时候，具体的还是要看自己写的sql的性能。sql性能快的话，会很快。

有一点推荐一下：在做分页的时候使用单表查询，对于所需要处理的外键对应的冗余字段，在外面一次性查出来放到map里面(推荐使用@MapKey注解)，然后遍历list的时候根据外键从map中获取对应的名称。

一个宗旨：少发查询sql, 才能更快的导出。

题外话：如果数据量过大，在使用count(1)查询总数的时候会很慢，可以通过调整mysql的缓冲池参数来加快查询。

还有就是遇到了一个问题，使用pagehelper的时候，数据量大的时候，limit 0,20W, limit 20W,40W, limit 40W,60W, limit 60W,80W 查询有的时候会很快，有的时候会很慢，待研究。另外，关注公众号Java技术栈，在后台回复：面试，可以获取我整理的 Java 系列面试题和答案，非常齐全。

还在用 POI？试试 EasyExcel，轻松导出 100W 数据，不卡死，好用到爆！

一. 简介

二. 案例

2.1 POM依赖

2.2 POJO对象

2.3 测试环境

2.3.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

2.3.2.数据量适中（100W以内）：一个SHEET分批查询导出

2.3.3.数据量很大（几百万都行）：多个SHEET分批查询导出

2.4 生产环境

2.4.0.Excel常量类

2.4.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

2.4.2.数据量适中（100W以内）：一个SHEET分批查询导出

2.4.3.数据里很大（几百万都行）：多个SHEET分批查询导出

三、总结

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

还在用 POI？试试 EasyExcel，轻松导出 100W 数据，不卡死，好用到爆！

一. 简介

二. 案例

2.1 POM依赖

2.2 POJO对象

2.3 测试环境

2.4.0.Excel常量类

2.4.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

热门文章

最新文章

相关电子书