Efficient Large-Scale Data Export in SpringBoot: Parallel EasyExcel to Multiple Files and ZIP Download

Problem

SpringBoot synchronous Excel export blocks the request thread until the file is generated, causing poor performance for large data sets.

Solution Overview

Split the export data, process each sub‑list asynchronously with CompletableFuture and a ThreadPoolTaskExecutor, generate multiple Excel files in parallel using EasyExcel template export, then compress all files into a ZIP for download.

Controller

@RestController
public class SalesOrderController {

    @Resource
    private SalesOrderExportService salesOrderExportService;

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

Service Implementation

@Slf4j
@Service
public class SalesOrderExportService {

    @Autowired
    @Qualifier("threadPoolTask")
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    @Resource
    private OrderManager orderManager;

    public void salesOrderExport(RequestDto req, HttpServletResponse response) {
        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);

            Path tmpDirRef = (tempDir = Files.createTempDirectory(req.userCode() + "dir_prefix"));

            CompletableFuture[] salesOrderCf = Lists.partition(orderDataList, 5).stream()
                .map(subList -> CompletableFuture.supplyAsync(() ->
                        subList.stream()
                               .map(order -> exportExcelToFile(tmpDirRef, outputStream, order))
                               .collect(Collectors.toList()),
                        threadPoolTaskExecutor)
                    .exceptionally(e -> { throw new RuntimeException(e); }))
                .toArray(CompletableFuture[]::new);

            CompletableFuture.allOf(salesOrderCf).get(3, TimeUnit.MINUTES);

            tempZipFilePath = Files.createTempFile(req.getUserCode() + TMP_ZIP_DIR_PRE, ".zip");
            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"));

            zipFileInputStream = Files.newInputStream(tempZipFilePath);
            IOUtils.copy(zipFileInputStream, response.getOutputStream());

        } catch (Exception e) {
            log.error("salesOrderExport,异常:", e);
            throw new RuntimeException("导出异常,请稍后重试");
        } finally {
            try {
                if (zipFileInputStream != null) {
                    zipFileInputStream.close();
                }
                if (tempDir != null) {
                    Files.walkFileTree(tempDir, new SimpleFileVisitor<Path>() {
                        @Override
                        public FileVisitResult visitFile(Path file, BasicFileAttributes attrs)
                                throws IOException {
                            Files.deleteIfExists(file);
                            return FileVisitResult.CONTINUE;
                        }

                        @Override
                        public FileVisitResult postVisitDirectory(Path dir, IOException exc)
                                throws IOException {
                            Files.deleteIfExists(dir);
                            return FileVisitResult.CONTINUE;
                        }
                    });
                }
                if (tempZipFilePath != null) {
                    Files.deleteIfExists(tempZipFilePath);
                }
            } catch (Exception e) {
                log.error("salesOrderExport, 关闭文件流失败:", e);
            }
        }
    }

    private Path exportExcelToFile(Path temporaryDir,
                                  ByteArrayOutputStream templateOutputStream,
                                  SalesOrder data) {
        Path temporaryFilePath;
        try {
            temporaryFilePath = 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 os = Files.newOutputStream(temporaryFilePath);
             BufferedOutputStream bos = new BufferedOutputStream(os)) {

            ExcelWriter excelWriter = EasyExcel.write(bos, 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();

            return temporaryFilePath;
        } catch (Exception e) {
            throw new RuntimeException("exportExcelToFile,导出excel文件失败:" + data.getOrderNo(), e);
        }
    }
}

Export Process Steps

Load the Excel template from the classpath.

Create a temporary directory for generated Excel files.

Partition the SalesOrder list into sub‑lists of five items.

For each sub‑list start a CompletableFuture that calls exportExcelToFile to produce an Excel file using EasyExcel template.

Wait for all futures to complete (timeout 3 minutes).

Compress the directory of Excel files into a ZIP file with ZipUtil.zip.

Set HTTP response headers and stream the ZIP file to the client.

In a finally block close streams and delete temporary files and directories.

Key Technical Points

Multithreading : CompletableFuture + ThreadPoolTaskExecutor parallelize export; Lists.partition splits orders, five per thread.

EasyExcel template export : Template loaded via getResourceAsStream; ExcelWriter fills placeholder “goods” and the order object.

Resource cleanup : Try‑with‑resources, explicit stream closing, and Files.walkFileTree ensure no resource leaks.

Error handling : Exceptions are logged and rethrown as runtime exceptions to signal unrecoverable failures.