vue 选择xlsx文件导入显示数据，并进行修改上传。

weixin_44097668

已于 2024-04-01 16:11:42 修改

阅读量592

点赞数 6

文章标签： vue.js 前端 javascript

于 2024-04-01 09:35:21 首次发布

本文链接：https://blog.csdn.net/weixin_44097668/article/details/137072836

版权

1.安装vue-xlsx-table组件

npm install vue-xlsx-table

2.组件文件

import.vue

<template>
  <div id="paste" class="paste">
    <div class="title">
      <div class="tip">
        先填写模板完毕, 再 ①导入模板内容<span style="width:30px;display:inline-block"></span> 1、单次导入行数最多为1000行。2、文件大小：不超过5M。3、带*栏为必填项。4、文件类型：excel文件。
        <span class="right-btn">
          <el-button class="download" size="small" type="primary" @click="downLoadTemplate">
            下载模板
          </el-button>
          <vue-xlsx-table class="importFile" @on-select-file="handleSelectedFile">
            导入文件
          </vue-xlsx-table>
        </span>
      </div>
    </div>
    <div class="hintBox" style="margin-bottom:20px">
      <div>
        全部<span>{{pasteData.length}}</span> 条，
        选中 <span>{{selectList.length}}</span> 条
      </div>
      <div>
        <el-button type="danger" size="mini" :disabled="!isSelect || isNext" @click="remove">删除</el-button>
      </div>
    </div>
    <el-table v-if="maxHeight" ref="pasteTable" id="pasteTable" :data="pasteData" @selection-change="selectionChange" empty-text="点击空白处，再复制（ctrl+v），单次限制导入500条" border style="width: 100%" :max-height="maxHeight">
      <el-table-column type="selection" align="center" width="50">
      </el-table-column>
      <el-table-column v-for="item in label" :key="item.prop" :prop="item.prop" :label="item.label">
        <template slot-scope="{row}">
          <input type="text" :disabled="isNext" v-model="row[item.prop]">
        </template>
      </el-table-column>
    </el-table>
    <div class="footer">
      <el-button size="small" @click="$router.go(-1)" :disabled="loading">返 回</el-button>
      <el-button v-if="!isNext" size="small" type="primary" @click="nextClick">下一步</el-button>
      <el-button v-if="isNext" size="small" @click="prevClick" :disabled="loading">上一步</el-button>
      <el-button v-if="isNext" :loading="loading" size="small" type="primary" @click="confirm">{{loading?'正在处理':'确认'}}</el-button>
    </div>
  </div>
