1. Import poi-related jar packages

For excel, which only operates on 2003 and previous versions, only poi-XXX.jar needs to be imported. If you need to operate on 2007 and later versions, you need to import poi-XXX.jar.

poi-ooxml-XXX.jar

poi-ooxml-schemas-XXX.jar

Maven mode

 1 <dependency>
 2           <groupId>org.apache.poi</groupId>
 3           <artifactId>poi</artifactId>
 4           <version>3.17</version>
 5  </dependency>
 6 
 7   <dependency>
 8           <groupId>org.apache.poi</groupId>
 9           <artifactId>poi-ooxml</artifactId>
10           <version>3.17</version>
11   </dependency>

2. Read excel files

ImportExcel Tool Class

 1 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 2 import org.apache.poi.ss.usermodel.*;
 3 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 4 
 5 import java.io.InputStream;
 6 import java.util.ArrayList;
 7 import java.util.HashMap;
 8 import java.util.List;
 9 import java.util.Map;
10 
11 public class ImportExcel {
12     // abc.xls

13     public static boolean isXls(String fileName){
14         // (?i)ignore case
15         if(fileName.matches("^.+\\.(?i)(xls)$")){
16             return true;
17         }else if(fileName.matches("^.+\\.(?i)(xlsx)$")){
18             return false;
19         }else{
20             throw new RuntimeException("Wrong format");
21         }
22     }
23 
24     public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) throws Exception{
25         
26         boolean ret = isXls(fileName);
27         Workbook workbook = null;
28         // Create different objects based on suffixes
29         if(ret){
30             workbook = new HSSFWorkbook(inputStream);
31         }else{
32             workbook = new XSSFWorkbook(inputStream);
33         }
34         Sheet sheet = workbook.getSheetAt(0);
35         // Get the title line
36         Row titleRow = sheet.getRow(0);
37         
38         int lastRowNum = sheet.getLastRowNum();
39         int lastCellNum = titleRow.getLastCellNum();
40         
41         List<Map<String, Object>> list = new ArrayList<>();
42         
43         for(int i = 1; i <= lastRowNum; i++ ){
44             Map<String, Object> map = new HashMap<>();
45             Row row = sheet.getRow(i);
46             for(int j = 0; j < lastCellNum; j++){
47                 // Column names
48                 String key = titleRow.getCell(j).getStringCellValue();
49                 Cell cell = row.getCell(j);
50                 cell.setCellType(CellType.STRING);
51                 
52                 map.put(key, cell.getStringCellValue());
53             }
54             list.add(map);
55         }
56         workbook.close();
57         return list;
58     
59     }
60 }

Front End: Give Upload Links

1 <div class="layui-form-item">
2             <label class="layui-form-label">Select file</label>
3             <div class="layui-input-block">
4                 <input type="file" name="mFile" id="no1" class="layui-input">
5             </div>
6         </div>
7         <div class="layui-form-item">
8             <input class="layui-btn" style="margin-left: 10%"  id="btn1" type="submit" value="Confirm import">
9         </div>

Background controller layer processes received excel files

 1  @RequestMapping("/staff/import.do")
 2     @ResponseBody
 3     public JsonBean importExcel(@RequestParam MultipartFile mFile){
 4         try {
 5             String fileName = mFile.getOriginalFilename();
 6             // Get the input stream of the uploaded file
 7             InputStream inputStream = mFile.getInputStream();
 8             // Call the method in the tool class to read excel Data in files
 9             List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);
10 
11             // Turn the object to json Format string, and then convert to List<SysUser> object
12             ObjectMapper objMapper = new ObjectMapper();
13             String infos = objMapper.writeValueAsString(sourceList);
14 
15             // json String Rotation Object
16             List<Staff> list = objMapper.readValue(infos, new TypeReference<List<Staff>>() {});
17 
18             // Batch addition
19             staffService.addStaffBatch(list);
20 
21             return JsonUtils.createJsonBean(1, null);
22 
23         } catch (Exception e) {
24             // TODO Auto-generated catch block
25             e.printStackTrace();
26 
27             return JsonUtils.createJsonBean(0, e.getMessage());
28         }
29 
30     }

Note: Also configure spring-bean.xml

<! - The value of parser id uploaded from a file cannot be changed - >
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <! -- The maximum size of the uploaded file, in unit bytes, such as 1024 * 1024 = 1M - >.
    <property name="maxUploadSize" value="1048576"></property>

</bean>

[Note] excel should have the same column name as the database field name!!

For data with date type, excel input 2019-12-12 will be changed into date format data, and date type will be converted into string when it is passed back to the background. The format of Excel will be wrong, and it will not be able to convert Date type.

So Excel table must store time-related data in text format!!!

Add @DateTimeFormat(pattern="yyyy-MM-dd") annotation to the corresponding entity class and time-related attributes of the database