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