目录

• 一、导入之基础校验
• 二、导入值自定义校验之重复值校验
• 三、导入值自定义校验之Collection对象校验
• 四、导入值自定义校验之Excel重复行校验
• 五、案例
• 总结 

一、导入之基础校验

现在产品需要对导入的Excel进行校验，不合法的Excel不允许入库，需要返回具体的错误信息给前端，提示给用户，错误信息中需要包含行号以及对应的错误。

因为 EasyPOI 支持 Hibernate Validator ，所以直接使用就可以了，因为要将错误信息以及错误行号返回，所以需要用到 EasyPOI 的高级用法，实现 IExcelDataModel与 IExcelModel接口，IExcelDataModel负责设置行号，IExcelModel 负责设置错误信息

如果使用到了 @Pattern 注解，则字段类型必须是 String 类型，否则会抛出异常

本文中的原 Integer 类型的 gender 修改成为 String 类型的 genderStr，record 字段也修改为了 String 类型的 recordStr等等

同理如果校验 Date 类型字段，先将类型改成String，正则表达式参考下文写法。也就是说原本Integer类型的

这里需要注意，如果@Excel注解中设置了 replace 属性，则Hibernate Validator 校验的是替换后的值

导出时候的实体类

复制
@Data
public class TalentUserInputEntity{
 
    @Excel(name = "姓名*")
    private String name;
 
    @Excel(name = "性别*")
    private Integer gender;
 
    @Excel(name = "手机号*")
    private String phone;
 
    @Excel(name = "开始工作时间*")
    private Date workTime;
 
    @Excel(name = "民族*")
    private String national;
 
    @Excel(name = "语言水平*")
    private String languageProficiency;
 
    @Excel(name = "出生日期*")
    private Date birth;
 
    @Excel(name = "职位*")
    private String jobsName;
 
    @Excel(name = "职位类型*")
    private String categoryName;
 
    @Excel(name = "薪资*")
    private Integer salary;
 
    @Excel(name = "工作地点*")
    private String workArea;
 
    @ExcelCollection(name = "工作经历*")
    private List<ExperienceInputEntity> experienceList;
 
    @ExcelCollection(name = "教育经历*")
    private List<EducationInputEntity> educationList;
 
    @ExcelCollection(name = "获奖情况")
    private List<AwardsInputEntity> awardList;
 
    @ExcelCollection(name = "技能证书")
    private List<PunishmentInputEntity> punishmentList;
 
    @Excel(name = "特长")
    private String specialty;
}

导入时候的实体类

复制
@Data
public class TalentUserInputEntity implements IExcelDataModel, IExcelModel {
    // 时间格式校验正则
    public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{}( )(00:00:00)( )(CST)( )\\d{4}";
 
    /**
     * 行号
     */
    private int rowNum;
 
    /**
     * 错误消息
     */
    private String errorMsg;
 
    @Excel(name = "姓名*")
    @NotBlank(message = "[姓名]不能为空")
    private String name;
 
    @Excel(name = "性别*", replace = {"男_", "女_1"})
    @Pattern(regexp = "[]", message = "性别错误")
    private String genderStr;
 
    @Excel(name = "手机号*")
    private String phone;
 
    @Excel(name = "开始工作时间*")
    @Pattern(regexp = DATE_REGEXP, message = "[开始工作时间]时间格式错误")
    private String workTimeStr;
 
    @Excel(name = "民族*")
    @NotBlank(message = "[民族]不能为空")
    private String national;
 
    @Excel(name = "语言水平*")
    @NotBlank(message = "[语言水平]不能为空")
    private String languageProficiency;
 
    @Excel(name = "出生日期*")
    @Pattern(regexp = DATE_REGEXP, message = "[出生日期]时间格式错误")
    private String birthStr;
 
    @Excel(name = "职位*")
    @NotBlank(message = "[职位]不能为空")
    private String jobsName;
 
    @Excel(name = "职位类型*")
    @NotBlank(message = "[职位类型]不能为空")
    private String categoryName;
 
    @Excel(name = "薪资*", replace = {"K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"})
    @Pattern(regexp = "[]", message = "薪资信息错误")
    private String salaryStr;
 
    @Excel(name = "工作地点*")
    @NotBlank(message = "[工作地点]不能为空")
    private String workArea;
 
