最近做了一个导出功能，需要将页面筛选条件之后的数据全部导出到excel中，每个分公司只能导出自己账号权限下的数据不会很多，但是如果是总公司不进行任何筛选直接导出全部数据大概会有17万左右的数据，后期可能还会更多，但是前端超时时间是30s。由于easyexcel不支持并发写入，所以只能从数据库方面入手。
自定义线程池
由于需要使用到多线程，最好自定义一个导出专用的线程池不要影响到其他功能
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import java.util.concurrent.ThreadPoolExecutor;
@Configuration
@EnableAsync
public class ThreadPoolConfiguration {
/**
* 导出任务线程池
* @return
*/
@Bean("exportTaskExecutor")
public ThreadPoolTaskExecutor exportTaskExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
// 核心线程数
executor.setCorePoolSize(5);
// 最大线程数
executor.setMaxPoolSize(10);
// 等待队列数
executor.setQueueCapacity(0);
// 非核心线程空闲存活时间
executor.setKeepAliveSeconds(60);
// 线程名称
executor.setThreadNamePrefix("export-task-");
// 拒绝策略：由主线程继续执行被拒绝的任务
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
// 等待所有任务结束后再关闭线程池
executor.setWaitForTasksToCompleteOnShutdown(true);
executor.setAwaitTerminationSeconds(30);
// 线程池初始化
executor.initialize();
return executor;
}
}多线程分页查询数据库并导出
// 设置文本内省
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
// 设置字符编码
response.setCharacterEncoding("utf-8");
String fileTimeStr = DateUtil.format(DateUtil.date(), "yyyyMMddHHmm");
String fileName = URLEncoder.encode("清单导出_" + fileTimeStr, StandardCharsets.UTF_8);
// 设置响应头
response.setHeader("Content-disposition", "filename=" + fileName + ".xlsx");
// 使用try-with-resources确保资源释放
log.info("开始添加多线程导出");
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExportItemListVo.class)
.registerWriteHandler(new HorizontalCellStyleStrategy(CustomExcelStyleUtil.getHeadStyle(),CustomExcelStyleUtil.getContentStyle()))
.build()
) {
WriteSheet writeSheet = EasyExcel.writerSheet("标的清单").build();
String finalCompanyCode = companyCode;
// 提交当前批次任务
List<Future<List<ExportItemListVo>>> batchFutures = new LinkedList<>();
// 分页查询并写入
for (int pageNum = 1; pageNum <= totalPages; pageNum++) {
// 分页
Page<ExportItemListVo> page = new Page<>(pageNum,
pageSize);
page.setSearchCount(false);
if (totalPages == 1) {
// 当只有一页则不分页 加速查询
batchFutures.add(exportTaskExecutor.submit(() -> itemMapService.exportItemList(params, finalCompanyCode, null)));
} else {
batchFutures.add(exportTaskExecutor.submit(() -> itemMapService.exportItemList(params, finalCompanyCode, page)));
}
}
log.info("开始写入excel");
while (!batchFutures.isEmpty()) {
Future<List<ExportItemListVo>> future = batchFutures.remove(0);
excelWriter.write(future.get(30, TimeUnit.SECONDS), writeSheet);
}
}多线程导出工具类
由于之前的写法过于重复，所以手动写了一个工具类简化流程
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.Builder;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
@Slf4j
@Component
public class ExcelExportUtil {
@Autowired
@Qualifier("exportTaskExecutor")
private ThreadPoolTaskExecutor exportTaskExecutor;
@Builder
@Getter
public static class ExportConfig<T> {
private final Class<T> clazz;
private final String sheetName;
private final String fileNamePrefix;
@Builder.Default
private final long pageSize = 20_000L;
}
/**
* 数据源接口，返回 MyBatis-Plus Page 对象
* 调用方负责把 searchCount 设置好后传入，工具类不感知
*/
@FunctionalInterface
public interface PageDataSupplier<T> {
Page<T> fetchPage(Page<T> page);
}
public <T> void export(
HttpServletResponse response,
ExportConfig<T> config,
PageDataSupplier<T> dataSupplier
) throws IOException, ExecutionException, InterruptedException {
setResponseHeaders(response, config.getFileNamePrefix());
try (ExcelWriter excelWriter = buildExcelWriter(response, config)) {
WriteSheet writeSheet = EasyExcel.writerSheet(config.getSheetName()).build();
// ① 第一页：searchCount=true，获取总数和第一批数据
Page<T> firstPage = new Page<>(1, config.getPageSize(), true);
Page<T> firstResult = dataSupplier.fetchPage(firstPage);
long totalPages = firstResult.getPages();
log.info("[ExcelExport] 文件={} 总数={} 总页数={}", config.getFileNamePrefix(), firstResult.getTotal(), totalPages);
if (totalPages == 0) {
return;
}
// ② 第一页数据直接写入，同时提交后续页任务
excelWriter.write(firstResult.getRecords(), writeSheet);
log.info("[ExcelExport] 第1页写入完成，数量={}", firstResult.getRecords().size());
if (totalPages > 1) {
List<Future<Page<T>>> futures = new LinkedList<>();
for (int pageNum = 2; pageNum <= totalPages; pageNum++) {
final int currentPage = pageNum;
futures.add(exportTaskExecutor.submit(() -> {
Page<T> page = new Page<>(currentPage, config.getPageSize(), false);
return dataSupplier.fetchPage(page);
}));
}
// ③ 按顺序写入后续批次
int batch = 2;
while (!futures.isEmpty()) {
Page<T> result = futures.remove(0).get();
excelWriter.write(result.getRecords(), writeSheet);
log.info("[ExcelExport] 第{}页写入完成，数量={}", batch++, result.getRecords().size());
}
}
}
log.info("[ExcelExport] 文件={} 导出完成", config.getFileNamePrefix());
}
private void setResponseHeaders(HttpServletResponse response, String fileNamePrefix) {
String fileName = URLEncoder.encode(fileNamePrefix, StandardCharsets.UTF_8);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
}
private <T> ExcelWriter buildExcelWriter(HttpServletResponse response, ExportConfig<T> config) throws IOException {
return EasyExcel.write(response.getOutputStream(), config.getClazz())
.registerWriteHandler(new HorizontalCellStyleStrategy(CustomExcelStyleUtil.getHeadStyle(), CustomExcelStyleUtil.getContentStyle()))
.build();
}
}使用工具类最终效果
controller
@PostMapping(value = "/export-item-list")
public void exportItemListCount(
@RequestHeader("Authorization") String authorization,
@Valid @RequestBody SearchItemListDto params
, HttpServletResponse response) {
String companyCode = checkTokenUtil.getCompanyCode(authorization);
itemMapService.exportItemList(params, companyCode, response);
}service
@Override
@SneakyThrows
public void exportItemList(SearchItemListDto params, String companyCode, HttpServletResponse response) {
String fileTimeStr = DateUtil.format(DateUtil.date(), "yyyyMMddHHmm");
String fileName = URLEncoder.encode("GIS系统标的上图_清单导出_" + fileTimeStr, StandardCharsets.UTF_8);
String sheetName = "标的清单";
ExcelExportUtil.ExportConfig<ExportItemListVo> config = ExcelExportUtil.ExportConfig.<ExportItemListVo>builder()
.clazz(ExportItemListVo.class)
.sheetName(sheetName)
.fileNamePrefix(fileName)
.build();
excelExportUtil.export(
response,
config,
page -> itemMapMapper.exportItemList(params, companyCode, page)
);
}