The upload requirement is to input the fields in the excel table as follows into the database, in which the fields of the park and laboratory are converted into the corresponding ID

Difficult points:

1. What method do I use to read data in Excel

       InputStream inputStream = file.getInputStream();
       ExcelReader reader = ExcelUtil.getReader(inputStream);
       List<List<Object>> hiddenDangerInfoList = reader.read(1, Integer.MAX_VALUE, false);
Copy the code

2. There is a one-to-many relationship between the campus and laboratories, and laboratories in different campuses have the same name

The relationship between the campus and lab fields can be determined by determining a record together

/ / the front controller
        @PostMapping(value = "/uploadExcel")
	public R upload(@RequestPart("file") MultipartFile file) {
		Msg msg= new Msg();
		try {
			msg = excelService.readFile(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return R.ok(msg);
	}
        
 //serviceImpl
     public Msg readFile(MultipartFile file) {
		List<List<Object>> hiddenDangerInfoList = new ArrayList<>();
		// Initialize the return information
		int rightNum = 0;
		String wrongMsg = "";
		try {
			InputStream inputStream = file.getInputStream();
			ExcelReader reader = ExcelUtil.getReader(inputStream);
			hiddenDangerInfoList = reader.read(1, Integer.MAX_VALUE, false);
			for (List<Object> dangerInfoL : hiddenDangerInfoList) {
				String park = (String) dangerInfoL.get(3);
				String laboratory = (String) dangerInfoL.get(4);
				// Execute the insert condition: In the lab table, the information can be found by the campus (string-id) and the lab (name lookup)
				ParkInfo parkInfo = parkInfoMapper.selectOne(new LambdaQueryWrapper<ParkInfo>().eq(ParkInfo::getParkName, park));
				if (parkInfo == null) {
					// Can not throw exceptions will affect the execution of the print record
					System.out.println(park + "The park does not exist.");
					wrongMsg += park + "The park does not exist;";
				} else {
					Integer parkId = parkInfo.getId();
					// Check whether this information exists in the lab table according to the campus ID and lab name
					LaboratoryInfo laboratoryInfo = laboratoryInfoMapper.selectOne(new LambdaQueryWrapper<LaboratoryInfo>()
							.eq(LaboratoryInfo::getId, parkId).eq(LaboratoryInfo::getLaboratoryName, laboratory));
					if(laboratoryInfo ! =null) {
						// There are records to read the values in Excel into the hidden information table
						String hidden_danger_name = (String) dangerInfoL.get(0);
						String hidden_danger_no = (String) dangerInfoL.get(1);
						String hidden_danger_source_name = (String) dangerInfoL.get(2);
						Integer laboratoryId = laboratoryInfo.getLaboratoryId();
						String addr_desc = (String) dangerInfoL.get(5);
						Date flowTime = (Date) dangerInfoL.get(7);

						HiddenDangerInfo dangerInfo = new HiddenDangerInfo();
						dangerInfo.setHiddenDangerName(hidden_danger_name)
								.setHiddenDangerNo(hidden_danger_no)
								.setHiddenDangerSourceName(hidden_danger_source_name)
								.setParkId(parkId)
								.setLaboratoryId(laboratoryId)
								.setAddrDesc(addr_desc)
								.setFlowTime(flowTime);
						baseMapper.insert(dangerInfo);
						rightNum++;
					} else {
						// Do not insert and print relevant information (there is no laboratory XXX under campus XXX)
						System.out.println("Campus" + park + "And the lab" + laboratory + "No match");
						wrongMsg += "Campus" + park + "And the lab" + laboratory + "No match;"; }}}}catch (IOException e) {
			e.printStackTrace();
		}
		Msg msg = new Msg();
		msg.setMsg(wrongMsg)
				.setWrongNum(hiddenDangerInfoList.size() - rightNum)
				.setRightNum(rightNum);
		return msg;
	}
Copy the code

Download function realization

Requirements: Filter the following information, and then export the corresponding Excel table (need to look up the table to export, the front end can only print the data of the current page)

Difficulty: it is not complicated logically, and the difficulty lies in the familiarity with the method.

//controller
        @apiOperation (value = "export hidden danger information table ")
	@GetMapping("/export")
	public void exportHiddenDangerInfo(HiddenDangerInfo hiddenDangerInfo, HttpServletResponse response){
		excelService.exportHiddenDangerInfo(hiddenDangerInfo,response,"Hidden message");
	}
        
//serviceImpl
        public void exportHiddenDangerInfo(HiddenDangerInfo hiddenDangerInfo, HttpServletResponse response,String fileName) {
		List<HiddenDangerInfoVo> list = hiddenDangerInfoMapper.getHiddenDangerInfoByAddition(hiddenDangerInfo);
		// The key of the alias must correspond to the name of the entity class.
		LinkedHashMap<String, String> map = new LinkedHashMap<>();
		map.put("hiddenDangerName"."Hidden danger Name");
		map.put("hiddenDangerNo"."Hazard Number");
		map.put("hiddenDangerSourceName"."Hidden source");
		map.put("parkName"."Campus");
		map.put("laboratoryName"."In the lab");
		map.put("addrDesc"."Location description");
		map.put("flowTime"."Governance time");
		ExcelWriter writer = new ExcelWriter();
		writer.setHeaderAlias(map);
		// align horizontally and vertically in the middle
		writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
		// All cells are 25 characters wide
		writer.setColumnWidth(-1.25);
		// Write out the content once, using the default style, forcing the output title
		writer.write(list, true);
		response.setContentType("application/vnd.ms-excel; charset=utf-8");
		ServletOutputStream out = null;
		try {
			// Set the request header properties
			response.setHeader("Content-Disposition"."attachment; filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
			out = response.getOutputStream();
			// Write to the file
			writer.flush(out, true);
			// Close Writer to release memory
			writer.close();
			// Remember to close the output Servlet stream here
			IoUtil.close(out);
		} catch (IOException e) {
			throw new CheckedException("File download failed =="+ e); }}Copy the code