异步线程池+多线程实现EXCEL填充，打包（zip）及上传

需求背景：

1、通过筛选条件过滤数据（多线程主要解决这一步查询慢的问题）

2、数据填充EXCEL

3、多EXCEL打zip包上传到文件服务器

4、先返回下载信息，再同步导出EXCEL报表数据

主要逻辑和方法：

注册异步线程池

     /**
     * 注册异步线程池
     */
    @Bean("asyncThreadPool")
    public ThreadPoolTaskExecutor asyncThreadPoolTaskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(5);
        executor.setMaxPoolSize(20);
        executor.setQueueCapacity(100);
        executor.setKeepAliveSeconds(30);
        executor.setThreadNamePrefix("async-Thread");
        executor.setWaitForTasksToCompleteOnShutdown(true);
        executor.setAwaitTerminationSeconds(60);
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.setTaskDecorator(new ContextCopyingDecorator());
        executor.initialize();
        return executor;
    }

service实现方法

    /**
     * @param request          查询条件入参
     * @param fileName         自定义导出文件名
     * @param minaShareDirPath 生成的excel临时保存地址
     * @param templatePathTag  模板保存在项目的相对路径：/download/xxx.xlsx
     * @param id               前一步生成的报表id主键
     */    
    @Override
    @Async("asyncThreadPool")
    public void exportProjectDetailByUser(DataCenterDetailByUserQueryRequest request, String fileName, String minaShareDirPath, String templatePathTag, String id) {
        //生成EXCEL的保存路径:文件路径+yyyyMMdd+UUID
        String excelFilePath = DataCenterReportUtil.getExcelDownloadPath(minaShareDirPath);
        //String local = "F:\\home\\excel";
        //String excelFilePath = local + File.separator + DateUtil.format(new Date(), "yyyyMMdd") + File.separator + UUIDGenerator.getUUID() + File.separator;
        try {
            //创建文件夹
            DataCenterReportUtil.createFile(excelFilePath);
            //填充模板数据并生成zip包
            this.exportTemplateDataThread(request, excelFilePath, templatePathTag, fileName);
            //zip包路径：上传文件服务器使用
            String zipFilePath = excelFilePath + fileName + ".zip";
            //上传后返回下载url
            String downloadUrl = fileService.uploadFileWithForest(zipFilePath);
            //更新表
            dataCenterService.updateDataCenter(id, downloadUrl, "报表导出成功");
        } catch (Exception e) {
            //更新表
            dataCenterService.updateDataCenter(id, null, "报表导出失败");
        } finally {
            //删除excel及zip
            DataCenterReportUtil.deleteExcelFile(new File(excelFilePath));
        }
    }

文件操作util类

主要是创建文件夹，获取文件地址，删除文件夹及文件方法等。

public class DataCenterReportUtil {

    private static final Logger logger = LoggerFactory.getLogger(DataCenterReportUtil.class);

    /**
     * 分页查询大小
     */
    public static final int PAGE_INTERVAL_SIZE = 2000;
    /**
     * excel导出大小
     */
    public static final int EXCEL_INTERVAL_SIZE = 10000;

    /**
     * 获取模板所在地址
     *
     * @return String
     */
    public static String getTemplatePath(String template) {
        return Objects.requireNonNull(DataCenterReportUtil.class.getResource(template)).getPath();
    }

    /**
     * 创建文件夹
     *
     * @param path 文件路径
     */
    public static void createFile(String path) {
        File file = new File(path);
        //判断文件是否存在;
        if (!file.exists()) {
            //创建文件;
            boolean bol = file.mkdirs();
            if (bol) {
                logger.info("路径创建创建成功：{}", path);
            } else {
                logger.info("路径创建失败：{}", path);
            }
        } else {
            logger.info("文件已经存在：{}", path);
        }
    }

    /**
     * 删除文件
     *
     * @param file
     * @return
     */
    public static boolean deleteExcelFile(File file) {
        String[] files = null;
        if (file != null) {
            files = file.list();
        }
        if (file.isDirectory()) {
            for (int i = 0; i < Objects.requireNonNull(files).length; i++) {
                boolean bol = deleteExcelFile(new File(file, files[i]));
                if (bol) {
                    logger.info("删除文件 {} 成功", files[i]);
                } else {
                    logger.info("删除文件 {} 失败", files[i]);
                }
            }
        }
        return file.delete();
    }

    /**
     * 获取excel下载地址路径
     *
     * @return 文件路径/yyyyMMdd/UUID/
     */
    public static String getExcelDownloadPath(String minaShareDirPath) {
        return minaShareDirPath + File.separator + DateUtil.format(new Date(), "yyyyMMdd") + File.separator + UUIDGenerator.getUUID() + File.separator;
    }

主要方法解析（线程池创建查询数据、填充模板、生成zip）

EXCEL的填充使用的是alibaba的EasyExcel，EasyExcel的使用方法可以参考链接：EasyExcel · 语雀

1、主方法