</template>
<script>
import Paste from "./paste.js";
import service from '@/utils/request'
export default {
  props: {
    /* 提交至后端的接口地址 */
    api: {
      type: String,
      default: "",
    },
    /* 下一步点击回调（数组，dom） */
    next: {
      type: Function,
      default () {
        return () => { };
      },
    },
    //模板下载地址
    href: {
      type: String,
      default: "javascript:;",
    },
    //模板文件名称
    download: {
      type: String,
      default: "模板文件.xlsx",
    },
    //表格数组对象key值
    label: {
      type: Array,
      default () {
        return [];
      },
    },
    /* 对excel数据进行特殊处理，比如时间*/
    handleExcel: {
      type: Function | String,
      default () {
        return "";
      },
    },
  },
  data () {
    return {
      pasteData: [],
      isSelect: false,
      selectList: [],
      isNext: false, // 是否可以下一步，将数据上传服务器
      loading: false,
      maxHeight: 0
    };
  },
  mounted () {
    var h = document.getElementById("paste").offsetHeight;
    this.maxHeight = h - 180;
    console.log(h, this.maxHeight)

    this.initPaste();
  },
  methods: {
    randomString (randomLen, min, max) {
      var str = "",
        range = min,
        arr = ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t",
          "u", "v", "w", "x", "y", "z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
          "S", "T", "U", "V", "W", "X", "Y", "Z",];
      // 随机产生
      if (randomLen) {
        range = Math.round(Math.random() * (max - min)) + min;
      }
      for (var i = 0; i < range; i++) {
        var pos = Math.round(Math.random() * (arr.length - 1));
        str += arr[pos];
      }
      return str;
    },
    createId () {
      return this.randomString(false, 10) + new Date().getTime()
    },
    downLoadTemplate () {
      service.download(this.href, {}, this.download)
    },
    clearAllError () {
      var table = window.document.getElementById("pasteTable");
      var cell = table
        .getElementsByTagName("tbody")[0]
        .getElementsByClassName("cell");
      console.log(cell.length);
      for (var i = 0; i < cell.length; i++) {
        cell[i].style.border = "none";
        cell[i].title = "";
      }
    },
    confirm () {
      this.loading = true;
      service(
        {
          url: this.api,
          method: "post",
          data: this.pasteData
        }
      )
        .then((res) => {
          /* 如果返回空数据代表数据正确 */
          if (res && res.code === 200 && res.data && !res.data.length) {
            this.$emit('success', res)
            this.$message.success("导入成功");
            this.$router.back();
          }
          /* 如果返回数组有数据代表数据验证报错 */
          if (res.data && res.data.length) {
            // this.$message.warning("数据格式有误，请修正后再提交");
            var err = res.data[0];
            this.$notify.error({
              title: '错误',
              message: `共${res.data.length}处错误，请修正！第${err.row}行"${this.label[err.col - 1].label}"错误：${err.reason}`
            });
            /* 将后端标记的错误展示到页面中 */
            var table = window.document.getElementById("pasteTable");
            this.clearAllError();
            res.data.map((item) => {
              var row = table.getElementsByTagName("tr")[item.row];
              var cell = row.getElementsByClassName("cell")[item.col];
              cell.style.border = "1px solid red";
              cell.title = item.reason;
            });
            this.isNext = false;
          }
        })
        .finally(() => {
          this.loading = false;
        });
    },
    // 上一步
    prevClick () {
      this.isNext = false;
    },
    // 下一步
    nextClick () {
      this.pasteData.forEach(item => {
        for (var k in item) {
          if (item[k]) {
            item[k] = item[k].replace(/(^\s*)|(\s*$)/g, "")
          }
        }
      })
      this.isNext = this.next(
        this.pasteData,
        window.document.getElementById("pasteTable")
      );
      if (this.isNext) this.clearAllError();
      console.log(this.isNext);
    },
    // 选择文件导入
    handleSelectedFile (convertedData) {
      this.isNext = false; //重置到需要验证的状态
      console.log(convertedData);
      var arr = convertedData.body || [];
      console.log(arr);
      var list = arr.map((item) => {
        var obj = {};
        this.label.map((name) => {
          obj[name.prop] = item[name.label];
        });
        obj.id = this.createId();
        return obj;
      });
      // 去除空格
      list.forEach(item => {

        for (var k in item) {
          if (item[k] && typeof (item[k]) === 'string') {
            item[k] = item[k].replace(/\s*/g, "");
          }
        }
      })
      console.log(list)
      /* 对excel数据进行特殊处理，比如时间*/
      var array = list;
      if (this.handleExcel) {
        array = this.handleExcel(JSON.parse(JSON.stringify(list)));
      }
      this.pasteData = this.pasteData.concat(array);
    },
    // 选择数据
    selectionChange (arr) {
      console.log(arr);
      this.selectList = JSON.parse(JSON.stringify(arr));
      this.isSelect = Boolean(arr.length);
    },
    // 删除数据
    remove () {
      this.selectList.map((item) => {
        console.log(item.id);
        for (var i = 0; i < this.pasteData.length; i++) {
          if (item.id == this.pasteData[i].id) {
            console.log(item.id);
            this.pasteData.splice(i, 1);
            break;
          }
        }
      });
      this.clearAllError();
    },
    // 添加行
    addRow (row) {
      var obj = row || {};
      this.label.map((item) => {
        obj[item.prop] = null;
      });
      obj.id = this.createId();
      this.pasteData.push(obj);
    },
    // 初始化实例，注册复制功能
    initPaste () {
      var key = this.label.map((item) => {
        return item.prop;
      });
      new Paste("paste", key, (arr) => {
        console.log(arr);
        arr.map((item) => {
          item.id = this.createId();
        });

        if (this.handleExcel) {
          arr = this.handleExcel(JSON.parse(JSON.stringify(arr)));
        }
        if (arr.length) {
          this.pasteData = this.pasteData.concat(arr);
          this.$forceUpdate;
        }
      });
    },
  },
};
</script>

