EasyExcel导入、导出、下载模板实战教学

EasyExcel导入导出完全实战指南

从入门到精通：手把手教你实现企业级Excel导入导出功能

📋 目录

• 快速入门
• 项目环境搭建
• Excel导出功能实现
• Excel导入功能实现
• 模板下载功能
• 数据验证与转换
• 错误处理机制
• 性能优化技巧
• 高级特性应用
• 实战案例解析

🚀 快速入门

什么是EasyExcel？

EasyExcel是阿里巴巴开源的Java Excel处理工具，基于Java的POI，但相比传统的POI操作，EasyExcel：

• 内存占用更少：100M内存可以读取75M的Excel文件
• 性能更优：读写速度比POI快2-3倍
• 使用更简单：注解驱动，代码更简洁

核心优势对比

🛠️ 项目环境搭建

1. Maven依赖配置

<dependencies>
    <!-- Spring Boot核心依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.6.13</version>
    </dependency>
    
    <!-- EasyExcel核心依赖 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.4</version>
    </dependency>
    
    <!-- 数据库相关 -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.9</version>
    </dependency>
    
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- 工具类 -->
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-core</artifactId>
        <version>5.8.4</version>
    </dependency>
    
    <!-- 参数验证 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    
    <!-- Lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

2. 项目结构设计

src/main/java/com/example/excel/
├── controller/          # 控制器层
│   └── ExcelController.java
├── service/            # 服务层
│   ├── ExcelService.java
│   └── impl/
│       └── ExcelServiceImpl.java
├── model/              # 数据模型
│   ├── dto/           # 传输对象
│   ├── vo/            # 视图对象
│   └── entity/        # 实体对象
├── utils/              # 工具类
│   └── ExcelUtils.java
├── convert/            # 转换器
│   ├── DictConverter.java
│   └── DateConverter.java
├── handler/            # 处理器
│   └── ExcelWriteHandler.java
└── config/             # 配置类
    └── ExcelConfig.java

3. 配置文件

# application.yml
spring:
  application:
    name: excel-demo
  servlet:
    multipart:
      max-file-size: 10MB      # 单个文件最大10MB
      max-request-size: 50MB   # 请求最大50MB
      
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/excel_demo?useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456

# 自定义配置
excel:
  temp-path: /tmp/excel/     # 临时文件目录
  max-rows: 10000           # 最大导入行数
  timeout: 300              # 超时时间(秒)

📤 Excel导出功能实现

1. 基础导出模型

@Data
@HeadRowHeight(30)                    // 表头行高
@ContentRowHeight(25)                 // 内容行高
@ColumnWidth(20)                      // 默认列宽
public class UserExportVO {
    
    @ExcelProperty(value = "用户ID", index = 0)
    @ColumnWidth(10)
    private Long id;
    
    @ExcelProperty(value = "用户名", index = 1)
    @ColumnWidth(15)
    private String username;
    
    @ExcelProperty(value = "真实姓名", index = 2)
    @ColumnWidth(15)
    private String realName;
    
    @ExcelProperty(value = "邮箱", index = 3)
    @ColumnWidth(25)
    private String email;
    
    @ExcelProperty(value = "手机号", index = 4)
    @ColumnWidth(15)
    private String phone;
    
    @ExcelProperty(value = "创建时间", index = 5, converter = DateConverter.class)
    @ColumnWidth(20)
    private Date createTime;
    
    @ExcelProperty(value = "状态", index = 6, converter = DictConverter.class)
    @DictFormat("user_status")
    @ColumnWidth(10)
    private Integer status;
}

2. 控制器接口

@RestController
@RequestMapping("/api/excel")
@Tag(name = "Excel操作", description = "Excel导入导出相关接口")
public class ExcelController {
    
    @Resource
    private ExcelService excelService;
    
    /**
     * 导出用户数据
     */
    @GetMapping("/export/users")
    @Operation(summary = "导出用户数据")
    public void exportUsers(
            @Valid UserExportQuery query,
            HttpServletResponse response) {
        excelService.exportUsers(query, response);
    }
    
    /**
     * 导出用户数据（异步）
     */
    @PostMapping("/export/users/async")
    @Operation(summary = "异步导出用户数据")
    public CommonResult<String> exportUsersAsync(@Valid UserExportQuery query) {
        String taskId = excelService.exportUsersAsync(query);
        return CommonResult.success(taskId);
    }
}

