异步通用导出报表文件设计

偏执的太偏执、 2022-11-06 12:56 183阅读 0赞

1.背景

导出几十万，上百万数据文件，会存在http请求时限，Mysql延迟，Excel文件生成慢等问题，需要异步操作。

2.涉及技术点

super-csv, easyPoi,策略模式，七牛云

3.废话不多说，先看一个粗糙的流程图

![00bfffad651510ae136749c2f2de50f3.png][]

## ![c56c99994ebfbc4087cb7d578090eb3c.png][] ##

代码案例：

1. 生成报表的主流程handle

package cn.demo.facade.excel;
    
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import lombok.NonNull;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.tuple.Pair;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.formula.functions.T;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    import org.springframework.util.StringUtils;
    import org.supercsv.io.CsvBeanWriter;
    import org.supercsv.io.ICsvBeanWriter;
    import org.supercsv.prefs.CsvPreference;
    
    import java.io.*;
    import java.nio.charset.StandardCharsets;
    import java.util.List;
    import java.util.Objects;
    import java.util.concurrent.CompletableFuture;
    import java.util.function.Function;
    import java.util.stream.Collectors;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipOutputStream;
    
    /**
     * @author xh
     * @description 报表导出
     * @date 2020/12/21
     */
    @Component
    @Slf4j
    public class ExportExcelHandle {
    
        // 线程池,这里只要能够实现异步操作
        @Autowired
        private ThreadPoolConfig threadPoolConfig;
        // 这里用来上传文件的对象
        @Autowired
        private FilePipesQiNiuHandle filePipesQiNiuHandle;
        // 用来存储文件的数据表
        @Autowired
        private FileDownloadLogService fileDownloadLogService;
    
        /**
         * CSV文件中设置最大行数限制 50万
         */
        private static final int ROW_MAX_SIZE = 500000;
    
        /**
         * 解析Excel文件
         *
         * @param exportFileBo   导出参数封装
         * @param callbackSheets 填充数据,校准,回调函数
         */
        public void handleFile(Long logId, @NonNull AnalysisExportFileBo exportFileBo,
                               Function<AnalysisExportFileBo, List<AnalysisExportFileBo.ExportSheet>> callbackSheets) {
            log.info("开始上传报表文件, logId: [{}]", logId);
            long startTime = System.currentTimeMillis();
            FileDownloadLog fileDownloadLog = new FileDownloadLog();
            fileDownloadLog.setId(logId);
            try {
                byte[] bytes;
                List<AnalysisExportFileBo.ExportSheet> exportSheets = callbackSheets.apply(exportFileBo);
                long rowCount = this.getRowCount(exportSheets);
    
                if (rowCount < 1) {
                    // 文件没有数据，保存为空文件状态
                    fileDownloadLog.setProduceStatus(FileProduceStateEnum.EMPTY.getType());
                    fileDownloadLogService.updateById(fileDownloadLog);
                    return;
                }
                // 构建 csv + zip 文件类型
                if (exportFileBo.isCsvZipState()) {
                    bytes = buildCsvZip(exportFileBo, fileDownloadLog, exportSheets, rowCount);
                } else {
                    // 构建xlsx or xls 文件
                    bytes = this.buildExcel(exportSheets);
                }
    
                FilePipesContext filePipesContext = new FilePipesContext(filePipesQiNiuHandle);
                // 判断是否加密上传
                FileUploadBo fileUploadBo;
                if (exportFileBo.isEncState()) {
                    // 加密上传
                    fileUploadBo = filePipesContext.uploadFileEnc(bytes);
                } else {
                    // 普通上传
                    fileUploadBo = filePipesContext.uploadFile(bytes);
                }
                long endTime = System.currentTimeMillis() - startTime;
                fileDownloadLogService.updateDownloadLogInfo(
                        logId, FileProduceStateEnum.SUCCESS,
                        fileUploadBo.getEncKey(), exportFileBo.getFileName(),
                        fileUploadBo.getFileUrl(),
                        bytes.length, rowCount, endTime
                );
                log.info("完成上传报表文件, logId: [{}]", logId);
            } catch (Exception e) {
                log.error("文件上传失败,taskId:[{}] ,exception:", logId, e);
                // 文件没有数据，保存为空文件状态
                fileDownloadLog.setProduceStatus(FileProduceStateEnum.FAIL.getType());
                fileDownloadLogService.updateById(fileDownloadLog);
            }
        }
    
        /**
         * 构建csv + zip 文件
         * 如果 文件行数大于 50万 行,则会生成多个cvs 文件与压缩包中
         *
         * @param exportFileBo
         * @param fileDownloadLog
         * @param exportSheets
         * @param rowCount
         * @return
         * @throws IOException
         */
        private byte[] buildCsvZip(@NonNull AnalysisExportFileBo exportFileBo, FileDownloadLog fileDownloadLog, List<AnalysisExportFileBo.ExportSheet> exportSheets, long rowCount) throws IOException {
            //  生成CSV文件，如果数据冲过500000行，则会动态生成多个CSV文件,放在压缩包里
            final AnalysisExportFileBo.ExportSheet csvSheet = exportSheets.get(0);
            List<Pair<String, String>> headerColPairs = csvSheet.getHeaderColPairs();
            final String[] csvHeader = headerColPairs.stream().map(Pair::getKey).collect(Collectors.toList()).toArray(new String[0]);
            final String[] csvBeanCol = headerColPairs.stream().map(Pair::getValue).collect(Collectors.toList()).toArray(new String[0]);
    
            List<T> rows = csvSheet.getRows();
            byte[] bytes;
            // 拿到去后缀的文件名
            final String fileName = exportFileBo.getFileName()
                    .replace(AnalysisExportFileBo.XLS_SUFFIX, "")
                    .replace(AnalysisExportFileBo.XLSX_SUFFIX, "")
                    .replace(AnalysisExportFileBo.CSV_SUFFIX, "")
                    .replace(AnalysisExportFileBo.ZIP_SUFFIX, "");
    
            // 拆分文件个数
            int fileNum = (int) (rowCount % ROW_MAX_SIZE == 0 ? rowCount / ROW_MAX_SIZE : rowCount / ROW_MAX_SIZE + 1);
            fileNum = rows.size() < ROW_MAX_SIZE ? 1 : fileNum;
    
            // 需要分文件，并且压缩
            ByteArrayOutputStream respOutputStream = new ByteArrayOutputStream();
            ZipOutputStream zipOutputStream = new ZipOutputStream(respOutputStream);
            Writer writer = null;
            ICsvBeanWriter csvWriter = null;
            int tmp = 0;
    
            for (int cc = 0; cc < fileNum; cc++) {
                // 定义压缩包里的文件名
                ZipEntry zipEntryXtv = new ZipEntry(fileName + "-" + (cc + 1) + AnalysisExportFileBo.CSV_SUFFIX);
                zipOutputStream.putNextEntry(zipEntryXtv);
                writer = new OutputStreamWriter(zipOutputStream, StandardCharsets.UTF_8);
                csvWriter = new CsvBeanWriter(writer, CsvPreference.STANDARD_PREFERENCE);
                // 填充数据
                writer.write('\uFEFF');
                csvWriter.writeHeader(csvHeader);
                for (int a = 0; a < ROW_MAX_SIZE; a++) {
    
                    if (tmp == rowCount) continue;
    
                    Object demoRow = rows.get(tmp);
                    csvWriter.write(demoRow, csvBeanCol);
                    tmp++;
                }
    
                csvWriter.flush();
                writer.flush();
                zipOutputStream.closeEntry();
    
                if (!exportFileBo.getFileName().endsWith(AnalysisExportFileBo.ZIP_SUFFIX)) {
                    // 避免使用接口时,传错文件格式，这里重新更新为ZIP格式
                    fileDownloadLog.setFileName(fileName + AnalysisExportFileBo.ZIP_SUFFIX);
                    fileDownloadLogService.updateById(fileDownloadLog);
                }
            }
            // 返回文件数组
            zipOutputStream.finish();
            zipOutputStream.close();
            respOutputStream.flush();
            bytes = respOutputStream.toByteArray();
            respOutputStream.close();
            if (Objects.nonNull(csvWriter)) {
                csvWriter.close();
            }
            if (Objects.nonNull(writer)) {
                writer.close();
            }
            return bytes;
        }
    
        /**
         * 生成Excel文件字节
         *
         * @param exportSheets
         * @return
         * @throws IOException
         */
        private byte[] buildExcel(List<AnalysisExportFileBo.ExportSheet> exportSheets) throws IOException {
            ByteArrayOutputStream fos;
            try (Workbook workbook = new HSSFWorkbook()) {
                for (AnalysisExportFileBo.ExportSheet sheet : exportSheets) {
                    ExportParams exportParams = new ExportParams();
                    if (!StringUtils.isEmpty(sheet.getSheetName())) {
                        exportParams.setSheetName(sheet.getSheetName());
                    }
                    if (!StringUtils.isEmpty(sheet.getTitle())) {
                        exportParams.setTitle(sheet.getTitle());
                    }
                    ExcelExportService service0 = new ExcelExportService();
                    service0.createSheet(workbook, exportParams, sheet.getCls(), sheet.getRows());
                }
                fos = new ByteArrayOutputStream();
                workbook.write(fos);
            }
            byte[] bytes = fos.toByteArray();
            fos.close();
            return bytes;
        }
    
        /**
         * 异步生成报表
         *
         * @param exportFileBo
         * @param callbackSheets
         * @return 返回当前工单Id
         */
        public Long syncHandleFile(AnalysisExportFileBo exportFileBo,
                                   Function<AnalysisExportFileBo, List<AnalysisExportFileBo.ExportSheet>> callbackSheets) {
            long logId = fileDownloadLogService.saveDownloadLog(exportFileBo.getUserId(), exportFileBo.getFileDownloadTypeEnum(), exportFileBo.getFileName());
            CompletableFuture.runAsync(() -> this.handleFile(logId, exportFileBo, callbackSheets), threadPoolConfig.coreTaskExecutor());
            return logId;
        }
    
        /**
         * 获取文件行数
         *
         * @param exportSheets
         * @return
         */
        private long getRowCount(List<AnalysisExportFileBo.ExportSheet> exportSheets) {
            long rowCount = 0;
            if (Objects.isNull(exportSheets)) {
                return rowCount;
            }
    
            for (AnalysisExportFileBo.ExportSheet sheet : exportSheets) {
                rowCount += sheet.getRows().size();
            }
            return rowCount;
        }
    
    
    }

