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

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();
}