Java中easypoi的使用之導(dǎo)入校驗

更新時間：2023年03月16日 09:02:01 作者：yololee_

因工作需要,使用easypoi導(dǎo)入表格,并進(jìn)行校驗,將表格中有問題的地方,給出提示信息,以表格形式返回,下面這篇文章主要給大家介紹了關(guān)于Java中easypoi的使用之導(dǎo)入校驗的相關(guān)資料,需要的朋友可以參考下

一、導(dǎo)入之基礎(chǔ)校驗

現(xiàn)在產(chǎn)品需要對導(dǎo)入的Excel進(jìn)行校驗，不合法的Excel不允許入庫，需要返回具體的錯誤信息給前端，提示給用戶，錯誤信息中需要包含行號以及對應(yīng)的錯誤。

因為 EasyPOI 支持 Hibernate Validator ，所以直接使用就可以了，因為要將錯誤信息以及錯誤行號返回，所以需要用到 EasyPOI 的高級用法，實現(xiàn) IExcelDataModel與 IExcelModel接口，IExcelDataModel負(fù)責(zé)設(shè)置行號，IExcelModel 負(fù)責(zé)設(shè)置錯誤信息

如果使用到了 @Pattern 注解，則字段類型必須是 String 類型，否則會拋出異常

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

同理如果校驗 Date 類型字段，先將類型改成String，正則表達(dá)式參考下文寫法。也就是說原本Integer類型的

這里需要注意，如果@Excel注解中設(shè)置了 replace 屬性，則Hibernate Validator 校驗的是替換后的值

導(dǎo)出時候的實體類

@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 = "工作經(jīng)歷*")
    private List<ExperienceInputEntity> experienceList;

    @ExcelCollection(name = "教育經(jīng)歷*")
    private List<EducationInputEntity> educationList;

    @ExcelCollection(name = "獲獎情況")
    private List<AwardsInputEntity> awardList;

    @ExcelCollection(name = "技能證書")
    private List<PunishmentInputEntity> punishmentList;

    @Excel(name = "特長")
    private String specialty;
}

導(dǎo)入時候的實體類

@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{2}( )(00:00:00)( )(CST)( )\\d{4}";

    /**
     * 行號
     */
    private int rowNum;

    /**
     * 錯誤消息
     */
    private String errorMsg;

    @Excel(name = "姓名*")
    @NotBlank(message = "[姓名]不能為空")
    private String name;

    @Excel(name = "性別*", replace = {"男_0", "女_1"})
    @Pattern(regexp = "[01]", 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 = {"3K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"})
    @Pattern(regexp = "[123456]", message = "薪資信息錯誤")
    private String salaryStr;

    @Excel(name = "工作地點*")
    @NotBlank(message = "[工作地點]不能為空")
    private String workArea;

    @ExcelCollection(name = "工作經(jīng)歷*")
    private List<ExperienceInputEntity> experienceList;

    @ExcelCollection(name = "教育經(jīng)歷*")
    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;
    }
    
    // 工作經(jīng)歷
@Data
public class ExperienceInputEntity {
    @Excel(name = "公司名稱*")
    private String companyName;

    @Excel(name = "所在行業(yè)*")
    private String industry;

    @Excel(name = "開始時間*")
    @Pattern(regexp = DATE_REGEXP, message = "[工作經(jīng)歷][開始時間]時間格式錯誤")
    private String beginTimeStr;

    @Excel(name = "結(jié)束時間*")
    @Pattern(regexp = DATE_REGEXP, message = "[工作經(jīng)歷][結(jié)束時間]時間格式錯誤")
    private String finishTimeStr;

    @Excel(name = "職位名稱*")
    private String jobTitle;

    @Excel(name = "所屬部門*")
    private String department;

    @Excel(name = "工作內(nèi)容*")
    private String description;
}

// 教育經(jīng)歷
@Data
public class EducationInputEntity {

    @Excel(name = "學(xué)校*")
    private String schoolName;

    @Excel(name = "學(xué)歷*", replace = {"初中及以下_1", "中專_2", "高中_3", "大專_4", "本科_5", "碩士_6", "博士_7"})
    @Pattern(regexp = "[1234567]", message = "學(xué)歷信息錯誤")
    private String recordStr;

    @Excel(name = "開始年份*")
    @Pattern(regexp = DATE_REGEXP, message = "[教育經(jīng)歷][開始年份]時間格式錯誤")
    private String beginTimeStr;

    @Excel(name = "畢業(yè)年份*")
    @Pattern(regexp = DATE_REGEXP, message = "[教育經(jīng)歷][畢業(yè)年份]時間格式錯誤")
    private String finishTimeStr;

    @Excel(name = "專業(yè)*")
    private String profession;
}
}