3. 查询参数对象

@Data
public class UserExportQuery {
    
    @Schema(description = "用户名关键字")
    private String username;
    
    @Schema(description = "状态")
    private Integer status;
    
    @Schema(description = "开始时间")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date startTime;
    
    @Schema(description = "结束时间")  
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date endTime;
    
    @Schema(description = "导出格式", allowableValues = {"xlsx", "csv"})
    private String format = "xlsx";
}

4. 服务层实现

@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {
    
    @Resource
    private UserMapper userMapper;
    
    @Override
    public void exportUsers(UserExportQuery query, HttpServletResponse response) {
        try {
            // 1. 查询数据
            List<User> users = userMapper.selectByQuery(query);
            
            // 2. 数据转换
            List<UserExportVO> exportData = users.stream()
                    .map(this::convertToExportVO)
                    .collect(Collectors.toList());
            
            // 3. 设置响应头
            String fileName = "用户数据_" + DateUtil.format(new Date(), "yyyyMMdd_HHmmss") + ".xlsx";
            ExcelUtils.setResponseHeader(response, fileName);
            
            // 4. 执行导出
            EasyExcel.write(response.getOutputStream(), UserExportVO.class)
                    .sheet("用户数据")
                    .registerWriteHandler(createStyle())          // 样式处理
                    .registerWriteHandler(new AutoWidthHandler()) // 自动列宽
                    .doWrite(exportData);
                    
        } catch (Exception e) {
            log.error("导出用户数据失败", e);
            throw new BusinessException("导出失败");
        }
    }
    
    /**
     * 创建导出样式
     */
    private HorizontalCellStyleStrategy createStyle() {
        // 表头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        headStyle.setFillPatternType(FillPatternTypeEnum.SOLID_FOREGROUND);
        
        WriteFont headFont = new WriteFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBold(true);
        headFont.setColor(IndexedColors.WHITE.getIndex());
        headStyle.setWriteFont(headFont);
        
        // 内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        WriteFont contentFont = new WriteFont();
        contentFont.setFontName("微软雅黑");
        contentFont.setFontHeightInPoints((short) 11);
        contentStyle.setWriteFont(contentFont);
        
        return new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }
    
    /**
     * 数据转换
     */
    private UserExportVO convertToExportVO(User user) {
        UserExportVO vo = new UserExportVO();
        BeanUtils.copyProperties(user, vo);
        return vo;
    }
}

📥 Excel导入功能实现

1. 导入数据模型

@Data
public class UserImportVO {
    
    @ExcelProperty(value = "用户名", index = 0)
    @NotBlank(message = "用户名不能为空")
    @Length(max = 50, message = "用户名长度不能超过50")
    private String username;
    
    @ExcelProperty(value = "真实姓名", index = 1)
    @NotBlank(message = "真实姓名不能为空")
    @Length(max = 20, message = "真实姓名长度不能超过20")
    private String realName;
    
    @ExcelProperty(value = "邮箱", index = 2)
    @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 = "年龄不能小于1")
    @Max(value = 150, message = "年龄不能大于150")
    private Integer age;
    
    @ExcelProperty(value = "生日", index = 5, converter = DateConverter.class)
    private Date birthday;
    
    @ExcelProperty(value = "状态", index = 6, converter = DictConverter.class)
    @DictFormat("user_status")
    private Integer status;
    
    // 非Excel字段，用于存储错误信息
    @ExcelIgnore
    private String errorMsg;
    
    // 行号，用于定位错误
    @ExcelIgnore
    private Integer rowNum;
}

2. 导入请求参数

@Data
public class UserImportRequest {
    
    @Schema(description = "Excel文件", required = true)
    @NotNull(message = "Excel文件不能为空")
    private MultipartFile file;
    
    @Schema(description = "是否覆盖重复数据", example = "false")
    private Boolean overwrite = false;
    
    @Schema(description = "跳过错误行继续导入", example = "true")
    private Boolean skipError = true;
    
    @Schema(description = "最大导入行数", example = "10000")
    @Max(value = 50000, message = "单次最大导入5万行")
    private Integer maxRows = 10000;
}

3. 导入响应结果

@Data
@Builder
public class ImportResult {
    
    @Schema(description = "总行数")
    private Integer totalRows;
    
    @Schema(description = "成功行数")
    private Integer successRows;
    
    @Schema(description = "失败行数") 
    private Integer failRows;
    
