devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

使用EasyPoi实现Excel文件导入_easypoi 导入

作者：凡人多烦事01 | 2024-05-05 18:42:20

赞

踩

easypoi 导入
一、功能介绍：使用EasyPoi将Excel文件中的数据导入数据库并显示。
二、实现效果

批量导入实现效果图

三、实现过程
        1、前端页面

1.1 Excel文件数据

1.2  前端代码如下：

  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
  6.     <title>EasyPOIProject</title>
  7. </head>
  8. <style type="text/css">
  9.     body{
  10.         margin: 0px;
  11.     }
  12.     #app{
  13.         width: 100%;
  14.     }
  15. </style>
  16. <body>
  17. <div id="app">
  18.     <div style="width: 100px; height: 50px;margin-top: 50px;margin-left: 100px;">
  19.         <el-button style="width: 100px;height: 50px;background-color: antiquewhite">批量导入</el-button>
  20.         <input style="width: 100px;height: 50px;background-color: #2e6da4;opacity:0;
  21.         position: absolute;top: 50px;left: 100px;" @change="upload" type="file" multiple ref="file" >
  22.     </div>
  23.     <div style="margin-top: 50px;" align="center">
  24.         <el-table
  25.                 :data="excelUploadAll"
  26.                 style="width: 80%;" >
  27.             <el-table-column
  28.                     prop="id"
  29.                     label="编号"
  30.                     width="90"
  31.                     align="center">
  32.             </el-table-column>
  33.             <el-table-column
  34.                     prop="name"
  35.                     label="姓名"
  36.                     width="120"
  37.                     align="center">
  38.             </el-table-column>
  39.             <el-table-column
  40.                     prop="nickName"
  41.                     label="昵称"
  42.                     width="120"
  43.                     align="center">
  44.             </el-table-column>
  45.             <el-table-column
  46.                     prop="birthday"
  47.                     label="出生日期"
  48.                     width="120"
  49.                     align="center">
  50.             </el-table-column>
  51.             <el-table-column
  52.                     prop="idCard"
  53.                     label="身份证号"
  54.                     width="200"
  55.                     align="center">
  56.             </el-table-column>
  57.             <el-table-column
  58.                     prop="address"
  59.                     label="家庭住址"
  60.                     width="180"
  61.                     align="center">
  62.             </el-table-column>
  63.             <el-table-column
  64.                     prop="grade"
  65.                     label="入学年份"
  66.                     width="90"
  67.                     align="center">
  68.             </el-table-column>
  69.             <el-table-column
  70.                     prop="status"
  71.                     label="状态"
  72.                     width="80"
  73.                     align="center">
  74.             </el-table-column>
  75.             <el-table-column
  76.                     prop="hobby"
  77.                     label="爱好"
  78.                     align="center">
  79.             </el-table-column>
  80.         </el-table>
  81.     </div>
  82. </div>
  83. </body>
  84. <!-- import Vue before Element -->
  85. <script src="https://unpkg.com/vue@2/dist/vue.js"></script>
  86. <!-- import JavaScript -->
  87. <script src="https://unpkg.com/element-ui/lib/index.js"></script>
  88. <script src="../axios.min.js"></script>
  89. <script>
  90.     let v = new Vue({
  91.         el: '#app',
  92.         data: function() {
  93.             return {
  94.                 excelUploadAll:[],
  95.                 flag:"",
  96.             }
  97.         },
  98.         created(){},
  99.         methods:{
  100.             upload(){
  101.                 let files = this.$refs.file.files;
  102.                 for (let i = 0; i < files.length; i++) {
  103.                     let formData = new FormData();
  104.                     formData.append('file', files[i]);
  105.                     axios.post("/excelUpload", formData, {
  106.                         'Content-Type': 'multipart/form-data'
  107.                     }).then(function (response) {
  108.                         v.flag=response.data;
  109.                         if (v.flag == "1"){
  110.                             v.$message.success("批量导入成功")
  111.                             //显示批量导入数据
  112.                             axios.get("/getExcelUploadList").then(function (response) {
  113.                                 v.excelUploadAll = response.data
  114.                             })
  115.                         }
  116.                     })
  117.                 }
  118.             },
  119.         }
  120.     })
  121. </script>
  122. </html>

前端实现效果图如下所示：

2、后端实现

2.1 导入EasyPoi有关依赖

从EasyPoi官方文档中复制相关依赖。

  1. <!--        引入EasyPOI有关依赖-->
  2.         <dependency>
  3.             <groupId>cn.afterturn</groupId>
  4.             <artifactId>easypoi-base</artifactId>
  5.             <version>3.2.0</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>cn.afterturn</groupId>
  9.             <artifactId>easypoi-web</artifactId>
  10.             <version>3.2.0</version>
  11.         </dependency>
  12.         <dependency>
  13.             <groupId>cn.afterturn</groupId>
  14.             <artifactId>easypoi-annotation</artifactId>
  15.             <version>3.2.0</version>
  16.         </dependency>

