excel导入校验

导入excel

 public void uploadExcel(MultipartFile file, String dataId) {

        // 校验后缀
        String filename = file.getOriginalFilename();
        if (filename != null) {
            String extension = filename.substring(filename.lastIndexOf(".") + 1);
            if (!(extension.equals("xls") || extension.equals("xlsx"))) {
                throw new EcoBootException("文件格式有误");
            }
        }

        List<Map<String, Object>> listDatas;
        List<DictModel> isNormalModels = projectUtils.getDictModelList(CHECK_ISNORMAL);
        //  读取excel数据
        ExcelReader reader;
        try {
            reader = ExcelUtil.getReader(file.getInputStream());
        } catch (IOException e) {
            throw new EcoBootException("文件异常");
        }

        try {
            listDatas = reader.readAll();
        }catch (Exception e){
            throw new EcoBootException("excel数据超出或缺失");
        }

        // 校验excel内容
        verifyExcel(listDatas, isNormalModels);
        // 入库
        insertToDb(listDatas, dataId, isNormalModels);
    }

/**
     * 校验excel内容
     *
     * @param listDatas excel数据
     */
    private void verifyExcel(List<Map<String, Object>> listDatas, List<DictModel> isNormalModels) {

        // 校验excel内容
        if (CollectionUtil.isEmpty(listDatas)) {
            throw new EcoBootException("excel 数据内容为空");
        }
        if (listDatas.size() != 90) {
            throw new EcoBootException("excel 数据超出或缺失");
        }
        // excel行数据校验
        checkRows(listDatas, isNormalModels);
    }

