Vue execl表格导入导出实际，工作中的业务逻辑

web开发
251 阅读1分钟

下载模板

DOM结构

        <div>
          <el-button type="primary" @click="thetemplate()">execl模板下载</el-button>
        </div>

methods

    // 向后端发送请求，拿到execl文件
    thetemplate() {
      this.$http({
        url: this.$http.adornUrl(
          "/terminal/doorLock/poi-lock-empower-download"
        ),
        method: "post",
        data: this.$http.adornData({}),
        responseType: "blob"
      }).then(({ data }) => {
        // 下载文件
        this.downloadFile(data);
      });
    },
    // 通过a标签特性，实现文件下载
    downloadFile(data) {
      // 文件导出
      if (!data) {
        return;
      }
      let url = window.URL.createObjectURL(new Blob([data]));
      let link = document.createElement("a");
      link.style.display = "none";
      link.href = url;
      link.setAttribute("download", "门锁导入批量模板.xlsx");
      document.body.appendChild(link);
      link.click();
    },

下载的模板表格样式

文件预览

安装xlsx模块

npm i xlsx -s

在文件中引入xlsx模块

import XLSX from "xlsx";

业务逻辑

DOM

      <el-upload
        class="upload-demo"
        action
        :on-change="handleChange"
        :on-exceed="handleExceed"
        :on-remove="handleRemove"
        :file-list="fileListUpload"
        :limit="limitUpload"
        accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
        :auto-upload="false"
      >
        <el-button type="primary" @click="handleclickto()">浏览文件</el-button>
      </el-upload>

data

  data() {
    return {
      fileListUpload: [],
      limitUpload: 1,
      table1: true,
      changestudentlist: [],
      studentlist: []
    };
  },

methods

getdata方法 将execl里面的时间转换为YYYY-mm-dd hh:mm:ss格式

  • 这里就是将execl转化为我们正常的时间，不懂可以跳过直接用就行
    // 将execl里面的时间转换为YYYY-mm-dd格式
    getdata(date) {
      // let date = 44120
      date = Math.round(date * 100000) / 100000;
      var Minustime = 1.33333 * 24 * 3600000;
      date = new Date(date * 24 * 3600000 - Minustime);
      date.setYear(date.getFullYear() - 70);
      // 根据需求更改 YYYY-mm-dd，YYYY-mm-dd hh:mm:ss...
      return this.dateFormat("YYYY-mm-dd", date);
    },
    // 转化时间格式
    dateFormat(fmt, date) {
      let ret;
      const opt = {
        "Y+": date.getFullYear().toString(), // 年
        "m+": (date.getMonth() + 1).toString(), // 月
        "d+": date.getDate().toString(), // 日
        "H+": date.getHours().toString(), // 时
        "M+": date.getMinutes().toString(), // 分
        "S+": date.getSeconds().toString() // 秒
        // 有其他格式化字符需求可以继续添加，必须转化成字符串
      };
      // console.log(opt)
      for (let k in opt) {
        ret = new RegExp("(" + k + ")").exec(fmt);
        if (ret) {
          fmt = fmt.replace(
            ret[1],
            ret[1].length == 1 ? opt[k] : opt[k].padStart(ret[1].length, "0")
          );
        }
      }
      return fmt;
    },

handleChange 方法,文件状态改变时调用的函数

    // 导入excel相关
    handleChange(file, fileList) {
      console.log(1);
      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.changestudentlist = [];
          this.table1 = true;
          // 对导入文件数据进行处理的函数
          this.importfxx(this.fileTemp);
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请删除后重新上传！"
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！"
        });
      }
    },

handleExceed 方法,文件超出个数限制时调用的函数，和handleChange 方法基本一致

//导入excel相关
    handleExceed(file, fileList) {
      console.log(2);
      fileList[0].name = file[0].name;
      this.fileTemps = file[0];
      if (this.fileTemps) {
        if (
          this.fileTemps.type ==
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
          this.fileTemps.type == "application/vnd.ms-excel"
        ) {
          this.changestudentlist = [];
          this.table1 = true;
          this.importfxx(this.fileTemps);
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请删除后重新上传！"
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！"
        });
      }
    },

重点！！！,importfxx 方法，处理导入文件中的数据

