Vue + element UI 前台解析excel批量导入到后台

Vue：

1.安装开发环境 安装webpack

npm install

npm install -g webpack

2.安装vue-cli （脚手架）

npm install -g vue-cli

3.安装elementui依赖

npm i element-ui@next -D

在main.js中添加：

//使用EUI组件
import ElementUI from 'element-ui'
import 'element-ui/lib/theme-chalk/index.css'
Vue.use(ElementUI)

4.getExceldata.vue内容

<template>：

<el-upload
                  class="upload"
                  action=""
                  :on-change="handleChange"
                  :on-remove="handleRemove"
                  :on-exceed="handleExceed"
                  :limit="limitUpload"
                  accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
                  :auto-upload="false"
                >
                  <el-button size="small" type="primary">批量导入</el-button>
                  <!-- <div slot="tip" class="el-upload__tip">只 能 上 传 xlsx / xls 文 件</div> -->
                </el-upload>

<script>:因为要做excel解析，所以要用到xlsx。

import XLSX from "xlsx";
Vue.use(XLSX);

return:

      limitUpload: 1,
      fileTemp: null,
      file: null,
      da: [],
      dalen: 0,
      getExceldata: {
          allExceldata: [],
        },

uploadExcel接口:

//getExcel接口
export const uploadExcel = (data) => {
  return axios.request({
    //接口url
    url: 'xxxx/xxxxx/uploadexcel',
    method: 'post',
    data
  })
}

methods:

handleChange(file, fileList) {
      this.fileTemp = file.raw;
      if (this.fileTemp) {
        if (
          this.fileTemp.type ==
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
          this.fileTemp.type == "application/vnd.ms-excel"
        ) {
          this.importfxx(this.fileTemp);
          //将数据传给后台
          this.douploadExcel();
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请删除后重新上传！",
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！",
        });
      }
    },
    handleExceed() {
      this.$message({
        type: "warning",
        message: "超出最大上传文件数量的限制！",
      });
      return;
    },
    handleRemove(file, fileList) {
      this.fileTemp = null;
    },
    importfxx(obj) {
      let _this = this;
      let inputDOM = this.$refs.inputer;
      // 通过DOM取文件数据

      this.file = event.currentTarget.files[0];

      var rABS = false; //是否将文件读取为二进制字符串
      var f = this.file;

      var reader = new FileReader();
      //if (!FileReader.prototype.readAsBinaryString) {
      FileReader.prototype.readAsBinaryString = function (f) {
        var binary = "";
        var rABS = false; //是否将文件读取为二进制字符串
        var pt = this;
        var wb; //读取完成的数据
        var outdata;
        var reader = new FileReader();
        const sheetspacing = {
          defval: "", //如果遇到excel中有控制空值，可以给他赋值"，不然数据中的空值的key将不显示
        };
        reader.onload = function (e) {
          var bytes = new Uint8Array(reader.result);
          var length = bytes.byteLength;
          for (var i = 0; i < length; i++) {
            binary += String.fromCharCode(bytes[i]);
          }
          var XLSX = require("xlsx");
          if (rABS) {
            wb = XLSX.read(btoa(fixdata(binary)), {
              //手动转化
              type: "base64",
            });
          } else {
            wb = XLSX.read(binary, {
              type: "binary",
            });
          }
          outdata = XLSX.utils.sheet_to_json(
            wb.Sheets[wb.SheetNames[0]],
            sheetspacing
          ); //outdata就是你想要的东西
          console.log("未处理的原始数据如下：");
          console.log(outdata);
          //将获取的数据赋值
          _this.formModel.getExceldata.allExceldata = outdata;

          //因为outdata已经将数据获取了，所以数据处理看个人需求
          //此处可对数据进行处理
          // let arr = [];
          // outdata.map((v) => {
          //   let obj = {};
          //   obj.id = v["id"];
          //   obj.cardnumber = v["CardNumber"];
          //   obj.operator = v["Operator"];
          //   obj.province = v["Province"];
          //   obj.api = v["API"];
          //   obj.purchase = v["Purchase"];
          //   obj.imei = v["IMEI"];
          //   obj.watermeter = v["WaterMeter"];
          //   obj.cardtype = v["CardType"];
          //   obj.remark = v["Remark"];
          //   obj.status = v["Status"];
          //   obj.isdeleted = v["IsDeleted"];
          //   obj.code = v["Code"];
          //   arr.push(obj);
          // });
          // _this.da = arr;
          // _this.dalen = arr.length;
          // return arr;
        };
        reader.readAsArrayBuffer(f);
      };
      if (rABS) {
        reader.readAsArrayBuffer(f);
      } else {
        reader.readAsBinaryString(f);
      }
    },
    //传数据给后台的接口
    douploadExcel() {
          uploadExcel(this.formModel.getExceldata).then((res) => {
            if (res.data.code === 200) {
              this.$Message.success(res.data.message);
              //传入成功后，刷新界面
              this.loadMobileQueryList();
            } else {
              this.$Message.warning(res.data.message);
            }
          });
        },

C# webapi

创建获取exceldata的类：

namespace xxxx.GetExcelModel
{

    public class GetExcelModel
    {
        public List<ExcelCreateViewModel> allExceldata { get; set; }
    }
}

接收excel数据的接口，接收并传入sql数据库：

        /// <summary>
        /// upload接收excel数据
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(200)]
        //接收并处理excel数据
        public IActionResult uploadExcel(GetExcelModel model)
        {
            var response = ResponseModelFactory.CreateInstance;
            using (_dbContext)
            {
                //for循环插入数据库 也可以用insert直接插入
                for(int i = 0; i < model.allExceldata.Count; i++) {
                    //如果在sql中设置了id自增，传入的数据中最好不要带有id
                    var entity = _mapper.Map<ExcelCreateViewModel, DncMobileQuery>(model.allExceldata[i]);
                    _dbContext.DncMobileQuery.Add(entity);
                    _dbContext.SaveChanges();
                }
                
                response.SetSuccess();
                return Ok(response);
            }
        }

取材自：https://blog.csdn.net/a736755244/article/details/99568133