    @Schema(description = "成功数据列表")
    private List<String> successList;
    
    @Schema(description = "失败数据详情")
    private List<ImportError> errorList;
    
    @Schema(description = "处理耗时(毫秒)")
    private Long costTime;
    
    @Data
    @Builder
    public static class ImportError {
        @Schema(description = "行号")
        private Integer rowNum;
        
        @Schema(description = "错误数据")
        private String rowData;
        
        @Schema(description = "错误原因")
        private String errorMsg;
    }
}

4. 导入接口实现

@PostMapping("/import/users")
@Operation(summary = "导入用户数据")
public CommonResult<ImportResult> importUsers(@Valid UserImportRequest request) {
    ImportResult result = excelService.importUsers(request);
    return CommonResult.success(result);
}

5. 导入服务实现

@Override
@Transactional(rollbackFor = Exception.class)
public ImportResult importUsers(UserImportRequest request) {
    long startTime = System.currentTimeMillis();
    
    try {
        // 1. 读取Excel数据
        List<UserImportVO> importData = readExcelData(request.getFile());
        
        // 2. 数据验证
        List<UserImportVO> validData = validateImportData(importData, request.getSkipError());
        
        // 3. 业务验证（用户名重复检查等）
        List<UserImportVO> finalData = businessValidate(validData, request.getOverwrite());
        
        // 4. 批量保存
        int successCount = batchSaveUsers(finalData);
        
        // 5. 构建返回结果
        return buildImportResult(importData, successCount, startTime);
        
    } catch (Exception e) {
        log.error("导入用户数据失败", e);
        throw new BusinessException("导入失败：" + e.getMessage());
    }
}

/**
 * 读取Excel数据
 */
private List<UserImportVO> readExcelData(MultipartFile file) throws IOException {
    List<UserImportVO> result = new ArrayList<>();
    
    EasyExcel.read(file.getInputStream(), UserImportVO.class, new AnalysisEventListener<UserImportVO>() {
        private int rowNum = 1; // 行号计数器
        
        @Override
        public void invoke(UserImportVO data, AnalysisContext context) {
            data.setRowNum(++rowNum); // 设置行号（跳过表头）
            result.add(data);
        }
        
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            log.info("Excel读取完成，共{}行数据", result.size());
        }
        
        @Override
        public void onException(Exception exception, AnalysisContext context) throws Exception {
            log.error("第{}行数据解析异常", context.readRowHolder().getRowIndex() + 1, exception);
            // 继续读取下一行
        }
    }).sheet().doRead();
    
    return result;
}

/**
 * 数据验证
 */
private List<UserImportVO> validateImportData(List<UserImportVO> importData, Boolean skipError) {
    List<UserImportVO> validData = new ArrayList<>();
    Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
    
    for (UserImportVO data : importData) {
        Set<ConstraintViolation<UserImportVO>> violations = validator.validate(data);
        
        if (violations.isEmpty()) {
            validData.add(data);
        } else {
            // 收集验证错误信息
            String errorMsg = violations.stream()
                    .map(ConstraintViolation::getMessage)
                    .collect(Collectors.joining("; "));
            data.setErrorMsg(errorMsg);
            
            if (!skipError) {
                throw new BusinessException(String.format("第%d行数据验证失败：%s", data.getRowNum(), errorMsg));
            }
        }
    }
    
    return validData;
}

/**
 * 业务验证
 */
private List<UserImportVO> businessValidate(List<UserImportVO> validData, Boolean overwrite) {
    List<UserImportVO> finalData = new ArrayList<>();
    
    // 提取所有用户名进行批量查询
    Set<String> usernames = validData.stream()
            .map(UserImportVO::getUsername)
            .collect(Collectors.toSet());
    
    Map<String, User> existingUsers = userMapper.selectByUsernames(usernames)
            .stream()
            .collect(Collectors.toMap(User::getUsername, Function.identity()));
    
    for (UserImportVO data : validData) {
        User existingUser = existingUsers.get(data.getUsername());
        
        if (existingUser != null) {
            if (overwrite) {
                // 覆盖模式：设置ID用于更新
                data.setId(existingUser.getId());
                finalData.add(data);
            } else {
                // 不覆盖：记录错误
                data.setErrorMsg("用户名已存在");
            }
        } else {
            // 新用户：直接添加
            finalData.add(data);
        }
    }
    
    return finalData;
}

/**
 * 批量保存
 */
