easy-poi 导入

1.导入模型

public class KhwxDatasrcEntity implements IExcelDataModel, IExcelModel {
    /**
     * 行号
     */
    private Integer rowNum;
    /**
     * 错误消息
     */
    private String errorMsg;

    @Excel(name = "产品实例Id")
    @NotBlank(message = "不能为空!")
    private String servId;

    @Excel(name = "客户Id")
    @NotBlank(message = "不能为空!")
    private String ownerCustId;

    // 省略。。。
}

2.自定义校验

@Component
public class KhwxDatasrcImportVerifyHandler implements IExcelVerifyHandler<KhwxDatasrcEntity> {

    @Override
    public ExcelVerifyHandlerResult verifyHandler(KhwxDatasrcEntity inputEntity) {
        StringJoiner joiner = new StringJoiner(",");
        // 装机地址 长度是否超过300
        String addressDesc = inputEntity.getAddressDesc();
        if (addressDesc.length() > 600) {
            joiner.add("装机地址字数超长（限300字）!");
        }
        if (joiner.length() != 0) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

3.controller层

@ApiOperation(value = "客户维系工单导入", notes = "客户维系工单导入")
    @PostMapping(value = "/importExcel")
    @ResponseBody
    public ResponseMessage<String> importExcel(@NotNull MultipartFile file) throws Exception {
        this.khwxDatasrcService.importExcel(file, this.getCurrentUser());
        return ResponseMessage.success();
    }

4.service

@Override
    @Transactional(rollbackFor = Exception.class)
    public void importExcel(MultipartFile file, UserVO currentUser) throws Exception {
        ImportParams params = new ImportParams();
        // 表头设置为2行
        params.setHeadRows(1);
        // 开启Excel校验
        params.setNeedVerify(true);
        params.setVerifyHandler(this.khwxDatasrcImportVerifyHandler);
        ExcelImportResult<KhwxDatasrcEntity> result =
            ExcelImportUtil.importExcelMore(file.getInputStream(), KhwxDatasrcEntity.class, params);
        if (!result.isVerifyFail()) {
            result.getList().forEach(khwxDatasrcEntity -> {
                KhwxDatasrcVO khwxDatasrcVO = new KhwxDatasrcVO();
                BeanUtils.copyProperties(khwxDatasrcEntity, khwxDatasrcVO);
                this.save(khwxDatasrcVO, currentUser, true);
            });
        } else {
            List<String> errMsgs = new LinkedList<>();
            for (KhwxDatasrcEntity entity : result.getFailList()) {
                int line = entity.getRowNum() + 1;
                String msg = "第" + line + "行" + entity.getErrorMsg();
                errMsgs.add(msg);
            }
            String msg = errMsgs.stream().collect(Collectors.joining(", "));
            throw new ServiceException(msg);
        }

    }

5.值替换

// replace格式为 "替换前的值_替换后的值"
@Excel(name = "性别*", replace = {"男_0", "女_1"})
private Integer gender;

6.格式校验

@Excel(name = "开始工作时间*")
    @Pattern(regexp = DATE_REGEXP, message = "[开始工作时间]时间格式错误")
    private String workTimeStr;

7.重复行的校验，这个没用到

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

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

@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() + 1;
                joiner.add("数据与第" + lineNumber + "行重复");
            }
        });
        // 添加本行数据对象到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;
    }
}

try {
    ImportParams params = new ImportParams();
    // 表头设置为2行
    params.setHeadRows(2);
    // 标题行设置为0行，默认是0，可以不设置
    params.setTitleRows(0);
    // 开启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();
    }
  }

// 重写equals，判断是否重复
@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);
}

参考：easypoi导入Excel最佳实践 – WeJan1 – 博客园 (cnblogs.com)