Java码农日常搬砖整理 一（实现EXCEL新增）

需求：

给用户下载EXCEL模板 ，然后用户按模板填写进行批量新增。

一，实现下载模板（文件下载）

分析：其实就是前台发送请求后，然后下载服务器内固定地址的文件；

请求方式：

function excelDownload(){
	window.open("${basePath}terwarning/terminal/downloadExcle.json")
}
复制代码

web层的代码：

/**
	 * 下载EXCEL模板
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("downloadExcle.json")
	public ResponseEntity<byte[]> downloadExcle(HttpServletRequest request)
			throws Exception {
		ServletContext servletContext = request.getServletContext();
		String fileName = "设备新增模板.xls";
		String realPath = servletContext.getRealPath("/attached/" + fileName);// 得到文件所在位置
		InputStream in = new FileInputStream(new File(realPath));// 将该文件加入到输入流之中
		byte[] body = null;
		body = new byte[in.available()];// 返回下一次对此输入流调用的方法可以不受阻塞地从此输入流读取（或跳过）的估计剩余字节数
		in.read(body);// 读入到输入流里面
		fileName = new String(fileName.getBytes("gbk"), "iso8859-1");// 防止中文乱码
		HttpHeaders headers = new HttpHeaders();// 设置响应头
		headers.add("Content-Disposition", "attachment;filename=" + fileName);
		HttpStatus statusCode = HttpStatus.OK;// 设置响应吗
		ResponseEntity<byte[]> response = new ResponseEntity<byte[]>(body,
				headers, statusCode);
		return response;
	}
复制代码

二.用户上传已经填好的模板，上传后批量新增

分析：

1.需要接收EXCEL文件 ，所以需要文件上传

2.需要解析EXCEL文件，  这里要用到apache的poi

3.解析到的数据提交到数据库，完成新增

注意：

1.为了方便用户，这里需要进行批量文件上传。

2.EXCEL里有几行数据就要新增几条数据到数据库，所以这里是批量新增提交，  后台要用for  循环添加。

3.  数据校验 ，每个字段必须都要做校验， 成功后方可新增提交；

4. 如果有一条数据校验失败，所有的数据都不允许新增， 要么全部都新增成功，要么全部新增失败， 保证用户一次操作的一致性，完整性。

模板示例：

前台文件上传form表单代码:

<form id="myFormId"
		action="${basePath}terwarning/terminal/excelAdd.save" method="post"
		target="frmright">
		<div class="box1" whiteBg="true">
			<table id="fileList" class="tableStyle" formMode=transparent>
				<tr>
					<td width="40%">选择模板：</td>
					<td><input type="file" name="files" multiple="multiple"
						onchange="filechange(files)" /></td>
				</tr>
				<tr>
					<td>所属组织：</td>
					<td><div class="selectTree validate[required]"
							name="orgData.keyId"
							url="${basePath}common/exclude/queryOrgListAndSub.json"
							selectedValue="${entity.orgData.keyId }"></div> <span
						class="star">*</span></td>
				</tr>
				<tr>
					<td colspan="2"><input id="submitBut" type="button" value="提交" />
						<input type="button" value="取消" onclick="quiDialog.close()" /></td>
				</tr>
			</table>
		</div>
	</form>
复制代码

JAVA后端逻辑：

/**
	 * EXCEL新增
	 * @RequestParam("file") MultipartFile file, HttpServletRequest
	 * request)
	 * 
	 * @return
	 * @throws @throws
	 * @throws Exception
	 */
	@RequestMapping("excelAdd.save")
	public ResponseEntity<String> excelAdd(
			@RequestParam("orgData.keyId") Integer orgId,
			@RequestParam("files") MultipartFile[] files,
			HttpServletRequest request) throws Exception {
		//此集合用于存放EXCEL每一条的值
		List<TerminalData> paramList = new ArrayList<TerminalData>();
		MessageModel messageModel = new MessageModel();//视图容器  ，非核心逻辑  
		LoginUserData loginUserData = CommonUtil.getCurrentUserData(request);
		UserData userData = loginUserData.getUserData();//获得当前登陆人对象

		// 判断file数组不能为空并且长度大于0
		if (files != null && files.length > 0) {
			// 判断文件是否是.xls的文件
			for (int i = 0; i < files.length; i++) {
				String string = files[i].getOriginalFilename();
				String xlString = string.substring(string.length() - 3, string.length());
				if (xlString.equals("xls") || xlString.equals("XLS")) {
				} else {
					messageModel.setOperFlag(ConstantData.OPER_ERROR);
					messageModel.setMessage("上传时请使用有效的EXCEL模板文件");
					return PrintUtil.printResponseEntity(messageModel);
				}
			}
			// 循环解析EXCEL ，并完成数据校验，最后把新增对象add到List里
			for (int i = 0; i < files.length; i++) {

				// 上传文件操作
				MultipartFile file = files[i];
				String excelPath = request.getSession().getServletContext().getRealPath("/") + "attached/add.xls";
				file.transferTo(new File(excelPath));//

				// 解析EXCEL    sheet.getRow()获取行
				InputStream inputStream = new FileInputStream(excelPath);
				Workbook workbook = WorkbookFactory.create(inputStream);
				Sheet sheet = workbook.getSheetAt(0);
				Row row = sheet.getRow(0);

				int rowNum = sheet.getPhysicalNumberOfRows();//获得总行数
				int colNum = row.getPhysicalNumberOfCells();//获得总列数
				//对每一行的数据进行校验 ， 放进一个List
				for (int j = 1; j < rowNum; j++) {
					row = sheet.getRow(j);
					if (row != null) {
						TerminalData terminalData = new TerminalData();
						OrgData orgData = new OrgData();
						orgData.setKeyId(orgId);
						terminalData.setOrgData(orgData);
						terminalData.setStasus(0);

						if (row.getCell(0) == null ||row.getCell(0).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("终端名称是必填项");
							return PrintUtil.printResponseEntity(messageModel);
						}
						if (row.getCell(1) == null ||row.getCell(1).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("终端负责人是必填项");
							return PrintUtil.printResponseEntity(messageModel);
						}
						if (row.getCell(2) == null ||row.getCell(2).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("IP地址是必填项目");
							return PrintUtil.printResponseEntity(messageModel);
						}
						if ( row.getCell(3) == null ||row.getCell(3).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("MAC地址是必填项");
							return PrintUtil.printResponseEntity(messageModel);
						}
						if ( row.getCell(4) == null ||row.getCell(4).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("硬盘序列号是必填项");
							return PrintUtil.printResponseEntity(messageModel);
						}
						if (row.getCell(5) == null ||row.getCell(5).equals("")) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("CPU是必填项");
							return PrintUtil.printResponseEntity(messageModel);
						}
						
						//因为EXCEL文件里,用.getStringCellValue()方法读取数字类型会报错， 这里做防止报错处理
						Cell numCell0 = row.getCell(0);
						if (numCell0 != null) {
							numCell0.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell0 = numCell0.getStringCellValue();

						Cell numCell1 = row.getCell(1);
						if (numCell1 != null) {
							numCell1.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell1 = numCell1.getStringCellValue();

						Cell numCell2 = row.getCell(2);
						if (numCell2 != null) {
							numCell2.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell2 = numCell2.getStringCellValue();

						Cell numCell3 = row.getCell(3);
						if (numCell3 != null) {
							numCell3.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell3 = numCell3.getStringCellValue();

						Cell numCell4 = row.getCell(4);
						if (numCell4 != null) {
							numCell4.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell4 = numCell4.getStringCellValue();

						Cell numCell5 = row.getCell(5);
						if (numCell5 != null) {
							numCell5.setCellType(Cell.CELL_TYPE_STRING);
						}
						String cell5 = numCell5.getStringCellValue();
						
						if(cell0.equals("")||cell0.length()==0&&
								cell1.equals("")||cell1.length()==0&&
								cell2.equals("")||cell2.length()==0&&
								cell3.equals("")||cell3.length()==0&&
								cell4.equals("")||cell4.length()==0&&
								cell5.equals("")||cell5.length()==0){
							
						}else {
							if (cell0.equals("")||cell0.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("终端名称是必填项");
								return PrintUtil.printResponseEntity(messageModel);
							}
							if (cell1.equals("")||cell1.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("终端负责人是必填项");
								return PrintUtil.printResponseEntity(messageModel);
							}
							if (cell2.equals("")||cell2.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("IP地址是必填项目");
								return PrintUtil.printResponseEntity(messageModel);
							}
							if (cell3.equals("")||cell3.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("MAC地址是必填项");
								return PrintUtil.printResponseEntity(messageModel);
							}
							if (cell4.equals("")||cell4.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("硬盘序列号是必填项");
								return PrintUtil.printResponseEntity(messageModel);
							}
							if (cell5.equals("")||cell5.length()==0) {
								messageModel.setOperFlag(ConstantData.OPER_ERROR);
								messageModel.setMessage("CPU是必填项");
								return PrintUtil.printResponseEntity(messageModel);
							}
						}
						
						
						//放进对象后add到List
						terminalData.setHostName(cell0);
						terminalData.setMasterName(cell1);
						terminalData.setIp(cell2);
						terminalData.setMac(cell3);
						terminalData.setHardDisk(cell4);
						terminalData.setCpu(cell5);
						terminalData.setType("1");
						terminalData.setCreateby(userData);
						terminalData.setDelflag("F");
						terminalData.setCreatetime(new Date());
						paramList.add(terminalData);
					} else {
						break;
					}
				}
			}

			TerminalData terminalData = new TerminalData();
			terminalData.setDelflag("F");
			//查询数据库已有全部设备，验证IP的MAC地址的唯一性 ，最后删除值全部为空的条目;
			List<TerminalData> termList = terminalService.queryList(terminalData);
			for (int i = 0; i < paramList.size(); i++) {
				if (!paramList.get(i).getHostName().equals("") && paramList.get(i).getHostName() != null
						&& !paramList.get(i).getMasterName().equals("") && paramList.get(i).getMasterName() != null
						&& !paramList.get(i).getIp().equals("") && paramList.get(i).getIp() != null
						&& !paramList.get(i).getMac().equals("") && paramList.get(i).getMac() != null
						&& !paramList.get(i).getHardDisk().equals("") && paramList.get(i).getHardDisk() != null
						&& !paramList.get(i).getCpu().equals("") && paramList.get(i).getCpu() != null) {

					String ipString = paramList.get(i).getIp();
					String macString = paramList.get(i).getMac();
					if (ipString.equals("") || ipString == null || macString.equals("") || macString == null) {
						messageModel.setOperFlag(ConstantData.OPER_ERROR);
						messageModel.setMessage("MAC地址和IP地址是必填的");
						return PrintUtil.printResponseEntity(messageModel);
					}
					for (TerminalData term : termList) {
						if (ipString.equals(term.getIp())) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("此IP地址已经存在,IP重复为" + ipString);
							return PrintUtil.printResponseEntity(messageModel);
						} else if (macString.equals(term.getMac())) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("此MAC地址已经存在,MAC地址重复为" + macString);
							return PrintUtil.printResponseEntity(messageModel);
						}
					}
				} else {
					paramList.remove(i);
					i--;
				}
			}
			//验证所有EXCEL文件内是否有IP地址和MAC地址的重复
			for (int i = 0; i < paramList.size(); i++) {
				for (int j = 0; j < paramList.size(); j++) {
					if (i == j) {
					} else {
						if (paramList.get(i).getIp().equals(paramList.get(j).getIp())) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("EXCEL内IP地址不能重复，重复的IP为：" + paramList.get(i).getIp());
							return PrintUtil.printResponseEntity(messageModel);
						}
						if (paramList.get(i).getMac().equals(paramList.get(j).getMac())) {
							messageModel.setOperFlag(ConstantData.OPER_ERROR);
							messageModel.setMessage("EXCEL内MAC地址不能重复，重复的MAC为：" + paramList.get(i).getMac());
							return PrintUtil.printResponseEntity(messageModel);
						}
					}
				}
			}
			//终于完成了所有的盐城
			// 最后  循环List  完成新增操作
			for (TerminalData param : paramList) {
				terminalService.addOrUpdateData(param);
			}
		} else {
			messageModel.setOperFlag(ConstantData.OPER_ERROR);
			messageModel.setMessage("请选择有效的xls模板文件");
			return PrintUtil.printResponseEntity(messageModel);
		}
		messageModel.setOperFlag(ConstantData.OPER_SUCCESS);
		messageModel.setMessage("操作成功");
		return PrintUtil.printResponseEntity(messageModel);
	}
复制代码

转载于:https://juejin.im/post/5c418753f265da616624bbdc