2.封装的文件BO对象

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @ApiModel(description = "报表导出参数封装")
    public class AnalysisExportFileBo<T> {
        /**
         * 导出用户
         */
        private Long userId;
    
        /**
         * 文件名
         */
        private String fileName;
    
        /**
         * 业务类型
         */
        private FileDownloadTypeEnum fileDownloadTypeEnum;
    
        /**
         * 是否加密上传到文件服务器
         * 默认不加密
         */
        private boolean encState = false;
    
        /**
         * Sheet集合
         * 在初始化的时候可以不填，可以在回调函数函数中返回
         */
        private List<ExportSheet<T>> exportSheets;
    
        /**
         * 是否生成Csv文件,并且压缩成ZIP
         * 默认 false
         */
        private boolean csvZipState = false;
    
    
        @Data
        @NoArgsConstructor
        @AllArgsConstructor
        public static class ExportSheet<T> {
    
            /**
             * sheet页名
             */
            private String sheetName;
    
            /**
             * 标题
             */
            private String title;
    
            /**
             * 行数据
             */
            private List<T> rows;
    
            /**
             * Excel 文件模型
             */
            private Class<T> cls;
    
            /**
             * key 是 CSV文件第一行标题
             * value 是 CSV 文件对象模型，数组元素等于对象的属性名
             * 如构建的是xls文件可以不用填值
             * 例如：有一列是订单号则填入键值对为 key=订单号,value=orderId
             */
            private List<Pair<String, String>> headerColPairs;
    
            public ExportSheet(String sheetName, String title, List<T> rows, Class<T> cls) {
                this.sheetName = sheetName;
                this.title = title;
                this.rows = rows;
                this.cls = cls;
            }
    
            public ExportSheet(List<T> rows, Class<T> cls, List<Pair<String, String>> headerColPairs) {
                this.rows = rows;
                this.cls = cls;
                this.headerColPairs = headerColPairs;
            }
        }
    
        /**
         * EXCEL 文件类型
         */
        public static final String XLSX_SUFFIX = ".xlsx";
        public static final String XLS_SUFFIX = ".xls";
        public static final String CSV_SUFFIX = ".csv";
    
        public static final String ZIP_SUFFIX = ".zip";
    
    }

