处理导入的excel,并且把异常数据原因写在excel最后一列，并导出新的excel

男娘i 2022-12-26 10:12 141阅读 0赞

## **1、Controller层逻辑处理** ##

@RequestMapping("/batchInsertByExcel")
    public ResultData batchInsertByExcel(MultipartFile file, HttpServletResponse response){
        ResultData resultData = new ResultData();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(file.getInputStream());
            classTeacherRelationBusiness.batchInsertByExcel(workbook);
    
            resultData.setSuccessStatus();
        } catch (IOException e) {
            HelpUtils.log_error(getClass(), "读取excel文件IO异常", e);
            resultData.setErrorStatus(1, "读取excel文件异常");
        } catch (InvalidArgumentException | BusinessException e) {
            HelpUtils.log_error(getClass(), "解析excel文件业务异常", e);
            resultData.setErrorStatus(1, e.getMessage());
        }catch (ExcelParseErrorException e){
            try {
                String fileName = UUID.randomUUID().toString().replace("-","");
                setResponseHeader(response, fileName.concat(".").concat(StringUtils.substringAfter(file.getOriginalFilename(), ".")));
                OutputStream os = response.getOutputStream();
                workbook.write(os);
                os.flush();
                os.close();
            } catch (Exception e1) {
                HelpUtils.log_error(getClass(), "解析excel文件系统异常", e);
                resultData.setErrorStatus(1, "【批量导入班级老师失败】");
            }
        }catch (Exception e){
            HelpUtils.log_error(getClass(), "解析excel文件系统异常", e);
            resultData.setErrorStatus(1, "【批量导入班级老师失败】");
        }
    
        response.setHeader("X-Frame-Options","SAMEORIGIN");
        return resultData;
    }

## **2、**ClassTeacherRelationBusiness层逻辑处理 ##

public void batchInsertByExcel(Workbook workbook) {
            List<ClassTeacherRelation> list = parseClassTeacherSheet(workbook);
            if(list.isEmpty()){
                HelpUtils.log_error(getClass(), "批量导入班级老师失败，没有数据");
                throw new BusinessException("【批量导入班级老师失败】没有数据");
            }
            //批量插入
            classTeacherRelationService.batchInsert(list);
        }

## **3、对导入excel数据进行逻辑处理** ##

