SpringBoot中大量数据导出方案：使用EasyExcel并行导出多个excel文件并压缩zip后下载

文章目录

前言
一、控制器层代码
二、服务层代码
三、代码亮点分析

前言

SpringBoot的同步excel导出方式中，服务会阻塞直到Excel文件生成完毕，如果导出数据很多时，效率低体验差。有效的方案是将导出数据拆分后利用CompletableFuture，将导出任务异步化，并行使用easyExcel导出多个excel文件，最后将所有文件压缩成ZIP格式以方便下载。

Springboot环境下基于以上方案，下面代码的高质量的完成导出销售订单信息到Excel文件，并将多个Excel文件打包成一个ZIP文件，最后发送给客户端：

一、控制器层代码

@RestController
public class SalesOrderController {@Resourceprivate SalesOrderExportService salesOrderExportService;@PostMapping(value = "/salesOrder/export")public void salesOrderExport(@RequestBody @Validated RequestDto req, HttpServletResponse response) {salesOrderExportService.salesOrderExport(req, response);}}

二、服务层代码

负责执行销售订单的导出逻辑:

将多个Excel文件打包成ZIP文件
多线程ThreadPoolTaskExecutor并行处理销售订单的导出

@Slf4j
@Service
public class SalesOrderExportService {@Autowired@Qualifier("threadPoolTask")private ThreadPoolTaskExecutor threadPoolTaskExecutor;@Resourceprivate OrderManager OrderManager;public void salesOrderExport(RequestDto req, HttpServletResponse response) {// 获取导出数据,每个SalesOrder实例需要分别导出到一个excel文件List<SalesOrder> orderDataList = OrderManager.getOrder(req.getUserCode());// 略...校验数据InputStream zipFileInputStream = null;Path tempZipFilePath = null;Path tempDir = null;// 获取导出模板try (InputStream templateInputStream = this.getClass().getClassLoader().getResourceAsStream("template/order_template.xlsx");ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {if (Objects.isNull(templateInputStream)) {throw new RuntimeException("获取模版文件异常");}// 多线程服用一个文件流IOUtils.copy(templateInputStream, outputStream);// 创建临时excel文件导出目录,用于将多个excel导出到此目录下Path tmpDirRef = (tempDir = Files.createTempDirectory(req.userCode() + "dir_prefix"));// 每5个salesOrder一个线程并行导出到excel文件中CompletableFuture[] salesOrderCf = Lists.partition(orderDataList, 5).stream().map(orderDataSubList -> CompletableFuture.supplyAsync(() -> orderDataSubList.stream().map(orderData -> this.exportExcelToFile(tmpDirRef, outputStream, orderData)).collect(Collectors.toList()), threadPoolTaskExecutor).exceptionally(e -> {throw new RuntimeException(e);})).toArray(CompletableFuture[]::new);// 等待所有excel文件导出完成CompletableFuture.allOf(salesOrderCf).get(3, TimeUnit.MINUTES);// 创建临时zip文件tempZipFilePath = Files.createTempFile(req.userCode() + TMP_ZIP_DIR_PRE, ".zip");// 将excel目录下的所有文件压缩到zip文件中,zipUtil有很多工具包都有ZipUtil.zip(tempDir.toString(), tempZipFilePath.toString());response.setContentType("application/octet-stream;charset=UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(tempZipFilePath.toFile().getName(), "utf-8"));// 写zip文件流到responsezipFileInputStream = Files.newInputStream(tempZipFilePath);IOUtils.copy(zipFileInputStream, response.getOutputStream());} catch (Exception e) {log.error("salesOrderExport,异常:", e);throw new RuntimeException("导出异常,请稍后重拾");} finally {try {  // 关闭流if (Objects.nonNull(zipFileInputStream)) {zipFileInputStream.close();}    // 删除临时文件及目录if (Objects.nonNull(tempDir)) {Files.walkFileTree(tempDir, new SimpleFileVisitor<Path>() {@Overridepublic FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {Files.deleteIfExists(file);return FileVisitResult.CONTINUE;}@Overridepublic FileVisitResult postVisitDirectory(Path dir, IOException exc) throws IOException {Files.deleteIfExists(dir);return FileVisitResult.CONTINUE;}});}if (Objects.nonNull(tempZipFilePath)) {Files.deleteIfExists(tempZipFilePath);}} catch (Exception e) {log.error("salesOrderExport, 关闭文件流失败:", e);}}

使用EasyExcel库基于模板导出每个销售订单到单独的Excel文件中
模板内容:

/*** 导出单个excle文件,上面的多线程代码调用**/private Path exportExcelToFile(Path temporaryDir, ByteArrayOutputStream templateOutputStream, SalesOrder data) {Path temproaryFilePath = null;try {// 创建临时文件 temproaryFilePath = Files.createTempFile(temporaryDir, data.getOrderNo(), ExcelTypeEnum.XLSX.getValue());} catch (IOException e) {throw new RuntimeException("exportExcelToFile,创建excel临时文件失败:" + data.getOrderNo());}try (InputStream templateInputStream = new ByteArrayInputStream(templateOutputStream.toByteArray());OutputStream temporaryFileOs = Files.newOutputStream(temproaryFilePath);BufferedOutputStream tempOutStream = new BufferedOutputStream(temporaryFileOs)) {// 使用easyExcel的模板功能导出订单数据到临时文件中 ExcelWriter excelWriter = EasyExcel.write(tempOutStream, SalesOrder.class).withTemplate(templateInputStream).excelType(ExcelTypeEnum.XLSX).build();// 填充模板数据WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(new FillWrapper("goods", data.getGoodsList()), fillConfig, writeSheet);excelWriter.fill(data, writeSheet);excelWriter.finish();// temproaryFilePath.toFile().deleteOnExit();return temproaryFilePath;} catch (Exception e) {throw new RuntimeException("exportExcelToFile,导出excel文件失败:" + data.getOrderNo(), e);}}