导入excel，对excel数据验证

//数据库中测评idList<Object> listIdFromDB=exchangeCourseDetailsServiceImpl.getTestId();//数据库中已存在的账号List<Object> listAccountFromDB=exchangeCourseDetailsServiceImpl.getTestAccount();MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;MultipartFile file = multipartRequest.getFile("upoadFileName");// 获得文件名：String realFileName = file.getOriginalFilename();System.out.println("获得文件名：" + realFileName);// 获取路径String ctxPath = request.getContextPath();ctxPath = request.getSession().getServletContext().getRealPath("/")+ "uploads\\exchangeCourse\\";System.out.println("路径：" + ctxPath);// 创建文件File dirPath = new File(ctxPath);if (!dirPath.exists()) {dirPath.mkdir();}// 构建随机文件名SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");String dateFileName = format1.format(new Date());// 随机文件名String newFileName = ctxPath + dateFileName + realFileName;File uploadFile = new File(newFileName);FileCopyUtils.copy(file.getBytes(), uploadFile);jxl.Workbook rwb = Workbook.getWorkbook(file.getInputStream());Sheet rs = rwb.getSheet(0);Cell[] cell = rs.getRow(0);int length = cell.length;//列名与列标映射Map<String, Integer> keyMap = null;if (length > 0) {keyMap = new HashMap<String, Integer>();for (int i = 0; i < length; i++) {String columnName = cell[i].getContents().trim();if (columnName != null) {keyMap.put(columnName, i);}}}//验证有效列数int idIndex=-1;int accountIndex=-1;int passwordIndex=-1;if(cell.length>3){request.setAttribute("errorMsg","上传表格的列数不符合要求，请参照模版!");return list(modelMap, request, sessionUserData);}try{idIndex=keyMap.get("测评类ID");accountIndex=keyMap.get("账号");passwordIndex=keyMap.get("密码");}catch(Exception e){e.printStackTrace();if(idIndex==-1){request.setAttribute("errorMsg","缺少测评类ID这一列");return list(modelMap, request, sessionUserData);}if(accountIndex==-1){request.setAttribute("errorMsg","缺少账户这一列");return list(modelMap, request, sessionUserData);}if(passwordIndex==-1){request.setAttribute("errorMsg","缺少密码这一列");return list(modelMap, request, sessionUserData);}return list(modelMap, request, sessionUserData);}Cell[] idCells = rs.getColumn(idIndex);//测评类id列Cell[] accountCells = rs.getColumn(accountIndex);//账号列Cell[] accountCellsTemp=accountCells; //账号列副本Cell[] passwordCells = rs.getColumn(passwordIndex);//密码列//逐行验证数据有效性for(int i=1;i<rs.getRows();i++){//********检验测评类id列数据*****************//if(idCells.length<rs.getRows()){request.setAttribute("errorMsg","第"+(idCells.length+1)+"行，测评类id不能为空!");return list(modelMap, request, sessionUserData);}if(idCells[i].getContents().trim()==""){request.setAttribute("errorMsg","第"+(i+1)+"行，测评类id不能为空!");return list(modelMap, request, sessionUserData);}boolean isHaveId=false;//标记测评id是否在数据库有记录for(Object obj:listIdFromDB){if(idCells[i].getContents().trim().equals(obj.toString())){isHaveId=true;}}if(isHaveId==false){request.setAttribute("errorMsg","第"+(i+1)+"行，测评类id在数据库中无记录，请核对正确性!");return list(modelMap, request, sessionUserData);}//********检验账号列数据*****************//if(accountCells.length<rs.getRows()){request.setAttribute("errorMsg","第"+(accountCells.length+1)+"行，账号不能为空!");return list(modelMap, request, sessionUserData);}if(accountCells[i].getContents().trim()==""){request.setAttribute("errorMsg","第"+(i+1)+"行，账号不能为空!");return list(modelMap, request, sessionUserData);}//验证表格本身账号是否有重复for(int k=1;k<accountCellsTemp.length;k++){//排除自身if (k != i) {if (accountCells[i].getContents().trim().equals(accountCellsTemp[k].getContents().trim())) {request.setAttribute("errorMsg", "第" + (i + 1) + "行与第"+ (k + 1) + "行的账号相同，请确保账号唯一性!");return list(modelMap, request, sessionUserData);}}}boolean isHaveAccount=false;//标记账号是否在数据库有记录//验证账号是否在数据库中已存在for(Object obj:listAccountFromDB){if(accountCells[i].getContents().trim().equals(obj.toString())){isHaveAccount=true;}}if(isHaveAccount==true){request.setAttribute("errorMsg","第"+(i+1)+"行，账号在数据库中已存在!");return list(modelMap, request, sessionUserData);}//********检验密码列数据*****************//if(passwordCells.length<rs.getRows()){request.setAttribute("errorMsg","第"+(passwordCells.length+1)+"行，密码不能为空!");return list(modelMap, request, sessionUserData);}if(passwordCells[i].getContents().trim()==""){request.setAttribute("errorMsg","第"+(i+1)+"行，密码不能为空!");return list(modelMap, request, sessionUserData);}}//通过验证后，把账号密码插入数据库try{exchangeCourseDetailsServiceImpl.saveDataFromImport(idCells, accountCells, passwordCells, sessionUserData.getUid());}catch(Exception e){e.printStackTrace();request.setAttribute("errorMsg","保存上传数据出错……");return list(modelMap, request, sessionUserData);}request.setAttribute("errorMsg","上传账号密码成功");return list(modelMap, request, sessionUserData);

?