使用的是阿里easyexcel中的填充模板的形式,需要在根目录(resources)下建一个templates文件夹,里面放对应的模板文件。注意模板名必须正确。
以下代码采用的是使用zip流将生成的excel统一打包导出:
接口方法 {
try {
// zip导出响应处理
response.setContentType("application/zip");
response.setHeader("Content-Disposition", "attachment; filename=\"export.zip\"");
try (ServletOutputStream outputStream = response.getOutputStream();
ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
数组.forEach(实体对象 -> {
// 整理数据
实体.get(...);
//文件名字
String fileName = "xxx" + new Date();
try (//文件模板输入流
InputStream inputStream = new ClassPathResource("templates/模板名.xlsx").getInputStream();
ByteArrayOutputStream excelOutputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(excelOutputStream)
.withTemplate(inputStream)
.autoCloseStream(true)
.build()) {
// 准备sheet(有几个写几个)
WriteSheet sheet0 = EasyExcel.writerSheet(0, "sheet名").build();
// 填充配置
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
// 填充(有几个sheet写几个)
excelWriter.fill(对象数据, sheet0);
// 填充完成
excelWriter.finish();
// 将 Excel 文件添加到 ZIP 压缩包中
byte[] excelData = excelOutputStream.toByteArray();
addToZip(zipOutputStream, fileName, excelData);
} catch (IOException e) {
e.printStackTrace();
}
});
} catch (Exception e) {
e.printStackTrace();
}
}
// zip导出
private void addToZip(ZipOutputStream zipOut, String fileName, byte[] data) throws IOException {
ZipEntry zipEntry = new ZipEntry(fileName + ".xlsx");
zipOut.putNextEntry(zipEntry);
zipOut.write(data);
zipOut.closeEntry();
}
如果不需要压缩zip,将ExcelWriter excelWriter = EasyExcel.write(excelOutputStream)中的参数改为以下getOutputStream()方法,去除zip响应头和压缩即可。
// excel导出流处理
private OutputStream getOutputStream(String fileName, HttpServletResponse response) {
try {
// 设置响应体内容
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
return response.getOutputStream();
} catch (IOException e) {
logger.error("【" + BaseBusinessExceptionEnum.FAILED_EXPORT_DATA_TO_EXCEL.getCode() + ":"
+ BaseBusinessExceptionEnum.FAILED_EXPORT_DATA_TO_EXCEL.getMsg() + "】" + e.getMessage());
throw new BusinessException(BaseBusinessExceptionEnum.FAILED_EXPORT_DATA_TO_EXCEL.getCode(),
BaseBusinessExceptionEnum.FAILED_EXPORT_DATA_TO_EXCEL.getMsg());
}
}