首先是前端页面代码，需要导入xlsx的依赖

npm install xlsx file-saver -S
npm install script-;oader -S -D

然后是前端的两个vue文件

1.index.vue

<template>
  <div>
    <input
      ref="excel-upload-input"
      class="excel-upload-input"
      type="file"
      accept=".xlsx, .xls"
      @change="handleClick"
    />
    <div
      class="drop"
      @drop="handleDrop"
      @dragover="handleDragover"
      @dragenter="handleDragover"
    >
      Drop excel file here or
      <el-button
        :loading="loading"
        style="margin-left: 16px"
        size="mini"
        type="primary"
        @click="handleUpload"
      >
        Browse
      </el-button>
    </div>
  </div>
</template>

<script>
import XLSX from "xlsx";

export default {
  props: {
    beforeUpload: Function, // eslint-disable-line
    onSuccess: Function, // eslint-disable-line
  },
  data() {
    return {
      loading: false,
      excelData: {
        header: null,
        results: null,
      },
    };
  },
  methods: {
    generateData({ header, results }) {
      this.excelData.header = header;
      this.excelData.results = results;
      this.onSuccess && this.onSuccess(this.excelData);
    },
    handleDrop(e) {
      e.stopPropagation();
      e.preventDefault();
      if (this.loading) return;
      const files = e.dataTransfer.files;
      if (files.length !== 1) {
        this.$message.error("Only support uploading one file!");
        return;
      }
      const rawFile = files[0]; // only use files[0]

      if (!this.isExcel(rawFile)) {
        this.$message.error(
          "Only supports upload .xlsx, .xls, .csv suffix files"
        );
        return false;
      }
      this.upload(rawFile);
      e.stopPropagation();
      e.preventDefault();
    },
    handleDragover(e) {
      e.stopPropagation();
      e.preventDefault();
      e.dataTransfer.dropEffect = "copy";
    },
    handleUpload() {
      this.$refs["excel-upload-input"].click();
    },
    handleClick(e) {
      const files = e.target.files;
      const rawFile = files[0]; // only use files[0]
      if (!rawFile) return;
      this.upload(rawFile);
    },
    upload(rawFile) {
      this.$refs["excel-upload-input"].value = null; // fix can't select the same excel

      if (!this.beforeUpload) {
        this.readerData(rawFile);
        return;
      }
      const before = this.beforeUpload(rawFile);
      if (before) {
        this.readerData(rawFile);
      }
    },
    readerData(rawFile) {
      this.loading = true;
      return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = (e) => {
          const data = e.target.result;
          const workbook = XLSX.read(data, { type: "array" });
          const firstSheetName = workbook.SheetNames[0];
          console.log(firstSheetName);
          const worksheet = workbook.Sheets[firstSheetName];
          console.log(worksheet);
          const header = this.getHeaderRow(worksheet);
          console.log(header);
          const results = XLSX.utils.sheet_to_json(worksheet);
          this.generateData({ header, results });
          this.loading = false;
          resolve();
        };
        reader.readAsArrayBuffer(rawFile);
      });
    },
    getHeaderRow(sheet) {
      const headers = [];
      const range = XLSX.utils.decode_range(sheet["!ref"]);
      let C;
      const R = range.s.r;
      /* start in the first row */
      for (C = range.s.c; C <= range.e.c; ++C) {
        /* walk every column in the range */
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
        /* find the cell in the first row */
        let hdr = "UNKNOWN " + C; // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
        headers.push(hdr);
      }
      return headers;
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name);
    },
  },
};
</script>

<style scoped>
.excel-upload-input {
  display: none;
  z-index: -9999;
}
.drop {
  border: 2px dashed #bbb;
  width: 600px;
  height: 160px;
  line-height: 160px;
  margin: 0 auto;
  font-size: 24px;
  border-radius: 5px;
  text-align: center;
  color: #bbb;
  position: relative;
}
</style>

2.导入index.vue的具体文件，其中利用addexcelnearportplan来和后端交互

<template>
  <div>
    <el-breadcrumb separator-class="el-icon-arrow-right">
      <el-breadcrumb-item :to="{ path: '/home' }">首页</el-breadcrumb-item>
      <el-breadcrumb-item>1</el-breadcrumb-item>
      <el-breadcrumb-item>excel测试页面</el-breadcrumb-item>
    </el-breadcrumb>
    <div class="app-container">
      <upload-excel-component
        :on-success="handleSuccess"
        :before-upload="beforeUpload"
      />
      <el-table
        :data="tableData"
        border
        highlight-current-row
        style="width: 100%; margin-top: 20px; vnd.ms-excel.numberformat:yyyy-mm"
      >
        <el-table-column
          v-for="item of tableHeader"
          :key="item"
          :prop="item"
          :label="item"
        />
      </el-table>
    </div>
    <el-button @click="addexcelnearportplan">提交</el-button>
  </div>
</template>
<script>
import UploadExcelComponent from "@/components/UploadExcel/index.vue";

