使用EasyExcel实现海量数据导出

1. 概述

我们将对数据进行分片，每个分片由一个单独的任务来处理。然后使用线程池并发执行这些任务，最后将各个分片的结果合并成一个最终的 Excel 文件。

2. 修改数据服务

首先，确保 DataService 支持分片查询。我们可以通过传入起始和结束 ID 来实现分片查询。

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<UserData, Long> {

    // 分片查询方法
    @Query("SELECT u FROM UserData u WHERE u.id BETWEEN :startId AND :endId")
    List<UserData> findByRange(@Param("startId") Long startId, @Param("endId") Long endId);
}

3. 修改任务管理器

为了支持分片并发导出，我们需要在 ExportTask 中记录分片的状态。

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;

import org.springframework.stereotype.Service;

@Service
public class ExportTaskManager {

    private final ConcurrentHashMap<String, ExportTask> taskMap = new ConcurrentHashMap<>();

    public String createTask(int totalShards) {
        String taskId = UUID.randomUUID().toString();
        ExportTask task = new ExportTask();
        task.setTaskId(taskId);
        task.setStatus("PENDING");
        task.setProgress(0);
        task.setTotalShards(totalShards);
        task.setCompletedShards(0);
        task.setShardStatus(new ArrayList<>(totalShards));
        for (int i = 0; i < totalShards; i++) {
            task.getShardStatus().add(new ShardStatus(i, "PENDING"));
        }
        taskMap.put(taskId, task);
        return taskId;
    }

    public ExportTask getTask(String taskId) {
        return taskMap.get(taskId);
    }

    public void updateTaskStatus(String taskId, String status) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setStatus(status);
        }
    }

    public void updateTaskProgress(String taskId, int progress) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setProgress(progress);
        }
    }

    public void updateShardStatus(String taskId, int shardIndex, String status) {
        ExportTask task = taskMap.get(taskId);
        if (task != null && shardIndex >= 0 && shardIndex < task.getShardStatus().size()) {
            task.getShardStatus().get(shardIndex).setStatus(status);
        }
    }

    public void incrementCompletedShards(String taskId) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setCompletedShards(task.getCompletedShards() + 1);
            int progress = (int) ((double) task.getCompletedShards() / task.getTotalShards() * 100);
            task.setProgress(progress);
            if (task.getCompletedShards() == task.getTotalShards()) {
                task.setStatus("COMPLETED");
            }
        }
    }

    public void removeTask(String taskId) {
        taskMap.remove(taskId);
    }
}

class ExportTask {
    private String taskId;
    private String status;
    private int progress;
    private String filePath;
    private int totalShards;
    private int completedShards;
    private List<ShardStatus> shardStatus;

    // Getters and Setters
    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public int getProgress() {
        return progress;
    }

    public void setProgress(int progress) {
        this.progress = progress;
    }

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public int getTotalShards() {
        return totalShards;
    }

    public void setTotalShards(int totalShards) {
        this.totalShards = totalShards;
    }

    public int getCompletedShards() {
        return completedShards;
    }

    public void setCompletedShards(int completedShards) {
        this.completedShards = completedShards;
    }

    public List<ShardStatus> getShardStatus() {
        return shardStatus;
    }

    public void setShardStatus(List<ShardStatus> shardStatus) {
        this.shardStatus = shardStatus;
    }
}

class ShardStatus {
    private int shardIndex;
    private String status;

    public ShardStatus(int shardIndex, String status) {
        this.shardIndex = shardIndex;
        this.status = status;
    }

    // Getters and Setters
    public int getShardIndex() {
        return shardIndex;
    }

