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