export default {
  name: "UploadExcel",
  components: { UploadExcelComponent },
  data() {
    return {
      tableData: [],
      tableHeader: [],
    };
  },
  methods: {
    addexcelnearportplan() {
      const { data: res } = this.$http.post("addexcelnearportplan", 
        this.tableData,
      );
      console.log(this.tableData);
      if (res != "success") {
        return this.$message.success("上传信息成功");
      }
      this.$message.error("上传信息失败");
    },
    beforeUpload(file) {
      const isLt1M = file.size / 1024 / 1024 < 1;

      if (isLt1M) {
        return true;
      }

      this.$message({
        message: "Please do not upload files larger than 1m in size.",
        type: "warning",
      });
      return false;
    },
    handleSuccess({ results, header }) {
      this.tableData = results;
      this.tableHeader = header;
      console.log(results);
    },
  },
};
</script>

<style lang = 'less'  scoped>
/* 面包屑样式 */
.el-breadcrumb {
  margin-bottom: 15px;
  font-size: 12px;
}
</style>

页面展示：

 excel文件展示

前端页面导入excel文件后显示 

后端利用addexcelnearportplan来接受前端传来的数据

后端分为六层：Bean，Dao，Mapper，Service，ServiceImpl，Controller

 1.Bean层

package com.sport.sports.Bean;

import java.sql.Timestamp;

public class NearPortPlan {
    String orderid;//订单号
    int userid;//用户id


    public NearPortPlan() {
    }


    public void setOrderid(String orderid) {
        this.orderid = orderid;
    }


    public String getOrderid() {
        return orderid;
    }

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }


    public NearPortPlan(String orderid, int userid,) {
        this.orderid = orderid;
        this.userid = userid;
    }

    @Override
    public String toString() {
        return "NearPortPlan{" +
                "orderid='" + orderid + '\'' +
                ", userid=" + userid +
                '}';
    }
}

2.Dao层 接受的是List

package com.sport.sports.dao;

import com.sport.sports.Bean.NearPortPlan;
import com.sport.sports.Bean.PortDailyWorkSchedule;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface NearPortPlanDao {
    public int insertlistnearportplan(List<NearPortPlan> list);
}

 3.Mapper层  和Dao层对应的数据库操作

<?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.sport.sports.dao.NearPortPlanDao">
        
    <insert id="insertlistnearportplan" parameterType="java.util.List" useGeneratedKeys="false">
        insert into nearportplan
        (orderid,userid)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.orderid},
            #{item.userid},
            )
        </foreach>
    </insert>
</mapper>

4.Controller层  用于接收前端的数据 

   @RequestMapping("/addexcelnearportplan")
    public String addexcelnearportplan(@RequestBody List<NearPortPlan> nearPortPlanList){
        System.out.println(nearPortPlanList);
        return nearPortPlanService.addexcelnearportplan(nearPortPlanList);
    }

5.Service层 接口书写

package com.sport.sports.Service;

import com.sport.sports.Bean.NearPortPlan;
import org.springframework.web.bind.annotation.RequestBody;

import java.util.List;

public interface NearPortPlanService {

    public String addexcelnearportplan(List<NearPortPlan> nearPortPlanList);
}

6.ServiceImpl层 对于Service层的接口具体的去实现

package com.sport.sports.Service.ServiceImpl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sport.sports.Bean.NearPortPlan;
import com.sport.sports.Service.NearPortPlanService;
import com.sport.sports.dao.BoxMessageDao;
import com.sport.sports.dao.NearPortPlanDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;

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

@Service
public class NearPortPlanServiceImpl implements NearPortPlanService {


    @Autowired
    NearPortPlanDao ndao;

    @Override
    public String addexcelnearportplan(List<NearPortPlan> nearPortPlanList){
        int i = ndao.insertlistnearportplan(nearPortPlanList);
        return "success";
    }
}

最终点击前端的提交按钮即可把excel中的数据传入到数据库中

不足之处

传入的excel里面的表头（header）需要属于后端Bean中的字段，因为后端是用List<Bean >来接收的，所以只能识别同样名称的表头的文件。这个是最大的不足，还会继续完善。

-------------------------------------------------------------------------------------20210723修改前端Key的操作

在前端页面可以进行转换，让不足之处排除

首先通过handleSuccess调用写好的rename方法

    handleSuccess({ results, header }) {
      this.tableData = results;
      this.results = results;
      this.tableHeader = header;
      console.log(results);
      this.rename(results);
      console.log(results);
    },

rename方法

    rename(result) {
      const renamelist = {
        订单号: "orderid",
        用户账号: "userid",
      };
      // console.log(renamelist["货名"]);
      for (var i in this.results) {
        for (var j in this.results[i]) {
          // if (j == "订单号") {
          //   this.results[i]["orderid"] = this.results[i][j];
          //   delete this.results[i]["订单号"];
          console.log(j);
          if (renamelist[j]) {
            this.results[i][renamelist[j]] = this.results[i][j];
            delete this.results[i][j];
          }
        }
      }
    },

最后成功改名