阿里开源（EasyExcel）---导出EXCEL

雨点打透心脏的1/2处 2022-03-08 19:22 576阅读 0赞

# 一. 简介 #

导出是后台管理系统的常用功能，当数据量特别大的时候会内存溢出和卡顿页面，曾经自己封装过一个导出，[POI百万级大数据量EXCEL导出][POI_EXCEL] 采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。不过一是存在封装不太友好使用不方便的问题，二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题，以及存在内存溢出的隐患。无意间查询到阿里开源的EasyExcel框架，发现可以将解析的EXCEL的内存占用控制在KB级别，并且绝对不会内存溢出（内部实现待研究）,还有就是速度极快， 大概100W条记录，十几个字段， 只需要70秒即可完成下载。遂抛弃自己封装的，转战研究阿里开源的EasyExcel. 不过 说实话，当时自己封装的那个还是有些技术含量的，例如 外观模式，模板方法模式，以及委托思想，组合思想，可以看看。

EasyExcel的github地址是：[https://github.com/alibaba/easyexcel][https_github.com_alibaba_easyexcel]

# 二. 案例 #

## 2.1 POM依赖 ##

<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
     * @date 2019/3/15
     * @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
     * @date 2019/3/18
     * @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。

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

### 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的缓冲池参数来加快查询，请参见博主的另一篇博文[MYSQL单表数据量过大查询过慢配置优化innodb\_buffer\_pool\_size][MYSQL_innodb_buffer_pool_size]。  还有就是遇到了一个问题，使用pagehelper的时候，数据量大的时候，limit 0,20W;  limit 20W,40W,  limit 40W,60W, limit 60W,80W 查询有的时候会很快，有的时候会很慢，待研究。

[POI_EXCEL]: https://blog.csdn.net/qq_35206261/article/details/82844159
[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel
[MYSQL_innodb_buffer_pool_size]: https://blog.csdn.net/qq_35206261/article/details/86606687