private List<ClassTeacherRelation> parseClassTeacherSheet(Workbook workbook){
            List<ClassTeacherRelation> caseList = new ArrayList<>();
            List<Long> canOperateSchoolIds = getCanOperateSchoolIds();
            if (CollectionUtils.isEmpty(canOperateSchoolIds)){
                throw new BusinessException("【批量导入班级老师失败】，您没有对学校的操作权限");
            }
            //防重处理
            Map<String, Object> map = new HashMap<>();
            //学校缓存，一般一次只导入一个学校的数据
            Map<String, SchoolInfo> schoolInfoMap = new HashMap<>();
    
            boolean hasError = false;
    
            Sheet sheet = workbook.getSheetAt(0);
            int cellNum = ExcelUtils.removeErrorCell(sheet);
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                try {
                    Row row = sheet.getRow(rowIndex);
                    if(ExcelUtils.isRowEmpty(row)){
                        continue;
                    }
                    //接收字段
                    ClassTeacherRelation classTeacher = new ClassTeacherRelation();
                    ClassInfo classInfo = new ClassInfo();
                    StaffInfo staffInfo = new StaffInfo();
    
                    readRow(row, classTeacher, classInfo, staffInfo);
                    //校验必填项
                    if (StringUtils.isEmpty(classInfo.getSchoolName())
                            || classInfo.getClassYear() == null
                            || StringUtils.isEmpty(classInfo.getGradeName())
                            || classInfo.getClassNo() == null
                            || classInfo.getClassYear() == null
                            || StringUtils.isEmpty(staffInfo.getName())
                            || StringUtils.isEmpty(staffInfo.getMobile())) {
                        ExcelUtils.writeInTemplate("有必填项为空", rowIndex, cellNum, sheet);
                        throw new InvalidArgumentException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，有必填项为空");
                    }
                    if(!RegexUtil.isMobile(staffInfo.getMobile())){
                        ExcelUtils.writeInTemplate("手机号格式错误", rowIndex, cellNum, sheet);
                        throw new InvalidArgumentException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，老师手机号格式错误");
                    }
                    if(classTeacher.getIsHeadMaster() != FscConstant.YES
                            && StringUtils.isEmpty(classTeacher.getSubject())){
                        ExcelUtils.writeInTemplate("非班主任任课科目必填", rowIndex, cellNum, sheet);
                        throw new InvalidArgumentException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，非班主任任课科目必填");
                    }
                    //校验学校
                    SchoolInfo schoolInfo = schoolInfoMap.get(classInfo.getSchoolName());
                    if(schoolInfo == null){
                        schoolInfo = schoolInfoService.selectSchoolId(classInfo.getSchoolName());
                        schoolInfoMap.put(classInfo.getSchoolName(), schoolInfo);
                    }
                    if (schoolInfo == null) {
                        ExcelUtils.writeInTemplate("该学校不存在", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，该学校不存在");
                    }
                    if (!canOperateSchoolIds.contains(schoolInfo.getId())) {
                        ExcelUtils.writeInTemplate("您没有该学校的操作权限", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，您没有该学校的操作权限");
                    }
                    //校验年级
                    GradeInfo gradeInfo = gradeInfoService.queryByName(classInfo.getGradeName());
                    if (gradeInfo == null) {
                        ExcelUtils.writeInTemplate("该年级不存在", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，该年级不存在");
                    }
                    //校验班级
                    ClassInfo classParam = new ClassInfo();
                    classParam.setSchoolId(schoolInfo.getId());
                    classParam.setClassYear(classInfo.getClassYear());
                    classParam.setClassNo(classInfo.getClassNo());
                    classParam.setGradeId(gradeInfo.getId());
                    classParam.setStatus(StatusEnum.ENABLE.code);
                    List<ClassInfo> classList = classService.queryClassList(classParam);
                    if(CollectionUtils.isEmpty(classList) || classList.size() > 1){
                        ExcelUtils.writeInTemplate("该班级不存在或存在一个以上相同班级", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，该班级不存在或存在一个以上相同班级");
                    }
                    ClassInfo existClass = classList.get(0);
                    classTeacher.setClassId(existClass.getId());
                    //校验老师
                    StaffInfo staffParam = new StaffInfo();
                    staffParam.setStatus(StatusEnum.ENABLE.code);
                    staffParam.setSchoolId(schoolInfo.getId());
                    staffParam.setMobile(staffInfo.getMobile());;
                    staffParam.setName(staffInfo.getName());
                    List<StaffInfo> userList = staffInfoService.queryBySelective(staffParam);
                    if(CollectionUtils.isEmpty(userList)){
                        ExcelUtils.writeInTemplate("该老师不存在，请检查学校、老师基本信息是否正确", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，该老师不存在，请检查学校、老师基本信息是否正确");
                    }
                    classTeacher.setTeacherId(userList.get(0).getId());
    
                    if(classTeacher.getIsHeadMaster() == FscConstant.YES
                            && existClass.getTeacherId() != null
                            && existClass.getTeacherId().compareTo(classTeacher.getTeacherId()) != 0){
                        ExcelUtils.writeInTemplate("该班级已经有班主任，请先移出", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，该班级已经有班主任，请先移出");
                    }
                    //校验excel数据是否重复
                    String key = "" + classTeacher.getClassId() + classTeacher.getTeacherId();
                    if(map.get(key) != null){
                        //excel存在相同身份证数据
                        ExcelUtils.writeInTemplate("excel数据与第【"+map.get(key)+"】行数据重复", rowIndex, cellNum, sheet);
                        throw new BusinessException("【批量导入班级老师失败】第【" + (rowIndex + 1) + "】行，excel数据与第【"+map.get(key)+"】行老师与班级重复");
                    }
                    map.put(key, rowIndex + 1);
    
                    caseList.add(classTeacher);
                } catch (InvalidArgumentException | BusinessException e) {
                    hasError = true;
                    HelpUtils.log_error(getClass(), "{}", e.getMessage(), e);
                }catch (Exception e){
                    hasError = true;
                    HelpUtils.log_error(getClass(), "【批量导入班级老师失败】第【{}】行", (rowIndex + 1), e);
                }
            }
            if(hasError){
                ExcelUtils.writeInTemplate("失败原因", 0, cellNum, sheet);
                throw new ExcelParseErrorException("excel解析异常");
            }
            return caseList;
        }

## **4、前端逻辑处理，上传excel** ##

<input type="file" name="file" id="batch_upload_class_teacher_file" onchange="batchUploadClassTeacher(this)" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="display: none">
    
    
    function batchUploadClassTeacher(ele){
        batchUploadExcelFile(ele, 'sec/class/teacher/batchInsertByExcel');
    }
    
    function batchUploadExcelFile(ele,url){
        //excel格式验证
        if (!ele || !$(ele).val()){
            return;
        }
        var patn = /\.xls$|\.xlsx$/i;
        if (!patn.test($(ele).val())) {
            alert('提示：仅支持excel文件！');
            $(ele).val('');
            return;
        }
        if(url==undefined || url == ''){
            alert('提示：上传路径错误！');
            return;
        }
        showLoading();
        var elementIds = ele.name; //flag为name属性名
        $.ajaxFileUpload({
            url: webUrlPrefix + url,//上传的url，根据自己设置
            type: 'post',
            secureuri: false, //一般设置为false
            fileElementId: ele.id, // 上传文件的id、name属性名
            dataType: 'json', //返回值类型，一般设置为json、application/json
            elementIds: elementIds, //传递参数到服务器
            success: function (data, status) {
                console.log(data);
                if(data.status.error == 0){
                    alert('批量导入数据成功');
                    getAllClassInfoByPage(1);
                }else{
                    alert(data.status.message);
                }
                hideLoading();
            },
            error: function (data, status, e) {
                alert("文件上传失败");
                hideLoading();
            }
        });
        hideLoading();
    }