背景:产品设计一个批量导出的功能,需要将生成的多个excel打成压缩包导出。
解决方案:因为我们之前项目的单个Excel导出都是用的easyexcel所以我这边还是希望用这个工具去生成excel,然后zip流导出。
探索过程参考了:https://www.jianshu.com/p/a082eeba88a9
//导包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.2</version>
</dependency>
//基础类
@Data
class AAAA {
private Integer age;
private String name;
}
//demo代码
@ResponseBody
@GetMapping(value = "/TEST2")
public void unDirectExport(HttpServletResponse response, Integer id) throws Exception {
String fileName = "test2.zip";
response.setCharacterEncoding("utf-8");
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
List<AAAA> list = get96Time();
List<AAAA> list2 = get96Time();
//按某个条件分组
Map<String, List<AAAA>> map = new HashMap();
map.put("test1.xls", list);
map.put("test2.xls", list2);
exportExcel(response, map);
}
private void exportExcel(HttpServletResponse response, Map<String, List<AAAA>> map) throws IOException {
ServletOutputStream outputStream = response.getOutputStream();
ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
try {
for (Map.Entry<String, List<AAAA>> entry : map.entrySet()) {
String k = entry.getKey();
List<AAAA> value = entry.getValue();
//构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错
ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();
//构建一个sheet页
WriteSheet writeSheet = EasyExcel.writerSheet("薪资单").build();
//构建excel表头信息
WriteTable writeTable0 = EasyExcel.writerTable(0).head(AAAA.class).needHead(Boolean.TRUE).build();
//将表头和数据写入表格
excelWriter.write(value, writeSheet, writeTable0);
//创建压缩文件
ZipEntry zipEntry = new ZipEntry(k);
zipOutputStream.putNextEntry(zipEntry);
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
//将excel对象以流的形式写入压缩流
workbook.write(zipOutputStream);
}
zipOutputStream.flush();
} catch (Exception e) {
log.error("导XXX失败,原因" + e.getMessage());
log.error(e.getMessage(), e);
//抛出异常结束程序
throw new CommonException(20001, "数据导出接口异常");
} finally {
//关闭数据流,注意关闭的顺序
zipOutputStream.close();
outputStream.close();
}
}
private List<AAAA> get96Time() {
List<AAAA> res = new ArrayList<>(96);
LocalDate localDate = LocalDate.now();
LocalDateTime now = LocalDateTime.of(localDate.getYear(), localDate.getMonth(), localDate.getDayOfMonth(), 0, 0);
for (int i = 1; i < 96; i++) {
AAAA a = new AAAA();
String format = now.plusMinutes(15 * i).format(DateTimeFormatter.ofPattern("HH:mm"));
a.setName(format);
a.setAge(i);
res.add(a);
}
return res;
}