需求

Excel樣例.png

Excel文件數(shù)據(jù)導(dǎo)入，即將表格中的數(shù)據(jù)存儲到數(shù)據(jù)庫中扒接，我這里使用了阿里的 easyExcel 實現(xiàn)整個過程精钮，使用Maven工具

思路

1.文件上傳，將Excel表格上傳
2.讀取Excel數(shù)據(jù)
3.數(shù)據(jù)存入數(shù)據(jù)庫中

操作

1.導(dǎo)入依賴

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

2.配置文件大小

spring.servlet.multipart.max-file-size=1MB
spring.servlet.multipart.max-request-size=1MB

3.前端

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE-edge"/>
    <title>上傳</title>
    <link rel="stylesheet" th:href="@{/css/elementUI.css}">

    <!-- 引入vue 在 elementUI 之前 -->
    <script th:src="@{/js/vue.js}"></script>
    <!--引入 axios.js -->
    <script th:src="@{/js/axios.js}"></script>
    <!-- 引入組件庫 -->
    <script th:src="@{/js/elementUI.js}"></script>
</head>
<body id="main">
<div style="float: right;" sec:authorize="hasRole('admin')">
    <el-button @click="attendanceDialogVisible = true">上傳表格</el-button>
    <el-dialog title="表格上傳" :visible.sync="attendanceDialogVisible" width="40%">
        <br><br>
        <el-row :gutter="80">
            <el-col :span="6" :offset="4">
                <el-date-picker
                        v-model="dateValue"
                        type="date"
                        placeholder="選擇日期"
                        format="yyyy年MM月dd日"
                        value-format="yyyy年MM月dd日">
                </el-date-picker>
            </el-col>
            <el-col :span="12">
                <el-upload
                        class="upload-demo"
                        ref="upload"
                        action="/manage/doUploadAttendance"
                        :data="attendanceTime"
                        :limit="1"
                        accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
                        :on-preview="handlePreview"
                        :on-remove="handleRemove"
                        :auto-upload="false"
                        :on-success="handleSuccess">
                    <el-button slot="trigger" size="small" type="primary">選取文件</el-button>
                    <el-button style="margin-left: 10px" size="small" type="success" @click="submitUp">
                        上傳文件
                    </el-button>
                    <div slot="tip" class="el-upload__tip">只能上傳Excel文件</div>
                </el-upload>
            </el-col>
        </el-row>
        <br> <br>
    </el-dialog>
</div>
</body>
<script>
    new Vue({
        el: "#main",
        data() {

            return {
                attendanceDialogVisible: false,
                dateValue: '',
                attendanceTime: {
                    upTime: ""
                }

            }
        },
        mounted() {
            
        },
        methods: {
            submitUp() {
                if (this.dateValue =="") {
                    this.$message({
                        showClose: true,
                        message: '請先選擇日期乞娄！',
                        type: 'error',
                        offset: 200,
                        duration: 1000
                    });
                    return;
                }
                this.attendanceTime.upTime = this.dateValue;
                this.$refs.upload.submit();
            },
            handlePreview(file, fileList) {

            },
            handleRemove(file, fileList) {
                console.log(file, fileList);
            },
            handleSuccess(response, file, fileList) {
                this.$message({
                    showClose: true,
                    message: '上傳成功',
                    type: 'success',
                    offset: 200,
                    duration: 1000
                });
                this.attendanceDialogVisible=false;
            }
        }
    })
</script>
</html>

4.后端

創(chuàng)建實體對象指定列下標號（也可以使用列名）這里使用lombok簡化

@Data
public class User{
    /*
     * 0 代表當前excel中的第一列瞬逊，下面依次對應(yīng)
     */
    @ExcelProperty(index = 0)
    private String username;
    @ExcelProperty(index = 1)
    private String password;
    @ExcelProperty(index = 2)
    private String dept;
}

UserMapper.xml

<?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.bcm.task.dao.userDao">

    <!--批量插入-->
    <insert id="insertList">
        insert into user(username, password, dept, upTime)
        values
        <foreach collection="list" item="user" separator=",">
            (
            #{user.username}, #{user.password},#{user.dept}, #{user.upTime}
            )
        </foreach>
    </insert>

    <!--批量刪除-->
    <delete id="batchDeleteByID">
        delete from user where username in
        (
          <foreach collection="list" item="username" separator=",">
              #{username}
          </foreach>
        )
    </delete>
</mapper>

創(chuàng)建數(shù)據(jù)持久化層

@Mapper
@Repository
public interface UserDao {
  Integer insertList(List<User> users);
}

創(chuàng)建監(jiān)聽器

public class UserDataListener extends AnalysisEventListener<User> {

    @Autowired
    UserDao userDao;
    /*
     * 創(chuàng)建一個List用來存放數(shù)據(jù)
     */
    List<User> userList = new ArrayList<>();

    private UserDao UserDao;
    private String upTime;


    public UserDataListener(UserDao userDao, String upTime) {
        this.userDao = userDao;
        this.upTime = upTime;
    }

    /**
     * 每一條數(shù)據(jù)的解析都會調(diào)用
     * @param user
     * @param context
     */
    @Override
    public void invoke(User user, AnalysisContext context) {
        user.setUpTime(upTime);
        UserList.add(user);
        /**
         * 數(shù)據(jù)量超過一定量后存儲數(shù)據(jù)庫显歧，防止過多數(shù)據(jù)在內(nèi)存，容易OOM  
         */
        if (userList.size()>= 300) {
            saveData();
            userList.clear();
        }
        saveData();
    }

    /**
     * 所有數(shù)據(jù)解析完成后會調(diào)用此方法
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    /*
     * 存儲數(shù)據(jù)
     */
    private void saveData() {
        userDao.insertList(userList);
    }
}

創(chuàng)建服務(wù)層

public interface IUserService {

    /*
     * 上傳接口
     */
    void doUpload(String upTime, MultipartFile file) throw IOException; 
}

@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    UserDao userDao;

    public void doUpload(String upTime, MultipartFile file) throw IOException {
        User user = new User();
        user.setUpTime(upTime);
        /*
         * headRowNumber 等于1 表示從excel第二行開始讀取數(shù)據(jù)（標題忽略）
         */
        EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userDao, upTime)).sheet().headRowNumber(1).doRead();
    }
}

創(chuàng)建控制器

@Controller
@RequestMapping(value = "/test")
public class TestController {

    @Autowired
    IUserService userService;

    // 去上傳頁面
    @GetMapping("toUpload")
    public String toUpload() {
        return "common/toUpload";
    }
    //上傳
    @PostMapping("doUpload")
    @ResponseBody
    public String doUpload(@RequestParam String upTime, @RequestParam MultipartFile file) throws IOException {
        userService.doUpload(upTime, file);
        return "上傳成功";
    }
}

不足

后端沒有對文件進行相應(yīng)的校驗确镊，如文件類型是否為xls 或 xlsx士骤，是否是對應(yīng)模板的數(shù)據(jù)......

EasyExcel的實現(xiàn)記錄

EasyExcel的實現(xiàn)記錄

需求

思路

操作

不足