栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

easy-excel导入

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

easy-excel导入

使用easy excel导入excel数据到系统

1、自定义校验注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDecimalValid {
    String min();
    String max();
    String message() default "小数类型数字填写超出范围";
}

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

    String regexp() default "";

    String name() default "";

    String message() default "不符合规则";
}


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIntValid {
    int min();
    int max();
    String message() default "整数数字填写超出范围";
}


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
    int length() default 0;
    String message() default "文字填写超出长度要求";
}


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

    
    String name() default "";
}

2、校验类

public class ExcelImportValid {

    
    @SneakyThrows
    public static void valid(Object object) throws ExcelValidException {
        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 RuntimeException(e);
            }

            //是否包含必填校验注解
            boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
            if (isExcelValid && Objects.isNull(fieldValue)) {
                String name = field.getAnnotation(ExcelValid.class).name();
                throw new ExcelValidException("【" + name + "】列有未填值项");
            }
            //是否包含字符串长度校验注解
            boolean isExcelStrValid = field.isAnnotationPresent(ExcelStrValid.class);
            if (isExcelStrValid) {
                String cellStr = fieldValue.toString();
                int length = field.getAnnotation(ExcelStrValid.class).length();
                if (StringUtils.isNotBlank(cellStr) && cellStr.length() > length) {
                    throw new ExcelValidException(field.getAnnotation(ExcelStrValid.class).message());
                }
            }
            //是否包含int类型校验注解
            boolean isExcelIntValid = field.isAnnotationPresent(ExcelIntValid.class);
            if (isExcelIntValid) {
                if (fieldValue instanceof Integer) {
                    int cellInt = Integer.parseInt(fieldValue.toString());
                    int min = field.getAnnotation(ExcelIntValid.class).min();
                    int max = field.getAnnotation(ExcelIntValid.class).max();
                    if (cellInt < min || cellInt > max) {
                        throw new ExcelValidException(field.getAnnotation(ExcelIntValid.class).message());
                    }
                }
            }
            //是否包含decimal类型注解
            boolean isExcelDecimalValid = field.isAnnotationPresent(ExcelDecimalValid.class);
            if (isExcelDecimalValid) {
                if (isBigDecimal(fieldValue.toString())) {
                    BigDecimal cellDecimal = new BigDecimal(fieldValue.toString());
                    BigDecimal min = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).min());
                    BigDecimal max = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).max());
                    if (cellDecimal.compareTo(min) < 0 || cellDecimal.compareTo(max) > 0) {
                        throw new ExcelValidException(field.getAnnotation(ExcelDecimalValid.class).message());
                    }
                } else {
                    throw new ExcelValidException("不是小数数字类型");
                }
            }

            // 是否包含正则校验
            boolean isExcelPatternValid = field.isAnnotationPresent(ExcelPatternValid.class);
            if (isExcelPatternValid) {
                String cellStr = fieldValue.toString();
                String pattern = field.getAnnotation(ExcelPatternValid.class).regexp();
                String name = field.getAnnotation(ExcelPatternValid.class).name();
                if (StringUtils.isBlank(name)) {
                    throw new ExcelValidException("【" + name + "】列有未填值项");
                }
                if (StringUtils.isNotBlank(cellStr) && !Pattern.matches(pattern, cellStr)) {
                    throw new ExcelValidException("【" + name + "】不符合规则");
                }
            }
        }
    }

    private static boolean isBigDecimal(String decimal) {
        try {
            BigDecimal bd = new BigDecimal(decimal);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
}

3、自定义异常处理

public class ExcelValidException extends RuntimeException {
    private static final long serialVersionUID = 1L;

    private int code;

    public ExcelValidException(String msg) {
        this(100, msg, null);
    }


    public ExcelValidException(int code, String msg) {
        this(code, msg, null);
    }

    public ExcelValidException(int code, String msg, Exception nestedEx) {
        super(msg, nestedEx);
        this.code = code;
    }
}

4、监听器

@Slf4j
public class ImportExcelDataListener extends AnalysisEventListener {
    //service的名称
    private final String serviceName;

    // 校验通过数据处理方法
    private final String accessMethodName;

    // 异常数据处理
    private final String errorMethodName;

    private final String version;

    private Map paramMap = new HashMap<>();

    public ImportExcelDataListener(String serviceName, Map param) {
        this.serviceName = serviceName;
        this.accessMethodName = "savaSuccessForExcel";
        this.errorMethodName = "saveErrorForExcel";
        version = UUID.randomUUID().toString();
        paramMap = param;
    }

    public ImportExcelDataListener(String serviceName) {
        this.serviceName = serviceName;
        this.accessMethodName = "savaSuccessForExcel";
        this.errorMethodName = "saveErrorForExcel";
        version = UUID.randomUUID().toString();
        paramMap = new HashMap<>();
    }

    // 校验通过数据
    private final List successList = new LinkedList<>();

    //异常数据  (key:原因,value:data项)
    private final Map> errorMap = new HashMap<>();

    
    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) throws ExcelValidException {
        //保存数据,确保最后的遗留数据也能保存到数据库或者进行其他操作
        this.execute();
        if (errorMap.size() > 0) {
            this.executeError();
        }
    }

    
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        exception.printStackTrace();
        if (exception instanceof ExcelDataConvertException) {
            Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
            Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
            String message = String.format("第%s行,第%s列数据格式有误,请核实", rowIndex, columnIndex);
            throw new RuntimeException(message);
        } else {
            super.onException(exception, context);
        }
    }

    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        //通用方法数据校验
        try {
            ExcelImportValid.valid(data);
        } catch (ExcelValidException e) {
            List ts = errorMap.get(e.getMessage());
            if (CollectionUtils.isEmpty(ts)) {
                ts = new LinkedList<>();
            }
            ts.add(data);
            errorMap.put(e.getMessage(), ts);
            return;
        }
        //将解析完的数据加入到list中
        successList.add(data);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (successList.size() >= 600) {
            try {
                this.execute();
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 存储完成清理 list
            successList.clear();
        }

    }

    //执行数据保存的操作
    public void execute() throws Exception {
        try {
            Object beanService = SpringUtil.getBean(serviceName + "Impl");
            Class beanClass = beanService.getClass();
            Method method = beanClass.getMethod(accessMethodName, List.class, String.class, Map.class);
            method.invoke(beanService, successList, version, paramMap);
        } catch (NoSuchMethodException | IllegalAccessException e) {
            log.warn("import error:", e);
            throw new RuntimeException("导入执行保存失败");
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            throw new ExcelValidException("有异常数据");
        }
    }

    //执行错误数据处理的操作
    public void executeError() {
        try {
            Object beanService = SpringUtil.getBean(serviceName + "Impl");
            Class beanClass = beanService.getClass();
            Method method = beanClass.getMethod(errorMethodName, Map.class, String.class);
            method.invoke(beanService, errorMap, version);
        } catch (NoSuchMethodException | IllegalAccessException e) {
            log.warn("execute error:", e);
            throw new RuntimeException("处理错误数据失败");
        } catch (InvocationTargetException e) {
            throw new ExcelValidException("有异常数据");
        }
    }
}

