第64集EasyExcel-Excel文件处理与数据导入导出实战

1. EasyExcel概述

EasyExcel是阿里巴巴开源的一个基于Java的Excel处理工具，具有高性能、内存占用小、API简洁等特点。本文将详细介绍EasyExcel框架、Excel文件处理、数据导入导出、模板设计和性能优化的完整解决方案。

1.1 核心功能

Excel读取: 支持大文件读取，内存占用小
Excel写入: 支持大数据量写入，性能优异
模板处理: 支持Excel模板和样式处理
数据验证: 支持数据格式验证和错误处理
性能优化: 流式处理，避免内存溢出

1.2 技术架构

Excel文件 → EasyExcel → 数据模型 → 业务处理
    ↓         ↓         ↓         ↓
文件解析 → 数据转换 → 验证处理 → 结果输出
    ↓         ↓         ↓         ↓
流式处理 → 内存优化 → 异常处理 → 响应返回

2. EasyExcel配置

2.1 Maven依赖配置

<!-- pom.xml -->
<dependencies>
    <!-- EasyExcel核心依赖 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.2</version>
    </dependency>
    
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- Spring Boot Validation -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    
    <!-- Apache POI (可选，用于复杂Excel操作) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

2.2 EasyExcel配置类

/**
 * EasyExcel配置类
 */
@Configuration
public class EasyExcelConfig {
    
    @Value("${easyexcel.temp-path:/tmp}")
    private String tempPath;
    
    @Value("${easyexcel.max-rows:100000}")
    private int maxRows;
    
    @Value("${easyexcel.batch-size:1000}")
    private int batchSize;
    
    /**
     * EasyExcel配置属性
     */
    @Bean
    public EasyExcelProperties easyExcelProperties() {
        return EasyExcelProperties.builder()
            .tempPath(tempPath)
            .maxRows(maxRows)
            .batchSize(batchSize)
            .build();
    }
    
    /**
     * Excel文件处理器
     */
    @Bean
    public ExcelFileHandler excelFileHandler() {
        return new ExcelFileHandler(easyExcelProperties());
    }
    
    /**
     * Excel模板处理器
     */
    @Bean
    public ExcelTemplateHandler excelTemplateHandler() {
        return new ExcelTemplateHandler(easyExcelProperties());
    }
}