    @ExcelCollection(name = "工作经历*")
    private List<ExperienceInputEntity> experienceList;
 
    @ExcelCollection(name = "教育经历*")
    private List<EducationInputEntity> educationList;
 
    @ExcelCollection(name = "获奖情况")
    private List<AwardsInputEntity> awardList;
 
    @ExcelCollection(name = "技能证书")
    private List<PunishmentInputEntity> punishmentList;
 
    @Excel(name = "特长")
    private String specialty;
    
    @Override
    public String getErrorMsg() {
        return errorMsg;
    }
 
    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
 
    @Override
    public Integer getRowNum() {
        return rowNum;
    }
 
    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }
    
    // 工作经历
@Data
public class ExperienceInputEntity {
    @Excel(name = "公司名称*")
    private String companyName;
 
    @Excel(name = "所在行业*")
    private String industry;
 
    @Excel(name = "开始时间*")
    @Pattern(regexp = DATE_REGEXP, message = "[工作经历][开始时间]时间格式错误")
    private String beginTimeStr;
 
    @Excel(name = "结束时间*")
    @Pattern(regexp = DATE_REGEXP, message = "[工作经历][结束时间]时间格式错误")
    private String finishTimeStr;
 
    @Excel(name = "职位名称*")
    private String jobTitle;
 
    @Excel(name = "所属部门*")
    private String department;
 
    @Excel(name = "工作内容*")
    private String description;
}
 
// 教育经历
@Data
public class EducationInputEntity {
 
    @Excel(name = "学校*")
    private String schoolName;
 
    @Excel(name = "学历*", replace = {"初中及以下_", "中专_2", "高中_3", "大专_4", "本科_5", "硕士_6", "博士_7"})
    @Pattern(regexp = "[]", message = "学历信息错误")
    private String recordStr;
 
    @Excel(name = "开始年份*")
    @Pattern(regexp = DATE_REGEXP, message = "[教育经历][开始年份]时间格式错误")
    private String beginTimeStr;
 
    @Excel(name = "毕业年份*")
    @Pattern(regexp = DATE_REGEXP, message = "[教育经历][毕业年份]时间格式错误")
    private String finishTimeStr;
 
    @Excel(name = "专业*")
    private String profession;
}
}

二、导入值自定义校验之重复值校验

上文所作的校验只是一些基本的校验，可能会有诸如Excel中重复行校验，Excel中数据与数据库重复校验等等。这种校验就无法通过 Hibernate Validator 来完成，只能写代码来实现校验逻辑了。

首先从简单的Excel数据与数据库值重复校验开始。为了便于演示，就不引入数据库了，直接Mock一些数据用来判断是否重复。

复制
@Service
public class MockTalentDataService {
    private static List<TalentUser> talentUsers = new ArrayList<>();
    static {
        TalentUser u = new TalentUser(1L, "凌风", "18311342567");
        TalentUser u = new TalentUser(2L, "张三", "18512343567");
        TalentUser u = new TalentUser(3L, "李四", "18902343267");
        talentUsers.add(u);
        talentUsers.add(u);
        talentUsers.add(u);
    }
 
    /**
     * 校验是否重复
     */
    public boolean checkForDuplicates(String name, String phone) {
        // 姓名与手机号相等个数不等于则为重复
        return talentUsers.stream().anyMatch(e -> e.getName().equals(name) && e.getPhone().equals(phone));
    }
}

其中Mock数据中 ID 为 1 的数据与示例Excel2 中的数据是重复的。

EasyPOI 提供了校验的接口，这需要我们自己写一个用于校验的类。在这个类中，可以对导入时的每一行数据进行校验，框架通过 ExcelVerifyHandlerResult 对象来判断是否校验通过，校验不通过需要传递 ErrorMsg。

