实体类
@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;
}