二、導(dǎo)入值自定義校驗之重復(fù)值校驗

上文所作的校驗只是一些基本的校驗，可能會有諸如Excel中重復(fù)行校驗，Excel中數(shù)據(jù)與數(shù)據(jù)庫重復(fù)校驗等等。這種校驗就無法通過 Hibernate Validator 來完成，只能寫代碼來實現(xiàn)校驗邏輯了。

首先從簡單的Excel數(shù)據(jù)與數(shù)據(jù)庫值重復(fù)校驗開始。為了便于演示，就不引入數(shù)據(jù)庫了，直接Mock一些數(shù)據(jù)用來判斷是否重復(fù)。

@Service
public class MockTalentDataService {
    private static List<TalentUser> talentUsers = new ArrayList<>();
    static {
        TalentUser u1 = new TalentUser(1L, "凌風(fēng)", "18311342567");
        TalentUser u2 = new TalentUser(2L, "張三", "18512343567");
        TalentUser u3 = new TalentUser(3L, "李四", "18902343267");
        talentUsers.add(u1);
        talentUsers.add(u2);
        talentUsers.add(u3);
    }

    /**
     * 校驗是否重復(fù)
     */
    public boolean checkForDuplicates(String name, String phone) {
        // 姓名與手機號相等個數(shù)不等于0則為重復(fù)
        return talentUsers.stream().anyMatch(e -> e.getName().equals(name) && e.getPhone().equals(phone));
    }
}

其中Mock數(shù)據(jù)中 ID 為 1 的數(shù)據(jù)與示例Excel2 中的數(shù)據(jù)是重復(fù)的。

EasyPOI 提供了校驗的接口，這需要我們自己寫一個用于校驗的類。在這個類中，可以對導(dǎo)入時的每一行數(shù)據(jù)進(jìn)行校驗，框架通過 ExcelVerifyHandlerResult 對象來判斷是否校驗通過，校驗不通過需要傳遞 ErrorMsg。

@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> {

    @Resource
    private MockTalentDataService mockTalentDataService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) {
        StringJoiner joiner = new StringJoiner(",");
        // 根據(jù)姓名與手機號判斷數(shù)據(jù)是否重復(fù)
        String name = inputEntity.getName();
        String phone = inputEntity.getPhone();
        // mock 數(shù)據(jù)庫
        boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
        if (duplicates) {
            joiner.add("數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)重復(fù)");
        }
        if (joiner.length() != 0) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

修改校驗處代碼，設(shè)置校驗類對象。

@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;

@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ImportParams params = new ImportParams();
  // 表頭設(shè)置為2行
  params.setHeadRows(2);
  // 標(biāo)題行設(shè)置為0行，默認(rèn)是0，可以不設(shè)置
  params.setTitleRows(0);
  // 開啟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() + 1;
    String msg = "第" + line + "行的錯誤是：" + entity.getErrorMsg();
    System.out.println(msg);
  }
  return true;
}

上傳示例Excel2 文件測試，結(jié)果輸出：

而第七行的數(shù)據(jù)正是與Mock中的數(shù)據(jù)相重復(fù)的。

三、導(dǎo)入值自定義校驗之Collection對象校驗

上文中還有一個待解決的問題，就是Collection中的對象添加了Hibernate Validator 注解校驗但是并未生效的問題，現(xiàn)在就來解決一下。上一步中實現(xiàn)了導(dǎo)入對象的校驗類，校驗類會校驗Excel中的每一條數(shù)據(jù)，那我是不是可以直接在校驗類中校驗Collection中對象了呢？實踐證明行不通，因為這個校驗類的verifyHandler方法只會被調(diào)用一次，所以Collection中只有一條記錄。既然這里行不通的話，就只能對導(dǎo)入結(jié)果再進(jìn)行校驗了。

因為Collection中的數(shù)據(jù)EasyPOI校驗不到，所以有問題的數(shù)據(jù)也可能會被框架放到result.getList()中而不是result.getFailList() 中，為了校驗需要將兩個集合合并為一個集合，使用 EasyPOI 自帶的工具類 PoiValidationUtil 進(jìn)行校驗 Collection 中的對象。

@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;

@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ImportParams params = new ImportParams();
  // 表頭設(shè)置為2行
  params.setHeadRows(2);
  // 標(biāo)題行設(shè)置為0行，默認(rèn)是0，可以不設(shè)置
  params.setTitleRows(0);
  // 開啟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()));

  // 合并結(jié)果集
  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() + 1;
    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 ，結(jié)果如下：

四、導(dǎo)入值自定義校驗之Excel重復(fù)行校驗