3.文件表记录POJO

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    @TableName(value = "file_download_log")
    public class FileDownloadLog {
        /**
         * 自增主键ID
         */
        @TableId(value = "id", type = IdType.AUTO)
        private Long id;
    
        /**
         * 用户id
         */
        @TableField(value = "user_id")
        private Long userId;
    
        /**
         * @see cn.FileDownloadTypeEnum
         * 文件类型
         */
        @TableField(value = "type")
        private String type;
    
        /**
         * 文件地址
         */
        @TableField(value = "file_url")
        private String fileUrl;
    
        /**
         * 文件名
         */
        @TableField(value = "file_name")
        private String fileName;
    
        /**
         * 文件大小，单位K
         */
        @TableField(value = "file_size")
        private Long fileSize;
    
        /**
         * 密钥
         */
        @TableField(value = "enc_key")
        private Integer encKey;
    
        /**
         * 文件行数
         */
        @TableField(value = "rows")
        private Long rows;
    
        /**
         * 文件生成时长
         */
        @TableField(value = "produce_at")
        private Long produceAt;
    
        /**
         * @see cn.demo.state
         * 生成状态：0开始，1=成功，2失败
         */
        @TableField(value = "produce_status")
        private Long produceStatus;
    
        /**
         * 是否删除：0未删除，1删除
         */
        @TableField(value = "delete_status")
        private Integer deleteStatus;
    
        /**
         * 插入用户ID
         */
        @TableField(value = "create_id")
        private Long createId;
    
        /**
         * 修改者ID
         */
        @TableField(value = "update_id")
        private Long updateId;
    
        /**
         * 插入时间
         */
        @TableField(value = "create_at")
        private LocalDateTime createAt;
    
        /**
         * 修改时间
         */
        @TableField(value = "update_at")
        private LocalDateTime updateAt;
    
        public static final String COL_ID = "id";
    
        public static final String COL_USER_ID = "user_id";
    
        public static final String COL_TYPE = "type";
    
        public static final String COL_FILE_URL = "file_url";
    
        public static final String COL_FILE_NAME = "file_name";
    
        public static final String COL_FILE_SIZE = "file_size";
    
        public static final String COL_ROWS = "rows";
    
        public static final String COL_PRODUCE_AT = "produce_at";
    
        public static final String COL_PRODUCE_STATUS = "produce_status";
    
        public static final String COL_DELETE_STATUS = "delete_status";
    
        public static final String COL_CREATE_ID = "create_id";
    
        public static final String COL_UPDATE_ID = "update_id";
    
        public static final String COL_CREATE_AT = "create_at";
    
        public static final String COL_UPDATE_AT = "update_at";
    
    }

