EasyExcel 分 Sheet 实现大数据量导出

        【场景】平台用户导出数据量达 w 级别的数据时界面白屏或按钮无响应。

        【解决方案】做异步导出，用户触发点击时创建导出消息并开启单独线程处理导出，处理成功以后将文件下载路径更新到消息中心提醒用户下载。

        【注意的问题】As we all knew, everything has its limits，Excel 也是（03 版本 Excel 单张 Sheet 容量是 65536 行 ，07 版本单张 Sheet 容量是 1048575 行），超过了容量则 api 报错。invalid row number (1048576) outside allowable range (0..1048575)

        【解决办法】分 Sheet 导出，如果导出的数据量超过单一张 Sheet 的容量就创建一个新 Sheet 进行存放

       

实现步骤如下：

引入依赖

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

具体方式（参考代码）

/**
 * 异步导出学生数据
 * @param param 查询参数
 * @param loginUserId 当前登录人ID
 * @param classId 班级ID
 * @return
 */
public Result exportStudentInfoBigData(StudentInfoExportQueryParam  param, Integer loginUserId, Integer classId) {
    try {
        String title = "学生信息报表"; // 表格顶部标题
        String fileName = new String((title + String.format("%tF", new Date()) + "-导出").getBytes(), StandardCharsets.UTF_8);
        // 创建任务
        String messageTitle = title + "导出正在处理中(可能时间比较长，请及时关注消息处理进度)...";
        MessageCenter downloadMsg = new MessageCenter(loginUserId, Constants.COMMON_NUMBER_1, messageTitle, null);
        messageCenterMapper.insert(downloadMsg);

        // 输出到临时目录
        String savePath = "";
        if (Arrays.asList("prod".split(",")).contains(appConfiguration.getActive())) { // 环境配置临时文件位置
            savePath = "/mnt/export/";
        } else {
            savePath = "D:/export/";
        }
        String fileStr = fileName + ".xlsx";
        String saveFileName = savePath + fileStr;
        // 创建临时文件
        File savefile = new File(savePath);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream(saveFileName);
        // 异步方式
        String bodyFormat = "您于" + DateUtils.dateTimeNow(DateUtils.YYYY_MM_DD_HH_MM_SS) + "提交导出的【%s】已完成, 下载地址: <a style='color: blue' href='%s' download='%s'>点击下载</a>";
        // 校验是否已经存在导出中的数据
        Map<String, Object> paraMap = new HashMap<String, Object>();
        paraMap.put("userId", loginUserId);
        paraMap.put("messageType", 1);
        paraMap.put("messageTitle", title);
        paraMap.put("status", Constants.COMMON_NUMBER_0); // 未读
        List<MessageCenter> downloadMsgList = messageCenterMapper.findListByParam(paraMap);
        if (!CollectionUtils.isEmpty(downloadMsgList)) {
            throw new CustomException("您已有正在处理中的下载任务，请到消息中查看状态完成后下载文件");
        }

        param.setUserClassId(classId);
        param.setLoginUserId(loginUserId);
        // 计算总数分片导出
        // 获取最大ID
        param.setQueryType(1);
        Integer maxId = studentInfoMapper.getMaxOrMinId(param);
        // 获取最小ID
        param.setQueryType(2);
        Integer minId = studentInfoMapper.getMaxOrMinId(param);
        // 查询此时最小ID~最大ID之间的总数
        param.setMaxId(maxId);
        param.setMinId(minId);
        // 根据当前条件查询到的总数据量
        Long totalCount = studentInfoMapper.selectCountStudentInfoExcel(param);
        log.info("当前导出总数量：{}", totalCount);
        ExcelWriter excelWriter = null;
        if(totalCount > Constants.EXCEL_SHEET_CAPACITY) { // 如果总数超出单个sheet的大小 1048575
            excelWriter = EasyExcel.write(fos, StudentInfoExportExcel.class).build();
            // 计算Sheet个数: 如果超过单个sheet大小，需要分sheet，否则报错: invalid row number (1048576) outside allowable range (0..1048575)
            Integer pageSize = Constants.EXPORT_MAX_SHEET_PAGE_SIZE; // 设置的单个Sheet的数据量，必须小于1048576，在此设置Constants.EXPORT_MAX_SHEET_PAGE_SIZE=100,0000
            long sheetCount = (totalCount + Constants.EXPORT_MAX_SHEET_PAGE_SIZE - 1) / pageSize;
            for (Integer pageNum = 1; pageNum <= sheetCount; ++pageNum) {
                WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, fileName + "_Sheet表_" + pageNum).head(StudentInfoExportExcel.class).build();
                PageHelper.startPage(pageNum, pageSize, false); // 分页不统计
                List<StudentInfoExportExcel> excelList = studentInfoMapper.findAndExportStudentInfoList(param);
                excelWriter.write(excelList, writeSheet);
                excelList.clear(); // 导出当前列表以后清空，释放服务器内存
            }
        }else { // 不超出则一次性全部导出
            excelWriter = EasyExcel.write(fos, StudentInfoExportExcel.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(fileName).head(StudentInfoExportExcel.class).build();
            List<StudentInfoExportExcel> excelList = studentInfoMapper.findAndExportStudentInfoList(param);
            excelWriter.write(excelList , writeSheet);
            excelList.clear(); // 导出当前列表以后清空，释放服务器内存
        }
        // 关闭
        excelWriter.finish();
        byte[] fileByte = FileUtils.readFileToBinary(saveFileName); // 将临时文件转为二进制数组，然后上传到云存储服务器
        if (Objects.nonNull(fileByte) && fileByte.length > 0) {
            InputStream is = new ByteArrayInputStream(fileByte);
            String url = cloudStorageService.uploadFile(new MockMultipartFile(fileName, fileStr, "", is));
            log.info("导出出来完成，数据量:{}, 路径:{}", totalCount, url);
            messageTitle = title + "导出处理完成";
            downloadMsg.setMessageTitle(messageTitle);
            downloadMsg.setMessageBody(String.format(bodyFormat, fileName, url, fileName));
        } else {
            messageTitle = title + "导出处理失败";
            downloadMsg.setMessageTitle(messageTitle);
            downloadMsg.setMessageBody(title + "导出处理失败，请重新点击导出");
        }
        downloadMsg.setStatus(0);
        messageCenterMapper.updateById(downloadMsg);
        savefile.delete();
        // 关闭流
        fos.close();
        return Result.success("导出已经处理正在处理中，请到消息中心查看处理结果并下载文件");
    } catch (Exception e) {
        log.info("学生信息信息导出异常：{}", e);
        return Result.error("学生信息导出系统异常");
    }
}

        【总结】

   1. 创建代办消息，提示告诉用户要耐心等待处理完成；与此通过线程池启动线程处理导出。

   2. 处理过程：