    //注入线程池
    @Autowired
    @Qualifier("asyncThreadPool")
    private ThreadPoolTaskExecutor asyncThreadPoolTaskExecutor;

/**
     * 多线程处理模板数据
     *
     * @param request         筛选条件入参
     * @param excelFilePath   excel存放路径
     * @param templatePathTag 模板地址标识
     * @param fileName        自定义excel文件名
     */
    private void exportTemplateDataThread(DataCenterDetailByUserQueryRequest request, String excelFilePath, String templatePathTag, String fileName) {
        try {
            Long total = dataCenterService.detailByUserCount(request);
            if (total == null || 0 >= total) {
                this.richExcel(new ArrayList<>(), excelFilePath, fileName, templatePathTag);
            } else {
                //所有循环的次数
                int foreachSize = total.intValue() % DataCenterReportUtil.PAGE_INTERVAL_SIZE == 0 ? total.intValue() / DataCenterReportUtil.PAGE_INTERVAL_SIZE : total.intValue() / DataCenterReportUtil.PAGE_INTERVAL_SIZE + 1;
                //分页查询结果
                List<DataCenterByUserReportVO> list = Collections.synchronizedList(new ArrayList<>());
                //定义添加线程的集合
                List<CompletableFuture> futureList = new ArrayList<>();
                //创建单个线程
                CompletableFuture<Void> future;
                //循环开始线程（还是使用线程池）
                for (int i = 1; i <= foreachSize; i++) {
                    DataCenterDetailByUserQueryRequest query = new DataCenterDetailByUserQueryRequest();
                    BeanUtils.copyProperties(request, query);
                    query.setPageNo(i);
                    query.setPageSize(DataCenterReportUtil.PAGE_INTERVAL_SIZE);
                    future = CompletableFuture.runAsync(() -> {
                        Page<DataCenterByUserReportVO> page = dataCenterService.detailByUser(query);
                        if (CollectionUtils.isNotEmpty(page.getRows())) {
                            list.addAll(page.getRows());
                        }
                    }, asyncThreadPoolTaskExecutor);
                    futureList.add(future);
                }
                //所有任务都执行完成
                CompletableFuture.allOf(futureList.toArray(new CompletableFuture[futureList.size()])).join();
                //把数据切分，每10000条插入一个excel
                List<List<DataCenterByUserReportVO>> splitList = ListUtil.split(list, DataCenterReportUtil.EXCEL_INTERVAL_SIZE);
                Map<String, List<DataCenterByUserReportVO>> dataMap = new HashMap<>(splitList.size());
                for (int i = 0; i < splitList.size(); i++) {
                    //设置EXCEL文件名
                    String key = fileName;
                    if (i >= 1) {
                        key = fileName + "(" + i + ")";
                    }
                    dataMap.put(key, splitList.get(i));
                }
                for (Map.Entry<String, List<DataCenterByUserReportVO>> entry : dataMap.entrySet()) {
                    //填充EXCEL
                    this.richExcel(entry.getValue(), excelFilePath, entry.getKey(), templatePathTag);
                }
            }
            //excel打包生成zip
            this.zip(excelFilePath, fileName);
        } catch (IOException e) {
            e.printStackTrace();
            log.info(e.toString());
        }
    }

2、填充excel

/**
     * 填充excel
     *
     * @param list            填充的数据List
     * @param excelFilePath   excel文件路径
     * @param excelName       excel文件名(没有后缀)
     * @param templatePathTag 模板地址相对路径 /download/xxxx.xlsx
     * @throws IOException io
     */
    private void richExcel(List list, String excelFilePath, String excelName, String templatePathTag) throws IOException {
        //excel模板地址
        String templatePath = DataCenterReportUtil.getTemplatePath(templatePathTag);
        //String templatePath = "F:\\home\\dataCenterDetailByUserTemplate.xlsx";
        String excel = excelName + ".xlsx";
        //生成的excel保存地址（全路径）
        String downloadFile = excelFilePath + excel;
        EasyExcel.write(downloadFile)
                .withTemplate(templatePath)
                .sheet()
                .doFill(list);
    }

3、文件流的方式打zip包

    /**
     * 生成zip文件
     *
     * @param excelFilePath excel文件路径
     * @param zipName       zip名（无后缀）
     * @throws IOException io
     */
    private void zip(String excelFilePath, String zipName) throws IOException {
        File[] files = new File(excelFilePath).listFiles();
        File file = new File(excelFilePath + zipName + ".zip");
        ZipOutputStream zipOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
        FileInputStream fileInputStream;
        byte[] buf = new byte[1024];
        int len;
        if (files != null && files.length > 0) {
            for (File excelFile : files) {
                String fileName = excelFile.getName();
                //EXCEL文件流
                fileInputStream = new FileInputStream(excelFile);
                //放入压缩zip包中;
                zipOutputStream.putNextEntry(new ZipEntry(fileName));
                //读取文件;
                while ((len = fileInputStream.read(buf)) > 0) {
                    zipOutputStream.write(buf, 0, len);
                }
                //关闭;
                zipOutputStream.closeEntry();
                fileInputStream.close();
            }
        }
        //一定要关闭，如果不关闭无法操作zip
        zipOutputStream.close();
    }

上传到文件服务器

上传使用的是forest包

引用：

<dependency>
    <groupId>com.dtflys.forest</groupId>
    <artifactId>forest-spring-boot-starter</artifactId>
    <version>1.5.13</version>
</dependency>

    /**
     * 定义的uploadClient接口类，接口定义上传方法
     */
    public interface ForestUploadClient {

    @Post("{0}")
    String upload(String url, @DataFile("Filedata") String filePath, OnProgress onProgress);
    }

    @Autowired
    private ForestUploadClient uploadClient;    

    @Override
    public String uploadFileWithForest(String filePath) {
        try {
            //文件服务器上传地址
            String uploadUrl = this.getAllUploadUrl();
            //实现方法(上传)
            String response = uploadClient.upload(uploadUrl, filePath, progress -> {
                System.out.println("progress: " + Math.round(progress.getRate() * 100) + "%");  // 已上传百分比
                if (progress.isDone()) {   // 是否上传完成
                    System.out.println("--------   Upload Completed!   --------");
                }
            });
            Document document = DocumentHelper.parseText(response);
            Element rootElement = document.getRootElement();
            String signedUrl = rootElement.element("signedUrl").getStringValue();
            return signedUrl;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }

到此完成。