poi实现多线程大数据导出

多线程导出

2000万数据量导出200万数据，10个字段，耗时16s.

@Configuration
@Slf4j
public class AsyncTaskPoolConfig {
    @Value("${async.executor.thread.core_pool_size}")
    private int corePoolSize;
    @Value("${async.executor.thread.max_pool_size}")
    private int maxPoolSize;
    @Value("${async.executor.thread.queue_capacity}")
    private int queueCapacity;
    @Value("${async.executor.thread.name.prefix}")
    private String namePrefix;

    @Bean(name = "taskExecutor")
    public Executor asyncServiceExecutor() {
        log.warn("start taskExecutor");
        //在这里修改
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //配置核心线程数
        executor.setCorePoolSize(corePoolSize);
        //配置最大线程数
        executor.setMaxPoolSize(maxPoolSize);
        //配置队列大小
        executor.setQueueCapacity(queueCapacity);
        //配置线程池中的线程的名称前缀
        executor.setThreadNamePrefix(namePrefix);
        // rejection-policy：当pool已经达到max size的时候，如何处理新任务
        // CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        //执行初始化
        executor.initialize();
        return executor;
    }
}

配置

spring:
  # DataSource Config
  datasource:
    druid:
      url: jdbc:mysql://127.0.0.1:3306/maruko?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
      initial-size: 5
      max-active: 10
      min-idle: 10
      max-wait: 20
      #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
# 异步线程配置
# 配置核心线程数
async:
  executor:
    thread:
      core_pool_size: 10
  # 配置最大线程数
      max_pool_size: 20
  # 配置队列大小
      queue_capacity: 30
  # 配置线程池中的线程的名称前缀
      name:
        prefix: async-excl-

 @GetMapping(value = "/exportEasyExcel2")
    public void exportEasyExcel2() {
        long start = System.currentTimeMillis();
        // 数据的总数
        int dataTotalCount = 2000000;
        int limit = 150000;
        // 计算出多少页，即循环次数
        int count = dataTotalCount / limit + (dataTotalCount % limit > 0 ? 1 : 0);
        CountDownLatch countDownLatch = new CountDownLatch(count);
        for (int i = 1; i <= count; i++) {
            Map<String, Object> map = new HashMap<>(3);
            map.put("start", (i - 1) * limit);
            map.put("end", limit);
            map.put("path", "d:\\excel\\");
            iUserService.excuteAsyncTaskDatabase(map, countDownLatch);
        }
        try {
            countDownLatch.await(); //保证之前的所有的线程都执行完成，才会走下面的；
            // 这样就可以在下面拿到所有线程执行完的集合结果
        } catch (Exception e) {
            log.error("阻塞异常:" + e.getMessage());
        } finally {
            long end = System.currentTimeMillis();
            log.info("excel任务执行完毕,共耗时：  " + (end - start) + "ms");
        }
    }

 @Override
    @Async("taskExecutor")
    public void excuteAsyncTaskDatabase(Map<String, Object> map, CountDownLatch cdl) {
        try {
            long startTime = System.currentTimeMillis();

            List<String> title = Arrays.asList("主键ID", "用户名称", "邮箱", "手机号", "性别", "密码", "年龄", "创建时间", "更新时间");

            List<String> key = Arrays.asList("id", "name", "email", "phone", "gender", "password", "age", "create_time", "update_time");

            List<Map<String, Object>> list = baseMapper.queryList(map);
            log.info("线程：" + Thread.currentThread().getName() + "读取数据耗时 ：" + (System.currentTimeMillis() - startTime) + "ms,查询到数据量为：" + list.size());
            String filePath = map.get("path").toString() + UUID.randomUUID() + ".xlsx";
            // 调用导出的文件方法
            Workbook workbook = SxssfWorkbookUtil.createWorkbook(title, key, list);
            //将workbook转换为文件
            ExcelUtil.workbookToFile(workbook, filePath);
            long endTime = System.currentTimeMillis();
            log.info("线程：" + Thread.currentThread().getName() + "导出excel" + map.get("start") + ".xlsx成功 ,耗时 ：" + (endTime - startTime) + "ms");

            log.info("剩余任务数  ===========================> " + cdl.getCount());
        } catch (Exception e) {
            log.error("文件生成异常" + e.getMessage());
        } finally {
            // 执行完线程数减1
            cdl.countDown();
        }

    }

    /**
     * 大文件导出
     *
     * @param title 列标题头
     * @param key   字段英文
     * @param data  数据
     * @return
     */
    public static SXSSFWorkbook createWorkbook(List<String> title, List<String> key, List<Map<String, Object>> data) {

        try {
            //创建poi导出数据对象
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);

            //创建sheet页
            SXSSFSheet sheet = sxssfWorkbook.createSheet();
            //设置表头信息
            SXSSFRow headRow = sheet.createRow(0);

            //设置标题头
            for (int j = 0; j < title.size(); j++) {
                headRow.createCell(j).setCellValue(title.get(j));
            }

            //遍历数据
            for (int i = 0; i < data.size(); i++) {
                SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                for (int j = 0; j < key.size(); j++) {
                    dataRow.createCell(j).setCellValue(data.get(i).get(key.get(j)).toString());
                }
            }

            return sxssfWorkbook;
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return null;
    }