阅读 125

十一. excel校验数据工具类

一. 概述

在开发中, 我们会做过很多的excel导入操作, 数据的校验无非就是: 非空判断 / 数据类型判断 / 数据有效性判断 , 结合我以往的校验经验总结了几个工具类, 省掉以往每次重复的校验工作

二. 前期准备

  • 我们引入easyExcel工具, 用来读取excel的数据, 引入方法这里就不介绍了

  • 我们准备两个实体类, 一个是用来直接读取excel数据的模板类, 另一个是储存业务数据的实体类

  • 准备一个校验注解, 用来记录错误信息的识别及处理

2.1 校验注解

@Target({ElementType.FIELD})  //作用的位置@Retention(RetentionPolicy.RUNTIME) //作用域@Documentedpublic @interface ExcelParamCheck {

    /**
     * 是否必填,默认为否
     * @return
     */
    boolean ifRequired() default false;
    /**
     * 必填为空时报错提示
     * @return
     */
    String errorMsg() default "参数不能为空";

    /**
     * 字典值: 用来给对应字段做个值的转换, 从指定字典查找
     * @return
     */
    String dicCode() default "";

    /**
     * 字典校验报错提示
     * @return
     */
    String dicErrorMsg() default "字典编码不存在";

    /**
     * 填写格式错误提示
     * @return
     */
    String formatErrorMsg() default "填写格式错误";}

2.2 导入数据实体类

@Datapublic class ImportModel implements Serializable {
    @ExcelParamCheck(ifRequired = true,errorMsg = "用户名不能为空")
    @ExcelProperty(value = "用户名", index = 0)
    private String username;

    @ExcelParamCheck(formatErrorMsg = "年龄格式错误")
    @ExcelProperty(value = "年龄", index = 1)
    private String age;

    @ExcelParamCheck(formatErrorMsg = "生日格式错误")
    @ExcelProperty(value = "生日", index = 2)
    private String birthday;

    // 导进来的数据是名字, 根据字典转换为编码
    @ExcelParamCheck(dicCode = "STATUS",dicErrorMsg = "状态字典值不存在")
    @ExcelProperty(value = "状态", index = 3)
    private String status;

    @ExcelProperty(value = "错误提示", index = 4)
    private String errorMsg;}

说明

  • 模板的数据类型全部为String, 先把数据全部读取再校验数据类型

  • 约定错误提示字段名为: errorMsg

2.3 业务数据实体类

@Datapublic class FormalData implements Serializable {
    /**
     * 用户名
     */
    private String username;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 生日
     */
    private LocalDate birthday;

    /**
     * 状态
     */
    private String status;}

三. 工具示例