<style lang="scss" scoped >
.hintBox {
  background: rgba(240, 156, 156, 0.2);
  border: 1px solid #de2910;
  border-radius: 4px;
  display: flex;
  justify-content: space-between;
  align-items: center;
  padding: 5px 20px;
  > div {
    display: flex;
    align-items: center;
  }
  span {
    color: #de2910;
    font-weight: bold;
    font-size: 14px;
    vertical-align: middle;
  }
}
::v-deep .el-table__body .el-table__row .el-table-column--selection > .cell {
  padding-left: 0;
}
::v-deep .el-table th,
::v-deep .el-table td {
  text-align: center;
}
.paste {
  min-height: calc(100vh - 200px);
  padding-bottom: 60px;
  position: relative;
  .btnBox {
    margin-bottom: 15px;
  }

  .title {
    margin-bottom: 20px;
    height: 32px;
    .tip {
      font-size: 14px;
      color: #252526;

      .red {
        color: #de2910;
        /*     cursor: pointer;
        &:hover {
          opacity: 0.7;
        }*/
      }
    }
    .right-btn {
      float: right;
      .download {
        padding: 2px 10px;
        line-height: 25px;
        margin-right: 10px;

        img {
          vertical-align: middle;
        }
      }
      .importFile {
        ::v-deep button {
          width: 78px;
          padding: 2px 9px;
          width: auto;
          line-height: 25px;
          > img {
            vertical-align: middle;
          }
          &:hover {
            opacity: 0.7;
          }
        }
      }
    }
  }
  input {
    width: 100%;
    min-height: 40px;
    border-color: transparent;
    text-align: center;
    padding: 0 12px;
  }
  input.upload {
    display: none;
  }
  ::v-deep td {
    padding: 0;
    > div.cell {
      min-height: 40px;
      line-height: 40px;
      padding: 0;
    }
  }
  .footer {
    position: absolute;
    width: 100%;
    border-top: 1px solid #ccc;
    bottom: 0;

    padding-top: 15px;

    text-align: center;
    button {
      margin: 0 20px;
    }
  }
}
</style>

paste.js

import { Message } from "element-ui";

export default class Paste {
    list = []
    constructor(id, key, callback) {
        /* 
            id：元素id
            key:包含label和prop的数组，用于生成json数据
            callback:回调（处理后的数组）
        */
        this.id = id;
        this.key = key;
        this.callback = callback;
        this.paste()
    }
    paste() {
        var that = this;
        document.getElementById(this.id).onpaste = function (e) {
            if (that.checkBrowser() !== 'Chrome' && that.checkBrowser() !== "Firefox") {
                Message.error("粘贴功能仅支持谷歌Chrome和火狐Firefox,请尝试excel文件导入");
                return;
            }
           
            if (e.target.tagName !== 'INPUT') {
                if (e.clipboardData) {
                    /* 判断数据格式 */
                    if (e.clipboardData.items.length > 2) {
                        that.handleArray_excel(e.clipboardData);
                    } else {
                        that.handleArray_table(e.clipboardData);
                    }
                } else {
                    Message.error("浏览器版本过低，暂时不支持该功能");
                }
            }
        }
    }
    handleArray_excel(ev) {
        var str = ev.getData('text');
        var arr = str.split(/[\s\n]/);
        // 去除末尾空项
        for (var x = arr.length - 1; x > 0; x--) {
            if (arr[x] === "" || arr[x] === undefined || arr[x] === null) {
                arr.pop()
            } else {
                break
            }
        }
        var newArr = [];
        if (this.checkBrowser() === 'Chrome') {
            for (var x = 0; x < arr.length; x = x + this.key.length + 1) {
                var obj = {};
                this.key.map((label, index) => {
                    obj[label] = arr[x + index] || ""
                })
                newArr.push(obj);
            }
        }
        if (this.checkBrowser() === "Firefox") {
            for (var x = 0; x < arr.length; x = x + this.key.length) {
                var obj = {};
                this.key.map((label, index) => {
                    obj[label] = arr[x + index] || ""
                })
                newArr.push(obj);
            }
        }
        this.list = newArr;
        this.callback(newArr);
    }