private int batchSaveUsers(List<UserImportVO> finalData) {
    if (CollUtil.isEmpty(finalData)) {
        return 0;
    }
    
    // 转换为实体对象
    List<User> users = finalData.stream()
            .map(this::convertToEntity)
            .collect(Collectors.toList());
    
    // 分离新增和更新
    List<User> insertList = users.stream()
            .filter(user -> user.getId() == null)
            .collect(Collectors.toList());
    
    List<User> updateList = users.stream()
            .filter(user -> user.getId() != null)
            .collect(Collectors.toList());
    
    int count = 0;
    
    // 批量新增
    if (CollUtil.isNotEmpty(insertList)) {
        count += userMapper.batchInsert(insertList);
    }
    
    // 批量更新
    if (CollUtil.isNotEmpty(updateList)) {
        count += userMapper.batchUpdate(updateList);
    }
    
    return count;
}

📄 模板下载功能

1. 模板配置类

@Data
@Component
@ConfigurationProperties(prefix = "excel.template")
public class ExcelTemplateConfig {
    
    /**
     * 模板文件存储路径
     */
    private String templatePath = "classpath:templates/excel/";
    
    /**
     * 模板配置映射
     */
    private Map<String, TemplateInfo> templates = new HashMap<>();
    
    @Data
    public static class TemplateInfo {
        private String fileName;        // 模板文件名
        private String displayName;     // 显示名称
        private String description;     // 描述
        private List<String> sheets;    // Sheet页配置
    }
}

2. 模板下载接口

@GetMapping("/template/{templateType}")
@Operation(summary = "下载导入模板")
public void downloadTemplate(
        @PathVariable String templateType,
        HttpServletResponse response) {
    excelService.downloadTemplate(templateType, response);
}

@GetMapping("/templates")
@Operation(summary = "获取模板列表")
public CommonResult<List<TemplateInfo>> getTemplateList() {
    List<TemplateInfo> templates = excelService.getTemplateList();
    return CommonResult.success(templates);
}

3. 动态模板生成

@Override
public void downloadTemplate(String templateType, HttpServletResponse response) {
    try {
        switch (templateType) {
            case "user":
                generateUserTemplate(response);
                break;
            case "product":
                generateProductTemplate(response);
                break;
            default:
                throw new BusinessException("不支持的模板类型");
        }
    } catch (Exception e) {
        log.error("下载模板失败", e);
        throw new BusinessException("模板下载失败");
    }
}

/**
 * 生成用户导入模板
 */
private void generateUserTemplate(HttpServletResponse response) throws IOException {
    String fileName = "用户导入模板_" + DateUtil.format(new Date(), "yyyyMMdd") + ".xlsx";
    ExcelUtils.setResponseHeader(response, fileName);
    
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
            .registerWriteHandler(new TemplateStyleHandler())
            .build();
    
    try {
        // 第一个Sheet：导入模板
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "用户导入模板")
                .head(UserImportVO.class)
                .build();
        
        // 写入示例数据
        List<UserImportVO> sampleData = createSampleData();
        excelWriter.write(sampleData, writeSheet1);
        
        // 第二个Sheet：数据字典
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "数据字典")
                .build();
        
        List<DictData> dictData = createDictData();
        excelWriter.write(dictData, writeSheet2);
        
        // 第三个Sheet：填写说明
        WriteSheet writeSheet3 = EasyExcel.writerSheet(2, "填写说明")
                .build();
        
        List<InstructionData> instructions = createInstructions();
        excelWriter.write(instructions, writeSheet3);
        
    } finally {
        excelWriter.finish();
    }
}

/**
 * 创建示例数据
 */
private List<UserImportVO> createSampleData() {
    UserImportVO sample = new UserImportVO();
    sample.setUsername("zhangsan");
    sample.setRealName("张三");
    sample.setEmail("zhangsan@example.com");
    sample.setPhone("13800138000");
    sample.setAge(25);
    sample.setBirthday(new Date());
    sample.setStatus(1);
    
    return Arrays.asList(sample);
}

4. 模板样式处理器

public class TemplateStyleHandler implements WorkbookWriteHandler {
    
    @Override
    public void afterWorkbookCreate(WorkbookWriteHandlerContext context) {
        Workbook workbook = context.getWriteContext().writeWorkbookHolder().getWorkbook();
        
        // 设置数据验证
        Sheet sheet = workbook.getSheetAt(0);
        setDataValidation(sheet, workbook);
        
        // 设置列宽
        setColumnWidth(sheet);
        
        // 冻结表头
        sheet.createFreezePane(0, 1);
    }
    