复制
@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> {
 
    @Resource
    private MockTalentDataService mockTalentDataService;
 
    @Override
    public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) {
        StringJoiner joiner = new StringJoiner(",");
        // 根据姓名与手机号判断数据是否重复
        String name = inputEntity.getName();
        String phone = inputEntity.getPhone();
        // mock 数据库
        boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
        if (duplicates) {
            joiner.add("数据与数据库数据重复");
        }
        if (joiner.length() !=) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

修改校验处代码，设置校验类对象。

复制
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
 
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ImportParams params = new ImportParams();
  // 表头设置为行
  params.setHeadRows();
  // 标题行设置为行，默认是0，可以不设置
  params.setTitleRows();
  // 开启Excel校验
  params.setNeedVerfiy(true);
  params.setVerifyHandler(talentImportVerifyHandler);
  ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
                                                                                    TalentUserInputEntity.class, params);
  System.out.println("是否校验失败: " + result.isVerfiyFail());
  System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList()));
  System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList()));
  for (TalentUserInputEntity entity : result.getFailList()) {
    int line = entity.getRowNum() +;
    String msg = "第" + line + "行的错误是：" + entity.getErrorMsg();
    System.out.println(msg);
  }
  return true;
}

上传 示例Excel2 文件测试，结果输出：

而第七行的数据正是与Mock中的数据相重复的。

三、导入值自定义校验之Collection对象校验

上文中还有一个待解决的问题，就是Collection中的对象添加了Hibernate Validator 注解校验但是并未生效的问题，现在就来解决一下。上一步中实现了导入对象的校验类，校验类会校验Excel中的每一条数据， 那我是不是可以直接在校验类中校验Collection中对象了呢？实践证明行不通，因为这个校验类的verifyHandler方法只会被调用一次，所以Collection中只有一条记录。既然这里行不通的话，就只能对导入结果再进行校验了。

因为Collection中的数据EasyPOI校验不到，所以有问题的数据也可能会被框架放到result.getList()中而不是result.getFailList() 中，为了校验需要将两个集合合并为一个集合，使用 EasyPOI 自带的工具类 PoiValidationUtil 进行校验 Collection 中的对象。

复制
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
 
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ImportParams params = new ImportParams();
  // 表头设置为行
  params.setHeadRows();
  // 标题行设置为行，默认是0，可以不设置
  params.setTitleRows();
  // 开启Excel校验
  params.setNeedVerfiy(true);
  params.setVerifyHandler(talentImportVerifyHandler);
  ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
                                                                                    TalentUserInputEntity.class, params);
  System.out.println("是否校验失败: " + result.isVerfiyFail());
  System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList()));
  System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList()));
 
  // 合并结果集
  List<TalentUserInputEntity> resultList = new ArrayList<>();
  resultList.addAll(result.getFailList());
  resultList.addAll(result.getList());
  for (TalentUserInputEntity inputEntity : resultList) {
    StringJoiner joiner = new StringJoiner(",");
    joiner.add(inputEntity.getErrorMsg());
    // 校验Collection的元素
    inputEntity.getExperienceList().forEach(e -> verify(joiner, e));
    inputEntity.getEducationList().forEach(e -> verify(joiner, e));
    inputEntity.getAwardList().forEach(e -> verify(joiner, e));
    inputEntity.getPunishmentList().forEach(e -> verify(joiner, e));
    inputEntity.setErrorMsg(joiner.toString());
  }
 
  for (TalentUserInputEntity entity : result.getFailList()) {
    int line = entity.getRowNum() +;
    String msg = "第" + line + "行的错误是：" + entity.getErrorMsg();
    System.out.println(msg);
  }
  return true;
}
 
private void verify(StringJoiner joiner, Object object) {
  String validationMsg = PoiValidationUtil.validation(object, null);
  if (StringUtils.isNotEmpty(validationMsg)) {
    joiner.add(validationMsg);
  }
}

上传 示例Excel2 ，结果如下：

四、导入值自定义校验之Excel重复行校验

上文中对Excel中数据与数据库数据进行重复校验，可有些需求是要求数据库在入库前需要对Excel的的重复行进行校验。这需要在校验类中完成，但校验类中并没有全部行的数据，该如何实现呢？博主的做法是将导入的数据放到 ThreadLocal 中进行暂存，从而达到在校验类中校验Excel重复行的目的。ThreadLocal使用注意完之后一定要及时清理！

首先定义什么叫重复行，完全相同的两行是重复行，本文中设定name 与 phone 相同的行为重复行，由于只需要比较这两个字段，所以我们需要重写导入对象的equals与hashCode方法。

复制
@Data
public class TalentUserInputEntity implements IExcelDataModel, IExcelModel {
    // 时间格式校验正则
    public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{}( )(00:00:00)( )(CST)( )\\d{4}";
 
    /**
     * 行号
     */
    private int rowNum;
 
