基于easyExcel实现通用导入接口

接口抽象类

public interface CommonExcelCheckService<T> {

    ImportResultItem<T>  checkMethod(String userId,CommonExcelImportEntity commonImportExcel);

    Integer saveAll(List<T> listForSave);
}

抽象实体类

public abstract class CommonExcelImportEntity {



    public abstract boolean isNull();

    public abstract boolean equals(Object o);

    public abstract int hashCode();
}

校验工具类

@Slf4j
public class CheckUtil {
    /**
     * 判断@ExcelProperty 注解的属性是否为空
     * @param o
     * @return
     * @throws Exception
     */
    public static boolean checkExcelIsNullOrEmpty(Object o) {
        Field[] declaredFields = o.getClass().getDeclaredFields();
        for (Field declaredField : declaredFields) {
            CanNull cannull = declaredField.getAnnotation(CanNull.class);
            if (cannull != null) {
                continue;
            }
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                Object getMethod = getGetMethod(o, declaredField.getName());
                if (String.class.isInstance(getMethod)) {
                    String o1 = (String) getMethod;
                    if (StrUtil.isEmpty(o1)) {
                        throw new CheckedException(annotation.value()[0] + "为空");
                    }
                } else {
                    Assert.isNull(getMethod, annotation.value()[0] + "为空");
                }
                //String o1 =(String) getGetMethod(o, declaredField.getName());
            }
        }

        return true;
    }

    public static boolean checkExcelIsNumber(Object o) {
        Field[] declaredFields = o.getClass().getDeclaredFields();
        for (Field declaredField : declaredFields) {
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                IsNumber isNumber = declaredField.getAnnotation(IsNumber.class);
                if (isNumber !=null){
                    Object getMethod = getGetMethod(o, declaredField.getName());
                    if (String.class.isInstance(getMethod)) {
                        String o1 = (String) getMethod;
                        if (StrUtil.isEmpty(o1)){
                            CanNull cannull = declaredField.getAnnotation(CanNull.class);
                            if (cannull != null) {
                                continue;
                            }
                        }
                        try {
                            new BigDecimal(o1);
                        }catch (NumberFormatException e){
                            throw new CheckedException(annotation.value()[0] + "应为数字，格式不正确");
                        }
                    }
                }

                //String o1 =(String) getGetMethod(o, declaredField.getName());
            }
        }

        return true;
    }

    /**
     * 判断@ExcelProperty 注解的属性是否为空
     * @param o 不区分字段类型
     * @return
     * @throws Exception
     */
    public static boolean checkExcelIsNullOrEmpty2(Object o) {

        Field[] declaredFields = o.getClass().getDeclaredFields();
        for (Field declaredField : declaredFields) {
            CanNull cannull = declaredField.getAnnotation(CanNull.class);
            if (cannull != null) {
                continue;
            }
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                Object getMethod = getGetMethod(o, declaredField.getName());
                if (String.class.isInstance(getMethod)) {
                    String o1 = (String) getMethod;
                    if (StrUtil.isEmpty(o1)) {
                        throw new CommonException(annotation.value()[0] + "为空");
                    }
                } else if(Integer.class.isInstance(getMethod)){
                    Assert.isNull(getMethod, annotation.value()[0] + "为空");
                } else if(BigDecimal.class.isInstance(getMethod)){
                    Assert.isNull(getMethod, annotation.value()[0] + "为空");
                }
                //String o1 =(String) getGetMethod(o, declaredField.getName());
            }
        }

        return true;
    }
    /**
     * 判断@ExcelProperty 注解的属性是否为空
     * @param o
     * @return
     * @throws Exception
     */
    public static boolean checkExcelIsNull(Object o)  {

        Field[] declaredFields = o.getClass().getDeclaredFields();
        for (Field declaredField : declaredFields) {
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation!=null){
                Object getMethod = getGetMethod(o, declaredField.getName());
                Assert.isNull(getMethod,annotation.value()[0]+"缺失");
            }
        }

        return true;
    }


    /**
     * 反射获取get方法
     * @param ob
     * @param name
     * @return
     * @throws Exception
     */
    public static Object getGetMethod(Object ob , String name){
        Method[] m = ob.getClass().getMethods();
        for(int i = 0;i < m.length;i++){
            if(("get"+name).toLowerCase().equals(m[i].getName().toLowerCase())){
                try {
                    return m[i].invoke(ob);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }


    /**
     * 反射获取get方法
     * @param ob
     * @param name
     * @return
     * @throws Exception
     */
    public static Method getSetMethod(Object ob , String name){
        Method[] m = ob.getClass().getMethods();
        for(int i = 0;i < m.length;i++){
            if(("set"+name).toLowerCase().equals(m[i].getName().toLowerCase())){
                try {
                    return m[i];
                } catch (Exception e){
                    log.error("系统异常",e);
                }
            }
        }
        return null;
    }
}

注解类

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

}



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

}

返回结果类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ImportResultItem<T> {

    public static final int SUCCESS = 0;

    public static final int FAIL = -1;

    private int code;

    private String msg;

    private T data;
}

异常结果类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ImportError {
    @ApiModelProperty("序号")
    private Integer no;
    @ApiModelProperty("行号")
    private Integer rowNo;
    @ApiModelProperty("原因")
    private String reason;


}