5、数据处理接口,使用范型

public interface EasyExcelImportService {
    
    void saveErrorForExcel(Map> errorMap,String version) throws ExcelValidException;

    
    void savaSuccessForExcel(List entityList,String version, Map param);
}

6、异常数据实体类(把校验失败的数据存入数据库,方便做导出,根据自己的具体业务自定义字段)

public class ImportErrorLog {
    @TableId(type = IdType.ASSIGN_UUID)
    private String id;

    
    private String service;

    
    private String data;

    
    private String version;

    
    @TableField(fill = FieldFill.INSERT)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
}

7、日期类型需要自定义转换器

public class LocalDateConverter implements Converter {

	@Override
	public Class supportJavaTypeKey() {
		return LocalDate.class;
	}

	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return CellDataTypeEnum.STRING;
	}

	@Override
	public LocalDate convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
		if (cellData.getType().equals(CellDataTypeEnum.NUMBER)) {
			LocalDate localDate = LocalDate.of(1900, 1, 1);
			//excel 有些奇怪的bug, 导致日期数差2
			localDate = localDate.plusDays(cellData.getNumberValue().longValue() - 2);
			return localDate;
		}else {
			return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
		}
	}

	@Override
	public WriteCellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
		return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
	}

}

8、业务处理类实现EasyExcelImportService接口即可

public class DemoServiceImpl implements EasyExcelImportService {
	
	@Override
    public void saveErrorForExcel(Map> errorMap, String version) throws ExcelValidException {
        LinkedList errorList = new LinkedList<>();
		
        ImportErrorLog errorLog = importErrorLogMapper.selectOne(new QueryWrapper()
                .eq("version", version)
                .last("limit 1")
        );
        // 因为注解只能校验数据的合法性,并不能校验是否符合业务逻辑,所以这里加了版本号,同一批次的错误信息保存一条记录
        if (errorLog == null){
            errorLog = ImportErrorLog.builder()
                    .service("service")
                    .version(version)
                    .data(JSON.toJSONString(errorList))
                    .build();
            // 入库
            importErrorLogMapper.insert(errorLog);
        }else {
            String data = errorLog.getData();
            List importDemoList = JSONObject.parseArray(data, Demo.class);
            errorList.addAll(importDemoList);
            errorLog.setData(JSON.toJSONString(errorList));
            // 入库
            importErrorLogMapper.updateById(errorLog);
        }
        // 为了提醒前端有异常数据
        throw new ExcelValidException("have error data");
    }
	// 正常信息处理
    @Override
    public void savaSuccessForExcel(List entityList, String version, Map param) {
        if (CollectionUtils.isEmpty(entityList)) {
            return;
        }
        // 业务逻辑校验
        Map> errorMap = new HashMap<>();
        Iterator iterator = entityList.iterator();
        while (iterator.hasNext()){
            Demo next = iterator.next();
            if (projectTempByCodeMap.get(next.getProjectCode()) == null){
                iterator.remove();
                List importDemoList = errorMap.get("项目编码错误");
                if (CollectionUtils.isEmpty(importDemoList)){
                    importDemoList = new LinkedList<>();
                }
                importPmDailyList.add(next);
                errorMap.put("项目编码错误",importPmDailyList);
            }
        }

        if (CollectionUtils.isEmpty(entityList)){
            // 存储错误信息
            if (errorMap.size()>0){
                saveErrorForExcel(errorMap, version);
            }
            return;
        }
        // 业务逻辑处理。。。。。。
}

9、如果想导出某一批次的异常数据,直接查询数据库,做导出即可

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/967414.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号