    /**
     * 错误消息
     */
    private String errorMsg;
 
    @Excel(name = "姓名*")
    @NotBlank(message = "[姓名]不能为空")
    private String name;
 
    @Excel(name = "性别*", replace = {"男_", "女_1"})
    @Pattern(regexp = "[]", message = "性别错误")
    private String genderStr;
 
    @Excel(name = "手机号*")
    @Pattern(regexp = "[-9]{11}", message = "手机号不正确")
    private String phone;
 
    @Excel(name = "开始工作时间*")
    @Pattern(regexp = DATE_REGEXP, message = "[开始工作时间]时间格式错误")
    private String workTimeStr;
 
    @Excel(name = "民族*")
    @NotBlank(message = "[民族]不能为空")
    private String national;
 
    @Excel(name = "语言水平*")
    @NotBlank(message = "[语言水平]不能为空")
    private String languageProficiency;
 
    @Excel(name = "出生日期*")
    @Pattern(regexp = DATE_REGEXP, message = "[出生日期]时间格式错误")
    private String birthStr;
 
    @Excel(name = "职位*")
    @NotBlank(message = "[职位]不能为空")
    private String jobsName;
 
    @Excel(name = "职位类型*")
    @NotBlank(message = "[职位类型]不能为空")
    private String categoryName;
 
    @Excel(name = "薪资*", replace = {"K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"})
    @Pattern(regexp = "[]", message = "薪资信息错误")
    private String salaryStr;
 
    @Excel(name = "工作地点*")
    @NotBlank(message = "[工作地点]不能为空")
    private String workArea;
 
    @ExcelCollection(name = "工作经历*")
    private List<ExperienceInputEntity> experienceList;
 
    @ExcelCollection(name = "教育经历*")
    private List<EducationInputEntity> educationList;
 
    @ExcelCollection(name = "获奖情况")
    private List<AwardsInputEntity> awardList;
 
    @ExcelCollection(name = "技能证书")
    private List<PunishmentInputEntity> punishmentList;
 
    @Excel(name = "特长")
    private String specialty;
 
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        TalentUserInputEntity that = (TalentUserInputEntity) o;
        return Objects.equals(name, that.name) &&
                Objects.equals(phone, that.phone);
    }
 
    @Override
    public int hashCode() {
        return Objects.hash(name, phone);
    }
    @Override
    public String getErrorMsg() {
        return errorMsg;
    }
 
    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
 
    @Override
    public Integer getRowNum() {
        return rowNum;
    }
 
    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }
}

修改校验类代码，实现重复行的校验逻辑

复制
@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> {
 
    private final ThreadLocal<List<TalentUserInputEntity>> threadLocal = new ThreadLocal<>();
 
    @Resource
    private MockTalentDataService mockTalentDataService;
 
    @Override
    public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) {
        StringJoiner joiner = new StringJoiner(",");
        // 根据姓名与手机号判断数据是否重复
        String name = inputEntity.getName();
        String phone = inputEntity.getPhone();
        // mock 数据库
        boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
        if (duplicates) {
            joiner.add("数据与数据库数据重复");
        }
 
        List<TalentUserInputEntity> threadLocalVal = threadLocal.get();
        if (threadLocalVal == null) {
            threadLocalVal = new ArrayList<>();
        }
 
        threadLocalVal.forEach(e -> {
            if (e.equals(inputEntity)) {
                int lineNumber = e.getRowNum() +;
                joiner.add("数据与第" + lineNumber + "行重复");
            }
        });
        // 添加本行数据对象到ThreadLocal中
        threadLocalVal.add(inputEntity);
        threadLocal.set(threadLocalVal);
        if (joiner.length() !=) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
 
    public ThreadLocal<List<TalentUserInputEntity>> getThreadLocal() {
        return threadLocal;
    }
}

由于校验类中使用了ThreadLocal，因此需要及时释放，修改导入处的代码。

