最近项目内有需要导出多个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等,以后需要再修改吧。牛马不易,摸鱼续命
凡是过往,皆为序章!
Discover local local cannabis shop in prague listings to learn about store locations, opening hours, and product categories. Review available information about CBD products, hemp goods, and local regulations. Organize your visit by checking current opening times, age restrictions, and Czech laws.
Check popular top weed shop in prague options and learn about store locations, opening hours, and available product categories. Explore local listings, check current Czech regulations, and learn about CBD products and hemp-derived goods. Prepare your visit by checking reliable information, age restrictions, and applicable rules.
Explore current buy weed in prague information to understand Czech cannabis laws and the products legally available to adults. Review local regulations, age restrictions, and reliable information about licensed retail options and CBD products. Remain informed about legal limits, permitted uses, and rules for public spaces.
Check popular reliable cannabis shop in prague listings to learn about store locations, opening hours, and product categories. Review available information about CBD products, hemp goods, and local regulations. Prepare your visit by checking current opening times, age restrictions, and Czech laws.
Explore local weed shop in prague city centre options and learn about store locations, opening hours, and available product categories. Explore local listings, check current Czech regulations, and learn about CBD products and hemp-derived goods. Prepare your visit by checking reliable information, age restrictions, and applicable rules.
Корисні статті для читачів https://infopark.com.ua містить пізнавальні матеріали та огляди.
Пізнавальний сайт для аудиторії https://elementarno.com.ua збирає актуальні публікації для щоденного читання.
Цікавий ресурс для читачів https://infokom.org.ua допомагають дізнаватися нове й стежити за актуальними подіями.
Пізнавальний сайт для аудиторії https://expertka.com.ua/budivnytstvo.html пропонує новини, статті та корисні поради.
Корисні статті для читачів https://dobraporada.com.ua містить пізнавальні матеріали та огляди.
Інформаційний ресурс для аудиторії https://databank.com.ua допомагає знаходити актуальну інформацію та нові матеріали.
Пізнавальний сайт для аудиторії https://dailymail.com.ua/nashi-hroshi.html допомагають дізнаватися нове й стежити за актуальними подіями.
Актуальні матеріали для читачів https://dailyfacts.com.ua допомагає знаходити актуальну інформацію та нові матеріали.