// 处理文件信息
importfxx(obj) {
      console.log(obj);
      let _this = this;
      // 通过DOM取文件数据
      this.file = obj;
      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();
        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就是你想要的东西
          outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]); 
          // 这个数据长得就是[{'楼栋':1,'房间号':1,..........}],这时候就要对调
          this.da = [...outdata];
          let arr = [this.da];
          console.log(this.da);
          // 将表格数据，转化为json格式数据
          this.da.map(v => {
            let obj = {};
            obj.buildingName = v["楼栋"];
            obj.roomName = v["房 间 号"];
            obj.cardNumber = v["卡号"];
            obj.endTime = v["有效时间"];
            obj.monday = v["星期一是否可开"];
            obj.tuesday = v["星期二是否可开"];
            obj.wednesday = v["星期三是否可开"];
            obj.thursday = v["星期四是否可开"];
            obj.friday = v["星期五是否可开"];
            obj.saturday = v["星期六是否可开"];
            obj.sunday = v["星期日是否可开"];
            obj.backLock = v["反锁开门"];
            obj.holidayTimeStageIndex = v["假日时段"];
            obj.mondayTimeStageIndex = v["周一时段"];
            obj.tuesdayTimeStageIndex = v["周二时段"];
            obj.wednesdayTimeStageIndex = v["周三时段"];
            obj.thursdayTimeStageIndex = v["周四时段"];
            obj.fridayTimeStageIndex = v["周五时段"];
            obj.saturdayTimeStageIndex = v["周六时段"];
            obj.sundayTimeStageIndex = v["周日时段"];
            obj.password = v["密码"];
            obj.doorType = v["门锁类型"];
            obj.userNumber = v["人员编号"];
            arr.push(obj);
          });
          // 将数据负责
          _this.studentlist = [...arr];
          /** 下面这些就是根据业务需求进行处理，我这边是楼栋名称、房间号、卡号、结束时间都是必须有的，如果表格没填写这个字段，我这边过滤掉 */
          // 如果表格中有时间数据，必须处理，我这边使用的是上方写的getdata方法
          let arrto = JSON.parse(JSON.stringify(_this.studentlist));
          console.log(arrto);
          let add11 = [];
          if (arrto.length > 0) {
            arrto.map(item => {
              if (
                item.buildingName == undefined ||
                item.roomName == undefined ||
                item.cardNumber == undefined ||
                item.endTime == undefined
              ) {
                return false;
              }
              // 对execl中的时间数据做处理，上方写的getdata方法
              item.endTime = _this.getdata(item.endTime);
              add11.push(item);
            });
          }
          // 复制给到你需要渲染表格数组
          _this.changestudentlist = add11;
        };
        reader.readAsArrayBuffer(f);
      };

      if (rABS) {
        reader.readAsArrayBuffer(f);
      } else {
        reader.readAsBinaryString(f);
      }
    },

导入的数据已经拿到，这时候就可以渲染列表了

      <el-table
        v-loading="loading"
        :data="changestudentlist"
        ref="table"
        border
        highlight-current-row
        style="width: 100%"
        :header-cell-style="{background:'#45c2b5',color:'#fff','text-align':'center'}"
      >
        <el-table-column prop="buildingName" label="楼栋" align="center"></el-table-column>
        <el-table-column prop="roomName" label="房间号" align="center"></el-table-column>
        <el-table-column prop="cardNumber" label="卡号" align="center"></el-table-column>
        <!-- 有效时间 -->
        <el-table-column prop="endTime" label="结束时间" align="center"></el-table-column>
        <el-table-column prop="backLock" label="反锁开门" align="center" width="35px"></el-table-column>
        <!-- 开门-礼拜 -->
        <el-table-column prop="Sunday" label="星期" align="center">
          <el-table-column prop="monday" label="一" align="center" width="35px"></el-table-column>
          <el-table-column prop="tuesday" label="二" align="center" width="35px"></el-table-column>
          <el-table-column prop="wednesday" label="三" align="center" width="35px" :disabled="true"></el-table-column>
          <el-table-column prop="thursday" label="四" align="center" width="35px"></el-table-column>
          <el-table-column prop="friday" label="五" align="center" width="35px"></el-table-column>
          <el-table-column prop="saturday" label="六" align="center" width="35px"></el-table-column>
          <el-table-column prop="sunday" label="日" align="center" width="35px"></el-table-column>
        </el-table-column>
        <!-- 星期时段 -->
        <el-table-column prop="SundayTime" label="星期时段" align="center">
          <el-table-column prop="mondayTimeStageIndex" label="一" align="center" width="38px"></el-table-column>
          <el-table-column prop="tuesdayTimeStageIndex" label="二" align="center" width="38px"></el-table-column>
          <el-table-column prop="wednesdayTimeStageIndex" label="三" align="center" width="38px"></el-table-column>
          <el-table-column prop="thursdayTimeStageIndex" label="四" align="center" width="38px"></el-table-column>
          <el-table-column prop="fridayTimeStageIndex" label="五" align="center" width="38px"></el-table-column>
          <el-table-column prop="saturdayTimeStageIndex" label="六" align="center" width="38px"></el-table-column>
          <el-table-column prop="sundayTimeStageIndex" label="日" align="center" width="38px"></el-table-column>
        </el-table-column>
        <!-- 假日时段 -->
        <el-table-column prop="holidayTimeStageIndex" label="假日时段" align="center" width="50px"></el-table-column>
        <el-table-column prop="password" label="密码" align="center"></el-table-column>
        <el-table-column prop="doorType" label="门锁类型" align="center"></el-table-column>
        <el-table-column prop="userNumber" label="人员编号" align="center"></el-table-column>
      </el-table>

