Java导入Excel对数据进行校验

废话不多说直接上代码

1、先创建注解跟校验字段的工具类

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author jiazm3
 * @date 2021/12/17 11:39
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
    String message() default "导入字段不能为空";
}

import com.lenovo.gsc.tech.framework.exception.BaseException;

import java.lang.reflect.Field;
import java.util.Objects;

/**
 * Excel导入字段校验
 * 校验的JavaBean 其属性须有自定义注解
 * @author jiazm3
 * @date 2021/12/17 11:44
 */
@SuppressWarnings("java:S1118")
public class ExcelImportValidUtil {
    @SuppressWarnings("java:S3011")
    public static void valid(Object object, Integer rowIndex) {
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置可访问
            field.setAccessible(true);
            //属性的值
            Object fieldValue = null;
            try {
                fieldValue = field.get(object);
            } catch (IllegalAccessException e) {
                throw new BaseException(e.getMessage());
            }

            //是否包含必填校验注解
            boolean isExcelValid = field.isAnnotationPresent(ExcelStrValid.class);
            if (isExcelValid && Objects.isNull(fieldValue)) {
                throw new BaseException("The " + rowIndex + " line," + field.getAnnotation(ExcelStrValid.class).message());
            }
        }
    }
}

2、注解使用

@Data
public class SystemInfoImportVO {
    /**
     * 系统类型
     */
    @ExcelProperty(value = "System Type", index = 0)
    @ExcelStrValid(message = "System Type cannot be empty")
    private String systemType;
    /**
     * 所属系统或模块
     */
    @ExcelProperty(value = "Application Area", index = 1)
    @ExcelStrValid(message = "Application Area cannot be empty")
    private String functionMod;
    /**
     * 应用类型
     */
    @ExcelProperty(value = "Application Type", index = 2)
    private String applicationType;

3、创建阿里EasyExcel 监听器 EasyExcelSystemInfoListener

public LdmpResponse<String> importSystemInfoExcel(MultipartFile file, HttpServletRequest request) {
        ImportExcelUtils.verifyFormat(file);
        String itCode = UserUtils.getUserItCode();
        var date = Date.from(LocalDateTime.now().atZone(ZoneId.systemDefault()).toInstant());
        //手动开启事务
        var transactionStatus = platformTransactionManager.getTransaction(transactionDefinition);
        try {
            EasyExcelFactory.read(file.getInputStream(), SystemInfoImportVO.class, new EasyExcelSystemInfoListener(
                    getConfigCenterVos(), this, dataStareMapper, objectInfoMapper,
                    systemInfoMapper, archiveStrategyMapper, itCode, date)).sheet("ECC S4 Object Profile").headRowNumber(3).doRead();
            packageObjectInfoLog(itCode, null, request.getServletPath(), true, null);
            //提交事务
            platformTransactionManager.commit(transactionStatus);
        } catch (Exception e) {
            log.error("system info import fail cause->{}", e.getMessage());
            //回滚事务
            platformTransactionManager.rollback(transactionStatus);
            //日志操作
            packageObjectInfoLog(itCode, null, request.getServletPath(), false, e.getMessage());
            throw new FileException(e.getMessage());
        }
        return LdmpResponse.success("import success");
    }

/**
     * 加载一条会调用此方法
     *
     * @param systemInfoImportVo
     * @param analysisContext
     */
    @Override
    public void invoke(SystemInfoImportVO systemInfoImportVo, AnalysisContext analysisContext) {
        //校验参数
        ExcelImportValidUtil.valid(systemInfoImportVo, analysisContext.readRowHolder().getRowIndex() + 1);
        packageData(systemInfoImportVo, analysisContext);
    }