3.1 用easyExcel读取数据

    /**
     * 测试简单导入
     * @throws Exception
     */
    @Test
    public void simpleImport() throws Exception{
        // 输入流
        FileInputStream inputStream = new FileInputStream("D:/导入.xlsx");
        //读取数据
        List<ImportModel> datas = readExcelWithModelNew(inputStream, ImportModel.class);
        // 打印
        System.out.println(JSON.toJSONString(datas));
    }
    /**
     * 使用 模型 来读取Excel
     *
     * @param fileInputStream Excel的输入流
     * @param tClass         模型的类
     * @return 返回 模型 的列表(为object列表,需强转)
     */
    public static <T> List<T> readExcelWithModelNew(InputStream fileInputStream, Class<T> tClass) throws IOException {
        AnalysisEventListenerImpl<T> listener = new AnalysisEventListenerImpl<T>();
        ExcelReader excelReader = EasyExcel.read(fileInputStream, tClass, listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        excelReader.finish();
        return listener.getDatas();
    }

3.2 检查导入数据非空校验和字典

    /**
     * 检查导入数据非空校验和字典
     * <<约定: 错误信息字段名为: errorMsg>></约定:>
     * @param list
     * @param errorFlag
     * @return <T>
     * @throws IOException
     */
    public static <T> void checkParamNoNullAndDic(List<T> list, AtomicBoolean errorFlag) {
        if(CollectionUtils.isNotEmpty(list)){
            // 先获取所有的字典
            Map<String, Map<String,String>> dicMap = new HashMap<>();
            Class<?> aClass = list.get(0).getClass();
            Field[] fields = aClass.getDeclaredFields();
            // 有ExcelParamCheck注解的属性
            List<Field> fieldsList = new ArrayList<>();

            if(!ObjectUtils.isEmpty(fields)){
                BaseClient baseClient = SpringContextHolder.getBean(BaseClient.class.getName());
                Arrays.stream(fields).forEach(field -> {
                    // 参数检查注解
                    ExcelParamCheck excelParamCheck = field.getAnnotation(ExcelParamCheck.class);
                    if(null != excelParamCheck){
                        // 字典编码
                        String dicCode = excelParamCheck.dicCode();
                        if (!ObjectUtils.isEmpty(dicCode)) {
                            Map<String, String> dicNameCode = getDicNameCode(dicCode, baseClient);
                            dicMap.put(dicCode,dicNameCode);
                        }
                        fieldsList.add(field);
                    }
                });

                if (CollectionUtils.isNotEmpty(fieldsList)) {
                    list.forEach(obj -> {
                        StringBuffer errorMsg = new StringBuffer();
                        try {
                            for(Field field : fieldsList){
                                field.setAccessible(true);
                                Object value = field.get(obj);
                                ExcelParamCheck annotation = field.getAnnotation(ExcelParamCheck.class);
                                String dicCode = annotation.dicCode(); // 字典编码
                                boolean ifRequired = annotation.ifRequired(); // 是否必填
                                if(ObjectUtils.isEmpty(value)){
                                    if(ifRequired){
                                        if(ObjectUtils.isEmpty(value)){
                                            String localeMsg = LocaleHandler.getLocaleMsg(annotation.errorMsg());
                                            errorMsg.append(localeMsg+" ;");
                                            errorFlag.set(true);
                                        }
                                    }
                                }else {
                                    String valueStr = value.toString().trim();
                                    if(!ObjectUtils.isEmpty(dicCode)){
                                        Map<String, String> nameCodeMap = dicMap.get(dicCode);
                                        String code = nameCodeMap.get(valueStr);
                                        if(!ObjectUtils.isEmpty(code)){
                                            field.set(obj,code);
                                        }else {
                                            String localeMsg = LocaleHandler.getLocaleMsg(annotation.dicErrorMsg());
                                            errorMsg.append(localeMsg+" ;");
                                            errorFlag.set(true);
                                        }
                                    }
                                }
                            }
                            Field field = aClass.getDeclaredField("errorMsg");
                            field.setAccessible(true);
                            if(errorMsg.length() > 0){
                                field.set(obj,errorMsg.toString());
                            }else {
                                field.set(obj,null);
                            }
                        } catch (Exception e) {
                            log.error("检查导入数据非空和字典工具类报错"+e.getMessage());
                            log.error("检查导入数据非空和字典工具类报错"+e);
                            throw new BaseException("检查导入数据非空和字典工具类报错!");
                        }
                    });
                }
            }
        }
    }

3.3 检查字段名一样的字段, 检查数据类型并赋值

    /**
     * 检查字段名一样的字段, 检查数据类型并赋值
     * @param list
     * @param aClass
     * @param errorFlag
     * @param <T>
     * @param <V>
     * @return
     */
    public static <T,V> List<V> dataTransform(List<T> list,Class<V> aClass,AtomicBoolean errorFlag){
        List<V> vList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(list)) {
            // 业务类字段
            Field[] businessFields = aClass.getDeclaredFields();
            // 导入实体类字段
            Class importClass = list.get(0).getClass();
            Field[] importFields = importClass.getDeclaredFields();
            if(!ObjectUtils.isEmpty(businessFields) && !ObjectUtils.isEmpty(importFields)){
                // 业务字段
                Map<String, Field> businessFieldMap = Arrays.stream(businessFields).collect(Collectors.toMap(Field::getName, Function.identity()));
                // 导入数据字段
                Map<String, Field> importFieldMap = Arrays.stream(importFields).collect(Collectors.toMap(Field::getName, Function.identity()));

                for(T obj:list){
                    // 手机错误信息
                    StringBuffer errorMsg = new StringBuffer();
                    V businessInstance = null;
                    try {
                        businessInstance = aClass.newInstance();
                    } catch (Exception e) {
                        log.error("创建实体类报错"+e.getMessage());
                        log.error("创建实体类报错"+e);
                    }
                    if (null != businessInstance) {
                        V finalBusinessInstance = businessInstance;
                        businessFieldMap.forEach((fieldName, field) -> {
                            field.setAccessible(true);
                            // 导入的字段
                            Field importField = importFieldMap.get(fieldName);
                            if(null != importField){
                                String typeName = field.getType().getSimpleName();
                                Object value = null;
                                try {
                                    // 获取导入的值
                                    importField.setAccessible(true);
                                    value = importField.get(obj);
                                    if (!ObjectUtils.isEmpty(value)) {
                                        String valueStr = value.toString();
                                        switch (typeName){
                                            case "Long":
                                                long aLong = Long.parseLong(valueStr);
                                                field.set(finalBusinessInstance,aLong);
                                                break;
                                            case "Integer":
                                                int i1 = Integer.parseInt(valueStr);
                                                field.set(finalBusinessInstance,i1);
                                                break;
                                            case "BigDecimal":
                                                BigDecimal decimal = new BigDecimal(valueStr);
                                                field.set(finalBusinessInstance,decimal);
                                                break;
                                            case "Date":
                                                Date date = DateUtil.parseDate(valueStr);
                                                field.set(finalBusinessInstance,date);
                                                break;
                                            case "LocalDate":
                                                Date date1 = DateUtil.parseDate(valueStr);
                                                LocalDate localDate = DateUtil.dateToLocalDate(date1);
                                                field.set(finalBusinessInstance,localDate);
                                                break;
                                            case "LocalDateTime":
                                                Date date2 = DateUtil.parseDate(valueStr);
                                                LocalDateTime localDateTime = DateUtil.dateToLocalDateTime(date2);
                                                field.set(finalBusinessInstance,localDateTime);
                                                break;
                                            case "Double":
                                                double aDouble = Double.parseDouble(valueStr);
                                                field.set(finalBusinessInstance,aDouble);
                                                break;
                                            default:
                                                field.set(finalBusinessInstance,valueStr);
                                        }
                                    }
                                } catch (Exception e) {
                                    ExcelParamCheck annotation = importField.getAnnotation(ExcelParamCheck.class);
                                    String formatErrorMsg = "字段格式错误";
                                    if(null != annotation){
                                        formatErrorMsg = annotation.formatErrorMsg();//                                        String localeMsg = LocaleHandler.getLocaleMsg(formatErrorMsg);
                                    }
                                    errorMsg.append(formatErrorMsg+" ;");
                                    errorFlag.set(true);
                                }
                            }
                        });
                        vList.add((V) finalBusinessInstance);
                        try {
                            Field errorMsgField = importClass.getDeclaredField("errorMsg");
                            errorMsgField.setAccessible(true);
                            if(errorMsg.length() > 0){
                                errorMsgField.set(obj,errorMsg.toString());
                            }else {
                                errorMsgField.set(obj,null);
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            }

        }
        return vList;
    }



作者:任未然
链接:https://www.jianshu.com/p/0e329f917c13


文章分类
后端
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