预览效果

导入数据

DOM

    <div class="packge_batch">
          <el-button type="primary" @click="packgebatchto()">导入数据</el-button>
    </div>

methods

// 文件提交
    packgebatchto() {
      if (this.changestudentlist.length == 0) {
        this.$message({
          message: "没有批量数据",
          type: "warning"
        });
        return false;
      }
      // 上传数据请求
      this.$http({
        url: this.$http.adornUrl("/terminal/doorLock/empower-name-excel-add"),
        method: "post",
        data: this.$http.adornData({
          messageContent: {
              nameExcelList:this.changestudentlist,
              unitGuid:this.changeschoolid
          },
          
          messageType: 0
        })
      }).then(({ data }) => {
        // console.log(data)
        if (data.messageCode == 500) {
          this.$message({
            message: data.errorMessage,
            type: "warning"
          });
        }
        if (data.messageCode == 200) {
          this.table1 = false;
          this.tablelist = data.messageContent;
          this.$message({
            message: "批量录入成功",
            type: "success"
          });
        }
      });
    },

完整代码

<template>
  <div>
    <div class="grid-content bg-purple">
      <div class="header_select">
        <div>
          <el-button type="primary" @click="thetemplate()">execl模板下载</el-button>
        </div>
        <div class="bulk_import">
          <el-upload
            class="upload-demo"
            action
            :on-change="handleChange"
            :on-exceed="handleExceed"
            :on-remove="handleRemove"
            :file-list="fileListUpload"
            :limit="limitUpload"
            accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
            :auto-upload="false"
          >
            <el-button type="primary" @click="handleclickto()">浏览文件</el-button>
          </el-upload>
        </div>
        <div class="packge_batch">
          <el-button type="primary" @click="packgebatchto()">导入数据</el-button>
        </div>
      </div>
      <el-table
        v-loading="loading"
        :data="changestudentlist"
        ref="table"
        border
        highlight-current-row
        style="width: 100%"
        :header-cell-style="{background:'#45c2b5',color:'#fff','text-align':'center'}"
      >
        <el-table-column prop="buildingName" label="楼栋" align="center"></el-table-column>
        <el-table-column prop="roomName" label="房间号" align="center"></el-table-column>
        <el-table-column prop="cardNumber" label="卡号" align="center"></el-table-column>
        <!-- 有效时间 -->
        <el-table-column prop="endTime" label="结束时间" align="center"></el-table-column>
        <el-table-column prop="backLock" label="反锁开门" align="center" width="35px"></el-table-column>
        <!-- 开门-礼拜 -->
        <el-table-column prop="Sunday" label="星期" align="center">
          <el-table-column prop="monday" label="一" align="center" width="35px"></el-table-column>
          <el-table-column prop="tuesday" label="二" align="center" width="35px"></el-table-column>

          <el-table-column prop="wednesday" label="三" align="center" width="35px" :disabled="true"></el-table-column>
          <el-table-column prop="thursday" label="四" align="center" width="35px"></el-table-column>

          <el-table-column prop="friday" label="五" align="center" width="35px"></el-table-column>

          <el-table-column prop="saturday" label="六" align="center" width="35px"></el-table-column>

          <el-table-column prop="sunday" label="日" align="center" width="35px"></el-table-column>
        </el-table-column>

        <!-- 星期时段 -->
        <el-table-column prop="SundayTime" label="星期时段" align="center">
          <el-table-column prop="mondayTimeStageIndex" label="一" align="center" width="38px"></el-table-column>
          <el-table-column prop="tuesdayTimeStageIndex" label="二" align="center" width="38px"></el-table-column>

          <el-table-column prop="wednesdayTimeStageIndex" label="三" align="center" width="38px"></el-table-column>

          <el-table-column prop="thursdayTimeStageIndex" label="四" align="center" width="38px"></el-table-column>
          <el-table-column prop="fridayTimeStageIndex" label="五" align="center" width="38px"></el-table-column>
          <el-table-column prop="saturdayTimeStageIndex" label="六" align="center" width="38px"></el-table-column>
          <el-table-column prop="sundayTimeStageIndex" label="日" align="center" width="38px"></el-table-column>
        </el-table-column>
        <!-- 假日时段 -->
        <el-table-column prop="holidayTimeStageIndex" label="假日时段" align="center" width="50px"></el-table-column>
        <el-table-column prop="password" label="密码" align="center"></el-table-column>
        <el-table-column prop="doorType" label="门锁类型" align="center"></el-table-column>
        <el-table-column prop="userNumber" label="人员编号" align="center"></el-table-column>
      </el-table>
    </div>
  </div>