    /**
     * 设置数据验证
     */
    private void setDataValidation(Sheet sheet, Workbook workbook) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        
        // 状态列下拉选择
        String[] statusOptions = {"启用", "禁用"};
        CellRangeAddressList statusRange = new CellRangeAddressList(1, 65535, 6, 6);
        DataValidationConstraint statusConstraint = helper.createExplicitListConstraint(statusOptions);
        DataValidation statusValidation = helper.createValidation(statusConstraint, statusRange);
        statusValidation.createErrorBox("输入错误", "请选择：启用 或 禁用");
        sheet.addValidationData(statusValidation);
        
        // 手机号格式验证
        CellRangeAddressList phoneRange = new CellRangeAddressList(1, 65535, 3, 3);
        DataValidationConstraint phoneConstraint = helper.createCustomConstraint("LEN(D2)=11");
        DataValidation phoneValidation = helper.createValidation(phoneConstraint, phoneRange);
        phoneValidation.createErrorBox("格式错误", "手机号必须为11位数字");
        sheet.addValidationData(phoneValidation);
    }
    
    /**
     * 设置列宽
     */
    private void setColumnWidth(Sheet sheet) {
        sheet.setColumnWidth(0, 20 * 256);  // 用户名
        sheet.setColumnWidth(1, 20 * 256);  // 真实姓名
        sheet.setColumnWidth(2, 30 * 256);  // 邮箱
        sheet.setColumnWidth(3, 20 * 256);  // 手机号
        sheet.setColumnWidth(4, 10 * 256);  // 年龄
        sheet.setColumnWidth(5, 20 * 256);  // 生日
        sheet.setColumnWidth(6, 15 * 256);  // 状态
    }
}

🔄 数据验证与转换

1. 自定义转换器

日期转换器

@Component
public class DateConverter implements Converter<Date> {
    
    private static final String[] DATE_PATTERNS = {
        "yyyy-MM-dd HH:mm:ss",
        "yyyy-MM-dd",
        "yyyy/MM/dd",
        "yyyyMMdd"
    };
    
    @Override
    public Class<?> supportJavaTypeKey() {
        return Date.class;
    }
    
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    
    @Override
    public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                  GlobalConfiguration globalConfiguration) throws Exception {
        String dateStr = cellData.getStringValue();
        if (StrUtil.isBlank(dateStr)) {
            return null;
        }
        
        // 尝试多种日期格式解析
        for (String pattern : DATE_PATTERNS) {
            try {
                return DateUtil.parse(dateStr, pattern);
            } catch (Exception ignored) {
                // 继续尝试下一种格式
            }
        }
        
        throw new IllegalArgumentException("日期格式不正确：" + dateStr);
    }
    
    @Override
    public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        if (value == null) {
            return new WriteCellData<>("");
        }
        return new WriteCellData<>(DateUtil.formatDateTime(value));
    }
}

字典转换器

@Component
public class DictConverter implements Converter<Integer> {
    
    @Resource
    private DictService dictService;
    
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }
    
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                     GlobalConfiguration globalConfiguration) throws Exception {
        String label = cellData.getStringValue();
        if (StrUtil.isBlank(label)) {
            return null;
        }
        
        // 获取字典类型
        String dictType = getDictType(contentProperty);
        
        // 根据标签获取值
        Integer value = dictService.getValueByLabel(dictType, label);
        if (value == null) {
            throw new IllegalArgumentException("无效的字典值：" + label);
        }
        
        return value;
    }
    
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        if (value == null) {
            return new WriteCellData<>("");
        }
        
        String dictType = getDictType(contentProperty);
        String label = dictService.getLabelByValue(dictType, value);
        return new WriteCellData<>(label != null ? label : String.valueOf(value));
    }
    
    private String getDictType(ExcelContentProperty contentProperty) {
        DictFormat dictFormat = contentProperty.getField().getAnnotation(DictFormat.class);
        return dictFormat != null ? dictFormat.value() : "";
    }
}

2. 数据验证注解

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DictFormat {
    String value();
}

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Constraint(validatedBy = UniqueValidator.class)
@Documented
public @interface Unique {
    String message() default "数据重复";
    Class<?>[] groups() default {};
    Class<? extends Payload>[] payload() default {};
    String field();     // 数据库字段名
    String table();     // 表名
}

