大数据量excel导出，最终生成zip格式

实际项目开发过程中，经常有数据导出的业务场景，如果是数据量比较少的情况下，会直接导出一个excel即可；但如果数据量比较多，比如说达到十万甚至百万级别，只导出一个excel的方案就不太适用了。

针对这种数据量比较大的导出，可以多线程查询数据，最终以zip压缩包的形式导出，以下是相关代码：

多线程分批次查询数据

    /**
     * 查询待导出的数据
     *
     * @param queryWrapper
     * @return
     */
    private List<TWithintableRisk> getListForExport(QueryWrapper queryWrapper) {
        // 计算查询次数
        long startTime = System.currentTimeMillis();
        String partitionKey = "";
        // TODO: 2024/12/04 用临时表多线程查询不能查询全部？暂时先单线程一次性查询所有数据
        int count = tWithintableRiskMapper.getCountForExport(partitionKey);
        int threads = count / QUERY_BATCH_SIZE + 1;
        ExecutorService exec = new ThreadPoolExecutor(10, 10, 60,
                TimeUnit.SECONDS, new LinkedBlockingQueue<>(4096),
                new ThreadPoolExecutor.CallerRunsPolicy());
        CompletionService<List<TWithintableRisk>> completionService = new ExecutorCompletionService<>(exec,
                new LinkedBlockingQueue<>());
        log.info("开始查询待导出的表内信用风险明细信息，总条数：[{}]，每批次处理数：[{}]，需启动[{}]个线程", count, QUERY_BATCH_SIZE, threads);
        for (int i = 0; i < threads; i++) {
            TWithInTableRiskQueryWorker worker = new TWithInTableRiskQueryWorker();
            worker.setTWithintableRiskMapper(tWithintableRiskMapper);
            worker.setPartitionKey(partitionKey);
            worker.setRowNumStart(i * QUERY_BATCH_SIZE);
            worker.setRowNumEnd((i + 1) * QUERY_BATCH_SIZE);
            worker.setTotal(threads);
            worker.setCurrentJobNum(i + 1);
            completionService.submit(worker);
        }
        List<TWithintableRisk> resultList = new ArrayList<>(count);
        for (int i = 0; i < threads; i++) {
            try {
                resultList.addAll(completionService.take().get());
            } catch (Exception e) {
                log.error("查询待导出的表内信用风险明细信息异常：", e);
            }
        }
        exec.shutdown();
        log.info("待导出的表内信用风险明细信息查询完毕，总条数：{}，总耗时：{}ms", CollectionUtils.size(resultList),
                (System.currentTimeMillis() - startTime));
        return resultList.stream()
                .sorted(Comparator.comparing(TWithintableRisk::getId).reversed())
                .collect(Collectors.toList());
    }

---

以zip压缩包形式导出

---

    public void exportZip(HttpServletRequest request, HttpServletResponse response, QueryWrapper queryWrapper) {
        List<TWithintableRisk> list = this.getListForExport(queryWrapper);
        long startTime = System.currentTimeMillis();
        String zipName = "表内信用风险明细结果.zip";
        ZipOutputStream zos = null;
        ServletOutputStream outputStream = null;
        try {
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(zipName, "UTF-8"));
            response.setContentType("application/vnd.ms-excel");
            // 准备zip输出流
            outputStream = response.getOutputStream();
            zos = new ZipOutputStream(outputStream);
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            // 按数据条数划分多个excel
            List<List<TWithintableRisk>> partitionList = Lists.partition(list, EXCEL_BATCH_SIZE);
            LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
            Map<String, Object> map = MapUtils.newHashMap();
            map.put(FIELD_USER_NAME, sysUser.getRealname());
            log.info("开始生成zip压缩包，总数据量条数：{}，批次数：{}，共需生成excel个数：{}", list.size(), EXCEL_BATCH_SIZE, partitionList.size());
            for (int i = 0; i < partitionList.size(); i++) {
                // 准备zip对象
                log.info("开始生成第{}个excel", i + 1);
                ZipEntry entry = new ZipEntry("表内信用风险明细结果" + (i + 1) + ".xlsx");
                zos.putNextEntry(entry);
                // 准备填充模板的对象
                ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream)
                        .withTemplate(templateFilePath)
                        .build();
                WriteSheet writeSheet = EasyExcel.writerSheet().build();
                excelWriter.fill(partitionList.get(i), writeSheet);
                excelWriter.fill(map, writeSheet);
                excelWriter.finish();
                // 添加到zip输出流中
                zos.write(byteArrayOutputStream.toByteArray());
                byteArrayOutputStream.reset();
                zos.flush();
                zos.closeEntry();
                log.info("第{}个excel生成成功", i + 1);
            }
        } catch (Exception ex) {
            log.error("表内信用风险明细结果导出失败：", ex);
        } finally {
            if (Objects.nonNull(zos)) {
                try {
                    zos.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
            if (Objects.nonNull(outputStream)) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
            log.info("表内信用风险明细结果zip压缩完成，总耗时：{}ms", (System.currentTimeMillis()) - startTime);
        }
    }