导入excel,对excel数据验证

//数据库中测评id
		List<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);

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值