❌ 错误处理机制

1. 全局异常处理

@RestControllerAdvice
@Slf4j
public class GlobalExceptionHandler {
    
    @ExceptionHandler(BusinessException.class)
    public CommonResult<Void> handleBusinessException(BusinessException e) {
        log.warn("业务异常：{}", e.getMessage());
        return CommonResult.error(e.getCode(), e.getMessage());
    }
    
    @ExceptionHandler(MethodArgumentNotValidException.class)
    public CommonResult<Void> handleValidationException(MethodArgumentNotValidException e) {
        String message = e.getBindingResult().getFieldErrors().stream()
                .map(FieldError::getDefaultMessage)
                .collect(Collectors.joining(", "));
        return CommonResult.error("参数验证失败：" + message);
    }
    
    @ExceptionHandler(MaxUploadSizeExceededException.class)
    public CommonResult<Void> handleMaxSizeException(MaxUploadSizeExceededException e) {
        return CommonResult.error("文件大小超过限制");
    }
}

2. Excel解析异常处理

public class ExcelReadListener extends AnalysisEventListener<UserImportVO> {
    
    private List<UserImportVO> successList = new ArrayList<>();
    private List<ImportError> errorList = new ArrayList<>();
    private int currentRow = 0;
    
    @Override
    public void invoke(UserImportVO data, AnalysisContext context) {
        currentRow++;
        data.setRowNum(currentRow);
        
        try {
            // 数据验证
            validateData(data);
            successList.add(data);
        } catch (Exception e) {
            ImportError error = ImportError.builder()
                    .rowNum(currentRow)
                    .rowData(JSON.toJSONString(data))
                    .errorMsg(e.getMessage())
                    .build();
            errorList.add(error);
        }
    }
    
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("第{}行解析异常", context.readRowHolder().getRowIndex() + 1, exception);
        
        ImportError error = ImportError.builder()
                .rowNum(context.readRowHolder().getRowIndex() + 1)
                .rowData("解析失败")
                .errorMsg("数据格式错误：" + exception.getMessage())
                .build();
        errorList.add(error);
    }
    
    private void validateData(UserImportVO data) {
        // 基础验证
        if (StrUtil.isBlank(data.getUsername())) {
            throw new IllegalArgumentException("用户名不能为空");
        }
        
        // 格式验证
        if (StrUtil.isNotBlank(data.getEmail()) && !Validator.isEmail(data.getEmail())) {
            throw new IllegalArgumentException("邮箱格式不正确");
        }
        
        // 业务验证
        if (data.getAge() != null && (data.getAge() < 1 || data.getAge() > 150)) {
            throw new IllegalArgumentException("年龄必须在1-150之间");
        }
    }
}

⚡ 性能优化技巧

1. 大文件处理

分批读取

@Override
public ImportResult importLargeFile(MultipartFile file) {
    final int BATCH_SIZE = 1000;
    final AtomicInteger totalCount = new AtomicInteger(0);
    final AtomicInteger successCount = new AtomicInteger(0);
    final List<ImportError> errorList = Collections.synchronizedList(new ArrayList<>());
    
    try {
        EasyExcel.read(file.getInputStream(), UserImportVO.class, new AnalysisEventListener<UserImportVO>() {
            private List<UserImportVO> batchData = new ArrayList<>();
            
            @Override
            public void invoke(UserImportVO data, AnalysisContext context) {
                totalCount.incrementAndGet();
                batchData.add(data);
                
                if (batchData.size() >= BATCH_SIZE) {
                    processBatch(batchData, successCount, errorList);
                    batchData.clear();
                }
            }
            
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 处理最后一批数据
                if (!batchData.isEmpty()) {
                    processBatch(batchData, successCount, errorList);
                }
            }
        }).sheet().doRead();
        
        return ImportResult.builder()
                .totalRows(totalCount.get())
                .successRows(successCount.get())
                .failRows(totalCount.get() - successCount.get())
                .errorList(errorList)
                .build();
                
    } catch (Exception e) {
        throw new BusinessException("大文件处理失败：" + e.getMessage());
    }
}