抽象监听类

@Data
@Slf4j
public class CommonExcelImportListener<T> extends AnalysisEventListener<CommonExcelImportEntity> {

    private List<ImportError> importErrors = new ArrayList<>();

    private int total;

    private int success;

    private int fail;

    private CommonExcelCheckService<T> commonExcelCheckService;

    private List<T> list = new ArrayList<>();

    private Map<CommonExcelImportEntity, Integer> map = new HashMap<>();

    private String userId;

    public CommonExcelImportListener(CommonExcelCheckService commonExcelCheckService, String userId) {
        this.commonExcelCheckService = commonExcelCheckService;
        this.userId = userId;
    }

    @Override
    public void invoke(CommonExcelImportEntity commonImportExcel, AnalysisContext analysisContext) {
        //去掉行号
        if (commonImportExcel.isNull()) {
            return;
        }
        total++;
        //当前行号
        Integer rowIndex = analysisContext.readRowHolder().getRowIndex() + 1;
        try {
            if (map.containsKey(commonImportExcel)) {
                throw new CheckedException("数据与第【" + map.get(commonImportExcel) + "】重复");
            } else {
                map.put(commonImportExcel, rowIndex);
            }
            CheckUtil.checkExcelIsNullOrEmpty(commonImportExcel);
            CheckUtil.checkExcelIsNumber(commonImportExcel);
            ImportResultItem<T> result = commonExcelCheckService.checkMethod(userId, commonImportExcel);
            if (result.getCode() == ImportResultItem.SUCCESS) {
                list.add(result.getData());
                success++;
            } else {
                fail++;
                //当前行号
                log.info("当前第{}行数据:{}", rowIndex, JSONObject.toJSONString(commonImportExcel));
                ImportError importError = new ImportError(importErrors.size() + 1, rowIndex, result.getMsg());
                importErrors.add(importError);
            }
        } catch (CheckedException checkedException) {
            fail++;
            log.info("当前第{}行数据:{}", rowIndex, JSONObject.toJSONString(commonImportExcel));
            ImportError importError = new ImportError(importErrors.size() + 1, rowIndex, checkedException.getMessage());
            importErrors.add(importError);
        }
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("list.size+{}", list.size());
        commonExcelCheckService.saveAll(list);
    }
}

测试demo

public class TestCommonExcelCheckServiceImpl implements CommonExcelCheckService<TestCommonImport.TestImportEntity2> {
    @Override
    public ImportResultItem<TestCommonImport.TestImportEntity2> checkMethod(String userId, CommonExcelImportEntity commonImportExcel) {
        ImportResultItem<TestCommonImport.TestImportEntity2> importResult = new ImportResultItem<>();
        TestCommonImport.TestImportEntity2 testImportEntity2 = new TestCommonImport.TestImportEntity2();
        BeanUtils.copyProperties(commonImportExcel,testImportEntity2);
        importResult.setCode(ImportResultItem.SUCCESS);
        importResult.setData(testImportEntity2);
        return importResult;
    }

    @Override
    public Integer saveAll(List<TestCommonImport.TestImportEntity2> listForSave) {
        for (TestCommonImport.TestImportEntity2 testImportEntity2 : listForSave) {
            System.out.println(testImportEntity2.getSupportName());
        }
        return null;
    }
}

public class TestCommonImport {

    public static void main(String[] args) throws FileNotFoundException {
        File file = new File("F:\\bright\\test\\test.xlsx");
        CommonExcelCheckService<TestImportEntity2> commonExcelCheckService = new TestCommonExcelCheckServiceImpl();
        CommonExcelImportListener<TestImportEntity2> commonExcelImportListener = new CommonExcelImportListener(commonExcelCheckService,"user");
        InputStream in = new FileInputStream(file);
        EasyExcel.read(in, TestImportEntity.class, commonExcelImportListener).sheet().headRowNumber(1).doRead();
        System.out.println(commonExcelImportListener.getSuccess());
    }
    @Data
    public static class TestImportEntity extends CommonExcelImportEntity {
        @ApiModelProperty("供方简称")
        @ExcelProperty(value = "供方简称",index = 0)
        private String supportName;

        @ApiModelProperty("热值")
        @ExcelProperty(value = "热值（cal/g）",index = 1)
        @IsNumber
        private String heatValue;

        @ApiModelProperty("硫分（%）")
        @ExcelProperty(value = "硫分（%）",index = 2)
        @IsNumber
        private String sgq;

        @ApiModelProperty("灰分（%）")
        @ExcelProperty(value = "灰分（%）",index = 3)
        @CanNull
        @IsNumber
        private String ash;
        @Override
        public boolean isNull() {
            return false;
        }


        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            TestImportEntity that = (TestImportEntity) o;
            return Objects.equals(supportName, that.supportName) && Objects.equals(heatValue, that.heatValue) && Objects.equals(sgq, that.sgq) && Objects.equals(ash, that.ash);
        }

        @Override
        public int hashCode() {
            return Objects.hash(supportName, heatValue, sgq, ash);
        }
    }

    @Data
    public static class  TestImportEntity2{
        @ApiModelProperty("供方简称")
        private String supportName;

        @ApiModelProperty("热值")
        private String heatValue;

        @ApiModelProperty("硫分（%）")
        private String sgq;
    }
}