/**
 * EasyExcel配置属性
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class EasyExcelProperties {
    private String tempPath;
    private int maxRows;
    private int batchSize;
    
    // 文件配置
    private String uploadPath = "/uploads";
    private String downloadPath = "/downloads";
    private long maxFileSize = 10 * 1024 * 1024; // 10MB
    
    // 缓存配置
    private boolean enableCache = true;
    private int cacheSize = 1000;
    private long cacheExpireTime = 3600000; // 1小时
    
    // 线程池配置
    private int corePoolSize = 5;
    private int maxPoolSize = 20;
    private int queueCapacity = 100;
}

3. 数据模型定义

3.1 Excel数据模型

/**
 * Excel数据模型
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserExcelModel {
    
    @ExcelProperty(value = "用户ID", index = 0)
    private Long id;
    
    @ExcelProperty(value = "用户名", index = 1)
    @NotBlank(message = "用户名不能为空")
    @Length(max = 50, message = "用户名长度不能超过50个字符")
    private String username;
    
    @ExcelProperty(value = "邮箱", index = 2)
    @NotBlank(message = "邮箱不能为空")
    @Email(message = "邮箱格式不正确")
    private String email;
    
    @ExcelProperty(value = "手机号", index = 3)
    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式不正确")
    private String phone;
    
    @ExcelProperty(value = "年龄", index = 4)
    @Min(value = 1, message = "年龄必须大于0")
    @Max(value = 120, message = "年龄不能超过120")
    private Integer age;
    
    @ExcelProperty(value = "性别", index = 5)
    @Pattern(regexp = "^(男|女)$", message = "性别只能是男或女")
    private String gender;
    
    @ExcelProperty(value = "创建时间", index = 6)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;
    
    @ExcelProperty(value = "状态", index = 7)
    @Pattern(regexp = "^(启用|禁用)$", message = "状态只能是启用或禁用")
    private String status;
    
    @ExcelIgnore
    private String errorMessage;
    
    @ExcelIgnore
    private Integer rowIndex;
}

/**
 * 产品Excel模型
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ProductExcelModel {
    
    @ExcelProperty(value = "产品ID", index = 0)
    private Long id;
    
    @ExcelProperty(value = "产品名称", index = 1)
    @NotBlank(message = "产品名称不能为空")
    @Length(max = 100, message = "产品名称长度不能超过100个字符")
    private String name;
    
    @ExcelProperty(value = "产品描述", index = 2)
    @Length(max = 500, message = "产品描述长度不能超过500个字符")
    private String description;
    
    @ExcelProperty(value = "价格", index = 3)
    @DecimalMin(value = "0.01", message = "价格必须大于0")
    @DecimalMax(value = "999999.99", message = "价格不能超过999999.99")
    private BigDecimal price;
    
    @ExcelProperty(value = "库存", index = 4)
    @Min(value = 0, message = "库存不能小于0")
    private Integer stock;
    
    @ExcelProperty(value = "分类", index = 5)
    @NotBlank(message = "分类不能为空")
    private String category;
    
    @ExcelProperty(value = "品牌", index = 6)
    @NotBlank(message = "品牌不能为空")
    private String brand;
    
    @ExcelProperty(value = "创建时间", index = 7)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;
    
    @ExcelIgnore
    private String errorMessage;
    
    @ExcelIgnore
    private Integer rowIndex;
}

/**
 * Excel导入结果
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelImportResult<T> {
    private boolean success;
    private int totalRows;
    private int successRows;
    private int errorRows;
    private List<T> successData;
    private List<ExcelErrorInfo> errorData;
    private String message;
    private String filePath;
}

/**
 * Excel错误信息
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelErrorInfo {
    private Integer rowIndex;
    private String fieldName;
    private String fieldValue;
    private String errorMessage;
    private String errorType;
}

/**
 * Excel导出请求
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelExportRequest {
    private String fileName;
    private String sheetName;
    private Class<?> modelClass;
    private List<?> data;
    private Map<String, Object> templateData;
    private boolean useTemplate;
    private String templatePath;
}

4. Excel文件处理器

4.1 Excel文件处理器

/**
 * Excel文件处理器
 */
@Service
public class ExcelFileHandler {
    
    private final EasyExcelProperties properties;
    private final ExcelTemplateHandler templateHandler;
    
    public ExcelFileHandler(EasyExcelProperties properties) {
        this.properties = properties;
        this.templateHandler = new ExcelTemplateHandler(properties);
    }
    
    /**
     * 读取Excel文件
     * @param file 文件
     * @param modelClass 数据模型类
     * @return 读取结果
     */
    public <T> ExcelImportResult<T> readExcel(MultipartFile file, Class<T> modelClass) {
        try {
            // 1. 文件验证
            validateFile(file);
            
            // 2. 创建临时文件
            File tempFile = createTempFile(file);
            
            // 3. 读取Excel数据
            List<T> dataList = new ArrayList<>();
            List<ExcelErrorInfo> errorList = new ArrayList<>();
            
            EasyExcel.read(tempFile, modelClass, new ExcelDataListener<T>(dataList, errorList))
                .sheet()
                .doRead();
            
            // 4. 数据验证
            validateData(dataList, errorList);
            
            // 5. 构建结果
            ExcelImportResult<T> result = ExcelImportResult.<T>builder()
                .success(errorList.isEmpty())
                .totalRows(dataList.size())
                .successRows(dataList.size() - errorList.size())
                .errorRows(errorList.size())
                .successData(dataList.stream()
                    .filter(data -> getErrorMessage(data) == null)
                    .collect(Collectors.toList()))
                .errorData(errorList)
                .message(errorList.isEmpty() ? "导入成功" : "导入完成，存在错误数据")
                .filePath(tempFile.getAbsolutePath())
                .build();
            
            // 6. 清理临时文件
            if (!properties.isEnableCache()) {
                tempFile.delete();
            }
            
            return result;
            
        } catch (Exception e) {
            log.error("读取Excel文件失败", e);
            throw new BusinessException("读取Excel文件失败: " + e.getMessage());
        }
    }
    