复制
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
 
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ExcelImportResult<TalentUserInputEntity> result;
  try {
    ImportParams params = new ImportParams();
    // 表头设置为行
    params.setHeadRows();
    // 标题行设置为行，默认是0，可以不设置
    params.setTitleRows();
    // 开启Excel校验
    params.setNeedVerfiy(true);
    params.setVerifyHandler(talentImportVerifyHandler);
    result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
                                             TalentUserInputEntity.class, params);
  } finally {
    // 清除threadLocal 防止内存泄漏
    ThreadLocal<List<TalentUserInputEntity>> threadLocal = talentImportVerifyHandler.getThreadLocal();
    if (threadLocal != null) {
      threadLocal.remove();
    }
  }
  System.out.println("是否校验失败: " + result.isVerfiyFail());
  System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList()));
  System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList()));
 
  // 合并结果集
  List<TalentUserInputEntity> resultList = new ArrayList<>();
  resultList.addAll(result.getFailList());
  resultList.addAll(result.getList());
  for (TalentUserInputEntity inputEntity : resultList) {
    StringJoiner joiner = new StringJoiner(",");
    joiner.add(inputEntity.getErrorMsg());
    // 校验Collection的元素
    inputEntity.getExperienceList().forEach(e -> verify(joiner, e));
    inputEntity.getEducationList().forEach(e -> verify(joiner, e));
    inputEntity.getAwardList().forEach(e -> verify(joiner, e));
    inputEntity.getPunishmentList().forEach(e -> verify(joiner, e));
    inputEntity.setErrorMsg(joiner.toString());
  }
 
  for (TalentUserInputEntity entity : result.getFailList()) {
    int line = entity.getRowNum() +;
    String msg = "第" + line + "行的错误是：" + entity.getErrorMsg();
    System.out.println(msg);
  }
  return true;
}
 
private void verify(StringJoiner joiner, Object object) {
  String validationMsg = PoiValidationUtil.validation(object, null);
  if (StringUtils.isNotEmpty(validationMsg)) {
    joiner.add(validationMsg);
  }
}

导入示例Excel2，结果如下：

五、案例

实体类

CourseEntity.java

复制
package com.mye.hleasypoi.api.pojo;
 
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
 
import java.util.List;
 
@Data
@ExcelTarget("courseEntity")
public class CourseEntity implements java.io.Serializable, IExcelModel, IExcelDataModel {
    /**
     * 主键
     */
    private String id;
    /**
     * 课程名称
     * needMerge    是否需要纵向合并单元格(用于list创建的多个row)
     */
    @Excel(name = "课程名称", orderNum = "", width = 25, needMerge = true)
    private String name;
    /**
     * 老师主键
     */
//    @ExcelEntity(id = "major")
    private TeacherEntity chineseTeacher;
    /**
     * 老师主键
     */
    @ExcelEntity(id = "absent")
    private TeacherEntity mathTeacher;
 
    @ExcelCollection(name = "学生", orderNum = "")
    private List<StudentEntity> students;
 
    private String errorMsg; //自定义一个errorMsg接受下面重写IExcelModel接口的get和setErrorMsg方法。
 
    private Integer rowNum;  //自定义一个rowNum接受下面重写IExcelModel接口的get和setRowNum方法。
 
    @Override
    public String getErrorMsg() {
        return errorMsg;
    }
 
    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
 
    @Override
    public Integer getRowNum() {
        return rowNum;
    }
 
    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }
}

StudentEntity.java

复制
package com.mye.hleasypoi.api.pojo;
 
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
 
import java.util.Date;
 
@Data
public class StudentEntity implements java.io.Serializable {
    /**
     * id
     */
    private String id;
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height =, width = 30, isImportField = "true")
    private String name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = {"男_", "女_2"}, suffix = "生", isImportField = "true")
    private int sex;
 
    @Excel(name = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width =)
    private Date birthday;
 
    @Excel(name = "进校日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
    private Date registrationDate;
}
 

TeacherEntity.java

复制
package com.mye.hleasypoi.api.pojo;
 
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
 
@Data
public class TeacherEntity {
    /**
     * 教师名称
     * isImportField   导入Excel时，对Excel中的字段进行校验，如果没有该字段，导入失败
     */
    @Excel(name = "教师姓名", width =, orderNum = "1" ,isImportField = "true")
    private String name;
    /**
     * 教师性别
     * replace      值的替换，`replace = {"男_", "女_2"} `将值为1的替换为男
     * suffix       文字后缀
     */
    @Excel(name = "教师性别", replace = {"男_", "女_2"}, suffix = "生", isImportField = "true",orderNum = "2")
    private int sex;
}

自定义校验类

