This article mainly describes the process of uploading excel under the Springboot-mybatis framework and importing it into the mysql database. If the user ID already exists, update and modify the information in the database. Since the front-end separation technology is used, the main record of the back-end Java part is the back-end interface. It can implement functions by connecting with the front-end interface, and use front-end frameworks such as layui to connect with it. You can also write front-end code yourself. This article starts with Controller and starts with the introduction process, which includes the conversion of the dictionary table.
1. Import annotations in the pom.xml file, mainly using POI
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
2.Controller interface
@PostMapping("/save") public String addUser(@RequestParam("file") MultipartFile file) { String fileName = file.getOriginalFilename(); try { return sysService.batchImport(fileName, file); } catch (MyException e) { e.printStackTrace(); return e.getMessage(); } catch(Exception e){ e.printStackTrace(); return "File exception, import failed"; } }3. Service layer interface
boolean import(String fileName, MultipartFile file) throws Exception;
4. Business layer implementation class
@Transactional(readOnly = false,rollbackFor = Exception.class) @Override public boolean import(String fileName, MultipartFile file) throws Exception { Map<String, Integer> departmentMap = findDepartment(); Map<String, Integer> roleMap = findRole(); boolean notNull = false; List<User> userList = new ArrayList<User>(); if (!fileName.matches("^.+//.(?i)(xls)$") && !fileName.matches("^.+//.(?i)(xlsx)$")) { throw new MyException("The upload file format is incorrect"); } boolean isExcel2003 = true; if (fileName.matches("^.+//.(?i)(xlsx)$"))) { isExcel2003 = false; } InputStream is = file.getInputStream(); Workbook wb = null; if (isExcel2003) { wb = new HSSFWorkbook(is); } else { wb = new XSSFWorkbook(is); } Sheet sheet = wb.getSheetAt(0); if(sheet!=null){ notNull = true; } User user; for (int r = 1; r <= sheet.getLastRowNum(); r++) { Row row = sheet.getRow(r); if (row == null){ continue; } user = new User(); if( row.getCell(0).getCellType() !=1){ throw new MyException("Import failed (row "+(r+1)+" and ID cell format is set to text format)"); } String id = row.getCell(0).getStringCellValue(); if(id==null || id.isEmpty()){ throw new MyException("Import failed (row "+(r+1)+" and ID not filled in)"); } String name = row.getCell(1).getStringCellValue(); if(name==null || name.isEmpty()){ throw new MyException("Import failed (line "+(r+1)+" and name not filled in)"); } String department = row.getCell(2).getStringCellValue(); if(departmentMap.get(department)==null){ throw new MyException("Import failed (line "+(r+1)+" and this unit does not exist or the unit is not filled in)"); } String role = row.getCell(3).getStringCellValue(); if(roleMap.get(role)==null){ throw new MyException("Import failed (line "+(r+1)+", this role does not exist or the role is not filled in)"); } Date date; if(row.getCell(4).getCellType() !=0){ throw new MyException("Import failed (line "+(r+1)+", the entry date format is incorrect or not filled in)"); }else{ date = row.getCell(4).getDateCellValue(); } user.setId(id); user.setName(name); user.setDepartmentId((int) departmentMap.get(department)); user.setRoleId((int) roleMap.get(role)); user.setDate(date); userList.add(user); } for (User user : userList) { String id = user.getId(); int cnt = userMapper.selectById(id); if (cnt == 0) { userMapper.addUser(user); } else { userMapper.updateUserById(user); } } return notNull; }Summarize
The above is what the editor introduces to you. Springboot uploads excel and imports table data into or updates the mySql database. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!