How to upload excel and import tabular data into or update mySql database by Springboot

This article mainly explains the "Springboot how to upload excel and table data into or update the mySql database", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Springboot how to upload excel and table data into or update the mySql database" it!

1. Import annotations in pom.xml files, mainly using POI

Org.apache.poi poi-ooxml 3.9 commons-fileupload commons-fileupload 1.3.1 commons-io commons-io 2.4

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 departmentMap = findDepartment (); Map roleMap = findRole (); boolean notNull = false; List userList = new ArrayList () If (! fileName.matches ("^. +\. (? I) (xls) $") & &! fileName.matches ("^. +\\. (? I) (xlsx) $") {throw new MyException ("incorrect upload format");} 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 (sheetworthiness null) {notNull = true;} User user; for (int r = 1; r