    public void setShardIndex(int shardIndex) {
        this.shardIndex = shardIndex;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

4. 修改导出服务

修改 ExportService 以支持分片并发导出。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Service
@EnableAsync
public class ExportService {

    @Autowired
    private DataService dataService;

    @Autowired
    private ExportTaskManager taskManager;

    private final String TEMP_DIR = System.getProperty("java.io.tmpdir") + File.separator + "excel_exports";
    private final int SHARD_SIZE = 100000; // 每个分片包含的数据量
    private final int THREAD_POOL_SIZE = 10; // 线程池大小

    public String startAsyncExport() {
        int totalCount = dataService.getTotalCount();
        int totalShards = (totalCount + SHARD_SIZE - 1) / SHARD_SIZE;
        String taskId = taskManager.createTask(totalShards);
        taskManager.updateTaskStatus(taskId, "STARTED");
        exportLargeDataAsync(taskId, totalShards);
        return taskId;
    }

    @Async
    public void exportLargeDataAsync(String taskId, int totalShards) {
        ExecutorService executorService = Executors.newFixedThreadPool(THREAD_POOL_SIZE);

        for (int shardIndex = 0; shardIndex < totalShards; shardIndex++) {
            long startId = (long) shardIndex * SHARD_SIZE + 1;
            long endId = Math.min(startId + SHARD_SIZE - 1, dataService.getTotalCount());
            executorService.submit(() -> exportShardAsync(taskId, shardIndex, startId, endId));
        }

        executorService.shutdown();
        while (!executorService.isTerminated()) {
            // 等待所有任务完成
        }

        // 合并分片文件
        mergeShards(taskId, totalShards);
    }

    @Async
    public void exportShardAsync(String taskId, int shardIndex, long startId, long endId) {
        try {
            taskManager.updateShardStatus(taskId, shardIndex, "STARTED");

            Path tempDirPath = Paths.get(TEMP_DIR);
            if (!Files.exists(tempDirPath)) {
                Files.createDirectories(tempDirPath);
            }

            File tempFile = new File(TEMP_DIR + File.separator + taskId + "_shard_" + shardIndex + ".xlsx");
            try (FileOutputStream fos = new FileOutputStream(tempFile)) {
                ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class);
                WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();

                int pageSize = 10000;
                for (long offset = startId; offset <= endId; offset += pageSize) {
                    long currentEndId = Math.min(offset + pageSize - 1, endId);
                    List<UserData> dataBatch = dataService.findByRange(offset, currentEndId);
                    writerBuilder.sheet(writeSheet).doWrite(dataBatch);
                    dataBatch.clear();
                }

                writerBuilder.finish();
                taskManager.updateShardStatus(taskId, shardIndex, "COMPLETED");
                taskManager.incrementCompletedShards(taskId);
            }
        } catch (Exception e) {
            e.printStackTrace();
            taskManager.updateShardStatus(taskId, shardIndex, "FAILED");
        }
    }

    private void mergeShards(String taskId, int totalShards) {
        File finalFile = new File(TEMP_DIR + File.separator + taskId + ".xlsx");
        try (FileOutputStream fos = new FileOutputStream(finalFile)) {
            ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class);
            WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();

            for (int shardIndex = 0; shardIndex < totalShards; shardIndex++) {
                File shardFile = new File(TEMP_DIR + File.separator + taskId + "_shard_" + shardIndex + ".xlsx");
                if (shardFile.exists()) {
                    EasyExcel.read(shardFile, UserData.class, new NoOpAnalysisEventListener<>())
                            .sheet()
                            .doRead();
                    EasyExcel.read(shardFile, UserData.class)
                            .sheet()
                            .doWrite(writerBuilder.sheet(writeSheet));
                    shardFile.delete(); // 删除临时分片文件
                }
            }

            writerBuilder.finish();
            taskManager.updateTaskStatus(taskId, "COMPLETED");
            taskManager.getTask(taskId).setFilePath(finalFile.getAbsolutePath());
        } catch (Exception e) {
            e.printStackTrace();
            taskManager.updateTaskStatus(taskId, "FAILED");
        }
    }

    public ExportTask getTaskStatus(String taskId) {
        return taskManager.getTask(taskId);
    }

    public File getExportFile(String taskId) {
        ExportTask task = taskManager.getTask(taskId);
        if (task != null && "COMPLETED".equals(task.getStatus())) {
            return new File(task.getFilePath());
        }
        return null;
    }
}

5. 创建控制器

控制器部分保持不变，仍然使用 ExportController 来处理 HTTP 请求。

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.File;

@RestController
@RequestMapping("/api/export")
public class ExportController {

    @Autowired
    private ExportService exportService;

    @PostMapping("/start")
    public String startExport() {
        return exportService.startAsyncExport();
    }

