最近项目内有需要导出多个excel自动合并zip压缩包，趁摸鱼之际，记录下造的轮子。
使用CompletableFuture将任务拆分导出再合并返回给客户端，提高导出效率。话不多说，show you the code!

引入依赖

<dependency>
  <groupId>cn.idev.excel</groupId>
  <artifactId>fastexcel</artifactId>
  <version>1.2.0</version>
</dependency>

工具代码

package com.hzj.labor.util;

import cn.hutool.core.thread.ThreadFactoryBuilder;
import cn.idev.excel.ExcelWriter;
import cn.idev.excel.FastExcel;
import cn.idev.excel.converters.Converter;
import cn.idev.excel.enums.CellDataTypeEnum;
import cn.idev.excel.metadata.GlobalConfiguration;
import cn.idev.excel.metadata.data.ReadCellData;
import cn.idev.excel.metadata.data.WriteCellData;
import cn.idev.excel.metadata.property.ExcelContentProperty;
import cn.idev.excel.write.builder.ExcelWriterBuilder;
import cn.idev.excel.write.metadata.WriteSheet;
import jakarta.servlet.http.HttpServletResponse;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.MediaTypeFactory;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Slf4j
@UtilityClass
public class ExcelZipUtil {

    private final ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors(), ThreadFactoryBuilder.create().setNamePrefix("excel-export-").build());

    private File getTemplateFile(String templatePath) {
        if (StringUtils.isBlank(templatePath)) {
            return null;
        }
        try {
            Resource resource = new ClassPathResource(templatePath);
            if (resource.exists()) {
                return resource.getFile();
            }
            return null;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public <T> void exportZip(String zipFilename, String templatePath, List<ExcelData<T>> excelDataList, HttpServletResponse response) {
        if (CollectionUtils.isEmpty(excelDataList)) {
            throw new RuntimeException("导出数据为空！");
        }
        File templateFile;
        if (StringUtils.isNotBlank(templatePath)) {
            templateFile = getTemplateFile(templatePath);
            if (templateFile == null) {
                throw new RuntimeException("模板文件不存在！");
            }
        } else {
            templateFile = null;
        }
        if (StringUtils.isBlank(zipFilename)) {
            zipFilename = "export.zip";
        } else if (!zipFilename.toLowerCase(Locale.ROOT).endsWith(".zip")) {
            zipFilename += ".zip";
        }
        // 根据实际的文件类型找到对应的 contentType
        String contentType = MediaTypeFactory.getMediaType(zipFilename).map(MediaType::toString).orElse("application/zip");
        response.setContentType(contentType);
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(zipFilename, StandardCharsets.UTF_8));

        try {
            // 并行生成所有Excel文件
            List<CompletableFuture<ExcelFileData>> futures = excelDataList.stream()
                    .map(excelData -> CompletableFuture.supplyAsync(() -> generateExcelBytes(excelData, templateFile), executor)
                            .exceptionally(e -> {
                                throw new RuntimeException(e);
                            }))
                    .toList();
            List<ExcelFileData> excelFiles = futures.stream()
                    .map(CompletableFuture::join)
                    .toList();

            // 统一写入ZIP
            try (ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream())) {
                for (ExcelFileData fileData : excelFiles) {
                    zipOut.putNextEntry(new ZipEntry(fileData.getFilename()));
                    zipOut.write(fileData.getBytes());
                    zipOut.closeEntry();
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        log.info("导出zip文件成功，文件名：{}", zipFilename);
    }

    /**
     * 生成单个Excel文件字节数组
     */
    private <T> ExcelFileData generateExcelBytes(ExcelData<T> excelData, File templateFile) {
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            ExcelWriterBuilder builder = FastExcel.write(outputStream)
                    .autoCloseStream(false)
                    .registerConverter(new ExcelBigNumberConvert());
            if (null != templateFile) {
                builder.withTemplate(templateFile);
            }
            ExcelWriter excelWriter = builder.build();
            WriteSheet sheet = FastExcel.writerSheet().build();

            if (null != templateFile) {
                // 写入模板数据
                excelWriter.fill(excelData.getData(), sheet);
            } else {
                // 写入普通数据
                excelWriter.write(List.of(excelData.getData()), sheet);
            }
            // 必须先完成Excel写入,才能获取完整的字节数组
            excelWriter.finish();
            log.info("生成Excel文件成功，文件名：{}", excelData.getFilename());
            return new ExcelFileData(excelData.getFilename(), outputStream.toByteArray());
        } catch (IOException e) {
            throw new RuntimeException("生成Excel文件失败：" + excelData.getFilename(), e);
        }
    }

    /**
     * Excel文件数据容器
     */
    @Getter
    private class ExcelFileData {
        private final String filename;
        private final byte[] bytes;

        public ExcelFileData(String filename, byte[] bytes) {
            this.filename = filename;
            this.bytes = bytes;
        }
    }


    /**
     * Excel 数值长度位15位 大于15位的数值转换位字符串
     */
    public class ExcelBigNumberConvert implements Converter<Long> {

        @Override
        public Class<Long> supportJavaTypeKey() {
            return Long.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }

        @Override
        public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            Object data = cellData.getData();
            if (data == null) {
                return null;
            }
            String s = String.valueOf(data);
            if (s.matches("^\\d+$")) {
                return Long.parseLong(s);
            }
            return null;
        }

        @Override
        public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            if (object != null) {
                String str = object.toString();
                if (str.length() > 15) {
                    return new WriteCellData<>(str);
                }
            }
            WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
            cellData.setType(CellDataTypeEnum.NUMBER);
            return cellData;
        }

    }

    /**
     * sheet数据
     */
    @Setter
    @Getter
    public static class ExcelData<T> {
        private T data;
        private String filename;

        public ExcelData(T data, String filename) {
            this.data = data;
            this.filename = filename;
        }
    }
}

使用

@Override
public void generatePrintExcelZip(List<Long> ids, HttpServletResponse resp) {
    // 校验ids是否为空
    Assert.isTrue(CollectionUtils.isNotEmpty(ids), "ids不能为空");
    List<LabPreWorkMeetingEntity> exportList = listByIds(ids);
    Assert.notEmpty(exportList, "导出数据为空");

    // 生成Excel文件
    List<ExcelZipUtil.ExcelData<LabPreWorkMeetingPrintVO>> voList = exportList.stream()
            .map(this::convertToExcelVO)
            .toList();
    // 压缩并下载
    ExcelZipUtil.exportZip(String.format("导出%s.zip", DateUtil.today()), "templates/meeting-print.xlsx", voList, resp);
}

private ExcelZipUtil.ExcelData<LabPreWorkMeetingPrintVO> convertToExcelVO(LabPreWorkMeetingEntity entity) {
    LabPreWorkMeetingPrintVO vo = BeanUtil.copyProperties(entity, LabPreWorkMeetingPrintVO.class);
    if (entity.getMeetingTime() != null) {
        vo.setMeetingTime(DateUtil.formatLocalDateTime(entity.getMeetingTime()));
    }
    vo.setPrintTime(DateUtil.now());
    return new ExcelZipUtil.ExcelData<>(vo, entity.getMeetingName()+".xlsx");
}

结语

可优化点还有很多，比如多模板，多sheet等，以后需要再修改吧。牛马不易，摸鱼续命
4e16591f73ff18b3335107e98772f241.jpg

凡是过往，皆为序章!