使用EasyPoi实现Excel文件导入

最新推荐文章于 2024-06-10 21:44:54 发布

锦小白

最新推荐文章于 2024-06-10 21:44:54 发布

阅读量1.3k

点赞数 3

文章标签： excel java

本文链接：https://blog.csdn.net/m0_74268633/article/details/136683918

版权

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

二、实现效果

批量导入实现效果图

三、实现过程

1、前端页面

1.1 Excel文件数据

1.2 前端代码如下：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
    <title>EasyPOIProject</title>
</head>
<style type="text/css">
    body{
        margin: 0px;
    }
    #app{
        width: 100%;
    }
</style>
<body>
<div id="app">
    <div style="width: 100px; height: 50px;margin-top: 50px;margin-left: 100px;">
        <el-button style="width: 100px;height: 50px;background-color: antiquewhite">批量导入</el-button>
        <input style="width: 100px;height: 50px;background-color: #2e6da4;opacity:0;
        position: absolute;top: 50px;left: 100px;" @change="upload" type="file" multiple ref="file" >
    </div>
    <div style="margin-top: 50px;" align="center">
        <el-table
                :data="excelUploadAll"
                style="width: 80%;" >
            <el-table-column
                    prop="id"
                    label="编号"
                    width="90"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="name"
                    label="姓名"
                    width="120"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="nickName"
                    label="昵称"
                    width="120"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="birthday"
                    label="出生日期"
                    width="120"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="idCard"
                    label="身份证号"
                    width="200"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="address"
                    label="家庭住址"
                    width="180"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="grade"
                    label="入学年份"
                    width="90"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="status"
                    label="状态"
                    width="80"
                    align="center">
            </el-table-column>
            <el-table-column
                    prop="hobby"
                    label="爱好"
                    align="center">
            </el-table-column>
        </el-table>
    </div>
</div>
</body>
<!-- import Vue before Element -->
<script src="https://unpkg.com/vue@2/dist/vue.js"></script>
<!-- import JavaScript -->
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="../axios.min.js"></script>
<script>
    let v = new Vue({
        el: '#app',
        data: function() {
            return {
                excelUploadAll:[],
                flag:"",
            }
        },
        created(){},
        methods:{
            upload(){
                let files = this.$refs.file.files;
                for (let i = 0; i < files.length; i++) {
                    let formData = new FormData();
                    formData.append('file', files[i]);
                    axios.post("/excelUpload", formData, {
                        'Content-Type': 'multipart/form-data'
                    }).then(function (response) {
                        v.flag=response.data;
                        if (v.flag == "1"){
                            v.$message.success("批量导入成功")
                            //显示批量导入数据
                            axios.get("/getExcelUploadList").then(function (response) {
                                v.excelUploadAll = response.data
                            })
                        }
                    })
                }
            },
        }
    })
</script>
</html>

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

2、后端实现

2.1 导入EasyPoi有关依赖

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

<!--        引入EasyPOI有关依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

2.2 实体类

package com.example.copy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;

@Data
@ExcelTarget("USER")
public class User implements Serializable {
    @Excel(name = "编号",orderNum = "0")
    private Integer id;

    @Excel(name = "姓名",orderNum = "1")
    private String name;

    @Excel(name = "昵称",orderNum = "2")
    private String nickName;

    @Excel(name = "出生日期",orderNum = "3")
    private String birthday;

    @Excel(name = "状态",orderNum = "4",replace = { "激活_1", "锁定_0" })
    private Integer status;

    @Excel(name = "爱好",orderNum = "5")
    private String hobby;

    @Excel(name = "身份证号",orderNum = "6")
    private String idCard;

    @Excel(name = "家庭住址",orderNum = "7")
    private String address;

    @Excel(name = "入学年份",orderNum = "8")
    private String grade;
}

2.3 Controller层

package com.example.copy.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.example.copy.entity.User;
import com.example.copy.mapper.UserMapper;
import lombok.Cleanup;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.List;

@RestController
public class UserController {
    @Autowired
    UserMapper userMapper;

//    从数据库中读取数据
    @RequestMapping("/getExcelUploadList")
    public List<User> getExcelUploadList(){
        List<User> userList = userMapper.selectAllExcelUser();
        return userList;
    }

//    批量导入数据库
    @RequestMapping("/excelUpload")
    public String excelUpload(MultipartFile file) throws Exception{
        //excel导入
        ImportParams params = new ImportParams();
         String fileName = file.getOriginalFilename();
//        导入Excel表中表名所占行
        params.setTitleRows(1);
//        导入Excel表中属性信息所占行
        params.setHeadRows(1);
        @Cleanup
        InputStream inputStream = file.getInputStream();
        List<User> userList = ExcelImportUtil.importExcel(inputStream,User.class,params);
        inputStream.close();
        System.out.println(fileName);
        for (User user : userList){
            System.out.println(user);
            userMapper.excelInsertUsers(user);
        }
        return "1";
    }

}

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

package com.example.copy.mapper;

import com.example.copy.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

//    读取表中全部信息
    List<User> selectAllExcelUser();

    void excelInsertUsers(User user);
}

2.5 SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.copy.mapper.UserMapper">

    <resultMap id="UserMap" type="com.example.copy.entity.User">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <result property="nickName" column="nickName"></result>
        <result property="birthday" column="birthday"></result>
        <result property="address" column="address"></result>
        <result property="idCard" column="idCard"></result>
        <result property="grade" column="grade"></result>
        <result property="status" column="status"></result>
        <result property="hobby" column="hobby"></result>
    </resultMap>

<!--    查询数据表中所有数据-->
    <select id="selectAllExcelUser" resultMap="UserMap">
        SELECT * FROM excel.user;
    </select>

<!--    插入数据-->
    <insert id="excelInsertUsers" parameterType="com.example.copy.entity.User">
        INSERT INTO excel.user (id, name, nickName, birthday, status, hobby, idCard, address, grade)
        VALUES (#{id}, #{name}, #{nickName}, #{birthday}, #{status}, #{hobby}, #{idCard}, #{address}, #{grade});
    </insert>

</mapper>