    @GetMapping("/status/{taskId}")
    public ExportTask getExportStatus(@PathVariable String taskId) {
        return exportService.getTaskStatus(taskId);
    }

    @GetMapping("/download/{taskId}")
    public ResponseEntity<Resource> downloadExport(@PathVariable String taskId, HttpServletResponse response) {
        File file = exportService.getExportFile(taskId);
        if (file == null || !file.exists()) {
            return ResponseEntity.notFound().build();
        }

        Resource resource = new FileSystemResource(file);

        return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + file.getName() + "\"")
                .contentType(MediaType.APPLICATION_OCTET_STREAM)
                .body(resource);
    }
}

6. 优化建议

  1. 增加缓冲区大小：
    调整 EasyExcel 的缓冲区大小以平衡内存使用和性能。

    ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class).bufferSize(100);

  2. 使用 CSV 格式：
    如果不需要复杂的格式（如合并单元格、样式等），导出为 CSV 格式可以进一步降低内存占用。

    import com.alibaba.excel.support.ExcelTypeEnum;

ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class).excelType(ExcelTypeEnum.CSV);

  3. 监控和日志：
    添加监控和日志记录，以便更好地跟踪任务的状态和性能。

    import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Service
@EnableAsync
public class ExportService {

    private static final Logger logger = LoggerFactory.getLogger(ExportService.class);

    // 其他方法...

    @Async
    public void exportShardAsync(String taskId, int shardIndex, long startId, long endId) {
        try {
            taskManager.updateShardStatus(taskId, shardIndex, "STARTED");
            logger.info("Starting shard {} of task {}", shardIndex, taskId);

            // 导出逻辑...

            taskManager.updateShardStatus(taskId, shardIndex, "COMPLETED");
            taskManager.incrementCompletedShards(taskId);
            logger.info("Completed shard {} of task {}", shardIndex, taskId);
        } catch (Exception e) {
            e.printStackTrace();
            taskManager.updateShardStatus(taskId, shardIndex, "FAILED");
            logger.error("Failed shard {} of task {}: {}", shardIndex, taskId, e.getMessage());
        }
    }
}

  4. 错误处理：
    在合并分片时，确保处理可能的错误情况。

    private void mergeShards(String taskId, int totalShards) {
    File finalFile = new File(TEMP_DIR + File.separator + taskId + ".xlsx");
    try (FileOutputStream fos = new FileOutputStream(finalFile)) {
        ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class);
        WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();

        for (int shardIndex = 0; shardIndex < totalShards; shardIndex++) {
            File shardFile = new File(TEMP_DIR + File.separator + taskId + "_shard_" + shardIndex + ".xlsx");
            if (shardFile.exists()) {
                EasyExcel.read(shardFile, UserData.class, new NoOpAnalysisEventListener<>())
                        .sheet()
                        .doRead();
                EasyExcel.read(shardFile, UserData.class)
                        .sheet()
                        .doWrite(writerBuilder.sheet(writeSheet));
                shardFile.delete(); // 删除临时分片文件
            } else {
                logger.warn("Shard file {} does not exist for task {}", shardFile.getAbsolutePath(), taskId);
            }
        }

        writerBuilder.finish();
        taskManager.updateTaskStatus(taskId, "COMPLETED");
        taskManager.getTask(taskId).setFilePath(finalFile.getAbsolutePath());
        logger.info("Merged shards for task {}", taskId);
    } catch (Exception e) {
        e.printStackTrace();
        taskManager.updateTaskStatus(taskId, "FAILED");
        logger.error("Failed to merge shards for task {}: {}", taskId, e.getMessage());
    }
}

7. 完整代码示例

UserData.java

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class UserData {
    @ExcelProperty("用户ID")
    private Long id;

    @ExcelProperty("用户名")
    private String name;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("手机号")
    private String phoneNumber;
}

DataService.java

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<UserData, Long> {

    // 分片查询方法
    @Query("SELECT u FROM UserData u WHERE u.id BETWEEN :startId AND :endId")
    List<UserData> findByRange(@Param("startId") Long startId, @Param("endId") Long endId);
}

interface DataService {
    List<UserData> findByRange(Long startId, Long endId);
    int getTotalCount();
}

