springboot整合easyExcel实现简单Excel文件的导入

前端

前端使用vue搭建，并且搭配element UI的小组件。而关于Excel文件的导入通过element组件el-upload实现；

<el-upload
 action="/api/videoprogress/upload" style="display: inline-flex;margin-left: 15px"
                    :show-file-list="false"
                    :before-upload="beforeUpload"
                    :on-success="onSuccess"
                    :on-error="onError"
                    :disabled="importDataDisabled"
                    :headers="headers">
                        <el-button  type="primary" :icon="importDataBtnIcon" :disabled="importDataDisabled">{{importDataBtnText}}</el-button>
                    </el-upload>

后端

后端通过springboot搭建起来，并且连接数据库。
首先需要在pom.xml中导入依赖：

<!-- 引入easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

编写一个监听器：

package com.alarming.manage.listener;

import com.alarming.manage.objectdata.VedioProgress;
import com.alarming.manage.service.VideoProgressService;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

@Slf4j
public class VedioProgressListener extends AnalysisEventListener<VedioProgress> {
    private VideoProgressService videoProgressService;

    public VedioProgressListener(VideoProgressService videoProgressService) {
        this.videoProgressService = videoProgressService;
    }


    private static final int BATCH_COUNT = 5;
    List<VedioProgress> list = new ArrayList<VedioProgress>();


    @Override
    public void invoke(VedioProgress data, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有数据解析完成！");
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        if (!CollectionUtils.isEmpty(list)) {
            for(VedioProgress v:list){
                videoProgressService.saveAll(v);
            }

        }
        log.info("存储数据库成功！");
    }
}

搭建controller层：

package com.alarming.manage.controller;

import com.alarming.manage.listener.VedioProgressListener;
import com.alarming.manage.objectdata.VedioProgress;
import com.alarming.manage.service.VideoProgressService;
import com.alibaba.excel.EasyExcel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@Slf4j
@RestController
@Api(tags = "学习进度接口")
@RequestMapping("/api/videoprogress")
public class VideoProgressController {
    @Autowired
    private VideoProgressService videoProgressService;

    @ApiOperation(value = "学习数据导入接口")
    @PostMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), VedioProgress.class, new VedioProgressListener(videoProgressService)).sheet().doRead();
        return "上传成功";
    }
}

service层：

void saveAll(VedioProgress vedioProgress);

service的实现层：

@Autowired
    private VideoProgressDao videoProgressDao;

    @Override
    public void saveAll(VedioProgress vedioProgress) {
        videoProgressDao.save(vedioProgress);
    }

本次代码的相关实现主要参考了以下这位大佬的代码进而实现的：
基于springboot，使用easyexcel实现excel表格导入数据库与数据库导出到excel表格
数据不便展示，若有需要，可以多多参考以上链接。

此次代码的总结主要记录了关于这段时间对Excel数据的导入相关功能代码的实现。