hutool导出excel表格，线程池增加效率

实体类

@Data
public class SheetDTO {
    /**
     * sheet页名称
     */
    private String sheetName;

    /**
     * 字段和别名，如果使用这个，properties 和 titles可以不用处理
     * Map<字段, 别名>  如：Map<"name", "姓名">
     */
    private Map<String, String> fieldAndAlias;

    /**
     * 列宽<br/>
     * 设置列宽时必须每个字段都设置才生效（columnWidth.size = fieldAndAlias.size）
     */
    private List<Integer> columnWidth;

    /**
     * 数据集
     */
    private Collection<?> collection;

}

线程读取数据

{
        List<SheetDTO> sheetList = new ArrayList<>();
        ThreadPoolExecutor poolExecutor = ExecutorBuilder.create()
                .setCorePoolSize(list.size()) // 初始线程
                .setMaxPoolSize(list.size()) // 最大线程
                .setWorkQueue(new LinkedBlockingQueue<>(100)) // 线程池策略
                .build();
        CountDownLatch downLatch = new CountDownLatch(list.size());
        list.forEach(s -> {
            poolExecutor.execute(() -> {
                String description = s.getDescription().replaceAll("/", "-");
                List<Device> devices = deviceMap.get(s.getId());
                //获取所有点位
                Map<String, String> map = new HashMap<>();
                devices.forEach(d -> {
                    Optional.ofNullable(pointMap.get(d.getId())).ifPresent(
                            k -> {
                                Map<String, String> collect = k.stream().collect(Collectors.toMap(Point::getColumnName, Point::getDescription));
                                map.putAll(collect);
                            });
                });
                List<Map> dataList = Collections.synchronizedList(deviceService.selectDataList(devices, map.keySet(), s, bo));
                SheetDTO sheetDTO = new SheetDTO();
                sheetDTO.setSheetName(description);
                sheetDTO.setFieldAndAlias(map);
                sheetDTO.setCollection(dataList);
                sheetList.add(sheetDTO);
                downLatch.countDown();
            });
        });
        try {
            downLatch.await();
        } catch (InterruptedException e) {
            log.error("线程错误{}",e);
        }finally {
            poolExecutor.shutdown();
        }
        return sheetList;
    }

写数据

 try {
            writer = ExcelUtil.getWriter(true);
            writer.renameSheet(0, list.get(0).getDescription());
            StyleSet styleSet = writer.getStyleSet();
            Workbook workbook = writer.getWorkbook();
            DataFormat dataFormat = workbook.createDataFormat();
            short format = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
            styleSet.getCellStyleForDate().setDataFormat(format);
            sheetList.forEach(s->{
                writer.setSheet(s.getSheetName());
                writer.addHeaderAlias("categoryName", "设备大类");
                writer.addHeaderAlias("description", "设备类型");
                writer.addHeaderAlias("name", "设备名称");
                writer.addHeaderAlias("time", "时间");
                s.getFieldAndAlias().forEach((k,v)->writer.addHeaderAlias(k,v));
                writer.setOnlyAlias(true);
                writer.write(s.getCollection(),true);
            });
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + bo.getCategoryName() + ".xlsx");
            ServletOutputStream outputStream = response.getOutputStream();
            writer.flush(outputStream,true);
            outputStream.close();
            writer.close();
            return ApiResult.ok();
        } catch (IOException e) {
            log.error("导出io异常：", e);
            return ApiResult.failed("失败");
        }

导出数据采用了多线程查询表数据，在多线程中不能写writer.write()操作，所以利用多线程把数据查完整后再一次性写出

第二种方法：

发现比第一个写法快了一点推测原因是并行了stream流

{
        List<SheetDTO> sheetList = new ArrayList<>();
        ThreadPoolExecutor poolExecutor = ExecutorBuilder.create()
                .setCorePoolSize(list.size()) // 初始线程
                .setMaxPoolSize(list.size()) // 最大线程
                .setWorkQueue(new LinkedBlockingQueue<>(100)) // 线程池策略
                .build();
        CountDownLatch downLatch = new CountDownLatch(list.size());
        list.parallelStream().forEach(s -> {
            poolExecutor.execute(() -> {
                String description = s.getDescription().replaceAll("/", "-");
                List<Device> devices = deviceMap.get(s.getId());
                //获取所有点位
                Map<String, String> map = new HashMap<>();
                devices.forEach(d -> {
                    Optional.ofNullable(pointMap.get(d.getId())).ifPresent(
                            k -> {
                                Map<String, String> collect = k.stream().collect(Collectors.toMap(Point::getColumnName, Point::getDescription));
                                map.putAll(collect);
                            });
                });
                List<Map> dataList = Collections.synchronizedList(deviceService.selectDataList(devices, map.keySet(), s, bo));
                SheetDTO sheetDTO = new SheetDTO();
                sheetDTO.setSheetName(description);
                sheetDTO.setFieldAndAlias(map);
                sheetDTO.setCollection(dataList);
                sheetList.add(sheetDTO);
                downLatch.countDown();
            });
        });
        try {
            downLatch.await();
        } catch (InterruptedException e) {
            log.error("线程错误{}",e);
        }finally {
            poolExecutor.shutdown();
        }
        return sheetList;
    }