2.2 实体类

  1. package com.example.copy.entity;
  2.  
  3. import cn.afterturn.easypoi.excel.annotation.Excel;
  4. import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
  5. import lombok.Data;
  6.  
  7. import java.io.Serializable;
  8.  
  9. @Data
  10. @ExcelTarget("USER")
  11. public class User implements Serializable {
  12.     @Excel(name = "编号",orderNum = "0")
  13.     private Integer id;
  14.  
  15.     @Excel(name = "姓名",orderNum = "1")
  16.     private String name;
  17.  
  18.     @Excel(name = "昵称",orderNum = "2")
  19.     private String nickName;
  20.  
  21.     @Excel(name = "出生日期",orderNum = "3")
  22.     private String birthday;
  23.  
  24.     @Excel(name = "状态",orderNum = "4",replace = { "激活_1", "锁定_0" })
  25.     private Integer status;
  26.  
  27.     @Excel(name = "爱好",orderNum = "5")
  28.     private String hobby;
  29.  
  30.     @Excel(name = "身份证号",orderNum = "6")
  31.     private String idCard;
  32.  
  33.     @Excel(name = "家庭住址",orderNum = "7")
  34.     private String address;
  35.  
  36.     @Excel(name = "入学年份",orderNum = "8")
  37.     private String grade;
  38. }

2.3 Controller层

  1. package com.example.copy.controller;
  2.  
  3. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  4. import cn.afterturn.easypoi.excel.entity.ImportParams;
  5. import com.example.copy.entity.User;
  6. import com.example.copy.mapper.UserMapper;
  7. import lombok.Cleanup;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.web.bind.annotation.RequestMapping;
  10. import org.springframework.web.bind.annotation.RestController;
  11. import org.springframework.web.multipart.MultipartFile;
  12.  
  13. import java.io.InputStream;
  14. import java.util.List;
  15.  
  16. @RestController
  17. public class UserController {
  18.     @Autowired
  19.     UserMapper userMapper;
  20.  
  21. //    从数据库中读取数据
  22.     @RequestMapping("/getExcelUploadList")
  23.     public List<User> getExcelUploadList(){
  24.         List<User> userList = userMapper.selectAllExcelUser();
  25.         return userList;
  26.     }
  27.  
  28. //    批量导入数据库
  29.     @RequestMapping("/excelUpload")
  30.     public String excelUpload(MultipartFile file) throws Exception{
  31.         //excel导入
  32.         ImportParams params = new ImportParams();
  33.          String fileName = file.getOriginalFilename();
  34. //        导入Excel表中表名所占行
  35.         params.setTitleRows(1);
  36. //        导入Excel表中属性信息所占行
  37.         params.setHeadRows(1);
  38.         @Cleanup
  39.         InputStream inputStream = file.getInputStream();
  40.         List<User> userList = ExcelImportUtil.importExcel(inputStream,User.class,params);
  41.         inputStream.close();
  42.         System.out.println(fileName);
  43.         for (User user : userList){
  44.             System.out.println(user);
  45.             userMapper.excelInsertUsers(user);
  46.         }
  47.         return "1";
  48.     }
  49.  
  50. }

2.4 mapper层定义数据库查询与插入方法

  1. package com.example.copy.mapper;
  2.  
  3. import com.example.copy.entity.User;
  4. import org.apache.ibatis.annotations.Mapper;
  5.  
  6. import java.util.List;
  7.  
  8. @Mapper
  9. public interface UserMapper {
  10.  
  11. //    读取表中全部信息
  12.     List<User> selectAllExcelUser();
  13.  
  14.     void excelInsertUsers(User user);
  15. }

2.5 SQL语句

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.example.copy.mapper.UserMapper">
  6.  
  7.     <resultMap id="UserMap" type="com.example.copy.entity.User">
  8.         <result property="id" column="id"></result>
  9.         <result property="name" column="name"></result>
  10.         <result property="nickName" column="nickName"></result>
  11.         <result property="birthday" column="birthday"></result>
  12.         <result property="address" column="address"></result>
  13.         <result property="idCard" column="idCard"></result>
  14.         <result property="grade" column="grade"></result>
  15.         <result property="status" column="status"></result>
  16.         <result property="hobby" column="hobby"></result>
  17.     </resultMap>
  18.  
  19. <!--    查询数据表中所有数据-->
  20.     <select id="selectAllExcelUser" resultMap="UserMap">
  21.         SELECT * FROM excel.user;
  22.     </select>
  23.  
  24. <!--    插入数据-->
  25.     <insert id="excelInsertUsers" parameterType="com.example.copy.entity.User">
  26.         INSERT INTO excel.user (id, name, nickName, birthday, status, hobby, idCard, address, grade)
  27.         VALUES (#{id}, #{name}, #{nickName}, #{birthday}, #{status}, #{hobby}, #{idCard}, #{address}, #{grade});
  28.     </insert>
  29.  
  30. </mapper>

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/540294
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号