复制
package com.mye.hleasypoi.api.verifyHandler;
 
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.mye.hleasypoi.api.pojo.CourseEntity;
 
public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> {
 
    @Override
    public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
        //假设我们要添加用户，
        //现在去数据库查询getName，如果存在则表示校验不通过。
        //假设现在数据库中有个getName 测试课程
        if ("测试课程".equals(courseEntity.getName())) {
            result.setMsg("该课程已存在");
            result.setSuccess(false);
            return result;
        }
        result.setSuccess(true);
        return result;
    }
}

测试类

复制
package com.mye.hleasypoi;
 
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
 
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.hutool.json.JSONUtil;
import com.mye.hleasypoi.api.pojo.*;
import com.mye.hleasypoi.api.verifyHandler.MyVerifyHandler;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.SpringRunner;
 
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;
 
@SpringBootTest(classes = HlEasypoiApplication.class)
@RunWith(SpringRunner.class)
public class TestPOI {
 
    @Test
    public void testExportExcel() throws Exception {
 
        List<CourseEntity> courseEntityList = new ArrayList<>();
        CourseEntity courseEntity = new CourseEntity();
        courseEntity.setId("");
        courseEntity.setName("测试课程");
 
        // 第二个
        CourseEntity courseEntity = new CourseEntity();
        courseEntity.setId("2");
        courseEntity.setName("数学");
 
        TeacherEntity teacherEntity = new TeacherEntity();
        teacherEntity.setSex(1);
        teacherEntity.setName("李老师");
 
        TeacherEntity teacherEntity = new TeacherEntity();
        teacherEntity.setName("张老师");
        teacherEntity.setSex();
 
        courseEntity.setMathTeacher(teacherEntity);
        courseEntity.setMathTeacher(teacherEntity1);
 
        List<StudentEntity> studentEntities = new ArrayList<>();
        for (int i =; i <= 2; i++) {
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setName("学生" + i);
            studentEntity.setSex(i);
            studentEntity.setBirthday(new Date());
            studentEntities.add(studentEntity);
        }
        courseEntity.setStudents(studentEntities);
        courseEntity.setStudents(studentEntities);
        courseEntityList.add(courseEntity);
        courseEntityList.add(courseEntity);
        System.out.println(courseEntityList+"");
        Date start = new Date();
        Workbook workbook = ExcelExportUtil.exportExcel( new ExportParams("导出测试",
                        null, "测试"),
                CourseEntity.class, courseEntityList);
        System.out.println(new Date().getTime() - start.getTime());
        File savefile = new File("E:/desktop/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("E:/desktop/excel/教师课程学生导出测试.xls");
        workbook.write(fos);
        fos.close();
    }
 
    @Test
    public void testImport() throws Exception {
        // 参数：导入excel文件流  参数2：导入类型  参数3：导入的配置对象
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(); // 设置标题列占几行
        importParams.setHeadRows();  // 设置字段名称占几行 即header
        importParams.setNeedVerify(true);//开启校验
        importParams.setVerifyHandler(new MyVerifyHandler());
        importParams.setStartSheetIndex();  // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File("E:/desktop/excel/教师课程学生导出测试.xls")));
        ExcelImportResult result = new ExcelImportService().importExcelByIs(bis, CourseEntity.class, importParams, true);
        //这个是正确导入的
        List<CourseEntity> list = result.getList();
 
        System.out.println("成功导入的集合："+JSONUtil.toJsonStr(list));
 
        List<CourseEntity> failList = result.getFailList();
        System.out.println("失败导入的集合"+JSONUtil.toJsonStr(failList));
        for (CourseEntity courseEntity : failList) {
            int line = courseEntity.getRowNum();
            String msg = "第" + line + "行的错误是:" + courseEntity.getErrorMsg();
            System.out.println(msg);
        }
 
        //将错误excel信息返回给客户端
        ExportParams exportParams = new ExportParams();
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, CourseEntity.class, failList);
//        HttpServletResponse response = null;
//        response.setHeader("content-Type", "application/vnd.ms-excel");
//        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户数据表","UTF-") + ".xls");
//        response.setCharacterEncoding("UTF-");
//        workbook.write(response.getOutputStream());
        FileOutputStream fos = new FileOutputStream("E:/desktop/excel/用户数据表.xls");
        workbook.write(fos);
        fos.close();
    }
}

导出结果

导入结果