java实现导入excel功能

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文件上传

搞定

 

posted @ 2022-03-17 20:33  勤快的懒羊羊  阅读(7311)  评论(0)  编辑  收藏  举报