4.触发生成报表的Controller接口

@GetMapping("/demo/all/export")
        public Result<Long> dmeo(
                @Valid CheckRecordSuccessRequest recordSuccessRequest) {
            AnalysisExportFileBo<DemoAllExportExcel> bo = new AnalysisExportFileBo<>();
            bo.setEncState(true);
            bo.setFileDownloadTypeEnum(FileDownloadTypeEnum.CHECK_RECORD_ALL);
            String fileName = "文件名称" + DateUtil.getDateStr();
            bo.setFileName(fileName + AnalysisExportFileBo.ZIP_SUFFIX);
            bo.setCsvZipState(true);
            bo.setUserId(context.userid);
            Long logId = exportExcelHandle.syncHandleFile(bo, it -> {
                // 回调获取数据
                final List<DemoAllExportExcel> successList = recordDetailService.exportListAll(recordSuccessRequest, false);
          
                AnalysisExportFileBo.ExportSheet<CheckRecordAllExportExcel> sheet1 = new AnalysisExportFileBo.ExportSheet<>(successList,
                        CheckRecordAllExportExcel.class, ExportCsvHeaderConsts.CHECK_RECORD_ALL_HEADER);
                return Collections.singletonList(sheet1);
            });
            return Result.successResult(logId);
        }

5. 通过logId下载文件的Controller接口

