在企业级应用中，Excel 导出是常见的功能需求。当导出数据量较大时，同步导出会导致接口超时、用户体验差等问题。本文将详细介绍如何实现 Excel 高性能异步导出方案，解决大数据量导出的性能瓶颈。
目录
为什么需要异步导出
同步导出的问题
• 数据量大时，接口响应时间长，容易超时• 占用 Tomcat 线程，影响其他请求处理• 用户需要等待，体验差• 内存消耗大，容易 OOM• 网络波动可能导致导出失败
真实场景
- 报表导出：财务报表、销售报表等，数据量可达百万级别
异步导出的优势
整体架构设计
系统架构图
flowchart TB subgraph 客户端层 User[用户] Browser[浏览器] App[移动App] end subgraph 应用服务层 SpringBoot[SpringBoot应用] ExportController[导出控制器] ExportService[导出服务] TaskService[任务管理服务] AsyncService[异步处理服务] end subgraph 处理层 ExcelGenerator[Excel生成器] DataService[数据查询服务] FileService[文件存储服务] end subgraph 存储层 LocalStorage[(本地存储)] ObjectStorage[(对象存储<br/>S3/OBS)] DB[(数据库<br/>任务记录)] Redis[(Redis<br/>任务状态)] end User --> Browser User --> App Browser --> ExportController App --> ExportController ExportController --> ExportService ExportService --> TaskService ExportService --> AsyncService AsyncService --> ExcelGenerator ExcelGenerator --> DataService ExcelGenerator --> FileService FileService --> LocalStorage FileService --> ObjectStorage TaskService --> DB TaskService --> Redis DataService --> DB
核心工作流程
技术选型
核心实现方案
1. 依赖配置
<dependencies><!-- Spring Boot Web --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- EasyExcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.4</version></dependency><!-- Spring Data Redis --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis</artifactId></dependency><!-- Spring Data JPA --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!-- MySQL Driver --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- Commons IO --><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.11.0</version></dependency><!-- Lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency></dependencies>
2. 任务实体设计
@Data@Entity@Table(name = "export_task")publicclassExportTask{@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Long id;@Column(name = "task_id", unique = true, nullable = false)private String taskId;@Column(name = "user_id", nullable = false)private String userId;@Column(name = "export_type", nullable = false)private String exportType;@Column(name = "params", columnDefinition = "text")private String params;@Column(name = "status", nullable = false)private String status; // PENDING, PROCESSING, COMPLETED, FAILED@Column(name = "progress")private Integer progress; // 0-100@Column(name = "file_path")private String filePath;@Column(name = "file_name")private String fileName;@Column(name = "file_size")private Long fileSize;@Column(name = "error_message")private String errorMessage;@Column(name = "create_time", nullable = false)private LocalDateTime createTime;@Column(name = "update_time")private LocalDateTime updateTime;@Column(name = "expire_time")private LocalDateTime expireTime;}
3. 导出服务接口
publicinterfaceExportService{/** * 创建导出任务 */ExportTask createTask(String userId, String exportType, Map<String, Object> params);/** * 获取任务状态 */ExportTask getTaskStatus(String taskId);/** * 取消任务 */booleancancelTask(String taskId);/** * 获取导出文件 */File getExportFile(String taskId);/** * 清理过期任务 */voidcleanupExpiredTasks();}
4. 异步导出实现
@Service@Slf4jpublicclassAsyncExportService{@Autowiredprivate TaskService taskService;@Autowiredprivate ExcelGenerator excelGenerator;@Autowiredprivate FileService fileService;@Async("exportTaskExecutor")publicvoidexportData(String taskId, String exportType, Map<String, Object> params){ ExportTask task = null;try {// 1. 更新任务状态为处理中 task = taskService.getTaskByTaskId(taskId); taskService.updateStatus(taskId, "PROCESSING", 0);// 2. 根据导出类型执行不同的导出逻辑switch (exportType) {case"user": exportUserList(taskId, params);break;case"order": exportOrderList(taskId, params);break;case"report": exportReport(taskId, params);break;default:thrownew IllegalArgumentException("Unsupported export type: " + exportType); } } catch (Exception e) { log.error("Export failed: taskId={}, error={}", taskId, e.getMessage(), e);if (task != null) { taskService.updateStatus(taskId, "FAILED", 0, e.getMessage()); } } }privatevoidexportUserList(String taskId, Map<String, Object> params){// 1. 计算总数据量long totalCount = userService.countUsers(params);int pageSize = 10000;int totalPages = (int) Math.ceil((double) totalCount / pageSize);// 2. 创建Excel文件 String fileName = "用户列表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx"; String filePath = fileService.createTempFile(fileName);try (OutputStream outputStream = new FileOutputStream(filePath)) {// 3. 初始化Excel写入器 ExcelWriter excelWriter = EasyExcel.write(outputStream, UserExportDTO.class) .registerWriteHandler(newLongestMatchColumnWidthStyleStrategy()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("用户列表").build();// 4. 分页查询并写入数据for (int pageNum = 1; pageNum <= totalPages; pageNum++) {int progress = (int) ((double) pageNum / totalPages * 100); taskService.updateStatus(taskId, "PROCESSING", progress);// 分页查询数据 List<UserExportDTO> userList = userService.getUsers(params, pageNum, pageSize);// 写入数据 excelWriter.write(userList, writeSheet);// 清理内存 userList.clear(); }// 5. 完成写入 excelWriter.finish();// 6. 计算文件大小 File file = new File(filePath);long fileSize = file.length();// 7. 更新任务状态为完成 taskService.updateStatus(taskId, "COMPLETED", 100, null); taskService.updateFileInfo(taskId, filePath, fileName, fileSize); } catch (Exception e) {thrownew RuntimeException("Export user list failed", e); } }}
5. 导出控制器
@RestController@RequestMapping("/api/export")@Slf4jpublicclassExportController{@Autowiredprivate ExportService exportService;@Autowiredprivate AsyncExportService asyncExportService;/** * 创建导出任务 */@PostMapping("/create")public ResponseEntity<ApiResponse<ExportTaskResponse>> createExportTask(@RequestBody@Valid ExportRequest request) {try {// 创建导出任务 ExportTask task = exportService.createTask( request.getUserId(), request.getExportType(), request.getParams() );// 异步执行导出 asyncExportService.exportData( task.getTaskId(), request.getExportType(), request.getParams() ); ExportTaskResponse response = ExportTaskResponse.builder() .taskId(task.getTaskId()) .status(task.getStatus()) .createTime(task.getCreateTime()) .message("导出任务已创建，正在处理中") .build();return ResponseEntity.ok(ApiResponse.success(response)); } catch (Exception e) { log.error("Create export task failed", e);return ResponseEntity.badRequest() .body(ApiResponse.error("创建导出任务失败: " + e.getMessage())); } }/** * 查询任务状态 */@GetMapping("/status/{taskId}")public ResponseEntity<ApiResponse<ExportTaskStatusResponse>> getTaskStatus(@PathVariable String taskId) {try { ExportTask task = exportService.getTaskStatus(taskId); ExportTaskStatusResponse response = ExportTaskStatusResponse.builder() .taskId(task.getTaskId()) .status(task.getStatus()) .progress(task.getProgress()) .fileName(task.getFileName()) .fileSize(task.getFileSize()) .errorMessage(task.getErrorMessage()) .updateTime(task.getUpdateTime()) .build();return ResponseEntity.ok(ApiResponse.success(response)); } catch (Exception e) { log.error("Get task status failed", e);return ResponseEntity.badRequest() .body(ApiResponse.error("查询任务状态失败: " + e.getMessage())); } }/** * 下载导出文件 */@GetMapping("/download/{taskId}")public ResponseEntity<?> downloadFile(@PathVariable String taskId) {try { ExportTask task = exportService.getTaskStatus(taskId);if (!"COMPLETED".equals(task.getStatus())) {return ResponseEntity.badRequest() .body(ApiResponse.error("导出任务尚未完成")); } File file = exportService.getExportFile(taskId);return ResponseEntity.ok() .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode(task.getFileName(), "UTF-8")) .header(HttpHeaders.CONTENT_TYPE, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") .header(HttpHeaders.CONTENT_LENGTH, String.valueOf(file.length())) .body(new FileSystemResource(file)); } catch (Exception e) { log.error("Download file failed", e);return ResponseEntity.badRequest() .body(ApiResponse.error("下载文件失败: " + e.getMessage())); } }/** * 取消导出任务 */@PostMapping("/cancel/{taskId}")public ResponseEntity<ApiResponse<String>> cancelTask(@PathVariable String taskId) {try {boolean success = exportService.cancelTask(taskId);if (success) {return ResponseEntity.ok(ApiResponse.success("任务已取消")); } else {return ResponseEntity.badRequest() .body(ApiResponse.error("取消任务失败")); } } catch (Exception e) { log.error("Cancel task failed", e);return ResponseEntity.badRequest() .body(ApiResponse.error("取消任务失败: " + e.getMessage())); } }}
Excel 生成优化
1. 流式写入
/** * 流式写入Excel，避免内存溢出 */privatevoidstreamWriteExcel(String filePath, List<UserExportDTO> dataList){// 使用 EasyExcel 流式写入 EasyExcel.write(filePath, UserExportDTO.class) .registerWriteHandler(newLongestMatchColumnWidthStyleStrategy()) .sheet("用户列表") .doWrite(newAbstractList<UserExportDTO>() {@Overridepublic UserExportDTO get(int index){// 按需生成数据，避免一次性加载全部数据到内存return dataList.get(index); }@Overridepublicintsize(){return dataList.size(); } });}
2. 分页查询
/** * 分页查询数据 */privatevoidexportWithPagination(String taskId, Map<String, Object> params){long totalCount = userService.countUsers(params);int pageSize = 5000; // 每页5000条int totalPages = (int) Math.ceil((double) totalCount / pageSize); String filePath = createTempFile();try (OutputStream outputStream = new FileOutputStream(filePath)) { ExcelWriter excelWriter = EasyExcel.write(outputStream, UserExportDTO.class).build(); WriteSheet writeSheet = EasyExcel.writerSheet("用户列表").build();for (int pageNum = 1; pageNum <= totalPages; pageNum++) {// 更新进度int progress = (int) ((double) pageNum / totalPages * 100); taskService.updateStatus(taskId, "PROCESSING", progress);// 分页查询 List<UserExportDTO> pageData = userService.getUsers(params, pageNum, pageSize);// 写入数据 excelWriter.write(pageData, writeSheet);// 清理内存 pageData.clear(); } excelWriter.finish(); }}
3. 内存优化
4. 并行处理
/** * 并行处理多Sheet */privatevoidparallelExportSheets(String taskId, Map<String, Object> params){ String filePath = createTempFile();try (OutputStream outputStream = new FileOutputStream(filePath)) { ExcelWriter excelWriter = EasyExcel.write(outputStream).build();// 并行处理多个Sheet CompletableFuture<Void> sheet1Future = CompletableFuture.runAsync(() -> { writeUserSheet(excelWriter, params, taskId, 0); }); CompletableFuture<Void> sheet2Future = CompletableFuture.runAsync(() -> { writeOrderSheet(excelWriter, params, taskId, 50); });// 等待所有Sheet处理完成 CompletableFuture.allOf(sheet1Future, sheet2Future).join(); excelWriter.finish(); }}
任务管理与状态跟踪
1. 任务状态管理
@ServicepublicclassTaskService{@Autowiredprivate ExportTaskRepository taskRepository;@Autowiredprivate StringRedisTemplate redisTemplate;privatestaticfinal String TASK_STATUS_PREFIX = "export:task:status:";privatestaticfinal String TASK_PROGRESS_PREFIX = "export:task:progress:";/** * 更新任务状态 */publicvoidupdateStatus(String taskId, String status, Integer progress){// 更新数据库 ExportTask task = taskRepository.findByTaskId(taskId);if (task != null) { task.setStatus(status); task.setProgress(progress); task.setUpdateTime(LocalDateTime.now()); taskRepository.save(task); }// 更新Redis缓存 redisTemplate.opsForValue().set(TASK_STATUS_PREFIX + taskId, status);if (progress != null) { redisTemplate.opsForValue().set(TASK_PROGRESS_PREFIX + taskId, progress.toString()); } }/** * 获取任务状态（优先从缓存获取） */public ExportTask getTaskStatus(String taskId){// 先从缓存获取状态 String status = redisTemplate.opsForValue().get(TASK_STATUS_PREFIX + taskId); String progressStr = redisTemplate.opsForValue().get(TASK_PROGRESS_PREFIX + taskId);// 从数据库获取完整信息 ExportTask task = taskRepository.findByTaskId(taskId);if (task == null) {thrownew TaskNotFoundException("Task not found: " + taskId); }// 更新缓存中的状态到任务对象if (status != null) { task.setStatus(status); }if (progressStr != null) { task.setProgress(Integer.parseInt(progressStr)); }return task; }/** * 取消任务 */publicbooleancancelTask(String taskId){ ExportTask task = taskRepository.findByTaskId(taskId);if (task == null) {returnfalse; }// 只有待处理或处理中的任务可以取消if ("PENDING".equals(task.getStatus()) || "PROCESSING".equals(task.getStatus())) { task.setStatus("CANCELLED"); task.setUpdateTime(LocalDateTime.now()); taskRepository.save(task);// 更新缓存 redisTemplate.opsForValue().set(TASK_STATUS_PREFIX + taskId, "CANCELLED");returntrue; }returnfalse; }/** * 清理过期任务 */@Scheduled(cron = "0 0 0 * * ?") // 每天凌晨执行publicvoidcleanupExpiredTasks(){ LocalDateTime cutoffTime = LocalDateTime.now().minusDays(7); List<ExportTask> expiredTasks = taskRepository.findByExpireTimeBefore(cutoffTime);for (ExportTask task : expiredTasks) {// 删除文件if (task.getFilePath() != null) { fileService.deleteFile(task.getFilePath()); }// 删除缓存 redisTemplate.delete(TASK_STATUS_PREFIX + task.getTaskId()); redisTemplate.delete(TASK_PROGRESS_PREFIX + task.getTaskId());// 删除数据库记录 taskRepository.delete(task); } }}
2. 实时进度查询
@RestController@RequestMapping("/api/export/progress")publicclassProgressController{@Autowiredprivate StringRedisTemplate redisTemplate;/** * 获取任务实时进度 */@GetMapping("/{taskId}")public ResponseEntity<ApiResponse<ProgressResponse>> getProgress(@PathVariable String taskId) { String status = redisTemplate.opsForValue().get("export:task:status:" + taskId); String progressStr = redisTemplate.opsForValue().get("export:task:progress:" + taskId); ProgressResponse response = ProgressResponse.builder() .taskId(taskId) .status(status != null ? status : "PENDING") .progress(progressStr != null ? Integer.parseInt(progressStr) : 0) .timestamp(LocalDateTime.now()) .build();return ResponseEntity.ok(ApiResponse.success(response)); }/** * 批量获取任务进度 */@PostMapping("/batch")public ResponseEntity<ApiResponse<Map<String, ProgressResponse>>> getBatchProgress(@RequestBody BatchProgressRequest request) { Map<String, ProgressResponse> progressMap = new HashMap<>();for (String taskId : request.getTaskIds()) { String status = redisTemplate.opsForValue().get("export:task:status:" + taskId); String progressStr = redisTemplate.opsForValue().get("export:task:progress:" + taskId); ProgressResponse response = ProgressResponse.builder() .taskId(taskId) .status(status != null ? status : "PENDING") .progress(progressStr != null ? Integer.parseInt(progressStr) : 0) .timestamp(LocalDateTime.now()) .build(); progressMap.put(taskId, response); }return ResponseEntity.ok(ApiResponse.success(progressMap)); }}
3. 任务队列管理
@ConfigurationpublicclassTaskExecutorConfig{@Bean(name = "exportTaskExecutor")public TaskExecutor exportTaskExecutor(){ ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor(); executor.setCorePoolSize(5); executor.setMaxPoolSize(10); executor.setQueueCapacity(100); executor.setThreadNamePrefix("export-task-"); executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy()); executor.initialize();return executor; }@Bean(name = "dataProcessExecutor")public TaskExecutor dataProcessExecutor(){ ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor(); executor.setCorePoolSize(10); executor.setMaxPoolSize(20); executor.setQueueCapacity(200); executor.setThreadNamePrefix("data-process-"); executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy()); executor.initialize();return executor; }}
文件存储与清理
1. 本地存储
@ServicepublicclassLocalFileServiceimplementsFileService{@Value("${file.storage.path:/tmp/export}")private String storagePath;@PostConstructpublicvoidinit(){ File directory = new File(storagePath);if (!directory.exists()) { directory.mkdirs(); } }@Overridepublic String createTempFile(String fileName){ String filePath = storagePath + File.separator + fileName; File file = new File(filePath);try {if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } file.createNewFile();return filePath; } catch (IOException e) {thrownew RuntimeException("Create temp file failed", e); } }@Overridepublic File getFile(String filePath){ File file = new File(filePath);if (!file.exists()) {thrownew FileNotFoundException("File not found: " + filePath); }return file; }@OverridepublicvoiddeleteFile(String filePath){ File file = new File(filePath);if (file.exists()) { file.delete(); } }@OverridepubliclonggetFileSize(String filePath){ File file = new File(filePath);return file.length(); }}
2. 对象存储（MinIO/S3）
@ServicepublicclassObjectStorageServiceimplementsFileService{@Autowiredprivate MinioClient minioClient;@Value("${minio.bucket.name:export}")private String bucketName;@PostConstructpublicvoidinit(){try {boolean found = minioClient.bucketExists(BucketExistsArgs.builder().bucket(bucketName).build());if (!found) { minioClient.makeBucket(MakeBucketArgs.builder().bucket(bucketName).build()); } } catch (Exception e) {thrownew RuntimeException("Initialize MinIO failed", e); } }@Overridepublic String createTempFile(String fileName){// 生成唯一对象键 String objectKey = "export/" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd")) + "/" + fileName;return objectKey; }@OverridepublicvoiduploadFile(String objectKey, InputStream inputStream, long size){try { minioClient.putObject( PutObjectArgs.builder() .bucket(bucketName) .object(objectKey) .stream(inputStream, size, -1) .contentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") .build() ); } catch (Exception e) {thrownew RuntimeException("Upload file failed", e); } }@Overridepublic InputStream getFile(String objectKey){try {return minioClient.getObject( GetObjectArgs.builder() .bucket(bucketName) .object(objectKey) .build() ); } catch (Exception e) {thrownew RuntimeException("Get file failed", e); } }@OverridepublicvoiddeleteFile(String objectKey){try { minioClient.removeObject( RemoveObjectArgs.builder() .bucket(bucketName) .object(objectKey) .build() ); } catch (Exception e) {thrownew RuntimeException("Delete file failed", e); } }}
3. 文件清理策略
4. 大文件处理
对于超大文件（>1GB），可以采用以下策略：
- 分块写入：将Excel分成多个Sheet或多个文件
完整代码示例
1. 项目结构
excel-async-export/├── src/│ ├── main/│ │ ├── java/com/example/excel/│ │ │ ├── ExcelApplication.java # 启动类│ │ │ ├── config/│ │ │ │ ├── TaskExecutorConfig.java # 任务执行器配置│ │ │ │ ├── RedisConfig.java # Redis配置│ │ │ │ └── MinioConfig.java # MinIO配置│ │ │ ├── controller/│ │ │ │ ├── ExportController.java # 导出控制器│ │ │ │ ├── ProgressController.java # 进度查询控制器│ │ │ │ └── DownloadController.java # 下载控制器│ │ │ ├── entity/│ │ │ │ └── ExportTask.java # 导出任务实体│ │ │ ├── repository/│ │ │ │ └── ExportTaskRepository.java # 任务仓库│ │ │ ├── service/│ │ │ │ ├── ExportService.java # 导出服务│ │ │ │ ├── TaskService.java # 任务管理服务│ │ │ │ ├── AsyncExportService.java # 异步导出服务│ │ │ │ ├── ExcelGenerator.java # Excel生成器│ │ │ │ ├── FileService.java # 文件服务接口│ │ │ │ ├── LocalFileService.java # 本地文件服务│ │ │ │ └── ObjectStorageService.java # 对象存储服务│ │ │ ├── dto/│ │ │ │ ├── ExportRequest.java # 导出请求│ │ │ │ ├── ExportTaskResponse.java # 任务响应│ │ │ │ └── UserExportDTO.java # 用户导出DTO│ │ │ ├── exception/│ │ │ │ ├── TaskNotFoundException.java # 任务未找到异常│ │ │ │ └── ExportException.java # 导出异常│ │ │ └── utils/│ │ │ ├── ExcelUtils.java # Excel工具类│ │ │ └── FileUtils.java # 文件工具类│ │ └── resources/│ │ ├── application.yml # 主配置文件│ │ └── application-prod.yml # 生产环境配置│ └── test/│ └── java/com/example/excel/│ ├── ExportServiceTest.java # 导出服务测试│ └── ExcelGeneratorTest.java # Excel生成测试├── docker/│ ├── docker-compose.yml # Docker Compose配置│ └── Dockerfile # 应用Dockerfile├── pom.xml # Maven配置└── README.md # 项目说明
2. Excel 生成器
@Service@Slf4jpublicclassExcelGenerator{@Autowiredprivate FileService fileService;/** * 生成Excel文件 */public <T> String generateExcel(String fileName, Class<T> clazz, List<T> dataList){ String filePath = fileService.createTempFile(fileName);try { EasyExcel.write(filePath, clazz) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("数据列表") .doWrite(dataList);return filePath; } catch (Exception e) { log.error("Generate Excel failed", e); fileService.deleteFile(filePath);thrownew RuntimeException("Generate Excel failed", e); } }/** * 流式生成Excel（大数据量） */public <T> String generateExcelStream(String fileName, Class<T> clazz, ExcelDataProvider<T> dataProvider){ String filePath = fileService.createTempFile(fileName);try (OutputStream outputStream = new FileOutputStream(filePath)) { ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("数据列表").build();// 分页获取数据int pageSize = 5000;int pageNum = 1; List<T> pageData;do { pageData = dataProvider.provide(pageNum, pageSize);if (!pageData.isEmpty()) { excelWriter.write(pageData, writeSheet); pageData.clear(); pageNum++; } } while (!pageData.isEmpty()); excelWriter.finish();return filePath; } catch (Exception e) { log.error("Generate Excel stream failed", e); fileService.deleteFile(filePath);thrownew RuntimeException("Generate Excel stream failed", e); } }/** * 数据提供器接口 */@FunctionalInterfacepublicinterfaceExcelDataProvider<T> {List<T> provide(int pageNum, int pageSize); }}
3. 导出服务实现
@Service@Slf4jpublicclassExportServiceImplimplementsExportService{@Autowiredprivate TaskService taskService;@Autowiredprivate FileService fileService;@Autowiredprivate ExcelGenerator excelGenerator;@Overridepublic ExportTask createTask(String userId, String exportType, Map<String, Object> params){// 生成任务ID String taskId = UUID.randomUUID().toString();// 创建任务记录 ExportTask task = new ExportTask(); task.setTaskId(taskId); task.setUserId(userId); task.setExportType(exportType); task.setParams(JSON.toJSONString(params)); task.setStatus("PENDING"); task.setProgress(0); task.setCreateTime(LocalDateTime.now()); task.setExpireTime(LocalDateTime.now().plusDays(7));// 保存任务 task = taskService.saveTask(task); log.info("Created export task: taskId={}, exportType={}, userId={}", taskId, exportType, userId);return task; }@Overridepublic ExportTask getTaskStatus(String taskId){return taskService.getTaskStatus(taskId); }@OverridepublicbooleancancelTask(String taskId){return taskService.cancelTask(taskId); }@Overridepublic File getExportFile(String taskId){ ExportTask task = taskService.getTaskByTaskId(taskId);if (task == null) {thrownew TaskNotFoundException("Task not found: " + taskId); }if (!"COMPLETED".equals(task.getStatus())) {thrownew ExportException("Export task is not completed"); }return fileService.getFile(task.getFilePath()); }@OverridepublicvoidcleanupExpiredTasks(){ taskService.cleanupExpiredTasks(); }}
4. 异步导出服务
@Service@Slf4jpublicclassAsyncExportService{@Autowiredprivate TaskService taskService;@Autowiredprivate ExcelGenerator excelGenerator;@Autowiredprivate FileService fileService;@Autowiredprivate UserService userService;@Autowiredprivate OrderService orderService;@Async("exportTaskExecutor")publicvoidexportData(String taskId, String exportType, Map<String, Object> params){ log.info("Start export task: taskId={}, exportType={}", taskId, exportType);try {// 更新任务状态为处理中 taskService.updateStatus(taskId, "PROCESSING", 0); String filePath = null; String fileName = null;switch (exportType) {case"user": fileName = "用户列表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx"; filePath = exportUserList(params);break;case"order": fileName = "订单列表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx"; filePath = exportOrderList(params);break;default:thrownew IllegalArgumentException("Unsupported export type: " + exportType); }// 计算文件大小long fileSize = fileService.getFileSize(filePath);// 更新任务状态为完成 taskService.updateStatus(taskId, "COMPLETED", 100); taskService.updateFileInfo(taskId, filePath, fileName, fileSize); log.info("Export task completed: taskId={}, fileName={}, fileSize={}", taskId, fileName, fileSize); } catch (Exception e) { log.error("Export task failed: taskId={}, error={}", taskId, e.getMessage(), e); taskService.updateStatus(taskId, "FAILED", 0, e.getMessage()); } }private String exportUserList(Map<String, Object> params){return excelGenerator.generateExcelStream("用户列表.xlsx", UserExportDTO.class, (pageNum, pageSize) -> userService.getUsers(params, pageNum, pageSize) ); }private String exportOrderList(Map<String, Object> params){return excelGenerator.generateExcelStream("订单列表.xlsx", OrderExportDTO.class, (pageNum, pageSize) -> orderService.getOrders(params, pageNum, pageSize) ); }}
性能测试与优化
1. 测试环境
2. 测试结果
3. 性能优化建议
4. 常见性能问题及解决方案
最佳实践总结
1. 架构设计
- 异步处理：使用Spring @Async实现异步导出
2. 代码规范
- 资源管理：使用try-with-resources管理资源
3. 部署建议
4. 安全措施
5. 扩展性
- 多格式支持：支持Excel、CSV、PDF等多种格式
6. 运维建议
小结
本文详细介绍了 Excel 高性能异步导出的完整方案，包括：
- 核心实现：异步导出服务、Excel生成器、任务管理
通过这套方案，可以有效解决大数据量Excel导出的性能问题，提高系统稳定性和用户体验。
互动话题
- 你在项目中遇到过哪些Excel导出的性能问题？如何解决的？
- 对于超大文件（>1GB）的导出，你有什么优化建议？
- 你认为Excel导出的最佳文件格式是什么？为什么？