/**
     * excel行数据校验
     * @param listDatas excel数据
     * @param isNormalModels 是否正常 字典
     */
    private void checkRows(List<Map<String, Object>> listDatas, List<DictModel> isNormalModels) {

        StringBuilder excelErrorMsg = new StringBuilder();

        HashMap<String, List<Integer>> map = new HashMap<>();
        HashMap<String, List<Integer>> paramValueMap = new HashMap<>();

        // 遍历校验
        for (int i = 0; i < listDatas.size(); i++) {
            Map<String, Object> oneRow = listDatas.get(i);

            //校验检查项、参数名称、推荐值为空
            if (!checkNull(oneRow.get(CHECK_NAME))) {
                addRowsInfo(map, CHECK_NAME,i);
            }

            if (!checkNull(oneRow.get(PARAM_NAME))) {
                addRowsInfo(map, PARAM_NAME,i);
            }

            if (!checkNull(oneRow.get(RECOMMEND_VALUE))) {
                addRowsInfo(map, RECOMMEND_VALUE, i);
            }

            //校验参数值长度
            Object paramValue = oneRow.get(PARAM_VALUE);
            if (StringUtil.isNotEmpty(String.valueOf(paramValue)) &&
                    String.valueOf(paramValue).length() > 100) {
                addRowsInfo(paramValueMap, PARAM_VALUE,i);
            }

            //获取isNormal字典
            List<String> isNormalTitles =
                    isNormalModels.stream().map(DictModel::getTitle).collect(Collectors.toList());

            //校验是否正常字段
            Object isNormal = oneRow.get(IS_NORMAL);
            if (StringUtil.isNotEmpty(String.valueOf(isNormal)) &&
                    !(isNormalTitles.contains(String.valueOf(isNormal)))) {
                addRowsInfo(map, IS_NORMAL,i);
            }
        }

        if (CollectionUtil.isNotEmpty(map)) {
            //检查项、参数名称、推荐值字段 行校验
            getErrorRows(excelErrorMsg,map);

            //校验 是否正常 行校验
            StringBuilder isNormalRowsMsg = new StringBuilder();
            if(CollectionUtil.isNotEmpty(map.get(IS_NORMAL))){
                checkRows(excelErrorMsg, map, isNormalRowsMsg, IS_NORMAL);
            }
        }

        // 参数值 行校验
        if (CollectionUtil.isNotEmpty(paramValueMap)) {
            for (String key : paramValueMap.keySet()) {
                List<Integer> rows = paramValueMap.get(key);
                StringBuilder stringBuilder = new StringBuilder();
                checkParamValueRows(rows,stringBuilder);
                excelErrorMsg.append("第").append(stringBuilder.toString()).append("行 “").append(key).append("”字数不能超过100 \r\n");
            }
        }
        if (StringUtil.isNotEmpty(excelErrorMsg.toString())) {
            throw new EcoBootException(excelErrorMsg.toString());
        }
    }

 /**
     * 空校验
     * @param param 参数
     * @return false为空
     */
    private Boolean checkNull(Object param){
        if (param==null){
            return false;
        }
        return !StringUtil.isEmpty((String.valueOf(param)));
    }

  /**
     * excel 参数值 行数据校验
     * @param rows 行数
     * @param rowMsg 行错误信息
     */
    private void checkParamValueRows(List<Integer> rows, StringBuilder rowMsg){
        for (int i = 0; i < rows.size(); i++) {
            if (i > 0) {
                rowMsg.append("、").append(rows.get(i));
                continue;
            }
            rowMsg.append(" ").append(rows.get(i));
        }
    }

  /**
     * 存入数据库
     *
     * @param listDatas excel数据
     * @param dataId 业务id
     */
    private void insertToDb(List<Map<String, Object>> listDatas, String dataId, List<DictModel> dictModels) {

        List<ProjectReportDetail> projectReportDetails = this.list(new LambdaQueryWrapper<ProjectReportDetail>()
                .eq(ProjectReportDetail::getDataId, dataId)
                .in(ProjectReportDetail::getStatus, Arrays.asList(OAConstant.IS_OK, OAConstant.IS_DRAFT))
                .orderByAsc(ProjectReportDetail::getSort)
        );

        List<ProjectReportDetail> datas = toDbCheck(projectReportDetails, listDatas, dictModels);
        this.updateBatchById(datas);
    }

  /**
     * 入库校验
     * @param projectReportDetails 模板数据
     * @param listDatas  excel数据
     * @param dictModels  字典
     * @return 返回模板数据
     */
    private List<ProjectReportDetail> toDbCheck(List<ProjectReportDetail> projectReportDetails, List<Map<String, Object>> listDatas, List<DictModel> dictModels){

        StringBuilder toDbErrorMsg = new StringBuilder();

        HashMap<String, List<Integer>> map = new HashMap<>();

        ArrayList<ProjectReportDetail> datas = new ArrayList<>();

        if (CollectionUtil.isNotEmpty(projectReportDetails)) {
            //遍历模板进行校验
            for (ProjectReportDetail detail : projectReportDetails) {
                String checkName = detail.getCheckName();
                String paramName = detail.getParamName();
                String recommendValue = detail.getRecommendValue();
                Integer sort = detail.getSort();

                detail.setStatus(OAConstant.IS_OK);

                // 根据sort获取所有行数据
                Map<String, Object> sortMap = listDatas.get(sort);

                // 校验所有行的参数是否合法
                if (!String.valueOf(sortMap.get(CHECK_NAME)).equals(checkName)) {
                    addRowsInfo(map, CHECK_NAME, sort);
                }

                if (!String.valueOf(sortMap.get(PARAM_NAME)).equals(paramName)) {
                    addRowsInfo(map, PARAM_NAME, sort);
                }

                if (!String.valueOf(sortMap.get(RECOMMEND_VALUE)).equals(recommendValue)) {
                    addRowsInfo(map, RECOMMEND_VALUE, sort);
                }

                // 获取lists中当前行的 paramValue、isNormal
                String paramValue = String.valueOf(sortMap.get(PARAM_VALUE));
                String isNormal = String.valueOf(sortMap.get(IS_NORMAL));

                // 从字典中获title
                Optional<DictModel> any =  dictModels.stream().filter(s -> isNormal.equals(s.getTitle())).findAny();

                String isNormalValue = "";
                // 取字典里的值
                if (any.isPresent()) {
                    isNormalValue = any.get().getValue();
                }
                // 赋值
                detail.setIsNormal(isNormalValue);
                detail.setParamValue(paramValue);
                datas.add(detail);
            }
        }
        if (CollectionUtil.isNotEmpty(map)) {
            //检查项、参数名称、推荐值字段 行校验
            getErrorRows(toDbErrorMsg,map);
        }

        if (StringUtil.isNotEmpty(toDbErrorMsg.toString())) {
            throw new EcoBootException(toDbErrorMsg.toString());
        }
        return datas;
    }

 /**
     * 将错误行信息放入map
     * @param map 封装的错误信息
     * @param key 标题 (key)
     * @param row 行信息 (value)
     */
    private void addRowsInfo(HashMap<String, List<Integer>> map, String key, Integer row) {
        // 字段存在
        if (map.containsKey(key)) {
            // 获取所有的行信息
            List<Integer> list = map.get(key);
            list.add(row + 2);

            map.put(key, list);
        } else {
            // 字段不存在
            ArrayList<Integer> list = new ArrayList<>();
            list.add(row + 2);

            map.put(key, list);
        }
    }

  /**
     * 获取错误行信息
     * @param errorMsg 总错误信息
     * @param map 封装的错误信息
     */
    private void getErrorRows(StringBuilder errorMsg, HashMap<String, List<Integer>> map){
        StringBuilder checkNameRowsMsg = new StringBuilder();
        StringBuilder paramNameRowsMsg = new StringBuilder();
        StringBuilder recommendRowsMsg = new StringBuilder();

        // 获取 检查项、参数名称、推荐值错误行号信息
        if (CollectionUtil.isNotEmpty(map.get(CHECK_NAME))){
            checkRows(errorMsg, map, checkNameRowsMsg,CHECK_NAME);
        }
        if (CollectionUtil.isNotEmpty(map.get(PARAM_NAME))){
            checkRows(errorMsg, map, paramNameRowsMsg, PARAM_NAME);
        }
        if(CollectionUtil.isNotEmpty(map.get(RECOMMEND_VALUE))){
            checkRows(errorMsg, map, recommendRowsMsg, RECOMMEND_VALUE);
        }
    }

 /**
     * 添加行错误信息
     * @param errorMsg 总错误信息
     * @param map 封装的错误信息
     * @param rowsMsg 行错误信息
     * @param param 参数
     */
    private void checkRows(StringBuilder errorMsg, HashMap<String, List<Integer>> map, StringBuilder rowsMsg , String param) {
        List<Integer> rows = map.get(param);
        for (int j = 0; j < rows.size(); j++) {
            if (j > 0) {
                rowsMsg.append("、").append(rows.get(j));
                continue;
            }
            rowsMsg.append(" ").append(rows.get(j));
        }
        errorMsg.append("第").append(rowsMsg.toString()).append("行 “").append(param).append("”数据异常 \r\n");
    }