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

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*=utf-8'zh_cn'" + URLEncoder.encode("数据明细","utf-8") + ".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()操作，所以利用多线程把数据查完整后再一次性写出

线程读取数据（2）发现比第一个写法快了一点推测原因是并行了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;
    }

hutool导入excel表格

一条一条读取，自己封装实体类
优势：可以自己校验每个参数，对输入值没有硬性要求
劣势：比较繁琐

	public static void main(String[] args) {
		File file=new File("文件路径")
		ExcelUtil.readBySax(file.getInputStream(), 0, new RowHandler() {
				@Override
				public void handle(int sheetIndex, long rowIndex, List<Object> rowCells) {
					List<Object> list = rowCells.stream().filter(k -> ObjectUtil.isNotEmpty(k) && StrUtil.isNotEmpty(k.toString())).collect(Collectors.toList());
					if (CollUtil.isEmpty(list))return;
					//每条数据信息 封装位置
					log.info("[{}] [{}] {}", sheetIndex, rowIndex+1, rowCells);
				}
			});
	}

封装好实体类，直接读取赋值到实体类
优势：快捷方便
劣势：无法校验每个参数，对输入值有硬性要求符合属性类型

public static void main(String[] args) {
		File file=new File("文件路径")
		ExcelReader reader = ExcelUtil.getReader(file);
			//设置忽略空行
			reader.setIgnoreEmptyRow(true);
			Map<String, String> headerMap = new HashMap<>();
			headerMap.put("标题","对应属性");
			reader.setHeaderAlias(headerMap);
			List<实体> 实体List = reader.readAll(实体.class);
			//打印数据
			String str = JSONUtil.toJsonPrettyStr(实体List);
			log.info(str);
	}