@Service
public class DataServiceImpl implements DataService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<UserData> findByRange(Long startId, Long endId) {
        return userRepository.findByRange(startId, endId);
    }

    @Override
    public int getTotalCount() {
        return (int) userRepository.count();
    }
}

ExportTaskManager.java

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;

import org.springframework.stereotype.Service;

@Service
public class ExportTaskManager {

    private final ConcurrentHashMap<String, ExportTask> taskMap = new ConcurrentHashMap<>();

    public String createTask(int totalShards) {
        String taskId = UUID.randomUUID().toString();
        ExportTask task = new ExportTask();
        task.setTaskId(taskId);
        task.setStatus("PENDING");
        task.setProgress(0);
        task.setTotalShards(totalShards);
        task.setCompletedShards(0);
        task.setShardStatus(new ArrayList<>(totalShards));
        for (int i = 0; i < totalShards; i++) {
            task.getShardStatus().add(new ShardStatus(i, "PENDING"));
        }
        taskMap.put(taskId, task);
        return taskId;
    }

    public ExportTask getTask(String taskId) {
        return taskMap.get(taskId);
    }

    public void updateTaskStatus(String taskId, String status) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setStatus(status);
        }
    }

    public void updateTaskProgress(String taskId, int progress) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setProgress(progress);
        }
    }

    public void updateShardStatus(String taskId, int shardIndex, String status) {
        ExportTask task = taskMap.get(taskId);
        if (task != null && shardIndex >= 0 && shardIndex < task.getShardStatus().size()) {
            task.getShardStatus().get(shardIndex).setStatus(status);
        }
    }

    public void incrementCompletedShards(String taskId) {
        ExportTask task = taskMap.get(taskId);
        if (task != null) {
            task.setCompletedShards(task.getCompletedShards() + 1);
            int progress = (int) ((double) task.getCompletedShards() / task.getTotalShards() * 100);
            task.setProgress(progress);
            if (task.getCompletedShards() == task.getTotalShards()) {
                task.setStatus("COMPLETED");
            }
        }
    }

    public void removeTask(String taskId) {
        taskMap.remove(taskId);
    }
}

class ExportTask {
    private String taskId;
    private String status;
    private int progress;
    private String filePath;
    private int totalShards;
    private int completedShards;
    private List<ShardStatus> shardStatus;

    // Getters and Setters
    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public int getProgress() {
        return progress;
    }

    public void setProgress(int progress) {
        this.progress = progress;
    }

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public int getTotalShards() {
        return totalShards;
    }

    public void setTotalShards(int totalShards) {
        this.totalShards = totalShards;
    }

    public int getCompletedShards() {
        return completedShards;
    }

    public void setCompletedShards(int completedShards) {
        this.completedShards = completedShards;
    }

    public List<ShardStatus> getShardStatus() {
        return shardStatus;
    }

    public void setShardStatus(List<ShardStatus> shardStatus) {
        this.shardStatus = shardStatus;
    }
}

class ShardStatus {
    private int shardIndex;
    private String status;

    public ShardStatus(int shardIndex, String status) {
        this.shardIndex = shardIndex;
        this.status = status;
    }

    // Getters and Setters
    public int getShardIndex() {
        return shardIndex;
    }