    /**
     * 写入Excel文件
     * @param request 导出请求
     * @return 文件路径
     */
    public String writeExcel(ExcelExportRequest request) {
        try {
            // 1. 生成文件路径
            String fileName = generateFileName(request.getFileName());
            String filePath = properties.getDownloadPath() + "/" + fileName;
            
            // 2. 创建目录
            File directory = new File(properties.getDownloadPath());
            if (!directory.exists()) {
                directory.mkdirs();
            }
            
            // 3. 写入Excel
            if (request.isUseTemplate() && request.getTemplatePath() != null) {
                // 使用模板写入
                templateHandler.writeWithTemplate(request, filePath);
            } else {
                // 直接写入
                EasyExcel.write(filePath, request.getModelClass())
                    .sheet(request.getSheetName())
                    .doWrite(request.getData());
            }
            
            log.info("Excel文件写入成功: {}", filePath);
            
            return filePath;
            
        } catch (Exception e) {
            log.error("写入Excel文件失败", e);
            throw new BusinessException("写入Excel文件失败: " + e.getMessage());
        }
    }
    
    /**
     * 批量读取Excel文件
     * @param files 文件列表
     * @param modelClass 数据模型类
     * @return 读取结果
     */
    public <T> List<ExcelImportResult<T>> batchReadExcel(List<MultipartFile> files, Class<T> modelClass) {
        List<ExcelImportResult<T>> results = new ArrayList<>();
        
        for (MultipartFile file : files) {
            try {
                ExcelImportResult<T> result = readExcel(file, modelClass);
                results.add(result);
            } catch (Exception e) {
                log.error("批量读取Excel文件失败: {}", file.getOriginalFilename(), e);
                
                ExcelImportResult<T> errorResult = ExcelImportResult.<T>builder()
                    .success(false)
                    .totalRows(0)
                    .successRows(0)
                    .errorRows(0)
                    .successData(new ArrayList<>())
                    .errorData(new ArrayList<>())
                    .message("文件读取失败: " + e.getMessage())
                    .build();
                
                results.add(errorResult);
            }
        }
        
        return results;
    }
    
    /**
     * 异步读取Excel文件
     * @param file 文件
     * @param modelClass 数据模型类
     * @return CompletableFuture
     */
    public <T> CompletableFuture<ExcelImportResult<T>> readExcelAsync(MultipartFile file, Class<T> modelClass) {
        return CompletableFuture.supplyAsync(() -> readExcel(file, modelClass));
    }
    
    /**
     * 文件验证
     * @param file 文件
     */
    private void validateFile(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new BusinessException("文件不能为空");
        }
        
        String fileName = file.getOriginalFilename();
        if (fileName == null || !fileName.toLowerCase().endsWith(".xlsx")) {
            throw new BusinessException("文件格式不正确，只支持.xlsx格式");
        }
        