上文中對Excel中數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)進(jìn)行重復(fù)校驗，可有些需求是要求數(shù)據(jù)庫在入庫前需要對Excel的的重復(fù)行進(jìn)行校驗。這需要在校驗類中完成，但校驗類中并沒有全部行的數(shù)據(jù)，該如何實現(xiàn)呢？博主的做法是將導(dǎo)入的數(shù)據(jù)放到 ThreadLocal 中進(jìn)行暫存，從而達(dá)到在校驗類中校驗Excel重復(fù)行的目的。ThreadLocal使用注意完之后一定要及時清理！

首先定義什么叫重復(fù)行，完全相同的兩行是重復(fù)行，本文中設(shè)定name 與 phone 相同的行為重復(fù)行，由于只需要比較這兩個字段，所以我們需要重寫導(dǎo)入對象的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{2}( )(00:00:00)( )(CST)( )\\d{4}";

    /**
     * 行號
     */
    private int rowNum;

    /**
     * 錯誤消息
     */
    private String errorMsg;

    @Excel(name = "姓名*")
    @NotBlank(message = "[姓名]不能為空")
    private String name;

    @Excel(name = "性別*", replace = {"男_0", "女_1"})
    @Pattern(regexp = "[01]", message = "性別錯誤")
    private String genderStr;

    @Excel(name = "手機號*")
    @Pattern(regexp = "[0-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 = {"3K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"})
    @Pattern(regexp = "[123456]", message = "薪資信息錯誤")
    private String salaryStr;

    @Excel(name = "工作地點*")
    @NotBlank(message = "[工作地點]不能為空")
    private String workArea;

    @ExcelCollection(name = "工作經(jīng)歷*")
    private List<ExperienceInputEntity> experienceList;

    @ExcelCollection(name = "教育經(jīng)歷*")
    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;
    }
}

修改校驗類代碼，實現(xiàn)重復(fù)行的校驗邏輯

@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(",");
        // 根據(jù)姓名與手機號判斷數(shù)據(jù)是否重復(fù)
        String name = inputEntity.getName();
        String phone = inputEntity.getPhone();
        // mock 數(shù)據(jù)庫
        boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
        if (duplicates) {
            joiner.add("數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)重復(fù)");
        }

        List<TalentUserInputEntity> threadLocalVal = threadLocal.get();
        if (threadLocalVal == null) {
            threadLocalVal = new ArrayList<>();
        }

        threadLocalVal.forEach(e -> {
            if (e.equals(inputEntity)) {
                int lineNumber = e.getRowNum() + 1;
                joiner.add("數(shù)據(jù)與第" + lineNumber + "行重復(fù)");
            }
        });
        // 添加本行數(shù)據(jù)對象到ThreadLocal中
        threadLocalVal.add(inputEntity);
        threadLocal.set(threadLocalVal);
        if (joiner.length() != 0) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }

    public ThreadLocal<List<TalentUserInputEntity>> getThreadLocal() {
        return threadLocal;
    }
}

由于校驗類中使用了ThreadLocal，因此需要及時釋放，修改導(dǎo)入處的代碼。

@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;

@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
  ExcelImportResult<TalentUserInputEntity> result;
  try {
    ImportParams params = new ImportParams();
    // 表頭設(shè)置為2行
    params.setHeadRows(2);
    // 標(biāo)題行設(shè)置為0行，默認(rèn)是0，可以不設(shè)置
    params.setTitleRows(0);
    // 開啟Excel校驗
    params.setNeedVerfiy(true);
    params.setVerifyHandler(talentImportVerifyHandler);
    result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
                                             TalentUserInputEntity.class, params);
  } finally {
    // 清除threadLocal 防止內(nèi)存泄漏
    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()));

  // 合并結(jié)果集
  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() + 1;
    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);
  }
}

導(dǎo)入示例Excel2，結(jié)果如下：

五、案例

實體類

CourseEntity.java

package com.mye.hl11easypoi.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創(chuàng)建的多個row)
     */
    @Excel(name = "課程名稱", orderNum = "0", width = 25, needMerge = true)
    private String name;
    /**
     * 老師主鍵
     */
