maven依赖
1 <!--添加POI的依赖用于Excel的操作--> 2 <dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>4.0.0</version> 6 </dependency> 7 <dependency> 8 <groupId>org.apache.poi</groupId> 9 <artifactId>poi-ooxml</artifactId> 10 <version>4.0.0</version> 11 </dependency>
前端
<html> <head> <title>导入excel</title> </head> <body> <form enctype="multipart/form-data" method="post" action="${pageContext.request.contextPath}/other/excel/importExcel"> 选择Excel表格： <input type="file" name="file"/> <br/> <input type="submit" value="上传"/> </form> </body> </html>
实体类（get set 自己补充）
1 public class Excel { 2 private int id; 3 4 private String name; 5 6 private int age; 7 8 private String createDate; 9 }
后端
1 package com.spring.other.controller; 2 3 import com.spring.other.entity.Excel; 4 import org.springframework.stereotype.Controller; 5 import org.springframework.web.bind.annotation.RequestMapping; 6 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 7 import org.apache.poi.hssf.usermodel.HSSFRow; 8 import org.apache.poi.hssf.usermodel.HSSFSheet; 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 10 import org.springframework.stereotype.Controller; 11 import org.springframework.web.bind.annotation.*; 12 import org.springframework.web.multipart.MultipartFile; 13 14 import javax.servlet.http.HttpServletRequest; 15 import java.text.SimpleDateFormat; 16 import java.util.ArrayList; 17 import java.util.List; 18 19 /** 20 * @author liuwenlong 21 * @create 2022-03-17 19:20:49 22 */ 23 @SuppressWarnings("all") 24 @Controller 25 @RequestMapping("/other/excel") 26 public class ExcelController { 27 28 @RequestMapping("importExcelView") 29 public String importExcelView() { 30 return "other/importExcel"; 31 } 32 33 @RequestMapping("importExcel") 34 public String importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) { 35 String contentType = file.getContentType(); 36 String fileName = file.getOriginalFilename(); 37 if (file.isEmpty()) { 38 return "文件为空！"; 39 } 40 try { 41 //根据路径获取这个操作excel的实例 42 HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream()); 43 //根据页面index 获取sheet页 44 HSSFSheet sheet = wb.getSheetAt(0); 45 HSSFRow row = null; 46 //循环sesheet页中数据从第二行开始，第一行是标题 47 48 List<Excel> list = new ArrayList<>(); 49 for (int i = 1; i < sheet.getPhysicalNumberOfRows()-1; i++) { 50 //获取每一行数据 51 row = sheet.getRow(i); 52 Excel excel = new Excel(); 53 excel.setId((int) row.getCell(0).toString()); 54 excel.setName(row.getCell(1).toString()); 55 excel.setAge((int) row.getCell(2).toString()); 56 SimpleDateFormat df = new SimpleDateFormat("yyyy年MM月dd日"); 57 excel.setCreateDate(df.format(df.parse(df.format(HSSFDateUtil.getJavaDate(row.getCell(3).getNumericCellValue()))))); 58 System.out.println(excel); 59 list.add(excel); 60 } 61 62 //循环展示导入的数据，实际应用中应该校验并存入数据库 63 for (Excel excel : list) { 64 System.out.println("ID:"+excel.getId()+" "+"姓名："+excel.getName()+" "+"年龄："+excel.getAge()+" "+"日期"+excel.getCreateDate()); 65 } 66 } catch (Exception e) { 67 e.printStackTrace(); 68 } 69 return "导入成功!"; 70 } 71 72 }
Excel
前端操作，选择excel文件上传
搞定