        if (file.getSize() > properties.getMaxFileSize()) {
            throw new BusinessException("文件大小不能超过" + (properties.getMaxFileSize() / 1024 / 1024) + "MB");
        }
    }
    
    /**
     * 创建临时文件
     * @param file 上传文件
     * @return 临时文件
     */
    private File createTempFile(MultipartFile file) throws IOException {
        String fileName = UUID.randomUUID().toString() + ".xlsx";
        File tempFile = new File(properties.getTempPath(), fileName);
        
        file.transferTo(tempFile);
        
        return tempFile;
    }
    
    /**
     * 数据验证
     * @param dataList 数据列表
     * @param errorList 错误列表
     */
    private <T> void validateData(List<T> dataList, List<ExcelErrorInfo> errorList) {
        Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
        
        for (int i = 0; i < dataList.size(); i++) {
            T data = dataList.get(i);
            Set<ConstraintViolation<T>> violations = validator.validate(data);
            
            if (!violations.isEmpty()) {
                for (ConstraintViolation<T> violation : violations) {
                    ExcelErrorInfo errorInfo = ExcelErrorInfo.builder()
                        .rowIndex(i + 2) // Excel行号从2开始（第1行是标题）
                        .fieldName(violation.getPropertyPath().toString())
                        .fieldValue(String.valueOf(violation.getInvalidValue()))
                        .errorMessage(violation.getMessage())
                        .errorType("VALIDATION_ERROR")
                        .build();
                    
                    errorList.add(errorInfo);
                }
                
                // 设置错误信息到数据对象
                setErrorMessage(data, errorList.get(errorList.size() - 1).getErrorMessage());
            }
        }
    }
    
    /**
     * 生成文件名
     * @param originalFileName 原始文件名
     * @return 生成的文件名
     */
    private String generateFileName(String originalFileName) {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String uuid = UUID.randomUUID().toString().substring(0, 8);
        
        if (originalFileName != null && originalFileName.contains(".")) {
            String extension = originalFileName.substring(originalFileName.lastIndexOf("."));
            return timestamp + "_" + uuid + extension;
        }
        
        return timestamp + "_" + uuid + ".xlsx";
    }
    
    /**
     * 获取错误信息
     * @param data 数据对象
     * @return 错误信息
     */
    private String getErrorMessage(Object data) {
        try {
            Field field = data.getClass().getDeclaredField("errorMessage");
            field.setAccessible(true);
            return (String) field.get(data);
        } catch (Exception e) {
            return null;
        }
    }
    
    /**
     * 设置错误信息
     * @param data 数据对象
     * @param errorMessage 错误信息
     */
    private void setErrorMessage(Object data, String errorMessage) {
        try {
            Field field = data.getClass().getDeclaredField("errorMessage");
            field.setAccessible(true);
            field.set(data, errorMessage);
        } catch (Exception e) {
            log.warn("设置错误信息失败", e);
        }
    }
}

/**
 * Excel数据监听器
 */
public class ExcelDataListener<T> implements ReadListener<T> {
    
    private final List<T> dataList;
    private final List<ExcelErrorInfo> errorList;
    
    public ExcelDataListener(List<T> dataList, List<ExcelErrorInfo> errorList) {
        this.dataList = dataList;
        this.errorList = errorList;
    }
    
    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("Excel数据读取完成，共读取{}条数据", dataList.size());
    }
    
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("Excel数据读取异常", exception);
        
        ExcelErrorInfo errorInfo = ExcelErrorInfo.builder()
            .rowIndex(context.readRowHolder().getRowIndex() + 1)
            .errorMessage(exception.getMessage())
            .errorType("READ_ERROR")
            .build();
        
        errorList.add(errorInfo);
    }
}

5. Excel模板处理器

5.1 Excel模板处理器

/**
 * Excel模板处理器
 */
@Service
public class ExcelTemplateHandler {
    
    private final EasyExcelProperties properties;
    
    public ExcelTemplateHandler(EasyExcelProperties properties) {
        this.properties = properties;
    }
    
    /**
     * 使用模板写入Excel
     * @param request 导出请求
     * @param filePath 文件路径
     */
    public void writeWithTemplate(ExcelExportRequest request, String filePath) {
        try {
            // 1. 读取模板文件
            File templateFile = new File(request.getTemplatePath());
            if (!templateFile.exists()) {
                throw new BusinessException("模板文件不存在");
            }
            
            // 2. 创建输出文件
            File outputFile = new File(filePath);
            
            // 3. 使用模板写入数据
            EasyExcel.write(outputFile, request.getModelClass())
                .withTemplate(templateFile)
                .sheet()
                .doWrite(request.getData());
            
            log.info("使用模板写入Excel成功: {}", filePath);
            
        } catch (Exception e) {
            log.error("使用模板写入Excel失败", e);
            throw new BusinessException("使用模板写入Excel失败: " + e.getMessage());
        }
    }
    
