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;
    }

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值