最近项目内有需要导出多个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等，以后需要再修改吧。牛马不易，摸鱼续命
凡是过往，皆为序章!