    /**
     * 创建Excel模板
     * @param modelClass 数据模型类
     * @param templatePath 模板路径
     */
    public void createTemplate(Class<?> modelClass, String templatePath) {
        try {
            // 1. 创建模板文件
            File templateFile = new File(templatePath);
            File parentDir = templateFile.getParentFile();
            if (!parentDir.exists()) {
                parentDir.mkdirs();
            }
            
            // 2. 写入模板（只写标题行）
            EasyExcel.write(templateFile, modelClass)
                .sheet("数据模板")
                .doWrite(new ArrayList<>());
            
            log.info("Excel模板创建成功: {}", templatePath);
            
        } catch (Exception e) {
            log.error("创建Excel模板失败", e);
            throw new BusinessException("创建Excel模板失败: " + e.getMessage());
        }
    }
    
    /**
     * 下载Excel模板
     * @param modelClass 数据模型类
     * @param fileName 文件名
     * @return 文件路径
     */
    public String downloadTemplate(Class<?> modelClass, String fileName) {
        try {
            // 1. 生成模板文件路径
            String templatePath = properties.getDownloadPath() + "/" + fileName;
            
            // 2. 创建模板
            createTemplate(modelClass, templatePath);
            
            return templatePath;
            
        } catch (Exception e) {
            log.error("下载Excel模板失败", e);
            throw new BusinessException("下载Excel模板失败: " + e.getMessage());
        }
    }
    
    /**
     * 验证Excel模板
     * @param file 模板文件
     * @param modelClass 数据模型类
     * @return 验证结果
     */
    public boolean validateTemplate(MultipartFile file, Class<?> modelClass) {
        try {
            // 1. 读取模板文件
            File tempFile = createTempFile(file);
            
            // 2. 验证模板格式
            List<Object> dataList = new ArrayList<>();
            EasyExcel.read(tempFile, modelClass, new ExcelDataListener<Object>(dataList, new ArrayList<>()))
                .sheet()
                .doRead();
            
            // 3. 清理临时文件
            tempFile.delete();
            
            return true;
            
        } catch (Exception e) {
            log.error("验证Excel模板失败", e);
            return false;
        }
    }
    
    /**
     * 创建临时文件
     * @param file 上传文件
     * @return 临时文件
     */
    private File createTempFile(MultipartFile file) throws IOException {
        String fileName = UUID.randomUUID().toString() + ".xlsx";
        File tempFile = new File(properties.getTempPath(), fileName);
        
        file.transferTo(tempFile);
        
        return tempFile;
    }
}

6. Excel控制器

6.1 Excel控制器