//    @ExcelEntity(id = "major")
    private TeacherEntity chineseTeacher;
    /**
     * 老師主鍵
     */
    @ExcelEntity(id = "absent")
    private TeacherEntity mathTeacher;

    @ExcelCollection(name = "學(xué)生", orderNum = "3")
    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.hl11easypoi.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;
    /**
     * 學(xué)生姓名
     */
    @Excel(name = "學(xué)生姓名", height = 20, width = 30, isImportField = "true")
    private String name;
    /**
     * 學(xué)生性別
     */
    @Excel(name = "學(xué)生性別", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true")
    private int sex;

    @Excel(name = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width = 20)
    private Date birthday;

    @Excel(name = "進(jìn)校日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
    private Date registrationDate;
}

TeacherEntity.java

package com.mye.hl11easypoi.api.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class TeacherEntity {
    /**
     * 教師名稱
     * isImportField   導(dǎo)入Excel時，對Excel中的字段進(jìn)行校驗，如果沒有該字段，導(dǎo)入失敗
     */
    @Excel(name = "教師姓名", width = 30, orderNum = "1" ,isImportField = "true")
    private String name;
    /**
     * 教師性別
     * replace      值的替換，`replace = {"男_1", "女_2"} `將值為1的替換為男
     * suffix       文字后綴
     */
    @Excel(name = "教師性別", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true",orderNum = "2")
    private int sex;
}

自定義校驗類

package com.mye.hl11easypoi.api.verifyHandler;

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.mye.hl11easypoi.api.pojo.CourseEntity;

public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> {

    @Override
    public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
        //假設(shè)我們要添加用戶，
        //現(xiàn)在去數(shù)據(jù)庫查詢getName，如果存在則表示校驗不通過。
        //假設(shè)現(xiàn)在數(shù)據(jù)庫中有個getName 測試課程
        if ("測試課程".equals(courseEntity.getName())) {
            result.setMsg("該課程已存在");
            result.setSuccess(false);
            return result;
        }
        result.setSuccess(true);
        return result;
    }
}

測試類

package com.mye.hl11easypoi;

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.hl11easypoi.api.pojo.*;
import com.mye.hl11easypoi.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.junit4.SpringRunner;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;

@SpringBootTest(classes = Hl11EasypoiApplication.class)
@RunWith(SpringRunner.class)
public class TestPOI {

    @Test
    public void testExportExcel() throws Exception {

        List<CourseEntity> courseEntityList = new ArrayList<>();
        CourseEntity courseEntity = new CourseEntity();
        courseEntity.setId("1");
        courseEntity.setName("測試課程");

        // 第二個
        CourseEntity courseEntity1 = new CourseEntity();
        courseEntity1.setId("2");
        courseEntity1.setName("數(shù)學(xué)");

        TeacherEntity teacherEntity1 = new TeacherEntity();
        teacherEntity1.setSex(1);
        teacherEntity1.setName("李老師");

        TeacherEntity teacherEntity = new TeacherEntity();
        teacherEntity.setName("張老師");
        teacherEntity.setSex(1);

        courseEntity.setMathTeacher(teacherEntity);
        courseEntity1.setMathTeacher(teacherEntity1);

        List<StudentEntity> studentEntities = new ArrayList<>();
        for (int i = 1; i <= 2; i++) {
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setName("學(xué)生" + i);
            studentEntity.setSex(i);
            studentEntity.setBirthday(new Date());
            studentEntities.add(studentEntity);
        }
        courseEntity.setStudents(studentEntities);
        courseEntity1.setStudents(studentEntities);
        courseEntityList.add(courseEntity);
        courseEntityList.add(courseEntity1);
        System.out.println(courseEntityList+"11111111111111");
        Date start = new Date();
        Workbook workbook = ExcelExportUtil.exportExcel( new ExportParams("導(dǎo)出測試",
                        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/教師課程學(xué)生導(dǎo)出測試.xls");
        workbook.write(fos);
        fos.close();
    }

    @Test
    public void testImport2() throws Exception {
        // 參數(shù)1：導(dǎo)入excel文件流  參數(shù)2：導(dǎo)入類型  參數(shù)3：導(dǎo)入的配置對象
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1); // 設(shè)置標(biāo)題列占幾行
        importParams.setHeadRows(2);  // 設(shè)置字段名稱占幾行 即header
        importParams.setNeedVerify(true);//開啟校驗
        importParams.setVerifyHandler(new MyVerifyHandler());
        importParams.setStartSheetIndex(0);  // 設(shè)置從第幾張表格開始讀取，這里0代表第一張表，默認(rèn)從第一張表讀取
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File("E:/desktop/excel/教師課程學(xué)生導(dǎo)出測試.xls")));
        ExcelImportResult result = new ExcelImportService().importExcelByIs(bis, CourseEntity.class, importParams, true);
        //這個是正確導(dǎo)入的
        List<CourseEntity> list = result.getList();

        System.out.println("成功導(dǎo)入的集合："+JSONUtil.toJsonStr(list));

        List<CourseEntity> failList = result.getFailList();
        System.out.println("失敗導(dǎo)入的集合"+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("用戶數(shù)據(jù)表","UTF-8") + ".xls");
//        response.setCharacterEncoding("UTF-8");
//        workbook.write(response.getOutputStream());
        FileOutputStream fos = new FileOutputStream("E:/desktop/excel/用戶數(shù)據(jù)表.xls");
        workbook.write(fos);
        fos.close();
    }
}