使用EasyExcel异步导出excel实现思路

目的：

提高用户体验，避免导出超大数据时用户一直等待

实现思路：

1. 主线程：处理请求响应，同时开启子线程，让子线程处理导出任务
2. 子线程：将导出的文件写入到磁盘临时文件，临时文件上传到**oss**中获取上传文件的url路径，记录url路径到数据库中，最后再删除临时文件
3. 通过单独一个页面查询导出文件流水的列表，进行下载文件

代码实现

线程池配置

@Component
public class ExcelExportThreadPoolConfig {

    private static final int CPU_SIZE = Runtime.getRuntime().availableProcessors();

    @Bean(value = "exportThreadPoolExecutor")
    public ThreadPoolExecutor poolExecutorSetting() {

        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(
            CPU_SIZE * 2,
            CPU_SIZE * 2,
            3,
            TimeUnit.MINUTES,
            new LinkedBlockingQueue<>(20),
            new CustomizableThreadFactory("文件导出线程池")
        );

        threadPoolExecutor.prestartAllCoreThreads();
        return threadPoolExecutor;
    }
}

controller层

/**
     * easyExcel异步导出通用案例2
     *
     * @param response
     * @return
     */
@GetMapping("asyncExportFileFlow")
public String asyncExportFileFlow(HttpServletResponse response) {

    CompletableFuture.runAsync(() -> {

        this.studentService.asyncExportFileFlow(response);

    }, exportThreadPoolExecutor).exceptionally(e -> {
        log.error("导出文件失败", e);
        return null;
    });

    return "ok";

}

service层

@Override
public void exportUserData(HttpServletResponse response) {

    List<Map<String, Object>> data = this.sysUserMapper.list();
    if (data.isEmpty()) {
        throw new RuntimeException("数据为空");
    }

    recordFileFlow(response, data, ExcelExportEnum.USER_EXCEL);

}

/**
     * 记录导出文件的流水
     *
     * @param response
     * @param data
     * @param excelExportEnum
     */
private void recordFileFlow(HttpServletResponse response,
                            List<Map<String, Object>> data,
                            ExcelExportEnum excelExportEnum) {

    ExportFileFlow exportFileFlow = new ExportFileFlow();
    exportFileFlow.setFileName(excelExportEnum.getFilename());
    exportFileFlow.setCreateTime(LocalDateTime.now());
    exportFileFlow.setOperateUser("系统用户");
    this.exportFileFlowMapper.insert(exportFileFlow);

    long beginTime = System.currentTimeMillis();
    try {
        String fileUrl = this.exportTemplate.uploadFileToOss(response, excelExportEnum, data);
        long endTime = System.currentTimeMillis();
        exportFileFlow.setExportStatus(1);
        exportFileFlow.setExportConsumeTime((endTime - beginTime) / 1000);
        exportFileFlow.setFileUrl(fileUrl);
    } catch (Exception e) {
        exportFileFlow.setExportStatus(2);
        exportFileFlow.setFailReason(e.toString());
        exportFileFlow.setCreateTime(null);
        throw new RuntimeException(e);
    } finally {
        this.exportFileFlowMapper.update(exportFileFlow);
    }
}