/**
 * Excel控制器
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {
    
    @Autowired
    private ExcelFileHandler excelFileHandler;
    
    @Autowired
    private ExcelTemplateHandler templateHandler;
    
    /**
     * 导入Excel文件
     */
    @PostMapping("/import")
    public ResponseEntity<Map<String, Object>> importExcel(
            @RequestParam("file") MultipartFile file,
            @RequestParam("modelType") String modelType) {
        try {
            // 1. 根据模型类型选择对应的模型类
            Class<?> modelClass = getModelClass(modelType);
            
            // 2. 读取Excel文件
            ExcelImportResult<?> result = excelFileHandler.readExcel(file, modelClass);
            
            // 3. 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", result.isSuccess());
            response.put("totalRows", result.getTotalRows());
            response.put("successRows", result.getSuccessRows());
            response.put("errorRows", result.getErrorRows());
            response.put("message", result.getMessage());
            response.put("errorData", result.getErrorData());
            
            return ResponseEntity.ok(response);
            
        } catch (Exception e) {
            log.error("导入Excel文件失败", e);
            
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "导入失败: " + e.getMessage());
            
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
        }
    }
    
    /**
     * 导出Excel文件
     */
    @PostMapping("/export")
    public ResponseEntity<Map<String, Object>> exportExcel(@RequestBody ExcelExportRequest request) {
        try {
            // 1. 写入Excel文件
            String filePath = excelFileHandler.writeExcel(request);
            
            // 2. 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("filePath", filePath);
            response.put("message", "导出成功");
            
            return ResponseEntity.ok(response);
            
        } catch (Exception e) {
            log.error("导出Excel文件失败", e);
            
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "导出失败: " + e.getMessage());
            
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
        }
    }
    
    /**
     * 批量导入Excel文件
     */
    @PostMapping("/batch-import")
    public ResponseEntity<Map<String, Object>> batchImportExcel(
            @RequestParam("files") List<MultipartFile> files,
            @RequestParam("modelType") String modelType) {
        try {
            // 1. 根据模型类型选择对应的模型类
            Class<?> modelClass = getModelClass(modelType);
            
            // 2. 批量读取Excel文件
            List<ExcelImportResult<?>> results = excelFileHandler.batchReadExcel(files, modelClass);
            
            // 3. 统计结果
            int totalFiles = results.size();
            int successFiles = (int) results.stream().filter(ExcelImportResult::isSuccess).count();
            int totalRows = results.stream().mapToInt(ExcelImportResult::getTotalRows).sum();
            int successRows = results.stream().mapToInt(ExcelImportResult::getSuccessRows).sum();
            int errorRows = results.stream().mapToInt(ExcelImportResult::getErrorRows).sum();
            
            // 4. 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("totalFiles", totalFiles);
            response.put("successFiles", successFiles);
            response.put("totalRows", totalRows);
            response.put("successRows", successRows);
            response.put("errorRows", errorRows);
            response.put("results", results);
            response.put("message", "批量导入完成");
            
            return ResponseEntity.ok(response);
            
        } catch (Exception e) {
            log.error("批量导入Excel文件失败", e);
            
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "批量导入失败: " + e.getMessage());
            
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
        }
    }
    
    /**
     * 下载Excel模板
     */
    @GetMapping("/template")
    public ResponseEntity<Map<String, Object>> downloadTemplate(
            @RequestParam("modelType") String modelType,
            @RequestParam(value = "fileName", required = false) String fileName) {
        try {
            // 1. 根据模型类型选择对应的模型类
            Class<?> modelClass = getModelClass(modelType);
            
            // 2. 生成文件名
            if (fileName == null) {
                fileName = modelType + "_template.xlsx";
            }
            
            // 3. 下载模板
            String templatePath = templateHandler.downloadTemplate(modelClass, fileName);
            
            // 4. 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("templatePath", templatePath);
            response.put("message", "模板下载成功");
            
            return ResponseEntity.ok(response);
            
        } catch (Exception e) {
            log.error("下载Excel模板失败", e);
            
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "模板下载失败: " + e.getMessage());
            
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
        }
    }
    
    /**
     * 验证Excel模板
     */
    @PostMapping("/validate-template")
    public ResponseEntity<Map<String, Object>> validateTemplate(
            @RequestParam("file") MultipartFile file,
            @RequestParam("modelType") String modelType) {
        try {
            // 1. 根据模型类型选择对应的模型类
            Class<?> modelClass = getModelClass(modelType);
            
            // 2. 验证模板
            boolean isValid = templateHandler.validateTemplate(file, modelClass);
            
            // 3. 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("isValid", isValid);
            response.put("message", isValid ? "模板验证通过" : "模板验证失败");
            
            return ResponseEntity.ok(response);
            
        } catch (Exception e) {
            log.error("验证Excel模板失败", e);
            
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "模板验证失败: " + e.getMessage());
            
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
        }
    }
    
    /**
     * 根据模型类型获取模型类
     * @param modelType 模型类型
     * @return 模型类
     */
    private Class<?> getModelClass(String modelType) {
        switch (modelType.toLowerCase()) {
            case "user":
                return UserExcelModel.class;
            case "product":
                return ProductExcelModel.class;
            default:
                throw new BusinessException("不支持的模型类型: " + modelType);
        }
    }
}