    public void setShardIndex(int shardIndex) {
        this.shardIndex = shardIndex;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

ExportService.java

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Service
@EnableAsync
public class ExportService {

    private static final Logger logger = LoggerFactory.getLogger(ExportService.class);

    @Autowired
    private DataService dataService;

    @Autowired
    private ExportTaskManager taskManager;

    private final String TEMP_DIR = System.getProperty("java.io.tmpdir") + File.separator + "excel_exports";
    private final int SHARD_SIZE = 100000; // 每个分片包含的数据量
    private final int THREAD_POOL_SIZE = 10; // 线程池大小
    private final int PAGE_SIZE = 10000; // 每次读取的数据量

    public String startAsyncExport() {
        int totalCount = dataService.getTotalCount();
        int totalShards = (totalCount + SHARD_SIZE - 1) / SHARD_SIZE;
        String taskId = taskManager.createTask(totalShards);
        taskManager.updateTaskStatus(taskId, "STARTED");
        exportLargeDataAsync(taskId, totalShards);
        return taskId;
    }

    @Async
    public void exportLargeDataAsync(String taskId, int totalShards) {
        ExecutorService executorService = Executors.newFixedThreadPool(THREAD_POOL_SIZE);

        for (int shardIndex = 0; shardIndex < totalShards; shardIndex++) {
            long startId = (long) shardIndex * SHARD_SIZE + 1;
            long endId = Math.min(startId + SHARD_SIZE - 1, dataService.getTotalCount());
            executorService.submit(() -> exportShardAsync(taskId, shardIndex, startId, endId));
        }

        executorService.shutdown();
        while (!executorService.isTerminated()) {
            // 等待所有任务完成
        }

        // 合并分片文件
        mergeShards(taskId, totalShards);
    }

    @Async
    public void exportShardAsync(String taskId, int shardIndex, long startId, long endId) {
        try {
            taskManager.updateShardStatus(taskId, shardIndex, "STARTED");
            logger.info("Starting shard {} of task {}", shardIndex, taskId);

            Path tempDirPath = Paths.get(TEMP_DIR);
            if (!Files.exists(tempDirPath)) {
                Files.createDirectories(tempDirPath);
            }

            File tempFile = new File(TEMP_DIR + File.separator + taskId + "_shard_" + shardIndex + ".xlsx");
            try (FileOutputStream fos = new FileOutputStream(tempFile)) {
                ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class).bufferSize(100);
                WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();

                for (long offset = startId; offset <= endId; offset += PAGE_SIZE) {
                    long currentEndId = Math.min(offset + PAGE_SIZE - 1, endId);
                    List<UserData> dataBatch = dataService.findByRange(offset, currentEndId);
                    writerBuilder.sheet(writeSheet).doWrite(dataBatch);
                    dataBatch.clear();
                }

                writerBuilder.finish();
                taskManager.updateShardStatus(taskId, shardIndex, "COMPLETED");
                taskManager.incrementCompletedShards(taskId);
                logger.info("Completed shard {} of task {}", shardIndex, taskId);
            }
        } catch (Exception e) {
            e.printStackTrace();
            taskManager.updateShardStatus(taskId, shardIndex, "FAILED");
            logger.error("Failed shard {} of task {}: {}", shardIndex, taskId, e.getMessage());
        }
    }

    private void mergeShards(String taskId, int totalShards) {
        File finalFile = new File(TEMP_DIR + File.separator + taskId + ".xlsx");
        try (FileOutputStream fos = new FileOutputStream(finalFile)) {
            ExcelWriterBuilder writerBuilder = EasyExcel.write(fos, UserData.class).bufferSize(100);
            WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();

            for (int shardIndex = 0; shardIndex < totalShards; shardIndex++) {
                File shardFile = new File(TEMP_DIR + File.separator + taskId + "_shard_" + shardIndex + ".xlsx");
                if (shardFile.exists()) {
                    EasyExcel.read(shardFile, UserData.class, new NoOpAnalysisEventListener<>())
                            .sheet()
                            .doRead();
                    EasyExcel.read(shardFile, UserData.class)
                            .sheet()
                            .doWrite(writerBuilder.sheet(writeSheet));
                    shardFile.delete(); // 删除临时分片文件
                    logger.info("Merged shard {} of task {}", shardIndex, taskId);
                } else {
                    logger.warn("Shard file {} does not exist for task {}", shardFile.getAbsolutePath(), taskId);
                }
            }

            writerBuilder.finish();
            taskManager.updateTaskStatus(taskId, "COMPLETED");
            taskManager.getTask(taskId).setFilePath(finalFile.getAbsolutePath());
            logger.info("Merged all shards for task {}", taskId);
        } catch (Exception e) {
            e.printStackTrace();
            taskManager.updateTaskStatus(taskId, "FAILED");
            logger.error("Failed to merge shards for task {}: {}", taskId, e.getMessage());
        }
    }

    public ExportTask getTaskStatus(String taskId) {
        return taskManager.getTask(taskId);
    }

    public File getExportFile(String taskId) {
        ExportTask task = taskManager.getTask(taskId);
        if (task != null && "COMPLETED".equals(task.getStatus())) {
            return new File(task.getFilePath());
        }
        return null;
    }

    private static class NoOpAnalysisEventListener extends AnalysisEventListener<UserData> {
        @Override
        public void invoke(UserData data, AnalysisContext context) {
            // Do nothing
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // Do nothing
        }
    }
}

ExportController.java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.File;

@RestController
@RequestMapping("/api/export")
public class ExportController {

