百万级别excel导出

一、背景

• Office2003及之前版本的Excel中，xls格式的表格，支持最大的列为256列，最大的行为65536行，之前司内所有的Excel导出最多只能导出6w多行，现在客户想要明细表每次导出300W数据，所以需要更换导出方案

二、技术选型

• 阿里巴巴的EasyExcel
• 多线程

注：为什么选型EasyExcel，因为它跟之前的技术方案Apache POI相比，它处理过程中，占用内存小很多，而且处理速度快
举例：
300W的数据：
Apache POI: 8G内存，等待一个多小时后，OOM了；
EasyExcel: 大概3-4min钟，内存占用没有超过4G；

三、引入依赖

3.1 maven依赖

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

四、JAVA代码实现

4.1相关常量声明

    /**
     * 返回体内容类型（excel）
     */
    private final String RESPONSE_CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
    /**
     * 返回体头1
     */
    private final String RESPONSE_HEADER1 = "Content-disposition";
    /**
     * 返回体头2
     */
    private final String RESPONSE_HEADER2 = "attachment;filename*=utf-8''";
    /**
     * 返回体头设置文件后缀
     */
    private final String RESPONSE_HEADER2_FILE_SUFFIX = ".xlsx";
    /**
     * 每个sheet的前缀
     */
    private final String EXPORT_SHEET_PREFIX = "sheet";
    /**
     * 支持导出的最大数量（300w）
     */
    private final Integer EXPORT_MAX_COUNT = 3000000;
    /**
     * 每次查询的页数（5w）
     */
    private final Integer EXPORT_QUERY_PAGE_SIZE = 50000;
    /**
     * 线程执行最大的等待时间(单位：分钟)
     */
    private final Integer EXPORT_WAIT_TIME = 20;
    /**
     * 导出的线程池
     */
    private ThreadPoolExecutor exportThread = new ThreadPoolExecutor(60, 80, 10L, TimeUnit.MINUTES,
            new LinkedBlockingDeque<>(), new NamedThreadFactory("thread-export-CxCheckDetails"));

4.2 核心代码

    public void exportAll(DataCxCheckDetailsQuery dataCxCheckDetailsQuery, HttpServletRequest request, HttpServletResponse response) throws IOException {

        //1. 查询数据的总数
        Integer exportCount = baseMapper.getExportCount(dataCxCheckDetailsQuery);
        Assert.isTrue(exportCount <= EXPORT_MAX_COUNT, "超过最大限制" + EXPORT_MAX_COUNT + "条记录");
        
        //2. 计算线程数量
        int treadCount = exportCount / EXPORT_QUERY_PAGE_SIZE + (exportCount % EXPORT_QUERY_PAGE_SIZE == 0 ? 0 : 1);
        
        //3. CountDownLatch用来控制线程是否都执行完毕
        final CountDownLatch latch = new CountDownLatch(treadCount);

        //4. 请求获取数据
        //4.1 设置默认参数分页查询50000
        dataCxCheckDetailsQuery.setPageSize(EXPORT_QUERY_PAGE_SIZE);
        //4.2 数据集合
        List<Future<List<CheckDetailsPageDTO>>> lists = new ArrayList<>(treadCount);
        for (int i = 0; i < treadCount; i++) {
            //4.3 启动线程，获取数据
            lists.add(exportThread.submit(new ExportThread(dataCxCheckDetailsQuery, i, latch)));
        }

        ServletOutputStream out = response.getOutputStream();
        //5. 设置返回数据的格式
        response.setContentType(RESPONSE_CONTENT_TYPE_XLSX);
        response.setHeader(RESPONSE_HEADER1, RESPONSE_HEADER2 + DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN) + RESPONSE_HEADER2_FILE_SUFFIX);
        
        //6. easyExcel导出（此处不理解的，建议参考官方文档）
        try (ExcelWriter excelWriter = EasyExcel.write(out).build()) {
            latch.await(EXPORT_WAIT_TIME, TimeUnit.MINUTES);
            // getSheetList(lists) 把各个线程的集合根据实际需求把数据进行合并的操作（因为我这里的代码写的太丑了，就不献丑了）
            List<List<CheckDetailsPageDTO>> sheetList = getSheetList(lists);
            //策略treadCount应该合并成几个sheet 50w为标准
            for (int i = 0; i < sheetList.size(); i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, EXPORT_SHEET_PREFIX + i).head(CheckDetailsPageDTO.class).build();
                excelWriter.write(sheetList.get(i), writeSheet);
            }
            excelWriter.finish();
            IoUtil.close(out);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (out != null) {
                IoUtil.close(out);
            }
        }
    }

4.3 线程ExportThread

    /**
     * 导出线程逻辑处理
     */
    public class ExportThread implements Callable<List<CheckDetailsPageDTO>> {
        private DataCxCheckDetailsQuery query;
        private Integer page;
        private CountDownLatch latch;

        public ExportThread(DataCxCheckDetailsQuery query,
                            Integer page,
                            CountDownLatch latch) {
            this.query = query;
            this.page = page;
            this.latch = latch;
        }

        @Override
        public List<CheckDetailsPageDTO> call() {
            DataCxCheckDetailsQuery dataCxCheckDetailsQuery = ObjectUtil.cloneByStream(query);
            dataCxCheckDetailsQuery.setPage(page + 1);
            List<CheckDetailsPageDTO> result = new ArrayList<>();
            try {
                result = baseMapper.getExportDetails(dataCxCheckDetailsQuery);
            } catch (Exception e) {

            } finally {
                latch.countDown();
            }
            return result;
        }
    }

五、补充说明

• 本次方案是实时下载的，缺陷是用户等待时间较长，可能需要3min左右，以及无法实现导出进度实时展示功能

• 待优化导出交互体验：

  • 方案一：前端可以用假的进度条，只有文件导出之后才让进度条进度到100%
  • 方案二：根据导出的情况，自己写个算法，把结果缓存到redis中，让前端不断地请求这个redis的数据
  • 方案三：通过websocket，自己写个算法，实时的把结果推送到前端
  • 方案四：通过消息通知的方式，通知用户已经下载好了，用户点击再去下载

注：方案一是最方便的，实现成本最低

---

附录：

• EasyExcel官网