@GetMapping("/export/download/file")
        public void downloadExport(
                @Min(NumberConsts.ONE_LONG)
                @ApiParam("报表记录ID")
                @RequestParam(value = "id", required = false) Long id,
                @ApiIgnore @AuthenticationPrincipal UserDetail userDetail,
                HttpServletRequest request,
                HttpServletResponse response
        ) throws IOException {
            fileUploadDownloadFacade.downloadLogFile(id, 0L, request, response);
        }

6. 文件下载和加解密facade

/**
         * 报表导出
         *
         * @param logId    工单ID
         * @param request
         * @param response
         * @return
         * @throws IOException
         */
        public void downloadLogFile(long logId, Long userId, HttpServletRequest request, HttpServletResponse response) throws IOException {
            FileDownloadLog fileDownloadLog = fileDownloadLogService.getOne(new QueryWrapper<FileDownloadLog>().eq(FileUploadTask.COL_ID, logId));
            if (Objects.isNull(fileDownloadLog)) {
                log.warn("没有找到对应的文件记录:[{}]", logId);
                throw new RequestParamErrorException("参数对应不上");
            }
            if (!userId.equals(fileDownloadLog.getUserId())) {
                log.warn("文件记录对应的UserId不正确:[{}]", userId);
                throw new RequestParamErrorException("参数对应不上");
            }
            if (!FileProduceStateEnum.SUCCESS.getType().equals(fileDownloadLog.getProduceStatus())) {
                log.warn("文件记录处于非成功状态", userId);
                throw new RequestParamErrorException("文件记录处于非成功状态");
            }
            String url = fileDownloadLog.getFileUrl();
            Integer encKey = fileDownloadLog.getEncKey();
            String fileName = fileDownloadLog.getFileName();
            this.downloadUrlFile(url, encKey, fileName, request, response);
        }
    
    
        /**
         * 指定url下载文件
         *
         * @param fileUrl  文件位置
         * @param encKey   密钥
         * @param fileName 文件名，传null或空，则使用随机名字
         * @param request
         * @param response
         * @throws IOException
         */
        public void downloadUrlFile(String fileUrl, Integer encKey, String fileName,
                                    HttpServletRequest request, HttpServletResponse response) throws IOException {
            byte[] bytes;
            FilePipesContext filePipesContext = new FilePipesContext(filePipesQiNiuHandle);
            // 这一步拿到下载的文件Byte资源就不多说了
            if (Objects.isNull(encKey) || encKey == 0) {
                bytes = filePipesContext.downloadFile(fileUrl);
            } else {
                bytes = filePipesContext.downloadFileEnc(fileUrl, encKey);
            }
            fileName = StringUtils.isEmpty(fileName) ? getRandomFileName() : fileName;
            ExportCorsUtil.resetHeader(URLEncoder.encode(fileName, CharEncoding.UTF_8), request, response);
            response.getOutputStream().write(bytes);
        }
    
    
    
    
        /**
         * 导出文件重置Header
         *
         * @date 2020/11/25
         */
        public static void resetHeader(String fileName, HttpServletRequest request, HttpServletResponse response) {
            response.reset();
            if (fileName.endsWith(AnalysisExportFileBo.ZIP_SUFFIX)) {
                response.setContentType("application/zip");
            }else {
                response.setContentType("application/vnd.ms-excel");
            }
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Expires", "0");
            response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
            response.setHeader("Access-Control-Allow-Credentials", "true");
            response.setHeader("Access-Control-Allow-Methods", "*");
            response.setHeader("Access-Control-Max-Age", "7200");
            response.setHeader("Access-Control-Expose-Headers", "*");
            response.setHeader(
                    "Access-Control-Allow-Headers",
                    "x-requested-with, Content-Type, Authorization, content-disposition"
            );
        }

[00bfffad651510ae136749c2f2de50f3.png]: /images/20221023/2413508859bf460dbc821e71022ad863.png
[c56c99994ebfbc4087cb7d578090eb3c.png]: /images/20221023/481bf652bf374f9ead6857199dbc05de.png