</template>

<script>
import FileSaver from "file-saver";
import XLSX from "xlsx";
import { JSEncrypt } from "jsencrypt";
export default {
  data() {
    return {
      loading: false,
      fileListUpload: [],
      limitUpload: 1,
      table1: true,
      changestudentlist: [],
      studentlist: []
    };
  },
  computed: {
    changeschoolid: {
      get() {
        return this.$store.state.user.changeschoolid;
      }
    }
  },
  methods: {
    handleSelectionChange() {},
    //excel模板下载
    thetemplate() {
      this.$http({
        url: this.$http.adornUrl(
          "/terminal/doorLock/poi-lock-empower-download"
        ),
        method: "post",
        data: this.$http.adornData({}),
        responseType: "blob"
      }).then(({ data }) => {
        console.log(data);
        this.downloadFile(data);
      });
    },
    // 文件下载
    downloadFile(data) {
      // 文件导出
      if (!data) {
        return;
      }
      let url = window.URL.createObjectURL(new Blob([data]));
      let link = document.createElement("a");
      link.style.display = "none";
      link.href = url;
      link.setAttribute("download", "门锁导入批量模板.xlsx");
      document.body.appendChild(link);
      link.click();
    },
    //导入excel相关
    handleRemove(file, fileList) {
      this.changestudentlist = [];
      this.fileTemp = null;
    },
    //导入excel相关
    handleChange(file, fileList) {
      console.log(1);
      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.changestudentlist = [];
          this.table1 = true;
          this.importfxx(this.fileTemp);
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请删除后重新上传！"
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！"
        });
      }
    },
    //导入excel相关
    handleExceed(file, fileList) {
      console.log(2);
      fileList[0].name = file[0].name;
      this.fileTemps = file[0];
      if (this.fileTemps) {
        if (
          this.fileTemps.type ==
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
          this.fileTemps.type == "application/vnd.ms-excel"
        ) {
          this.changestudentlist = [];
          this.table1 = true;
          this.importfxx(this.fileTemps);
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请删除后重新上传！"
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！"
        });
      }
    },
    // 时间转换
    getdata(date) {
      // let date = 44120
      date = Math.round(date * 100000) / 100000;
      var Minustime = 1.33333 * 24 * 3600000;
      date = new Date(date * 24 * 3600000 - Minustime);
      date.setYear(date.getFullYear() - 70);
      return this.dateFormat("YYYY-mm-dd", date);
    },
    dateFormat(fmt, date) {
      let ret;
      const opt = {
        "Y+": date.getFullYear().toString(), // 年
        "m+": (date.getMonth() + 1).toString(), // 月
        "d+": date.getDate().toString(), // 日
        "H+": date.getHours().toString(), // 时
        "M+": date.getMinutes().toString(), // 分
        "S+": date.getSeconds().toString() // 秒
        // 有其他格式化字符需求可以继续添加，必须转化成字符串
      };
      // console.log(opt)
      for (let k in opt) {
        ret = new RegExp("(" + k + ")").exec(fmt);
        if (ret) {
          fmt = fmt.replace(
            ret[1],
            ret[1].length == 1 ? opt[k] : opt[k].padStart(ret[1].length, "0")
          );
        }
      }
      return fmt;
    },
    // 处理文件信息
    importfxx(obj) {
      console.log(obj);
      let _this = this;
      // 通过DOM取文件数据
      this.file = obj;
      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();
        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]]); //outdata就是你想要的东西
          this.da = [...outdata];
          let arr = [this.da];
          console.log(this.da);
          this.da.map(v => {
            let obj = {};
            obj.buildingName = v["楼栋"];
            obj.roomName = v["房 间 号"];
            obj.cardNumber = v["卡号"];
            obj.endTime = v["有效时间"];
            obj.monday = v["星期一是否可开"];
            obj.tuesday = v["星期二是否可开"];
            obj.wednesday = v["星期三是否可开"];
            obj.thursday = v["星期四是否可开"];
            obj.friday = v["星期五是否可开"];
            obj.saturday = v["星期六是否可开"];
            obj.sunday = v["星期日是否可开"];
            obj.backLock = v["反锁开门"];
            obj.holidayTimeStageIndex = v["假日时段"];
            obj.mondayTimeStageIndex = v["周一时段"];
            obj.tuesdayTimeStageIndex = v["周二时段"];
            obj.wednesdayTimeStageIndex = v["周三时段"];
            obj.thursdayTimeStageIndex = v["周四时段"];
            obj.fridayTimeStageIndex = v["周五时段"];
            obj.saturdayTimeStageIndex = v["周六时段"];
            obj.sundayTimeStageIndex = v["周日时段"];
            obj.password = v["密码"];
            obj.doorType = v["门锁类型"];
            obj.userNumber = v["人员编号"];
            arr.push(obj);
          });
          _this.studentlist = [...arr];
          let arrto = JSON.parse(JSON.stringify(_this.studentlist));
          console.log(arrto);
          let add11 = [];
          if (arrto.length > 0) {
            arrto.map(item => {
              if (
                item.buildingName == undefined ||
                item.roomName == undefined ||
                item.cardNumber == undefined ||
                item.endTime == undefined
              ) {
                return false;
              }
              item.endTime = _this.getdata(item.endTime);
              add11.push(item);
            });
          }
          _this.changestudentlist = add11;
        };
        reader.readAsArrayBuffer(f);
      };

      if (rABS) {
        reader.readAsArrayBuffer(f);
      } else {
        reader.readAsBinaryString(f);
      }
    },
    // 文件提交
    packgebatchto() {
      if (this.changestudentlist.length == 0) {
        this.$message({
          message: "没有批量数据",
          type: "warning"
        });
        return false;
      }
      this.$http({
        url: this.$http.adornUrl("/terminal/doorLock/empower-name-excel-add"),
        method: "post",
        data: this.$http.adornData({
          messageContent: {
              nameExcelList:this.changestudentlist,
              unitGuid:this.changeschoolid
          },
          
          messageType: 0
        })
      }).then(({ data }) => {
        // console.log(data)
        if (data.messageCode == 500) {
          this.$message({
            message: data.errorMessage,
            type: "warning"
          });
        }
        if (data.messageCode == 200) {
          this.table1 = false;
          this.tablelist = data.messageContent;
          this.$message({
            message: "批量录入成功",
            type: "success"
          });
        }
      });
    },
    handleclickto() {}
  }
};
</script>
<style lang="scss" scoped>
.header_select {
  height: 80px;
  // background-color: #F5F5F5;
  width: 100%;
  display: flex;
  align-items: center;
  padding: 20px;
  .cardnumber,
  .username,
  .usernumber {
    display: flex;
    align-items: center;
    padding-left: 10px;
    width: 200px;
    div:nth-child(1) {
      width: 40px;
    }
  }
  .searchbutton {
    // height:36px;
    padding-left: 10px;
    display: flex;
    flex: 1;
    justify-content: space-between;
    .addstudent {
      padding-right: 20px;
    }
    .el-checkbox {
      // align-self: left;
      width: 150px;
      margin-top: 20px;
    }
  }
  .bulk_import {
    padding-left: 20px;
    .upload-demo {
      display: flex;
      align-items: center;
      /deep/.el-upload-list__item-name {
        margin-right: 22px;
      }
    }
  }
  .packge_batch {
    padding-left: 20px;
  }
}
</style>
avatar
文章
阅读
粉丝