7. 性能优化

7.1 性能优化策略

/**
 * Excel性能优化
 */
@Service
public class ExcelPerformanceOptimizer {
    
    private final EasyExcelProperties properties;
    private final ThreadPoolExecutor executor;
    
    public ExcelPerformanceOptimizer(EasyExcelProperties properties) {
        this.properties = properties;
        this.executor = new ThreadPoolExecutor(
            properties.getCorePoolSize(),
            properties.getMaxPoolSize(),
            60L,
            TimeUnit.SECONDS,
            new LinkedBlockingQueue<>(properties.getQueueCapacity()),
            new ThreadFactory() {
                private final AtomicInteger threadNumber = new AtomicInteger(1);
                @Override
                public Thread newThread(Runnable r) {
                    Thread t = new Thread(r, "excel-processor-" + threadNumber.getAndIncrement());
                    t.setDaemon(true);
                    return t;
                }
            }
        );
    }
    
    /**
     * 异步处理Excel文件
     * @param file 文件
     * @param modelClass 模型类
     * @return CompletableFuture
     */
    public <T> CompletableFuture<ExcelImportResult<T>> processExcelAsync(MultipartFile file, Class<T> modelClass) {
        return CompletableFuture.supplyAsync(() -> {
            try {
                // 1. 文件验证
                validateFile(file);
                
                // 2. 创建临时文件
                File tempFile = createTempFile(file);
                
                // 3. 分块读取数据
                List<T> dataList = new ArrayList<>();
                List<ExcelErrorInfo> errorList = new ArrayList<>();
                
                EasyExcel.read(tempFile, modelClass, new ExcelDataListener<T>(dataList, errorList))
                    .sheet()
                    .doRead();
                
                // 4. 数据验证
                validateData(dataList, errorList);
                
                // 5. 构建结果
                ExcelImportResult<T> result = ExcelImportResult.<T>builder()
                    .success(errorList.isEmpty())
                    .totalRows(dataList.size())
                    .successRows(dataList.size() - errorList.size())
                    .errorRows(errorList.size())
                    .successData(dataList.stream()
                        .filter(data -> getErrorMessage(data) == null)
                        .collect(Collectors.toList()))
                    .errorData(errorList)
                    .message(errorList.isEmpty() ? "导入成功" : "导入完成，存在错误数据")
                    .filePath(tempFile.getAbsolutePath())
                    .build();
                
                // 6. 清理临时文件
                if (!properties.isEnableCache()) {
                    tempFile.delete();
                }
                
                return result;
                
            } catch (Exception e) {
                log.error("异步处理Excel文件失败", e);
                throw new BusinessException("处理Excel文件失败: " + e.getMessage());
            }
        }, executor);
    }
    
    /**
     * 批量异步处理Excel文件
     * @param files 文件列表
     * @param modelClass 模型类
     * @return CompletableFuture列表
     */
    public <T> List<CompletableFuture<ExcelImportResult<T>>> batchProcessExcelAsync(
            List<MultipartFile> files, Class<T> modelClass) {
        return files.stream()
            .map(file -> processExcelAsync(file, modelClass))
            .collect(Collectors.toList());
    }
    