private void processBatch(List<UserImportVO> batchData, AtomicInteger successCount, List<ImportError> errorList) {
    try {
        // 批量验证
        List<UserImportVO> validData = batchData.stream()
                .filter(this::validateData)
                .collect(Collectors.toList());
        
        // 批量保存
        if (!validData.isEmpty()) {
            int saved = batchSaveUsers(validData);
            successCount.addAndGet(saved);
        }
        
    } catch (Exception e) {
        log.error("批量处理失败", e);
        // 记录整批错误
        for (UserImportVO data : batchData) {
            ImportError error = ImportError.builder()
                    .rowNum(data.getRowNum())
                    .rowData(JSON.toJSONString(data))
                    .errorMsg("批量处理失败：" + e.getMessage())
                    .build();
            errorList.add(error);
        }
    }
}

异步处理

@Service
public class AsyncExcelService {
    
    @Autowired
    private TaskExecutor taskExecutor;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    public String importUsersAsync(MultipartFile file) {
        String taskId = UUID.randomUUID().toString();
        
        // 设置初始状态
        ExcelTaskStatus status = ExcelTaskStatus.builder()
                .taskId(taskId)
                .status("PROCESSING")
                .startTime(new Date())
                .totalRows(0)
                .processedRows(0)
                .build();
        
        redisTemplate.opsForValue().set("excel_task:" + taskId, status, Duration.ofHours(24));
        
        // 异步处理
        taskExecutor.execute(() -> {
            try {
                ImportResult result = importUsers(file);
                
                // 更新状态
                status.setStatus("COMPLETED");
                status.setEndTime(new Date());
                status.setTotalRows(result.getTotalRows());
                status.setProcessedRows(result.getSuccessRows());
                status.setResult(result);
                
                redisTemplate.opsForValue().set("excel_task:" + taskId, status, Duration.ofHours(24));
                
            } catch (Exception e) {
                log.error("异步导入失败", e);
                status.setStatus("FAILED");
                status.setEndTime(new Date());
                status.setErrorMsg(e.getMessage());
                
                redisTemplate.opsForValue().set("excel_task:" + taskId, status, Duration.ofHours(24));
            }
        });
        
        return taskId;
    }
    
    public ExcelTaskStatus getTaskStatus(String taskId) {
        return (ExcelTaskStatus) redisTemplate.opsForValue().get("excel_task:" + taskId);
    }
}

2. 内存优化

// 配置EasyExcel的内存使用
EasyExcel.read(inputStream, clazz, listener)
        .readCache(new MapCache())          // 使用Map缓存
        .mandatoryUseInputStream(true)      // 强制使用InputStream
        .autoCloseStream(false)             // 手动管理流
        .sheet()
        .doRead();

🎯 高级特性应用

1. 多Sheet处理

public void exportMultiSheet(HttpServletResponse response) {
    ExcelUtils.setResponseHeader(response, "多Sheet导出.xlsx");
    
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
    
    try {
        // Sheet1: 用户数据
        WriteSheet userSheet = EasyExcel.writerSheet(0, "用户数据")
                .head(UserExportVO.class)
                .build();
        List<UserExportVO> users = getUserData();
        excelWriter.write(users, userSheet);
        
        // Sheet2: 角色数据
        WriteSheet roleSheet = EasyExcel.writerSheet(1, "角色数据")
                .head(RoleExportVO.class)
                .build();
        List<RoleExportVO> roles = getRoleData();
        excelWriter.write(roles, roleSheet);
        
        // Sheet3: 统计数据
        WriteSheet statSheet = EasyExcel.writerSheet(2, "统计数据")
                .head(StatExportVO.class)
                .build();
        List<StatExportVO> stats = getStatData();
        excelWriter.write(stats, statSheet);
        
    } finally {
        excelWriter.finish();
    }
}

2. 动态表头

public void exportDynamicHead(HttpServletResponse response) {
    // 动态构建表头
    List<List<String>> head = new ArrayList<>();
    head.add(Arrays.asList("基本信息", "用户ID"));
    head.add(Arrays.asList("基本信息", "用户名"));
    head.add(Arrays.asList("基本信息", "姓名"));
    head.add(Arrays.asList("联系方式", "邮箱"));
    head.add(Arrays.asList("联系方式", "手机"));
    
    // 动态数据
    List<List<Object>> data = new ArrayList<>();
    // ... 填充数据
    
    EasyExcel.write(response.getOutputStream())
            .head(head)
            .sheet("动态表头")
            .doWrite(data);
}

3. 图表集成

public class ChartWriteHandler implements WorkbookWriteHandler {
    