（1）根据当前用户点击导出时的查询条件，获取当前目标导出数据的最小 ID 和最大 ID；

       （2）再统计最小 ID~ 最大 ID 范围内所有的数据总数 totalCount（用于下一步计算要多少个 Sheet 来装）

       （3）判断 totalCount 没有超过 Sheet 容量就一次性导出，如果超过则分 Sheet

       （4）根据数据总数 totalCount 计算 Sheet 张数，每张 Sheet 存储 100w 条数据；

Integer pageSize = Constants.EXPORT_MAX_SHEET_PAGE_SIZE; // 设置的单个 Sheet 的数据量，必须小于 1048576，在此设置（Constants.EXPORT_MAX_SHEET_PAGE_SIZE=100,0000）

                                       long sheetCount = (totalCount + Constants.EXPORT_MAX_SHEET_PAGE_SIZE - 1) / pageSize;

        （5）循环 Sheet 数量进行分页查询和加载到 Excel 中，加载每一页后要清空 List，否则服务器内存容易会被消耗完而导致服务器崩溃重启。

       （6）将生成 Excel 存储到 Linux / 本地服务器空间比较大的文件路径，将其读取并上传到云存储，上传完成后得到下载 url，删除 Linux / 本地服务器文件释放磁盘空间。

        （7）更新消息中心信息，把 url 放入消息体，提示用户当前的导出处理完成，可以点击链接下载。

           最近在工作中遇到用户反馈说导出功能不好用，就单独抽出时间来优化平台中导出的功能，之前把导出优化的任务分给同事来搞，写的代码看起来就重写，导出的数据都不对，最后还是要我出马。

 积硅步以致千里

                      --------------------------------------------------------------------->>[Every problem has its solutions]