    handleArray_table(ev) {
        var str = ev.getData('text');
        console.log(str)
        var arr = str.split("\n");
        var newArr = [];
        for (var x = 0; x < arr.length; x = x + this.key.length) {
            var obj = {};
            this.key.map((label, index) => {
                obj[label] = arr[x + index] || ""
            })
            newArr.push(obj);
        }
        this.list = newArr;
        this.callback(newArr);
    }
    /* 判断浏览器 */
    checkBrowser() {
        var userAgent = navigator.userAgent; //取得浏览器的userAgent字符串
        var isOpera = userAgent.indexOf("Opera") > -1; //判断是否Opera浏览器
        var isIE = userAgent.indexOf("compatible") > -1
            && userAgent.indexOf("MSIE") > -1 && !isOpera; //判断是否IE浏览器
        var isEdge = userAgent.indexOf("Edge") > -1; //判断是否IE的Edge浏览器
        var isFF = userAgent.indexOf("Firefox") > -1; //判断是否Firefox浏览器
        var isSafari = userAgent.indexOf("Safari") > -1
            && userAgent.indexOf("Chrome") == -1; //判断是否Safari浏览器
        var isChrome = userAgent.indexOf("Chrome") > -1
            && userAgent.indexOf("Safari") > -1; //判断Chrome浏览器

        if (isIE) {
            var reIE = new RegExp("MSIE (\\d+\\.\\d+);");
            reIE.test(userAgent);
            var fIEVersion = parseFloat(RegExp["$1"]);
            if (fIEVersion == 7) {
                return "IE7";
            } else if (fIEVersion == 8) {
                return "IE8";
            } else if (fIEVersion == 9) {
                return "IE9";
            } else if (fIEVersion == 10) {
                return "IE10";
            } else if (fIEVersion == 11) {
                return "IE11";
            } else {
                return "IE";
            }
        }
        if (isOpera) {
            return "Opera";
        }
        if (isEdge) {
            return "Edge";
        }
        if (isFF) {
            return "Firefox";
        }
        if (isSafari) {
            return "Safari";
        }
        if (isChrome) {
            return "Chrome";
        }

    }

}

3.使用

<template>
  <div class="app-container">
    <jf-import :label='label' :handleExcel="handleExcel" :next="next" api="/points/integralAward/integralAwardBatchImport" download="专家组导入模板.xls" href="/points/integralAward/importTemplate"></jf-import>
  </div>