    @Override
    public void afterWorkbookCreate(WorkbookWriteHandlerContext context) {
        Workbook workbook = context.getWriteContext().writeWorkbookHolder().getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        
        // 创建图表
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
        
        Chart chart = drawing.createChart(anchor);
        ChartLegend legend = chart.getOrCreateLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);
        
        // 添加数据系列
        ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
        ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
        ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
        
        chart.plot(data, bottomAxis, leftAxis);
    }
}

💡 实战案例解析

案例1：用户批量导入系统

需求：

• 支持10万级用户数据导入
• 重复数据检测和处理
• 导入进度实时显示
• 错误数据详细反馈

解决方案：

@Service
public class UserBatchImportService {
    
    public String importUsers(MultipartFile file) {
        String taskId = UUID.randomUUID().toString();
        
        // 创建导入任务
        ImportTask task = ImportTask.builder()
                .taskId(taskId)
                .fileName(file.getOriginalFilename())
                .fileSize(file.getSize())
                .status(ImportStatus.PROCESSING)
                .createTime(new Date())
                .build();
        
        importTaskMapper.insert(task);
        
        // 异步处理
        CompletableFuture.runAsync(() -> processImport(file, task));
        
        return taskId;
    }
    
    private void processImport(MultipartFile file, ImportTask task) {
        try {
            // 1. 预读取统计总行数
            int totalRows = countRows(file);
            updateTaskProgress(task.getTaskId(), 0, totalRows);
            
            // 2. 分批处理
            AtomicInteger processedRows = new AtomicInteger(0);
            List<ImportError> errors = new ArrayList<>();
            
            EasyExcel.read(file.getInputStream(), UserImportVO.class, 
                new PageReadListener<UserImportVO>(dataList -> {
                    // 批量处理逻辑
                    ProcessResult result = processBatch(dataList);
                    
                    // 更新进度
                    int current = processedRows.addAndGet(dataList.size());
                    updateTaskProgress(task.getTaskId(), current, totalRows);
                    
                    // 收集错误
                    errors.addAll(result.getErrors());
                }, 1000))
                .sheet().doRead();
            
            // 3. 完成任务
            completeTask(task.getTaskId(), processedRows.get(), errors);
            
        } catch (Exception e) {
            failTask(task.getTaskId(), e.getMessage());
        }
    }
}

案例2：财务报表导出

需求：

• 复杂的多级表头
• 数据汇总和计算
• 样式美化
• 大数据量支持

解决方案：

public void exportFinancialReport(FinancialQuery query, HttpServletResponse response) {
    // 1. 构建复杂表头
    List<List<String>> complexHead = buildComplexHead();
    
    // 2. 查询和计算数据
    List<FinancialData> rawData = financialMapper.selectByQuery(query);
    List<List<Object>> processedData = processFinancialData(rawData);
    
    // 3. 导出
    EasyExcel.write(response.getOutputStream())
            .head(complexHead)
            .registerWriteHandler(new FinancialStyleHandler())
            .registerWriteHandler(new FormulaWriteHandler())
            .sheet("财务报表")
            .doWrite(processedData);
}

// 样式处理器
public class FinancialStyleHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        
        // 根据数据类型设置样式
        if (isAmountCell(context)) {
            // 金额格式
            CellStyle style = createAmountStyle(context.getWorkbook());
            cell.setCellStyle(style);
        } else if (isPercentCell(context)) {
            // 百分比格式
            CellStyle style = createPercentStyle(context.getWorkbook());
            cell.setCellStyle(style);
        }
    }
}

🎯 总结

本指南全面介绍了EasyExcel的核心功能和最佳实践：

核心要点

1. 环境搭建：正确的依赖配置和项目结构
2. 模型设计：合理的注解使用和字段配置
3. 数据处理：完善的验证和转换机制
4. 错误处理：详细的异常处理和错误反馈
5. 性能优化：大文件处理和内存优化技巧

最佳实践

• 使用注解驱动简化开发
• 实现完善的数据验证机制
• 采用分批处理应对大数据量
• 提供友好的错误提示和进度反馈
• 合理的样式设计提升用户体验

适用场景

• 企业级数据导入导出系统
• 财务报表和统计分析
• 用户数据批量管理
• 商品信息维护系统

通过本指南的学习，你应该能够熟练使用EasyExcel构建企业级的Excel处理系统。

本指南持续更新，如有问题请参考官方文档或提交Issue。