Java把excel表格数据导入数据库（二）

一，我这边有一些几个关联几个表，所以里面要做一些判断，基本上就是根据execl的列来拿值，第一列从0开始

/**
			     * 导入学生表<br/>
			     *
			     * @param srcFile 上传excel文件
			     *
			     * @return 是否成功
			     */
			    @RequestMapping(value = "/importClassCourse", method = RequestMethod.POST)
			    @ResponseBody
			    public ApiResponseResult importClassCourse(@RequestParam("file") MultipartFile srcFile, Long hotelId )
			            throws IOException {
			        return ApiResponseResult.success(importClassCourse2(srcFile, hotelId ));
			    }
			    
			    public String importClassCourse2(MultipartFile srcFile, Long hotelId ) throws IOException {
			        // 导入结果说明
			        StringBuilder message = new StringBuilder("");
			        if (srcFile.isEmpty()) return "请先选择xls格式的导入文件";
			        File dest;
			        File tmp = new File(System.getProperty("java.io.tmpdir"));

			        try {
			            dest = new File(tmp, srcFile.getOriginalFilename());
			            OutputStream outputStream = new FileOutputStream(dest);
			            IOUtils.copy(srcFile.getInputStream(), outputStream);
			            IOUtils.closeQuietly(outputStream); //关流
			        } catch (IOException e) {
			            return "导入失败，请重试";
			        }

			        Hotel h = hotelService.getHotel(hotelId);
			        
			        int count = 0; //计数器：不合格的课程数
			      
			        // 解析excel文件，并写入数据库
			        InputStream inputStream = new FileInputStream(dest);
			        Sheet sheet = PoiUtilReader.getHSSFSheet(inputStream);
			        
			        // 循环行
			        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			            Row row = sheet.getRow(i);            
			            String customerName = PoiUtilReader.getPoiCell(row, 0).trim();
			            String idnumber= PoiUtilReader.getPoiCell(row, 1).trim();
			            String cardid= PoiUtilReader.getPoiCell(row, 2).trim();
			            String cardno= PoiUtilReader.getPoiCell(row, 3).trim();
			            String grade= PoiUtilReader.getPoiCell(row, 4).trim();
			            String major= PoiUtilReader.getPoiCell(row, 5).trim();
			            String classname= PoiUtilReader.getPoiCell(row, 6).trim();
			            String sex= PoiUtilReader.getPoiCell(row, 7).trim();
			            String mobilePhone= PoiUtilReader.getPoiCell(row, 8).trim();
			            if (StringUtils.isBlank(customerName) && StringUtils.isBlank(idnumber)&& StringUtils.isBlank(cardid)
			            && StringUtils.isBlank(cardno)&& StringUtils.isBlank(grade)&& StringUtils.isBlank(major)&& StringUtils.isBlank(classname)
			            && StringUtils.isBlank(sex)&& StringUtils.isBlank(mobilePhone)) {
			              message.append("第"+i).append("行有空值<br/>");
			      		  count++;
			              continue;
			      	     }
			            Hotel hotel=hotelService.getHotel(hotelId);
			            TecherInfo techerInfo=techerInfoService.getHotelAndId(hotelId, idnumber);
			            if(techerInfo!=null){
			        		  message.append(idnumber + "学工号已经存在，不可重复").append("<br/>");
			        		  count++;
			                  continue;
			        	}
			            StudentInfo studentInfo=studentInfoService.getHotelAndId(hotelId, idnumber);
			        	if(studentInfo!=null){
			        		message.append(idnumber + "学工号已经存在，不可重复").append("<br/>");
			        		 count++;
			                continue;
			        	}     
			            
			            Long gradeid=null;
			            Long majorid=null;
			            Long classid=null;
			            SchoolGrade schoolGrade1=schoolGradeService.getHotelAndGrade(hotelId, grade);
			        	if(schoolGrade1==null) {
			        		SchoolGrade schoolGrade2=new SchoolGrade();
			        		schoolGrade2.setHotel(hotel);
			        		schoolGrade2.setGrade(grade);
			        		SchoolGrade schoolGrade3=schoolGradeService.addOrUpdate(schoolGrade2);
			        		gradeid=schoolGrade3.getId();
			        	}else {
			        		gradeid=schoolGrade1.getId();
			        	}
			        	Major major1=majorService.getHotelAndMajor(hotelId, gradeid, major);
			        	if(major1==null) {
			        		Major major2=new Major();
			        		SchoolGrade schoolGrade4=schoolGradeService.getSchoolGrade(gradeid);
			        		major2.setSchoolGrade(schoolGrade4);
			        		major2.setHotel(h);
			        		major2.setMajor(major);
			        		Major major3=majorService.addOrUpdate(major2);
			        		majorid=major3.getId();
			        	}else {
			        		majorid=major1.getId();
			        	}
			        	int classname1=Integer.valueOf(classname);
			        	SchoolClass schoolClass1=schoolClassService.getHotelAndSchoolClass(hotelId, gradeid, majorid, classname1);
			        	if(schoolClass1==null) {
			        		SchoolClass schoolClass2=new SchoolClass();
			        		SchoolGrade schoolGrade4=schoolGradeService.getSchoolGrade(gradeid);
			        		Major major4=majorService.getMajor(majorid);
			        		schoolClass2.setSchoolGrade(schoolGrade4);
			        		schoolClass2.setHotel(h);
			        		schoolClass2.setMajor(major4);
			        		schoolClass2.setClassname(classname1);
			        		SchoolClass schoolClass3=schoolClassService.addOrUpdate(schoolClass2);
			        		classid=schoolClass3.getId();
			        	}else {
			        		classid=schoolClass1.getId();
			        	}
			        	   if (count > 0) {
					            message.insert(0, count + "个导入失败，其它已成功导入：<br/>");
					            return message.toString();
					        }
			        	SchoolGrade schoolGrade4=schoolGradeService.getSchoolGrade(gradeid);
			        	Major major4=majorService.getMajor(majorid);
			        	SchoolClass schoolClass4=schoolClassService.getSchoolClass(classid);
			        	StudentInfo studentInfo2=new StudentInfo();
			        	studentInfo2.setCustomerName(customerName);
			        	studentInfo2.setIdNumber(idnumber);
			        	studentInfo2.setCardId(cardid);
			        	studentInfo2.setCardno(cardno);
			        	studentInfo2.setSchoolGrade(schoolGrade4);
			        	studentInfo2.setMajor(major4);
			        	studentInfo2.setSchoolClass(schoolClass4);
			        	studentInfo2.setSex(sex);
			        	studentInfo2.setHotel(h);
			        	studentInfo2.setMobilePhone(mobilePhone);
			        	studentInfoService.addOrUpdate(studentInfo2);
			        	Card card1=cardService.findByHotelIdAndCardno(hotelId, cardno);
			        	Card card2=cardService.findByHotelIdAndCardid(hotelId, cardid);
			        	if(card1==null&&card2==null) {
			        		Card card3=new Card();
			        		card3.setCardid(cardid);
			        		card3.setCardno(cardno);
			        		card3.setHotel(h);
			        		cardService.addorUpdate(card3);
			        	}
			        }
			     
			        return "学生信息导入成功";
			    }