    /**
     * 流式处理Excel文件
     * @param file 文件
     * @param modelClass 模型类
     * @param processor 数据处理器
     */
    public <T> void processExcelStream(MultipartFile file, Class<T> modelClass, 
                                       Consumer<List<T>> processor) {
        try {
            // 1. 文件验证
            validateFile(file);
            
            // 2. 创建临时文件
            File tempFile = createTempFile(file);
            
            // 3. 流式读取数据
            List<T> batch = new ArrayList<>();
            EasyExcel.read(tempFile, modelClass, new ReadListener<T>() {
                @Override
                public void invoke(T data, AnalysisContext context) {
                    batch.add(data);
                    
                    // 达到批次大小时处理数据
                    if (batch.size() >= properties.getBatchSize()) {
                        processor.accept(new ArrayList<>(batch));
                        batch.clear();
                    }
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    // 处理剩余数据
                    if (!batch.isEmpty()) {
                        processor.accept(new ArrayList<>(batch));
                    }
                }
                
                @Override
                public void onException(Exception exception, AnalysisContext context) {
                    log.error("流式处理Excel数据异常", exception);
                }
            }).sheet().doRead();
            
            // 4. 清理临时文件
            tempFile.delete();
            
        } catch (Exception e) {
            log.error("流式处理Excel文件失败", e);
            throw new BusinessException("流式处理Excel文件失败: " + e.getMessage());
        }
    }
    
    /**
     * 文件验证
     * @param file 文件
     */
    private void validateFile(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new BusinessException("文件不能为空");
        }
        
        String fileName = file.getOriginalFilename();
        if (fileName == null || !fileName.toLowerCase().endsWith(".xlsx")) {
            throw new BusinessException("文件格式不正确，只支持.xlsx格式");
        }
        
        if (file.getSize() > properties.getMaxFileSize()) {
            throw new BusinessException("文件大小不能超过" + (properties.getMaxFileSize() / 1024 / 1024) + "MB");
        }
    }
    
    /**
     * 创建临时文件
     * @param file 上传文件
     * @return 临时文件
     */
    private File createTempFile(MultipartFile file) throws IOException {
        String fileName = UUID.randomUUID().toString() + ".xlsx";
        File tempFile = new File(properties.getTempPath(), fileName);
        
        file.transferTo(tempFile);
        
        return tempFile;
    }
    
    /**
     * 数据验证
     * @param dataList 数据列表
     * @param errorList 错误列表
     */
    private <T> void validateData(List<T> dataList, List<ExcelErrorInfo> errorList) {
        Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
        
        for (int i = 0; i < dataList.size(); i++) {
            T data = dataList.get(i);
            Set<ConstraintViolation<T>> violations = validator.validate(data);
            
            if (!violations.isEmpty()) {
                for (ConstraintViolation<T> violation : violations) {
                    ExcelErrorInfo errorInfo = ExcelErrorInfo.builder()
                        .rowIndex(i + 2)
                        .fieldName(violation.getPropertyPath().toString())
                        .fieldValue(String.valueOf(violation.getInvalidValue()))
                        .errorMessage(violation.getMessage())
                        .errorType("VALIDATION_ERROR")
                        .build();
                    
                    errorList.add(errorInfo);
                }
            }
        }
    }
    
    /**
     * 获取错误信息
     * @param data 数据对象
     * @return 错误信息
     */
    private String getErrorMessage(Object data) {
        try {
            Field field = data.getClass().getDeclaredField("errorMessage");
            field.setAccessible(true);
            return (String) field.get(data);
        } catch (Exception e) {
            return null;
        }
    }
}

8. 总结

通过EasyExcel的实现，我们成功构建了一个高性能的Excel文件处理系统。关键特性包括：

8.1 核心优势

Excel读取: 支持大文件读取，内存占用小
Excel写入: 支持大数据量写入，性能优异
模板处理: 支持Excel模板和样式处理
数据验证: 支持数据格式验证和错误处理
性能优化: 流式处理，避免内存溢出

8.2 最佳实践

文件处理: 合理的文件验证和临时文件管理
数据验证: 完善的数据验证和错误处理机制
性能优化: 异步处理和流式处理策略
模板设计: 灵活的模板设计和样式处理
错误处理: 详细的错误信息和异常处理

这套EasyExcel方案不仅能够提供高性能的Excel文件处理能力，还包含了数据验证、模板处理、性能优化等核心功能，是企业级Excel处理的重要工具。