</template>
<script>
import jfImport from "@/components/import"
export default {
  components: {
    jfImport
  },
  data () {
    return {

      label: [
        {
          label: "登录名",
          prop: "title",
        },
        {
          label: "密码",
          prop: "remarks",
        },
        {
          label: "姓名",
          prop: "realName",
        },
        {
          label: "性别",
          prop: "phoneNumber",
        },
        {
          label: "出生年月",
          prop: "awardTime"
        },
        {
          label: "从事专业",
          prop: "event"
        },
        {
          label: "工作单位",
          prop: "eventRemarks",
        },
        {
          label: "职务/职称",
          prop: "point",
        },
        {
          label: "办公电话",
          prop: "pieceworkNum",
        },
        {
          label: "身份证号",
          prop: "pieceworkNum2",
        },
      ],
      errorMessage: []
    };
  },
  created () { },
  methods: {
    /* 将表格数据里面的时间处理 */
    handleExcel (list) {
      list.map((item) => {
        if (item.awardTime) {
          console.log(item.awardTime, new Date(item.awardTime))
          var date = new Date(item.awardTime);
          item.awardTime = this.getTime(date);
        }

      });
      return list;
    },
    clearError (row, index) {
      var cell = row.getElementsByClassName("cell")[index];
      console.log(row, index, cell)
      cell.style.border = "none";
      cell.title = "";
    },
    addError (row, index, msg, rowIndex) {
      var cell = row.getElementsByClassName("cell")[index];
      cell.style.border = "1px solid red";
      cell.title = msg;
      this.errorMessage.push({
        rowIndex,
        colIndex: index,
        msg: `第${rowIndex + 1}行"${this.label[index - 1].label}"格式错误：${msg}`
      })
    },
    /* 获取当前时间 */
    getTime (date) {
      var year = date.getFullYear();
      var month = date.getMonth() - 0 + 1;
      month = month < 10 ? "0" + month : month;
      var day = date.getDate();
      day = day < 10 ? "0" + day : day;
      var h = date.getHours();
      h = h < 10 ? "0" + h : h;
      var m = date.getMinutes();
      m = m < 10 ? "0" + m : m;
      var s = date.getSeconds();
      s = s < 10 ? "0" + s : s;
      var str = year + "-" + month + "-" + day;
      return str;
    },

    next (arr, ele) {
      this.errorMessage = [];
      console.log(arr, ele);
      /* 验证数据长度 */
      if (!arr.length) {
        this.$message.warning("最少需要一条数据！");
        return false;
      }
      if (arr.length > 500) {
        this.$message.warning("最多500条数据！");
        return false;
      }
      /* 验证数据格式 */
      let flag = true;
      arr.map((item, index) => {
        var row = ele.getElementsByTagName("tr")[index + 1];
        this.clearError(row, 1);
        if (!item.title) {
          this.addError(row, 1, "主题名称不能为空", index);
          flag = false;
        }
        this.clearError(row, 3);
        if (!item.realName) {
          this.addError(row, 3, "成员姓名不能为空", index);
          flag = false;
        }
        this.clearError(row, 4);
        if (!item.phoneNumber) {
          this.addError(row, 4, "成员手机号不能为空", index);
          flag = false;
        }
        if (item.phoneNumber && !/^1[0-9]{10}$/.test(item.phoneNumber)) {
          this.addError(row, 4, "手机号不正确", index);
          flag = false;
        }
        this.clearError(row, 5);
        if (!item.awardTime) {
          this.addError(row, 5, "加减分时间不能为空", index);
          flag = false;
        }
        if (
          item.awardTime &&
          !/^(\d{4})-(\d{2})-(\d{2})$/.test(item.awardTime)
        ) {
          this.addError(row, 5, "时间格式不正确!   正确格式：xxxx-xx-xx", index);
          flag = false;
        }
        this.clearError(row, 6);
        if (!item.event) {
          this.addError(row, 6, "加减分项目不能为空", index);
          flag = false;
        }
        this.clearError(row, 8);
        if (!item.point) {
          this.addError(row, 8, "分值不能为空", index);
          flag = false;
        }
        if (item.point && !/^[+-]?\d*(\.\d*)?(e[+-]?\d+)?$/.test(item.point)) {
          this.addError(row, 8, "分值只能输入数值", index);
          flag = false;
        }
      });
      if (!flag && this.errorMessage[0]) {
        this.$notify.error({
          title: '错误',
          message: `共${this.errorMessage.length}处错误，请修正！ ${this.errorMessage[0].msg}`
        });
      }
      return flag;
    },
  },
};
</script>

<style lang='scss' scoped>
</style>