    @Autowired
    private ExportService exportService;

    @PostMapping("/start")
    public String startExport() {
        return exportService.startAsyncExport();
    }

    @GetMapping("/status/{taskId}")
    public ExportTask getExportStatus(@PathVariable String taskId) {
        return exportService.getTaskStatus(taskId);
    }

    @GetMapping("/download/{taskId}")
    public ResponseEntity<Resource> downloadExport(@PathVariable String taskId, HttpServletResponse response) {
        File file = exportService.getExportFile(taskId);
        if (file == null || !file.exists()) {
            return ResponseEntity.notFound().build();
        }

        Resource resource = new FileSystemResource(file);

        return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + file.getName() + "\"")
                .contentType(MediaType.APPLICATION_OCTET_STREAM)
                .body(resource);
    }
}

ExcelExportApplication.java

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;

@SpringBootApplication
@EnableAsync
public class ExcelExportApplication {

    public static void main(String[] args) {
        SpringApplication.run(ExcelExportApplication.class, args);
    }
}

8. 运行和测试

  1. 启动应用：
    运行你的 Spring Boot 应用。

  2. 启动导出任务：
    发送 POST 请求到 /api/export/start 启动导出任务，并获取任务 ID。

    curl -X POST http://localhost:8080/api/export/start

    响应示例：

    "9d7b6e5c-8f42-4b3d-8d1c-8d7b6e5c8f42"

  3. 查询任务状态：
    发送 GET 请求到 /api/export/status/{taskId} 查询任务状态。

    curl http://localhost:8080/api/export/status/9d7b6e5c-8f42-4b3d-8d1c-8d7b6e5c8f42

    响应示例：

    {
  "taskId": "9d7b6e5c-8f42-4b3d-8d1c-8d7b6e5c8f42",
  "status": "COMPLETED",
  "progress": 100,
  "filePath": "/tmp/excel_exports/9d7b6e5c-8f42-4b3d-8d1c-8d7b6e5c8f42.xlsx",
  "totalShards": 10,
  "completedShards": 10,
  "shardStatus": [
    {"shardIndex": 0, "status": "COMPLETED"},
    {"shardIndex": 1, "status": "COMPLETED"},
    {"shardIndex": 2, "status": "COMPLETED"},
    {"shardIndex": 3, "status": "COMPLETED"},
    {"shardIndex": 4, "status": "COMPLETED"},
    {"shardIndex": 5, "status": "COMPLETED"},
    {"shardIndex": 6, "status": "COMPLETED"},
    {"shardIndex": 7, "status": "COMPLETED"},
    {"shardIndex": 8, "status": "COMPLETED"},
    {"shardIndex": 9, "status": "COMPLETED"}
  ]
}

  4. 下载导出文件：
    当任务状态为 COMPLETED 时，发送 GET 请求到 /api/export/download/{taskId} 下载导出文件。

    curl -OJ http://localhost:8080/api/export/download/9d7b6e5c-8f42-4b3d-8d1c-8d7b6e5c8f42

9. 总结

通过上述步骤，我们实现了支持分片并发访问的高效异步导出系统。以下是关键点的总结：

  • 分片查询：通过传入起始和结束 ID 来实现分片查询。
  • 异步导出：使用 Spring 的 @Async 注解实现异步任务，提升用户体验。
  • 任务管理：使用 ExportTaskManager 来存储和管理导出任务的状态，包括分片状态。
  • 进度提示：通过 API 查询任务状态和进度。
  • 文件下载：提供下载接口，允许用户下载已完成的导出文件。
  • 分片并发：通过线程池并发处理多个分片的数据导出，显著提高导出速度。
  • 合并分片：将各个分片的结果合并成一个最终的 Excel 文件。

希望这个示例对你有所帮助！如果有任何问题或需要进一步的优化建议，请随时提问。

posted @ 2025-04-28 15:42  好奇成传奇  阅读(243)  评论(0)    收藏  举报