Home > Database > Mysql Tutorial > body text

How does Springboot upload excel and import table data into or update mySql database?

WBOY
Release: 2023-06-03 08:46:07
forward
1362 people have browsed it

1.在pom.xml文件中导入注解,主要利用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>
Copy after login

2.Controller接口

@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 "文件异常,导入失败"; 
   
 } 
}
Copy after login

3.服务层接口

boolean import(String fileName, MultipartFile file) throws Exception;
Copy after login

4.业务层实现类

@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("上传文件格式不正确"); 
 } 
 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("导入失败(第"+(r+1)+"行,ID单元格格式请设为文本格式)"); 
  } 
  String id = row.getCell(0).getStringCellValue(); 
  if(id==null || id.isEmpty()){ 
    throw new MyException("导入失败(第"+(r+1)+"行,ID未填写)"); 
  } 
  String name = row.getCell(1).getStringCellValue(); 
  if(name==null || name.isEmpty()){ 
    throw new MyException("导入失败(第"+(r+1)+"行,姓名未填写)"); 
  } 
  String department = row.getCell(2).getStringCellValue(); 
  if(departmentMap.get(department)==null){ 
    throw new MyException("导入失败(第"+(r+1)+"行,不存在此单位或单位未填写)"); 
  } 
  String role = row.getCell(3).getStringCellValue(); 
  if(roleMap.get(role)==null){ 
   throw new MyException("导入失败(第"+(r+1)+"行,不存在此角色或角色未填写)"); 
  } 
  Date date; 
  if(row.getCell(4).getCellType() !=0){ 
   throw new MyException("导入失败(第"+(r+1)+"行,入职日期格式不正确或未填写)"); 
   }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; 
}
Copy after login

The above is the detailed content of How does Springboot upload excel and import table data into or update mySql database?. For more information, please follow other related articles on the PHP Chinese website!

source:yisu.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template