Springboot使用EasyExcel（导入）

1、自定义注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ZdyExcelValid {
    String message() default "导入有未填入的字段";
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ZdyExcelDate {

    String message() default "日期格式不正确";
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ZdyExcelGender {

    String message() default "性别格式不正确";
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ZdyExcelIdCard {

    String message() default "身份证号格式不正确";
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ZdyExcelMobile {

    String message() default "手机号码格式不正确";
}

2、Excel导入字段校验工具类（代码中用到了hutool里的工具类）

@Slf4j
public class ExcelImportValid {
    /**
     * Excel导入字段校验
     *
     * @param object 校验的JavaBean 其属性须有自定义注解
     */
    public static String valid(Object object) {
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置可访问
            field.setAccessible(true);
            //属性的值
            Object fieldValue = null;
            try {
                fieldValue = field.get(object);
            } catch (IllegalAccessException e) {
                return field.getAnnotation(ZdyExcelValid.class).message();
            }
            //是否包含必填校验注解
            boolean isExcelValid = field.isAnnotationPresent(ZdyExcelValid.class);
            if (isExcelValid && Objects.isNull(fieldValue)) {
                return field.getAnnotation(ZdyExcelValid.class).message();
            }

            //是否包含日期校验注解
            boolean isExcelDate = field.isAnnotationPresent(ZdyExcelDate.class);
            if (isExcelDate && !Objects.isNull(fieldValue) && !ZdyRegexpUtil.isDateFormat(fieldValue.toString())) {
                return field.getAnnotation(ZdyExcelDate.class).message();
            }

            //是否包含身份证号注解
            boolean isExcelIdCard = field.isAnnotationPresent(ZdyExcelIdCard.class);
            if (isExcelIdCard && !Objects.isNull(fieldValue) && fieldValue.toString().length() != 18) {
                return field.getAnnotation(ZdyExcelIdCard.class).message();
            }

            //是否包含手机号注解
            boolean isMobile = field.isAnnotationPresent(ZdyExcelMobile.class);
            if (isMobile && !Objects.isNull(fieldValue) && !Validator.isMobile(fieldValue.toString())){
                return field.getAnnotation(ZdyExcelMobile.class).message();
            }

            //是否包含性别注解
            boolean isExcelGender = field.isAnnotationPresent(ZdyExcelGender.class);
            if (isExcelGender && !Objects.isNull(fieldValue)) {
                String genderCode = StateGridEnum.GENDER.getGenderCode(fieldValue.toString());
                if (genderCode == null){
                    return field.getAnnotation(ZdyExcelGender.class).message();
                }
            }
        }
        return null;
    }


    public static boolean isLineNullValue(Object data){
        if (ObjectUtil.isNull(data)){
            return true;
        }
        try {
            List<Field> fields  = Arrays.stream(data.getClass().getDeclaredFields()).filter(e -> e.isAnnotationPresent(ExcelProperty.class)).collect(Collectors.toList());

            List<Boolean> lineNullList = new ArrayList<>(fields.size());

            for (Field field : fields) {
                field.setAccessible(true);
                Object value = field.get(data);
                if (ObjectUtil.isNull(value)) {
                    lineNullList.add(Boolean.TRUE);
                } else {
                    lineNullList.add(Boolean.FALSE);
                }
            }
            return lineNullList.stream().allMatch(Boolean.TRUE::equals);
        }catch (Exception e){
            log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
        }
        return true;
    }
}

3、添加导入的excel实体

@ExcelIgnoreUnannotated
@Data
public class UserExcel implements Serializable {
    private static final long serialVersionUID = -5780275758531480972L;
    
    //用户名
    @ZdyExcelValid(message = "[用户名]不能为空")
    @ExcelProperty(value = "用户名")
    private String username;
    //密码
    @ZdyExcelValid(message = "[密码]不能为空")
    @ExcelProperty(value = "密码")
    private String password;
    //姓名
    @ZdyExcelValid(message = "[姓名]不能为空")
    @ExcelProperty(value = "姓名")
    private String name;
    //身份证号
    @ZdyExcelIdCard(message = "[身份证号]格式不正确")
    @ZdyExcelValid(message = "[身份证号]不能为空")
    @ExcelProperty(value = "身份证号")
    private String identityId;
    //手机号码
    @ZdyExcelMobile(message = "[手机号码]格式不正确")
    @ZdyExcelValid(message = "[手机号码]不能为空")
    @ExcelProperty(value = "手机号码")
    private String mobile;
    //出生日期
    @ZdyExcelDate(message = "[出生日期]格式不正确")
    @ZdyExcelValid(message = "[出生日期]不能为空")
    @ExcelProperty(value = "出生日期")
    private String birthDate;
    // 性别
    @ZdyExcelGender(message = "[性别]格式不正确")
    @ZdyExcelValid(message = "[性别]不能为空")
    @ExcelProperty(value = "性别")
    private String gender;

}

4、导入方法

public class UserListener  extends AnalysisEventListener<UserExcel> {

    private final IUserService userService;
    List<String> errorList = new ArrayList<>();

    public UserListener(IUserService userService) {
        this.userService = userService;
    }

    List<User> addList = new ArrayList<>();
    
    @Override
    public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {

        Integer rowNumber = analysisContext.readSheetHolder().getApproximateTotalRowNumber();
//         设置execl最多20000行（含表头）
        if (rowNumber > 20001){
            throw new ExcelAnalysisException("超出2万行限制，总行数为：" + (rowNumber - 1));
        }
        //当前行号
        Integer row = analysisContext.readRowHolder().getRowIndex() + 1;
        //空行校验
        boolean lineNullValue = ExcelImportValid.isLineNullValue(userExcel);
        if (lineNullValue){
            return;
        }
        List<String> errorMsg = new ArrayList<>();
        //字段校验
        String valid = ExcelImportValid.valid(userExcel);
        if (valid != null){
            errorMsg.add(String.format("第%s行：%s", row, valid));
        }
        User user = new User();
        BeanUtil.copyProperties(userExcel, user, CopyOptions.create().setIgnoreNullValue(true).setIgnoreError(true));
        addList.add(User);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (CollUtil.isEmpty(errorList)){
            List<User> arrays = addList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(()
                    -> new TreeSet<>(Comparator.comparing(e -> e.getUserName().trim() + e.getIdentityId().trim()))), ArrayList::new));

            userService.saveBatch(arrays);
            addList.clear();
        }else {
            addList.clear();
            throw new ExcelAnalysisException("导入异常：" + JSONUtil.toJsonStr(errorList));
        }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        Set<String> collect = new HashSet<>(Arrays.asList(
                "用户名",
                "密码",
                "姓名",
                "身份证号",
                "手机号码",
                "出生日期",
                "性别"
        ));
        for (Map.Entry<Integer, String> entry : headMap.entrySet()){
            if (StrUtil.isNotBlank(entry.getValue()) && !collect.contains(entry.getValue())){
                throw new ExcelAnalysisException("导入文件不正确，表头：["+entry.getValue()+"]不匹配");
            }
        }
        super.invokeHeadMap(headMap, context);
    }
}

5、调用

        UserListener userListener = new UserListener(userService);
        InputStream inputStream = file.getInputStream();
        EasyExcel.read(inputStream, UserExcel.class, userListener).sheet().doRead();