public String uploadFileToOss(HttpServletResponse response,
                              ExcelExportEnum excelExportEnum,
                              List<Map<String, Object>> writeData) throws IOException {

    BufferedOutputStream tempOutStream = null;
    BufferedInputStream tempInStream = null;
    Path newFilePath = null;
    try {

        // 1.加载类路径下的模板
        ClassPathResource classPathResource = new ClassPathResource(excelExportEnum.getFileClassPath());
        String originalFileName = classPathResource.getFilename();
        if (StringUtils.isEmpty(originalFileName)) {
            throw new RuntimeException("文件名不能为空");
        }

        // 2.创建临时目录
        File file = new File(DISK_TEMPORARY_LOCATION);
        // 目录不存在则新建，如果新建失败则终止
        boolean createDiskDirFail = !file.exists() && !file.mkdirs();
        if (createDiskDirFail) {
            throw new RuntimeException("临时文件目录创建失败");
        }
        String fileSuffix = originalFileName.substring(originalFileName.lastIndexOf("."));
        // 临时文件起个随机名，防止并发时导致出现前面线程把后边的临时文件删掉，导致后边删除出现异常
        newFilePath = Paths.get(file.getPath() + UUID.randomUUID() + fileSuffix);
        // 使用缓冲流，加快读写
        tempOutStream = new BufferedOutputStream(Files.newOutputStream(newFilePath));

        // 3.往模板中写数据，并将写好的文件写入到磁盘临时文件
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
            // 用于指定生成 Excel 文件的路径
            .file(tempOutStream)
            // 指定模板位置
            .withTemplate(classPathResource.getInputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().build();
        excelWriter.fill(new FillWrapper("data", writeData), fillConfig, writeSheet);
        excelWriter.finish();

        // 4.上传oss
        tempInStream = new BufferedInputStream(Files.newInputStream(newFilePath));
        String fileUrl = this.aliOssTemplate.uploadFile(tempInStream, classPathResource.getFilename());
        log.info("文件上传成功，地址：{}", fileUrl);
        return fileUrl;

    } finally {
        // 5.最后再删掉磁盘上临时文件
        assert newFilePath != null;
        Files.delete(newFilePath);
        assert tempOutStream != null;
        tempOutStream.close();
        assert tempInStream != null;
        tempInStream.close();
    }

}

public String uploadFile(InputStream inputStream, String originalFileName) {

    if (StringUtils.isEmpty(originalFileName)) {
        throw new RuntimeException("文件名不能为空");
    }

    // 1.重命名文件，oss目录是 2021-9-10/uuid.文件后缀
    String fileSuffix = originalFileName.substring(originalFileName.lastIndexOf("."));
    String newFileName = UUID.randomUUID().toString().replace("-", "");
    String renameFileName = LocalDate.now() + "/" + newFileName + fileSuffix;

    // 2.文件上传
    OSS ossClient = null;
    try {
        ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        PutObjectRequest putObjectRequest = new PutObjectRequest(bucketName, renameFileName, inputStream);
        ossClient.putObject(putObjectRequest);
    } finally {
        assert ossClient != null;
        ossClient.shutdown();
    }

    // 3.组装获取返回的url
    //https://classroomsys.oss-cn-beijing.aliyuncs.com/objectdir/a.png
    return "https://" + bucketName + "." + endpoint + "/" + renameFileName;
}

public enum ExcelExportEnum {

    /**
     * 用户信息导出
     */
    USER_EXCEL("用户信息导出", "/files/用户信息.xlsx"),

    /**
     * 流水信息导出
     */
    EXPORT_FILE_FLOW("流水信息导出", "/files/导出文件流水信息.xlsx"),

    ;

    /**
     * 类型
     */
    private final String filename;

    /**
     * 描述内容
     */
    private final String fileClassPath;

    ExcelExportEnum(String filename, String fileClassPath) {
        this.filename = filename;
        this.fileClassPath = fileClassPath;
    }

    /**
     * 通过类型获取枚举常量
     *
     * @param type
     * @return
     */
    public static ExcelExportEnum getExcelExportEnumByType(String type) {

        ExcelExportEnum[] values = ExcelExportEnum.values();
        for (ExcelExportEnum element : values) {
            if (element.filename.equals(type)) {
                return element;
            }
        }
        return null;
    }

    /**
     * 是否包含此类型
     *
     * @param type
     * @return
     */
    public static boolean contains(String type) {

        ExcelExportEnum[] values = ExcelExportEnum.values();
        for (ExcelExportEnum element : values) {
            if (element.filename.equals(type)) {
                return true;
            }
        }
        return false;
    }


    public String getFilename() {
        return filename;
    }

    public String getFileClassPath() {
        return fileClassPath;
    }
}

数据库文件

CREATE TABLE `export_file_flow`  (
    `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '文件名',
    `file_url` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件url',
    `export_status` int(0) NULL DEFAULT 2 COMMENT '导出状态：1处理中，2处理成功，3处理失败',
    `fail_reason` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '导出失败原因',
    `export_consume_time` bigint(0) NULL DEFAULT NULL COMMENT '导出耗时（秒）',
    `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `operate_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '操作人',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 244 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文件下载列表' ROW_FORMAT = Dynamic;

模板及内容

注意事项：

• 读写临时文件为什么不使用ByteArrayOutputStream？

  是因为这个流是基于内存交互的流，不适合大文件。所以使用了FileInputStream与磁盘交互的流，同时使用了缓冲流能够加快读写速度
  

• EasyExcel优点与传统poi对比

  Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，`poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。`
  `easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出`；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便
  

• EasyExcel使用模板填充，更加通用性，指哪写哪