[大数据]vue+springboot实现excel数据表格导入mysql数据库

前端VUE界面代码

1，首先vue要安装element ui依赖

<template>
    <div>
      <el-upload
        class="upload-demo"
        drag
        action="http://localhost:8888/index/Excel"
        multiple>
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
        <div class="el-upload__tip" slot="tip">只能上传excel文件，且不超过500kb</div>
      </el-upload>
    </div>
</template>

后端代码

1引入easyexcel依赖

        <!--easy excel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

2，创建excel表格

3，springboot项目中创建Shop实体类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class Shop{
//    通过@ExcelProperty("商品")进行映射到实体类中的对象
    @ExcelProperty("商品")
    private String name;
    @ExcelProperty("价格")
    private String price;
}

4，service层创建ExcelService类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.xxxx.shangchuan.entity.Shop;

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

public class ExcelService {
    public List<Shop> list(InputStream inputStream) {
        List<Shop> list = new ArrayList<>();
        EasyExcel.read( inputStream ).head( Shop.class ).sheet()
                //通过监听器去解析表格
                .registerReadListener( new AnalysisEventListener<Shop>() {

                    @Override
                    //一行一行的进行解析
                    public void invoke(Shop shop, AnalysisContext analysisContext) {
                        list.add( shop );
                    }

                    @Override
                    //解析完成之后执行此回调
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println( "解析完成" );
                    }
                } ).doRead();//doRead开始执行
                return list;
    }
}

controller层创建ExcelController接收前端请求调用service层实现业务逻辑

import com.xxxx.shangchuan.entity.Shop;
import com.xxxx.shangchuan.service.ExcelService;
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.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@RequestMapping("/index")
@RestController
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @PostMapping("/Excel")
    public String Excel(@RequestParam("file")MultipartFile file){
        try{
            List<Shop> list = excelService.list( file.getInputStream() );
//            迭代器查看是否解析成功
            for(Shop shop : list){
                System.out.println( shop );
            }
        }catch (Exception e){
            return "error";
        }
        return "success";
    }
}

最后控制台可以看到数据已经解析成功

此时解析出来的数据存在于list集合中使用最后使用mybatis动态sql批量insert或者迭代器里一条一条插入mysql也可。。。