小谢第9问：前端JS实现一键导入excel表格

实现表格导入，使表格内容转化为响应json，可校验字符串、图片、ip

<el-button @click="clickhandler ()">上传excel</el-button>

<el-button @click="importTable ()">导入excel</el-button>

clickhandler() {
            this.excleData = [];
            this.successcount = 0;
            this.failcount = 0;
            this.tableData = [];
            this.crateForm3($(this), 'excelFile');
        }
crateForm3(that, name) {
      var thatt = this;
      var input2 = document.createElement('input');
      input2.type = 'file';
      input2.setAttribute(
        'accept',
        'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      ); //文件格式只接受
      input2.setAttribute('name', name);
      input2.setAttribute('style', 'display:none');
      //表单文本域change事件
      input2.onchange = function(e) {
        var fileList = this.files;
        var value = fileList[0].name;
        var value1 = value.substring(value.lastIndexOf('.') + 1, value.length);
        if (
          value.indexOf('@') != -1 ||
          value.indexOf('#') != -1 ||
          value.indexOf('￥') != -1 ||
          value.indexOf('%') != -1 ||
          value.indexOf('&') != -1 ||
          value.indexOf('*') != -1 ||
          value.indexOf('<') != -1 ||
          value.indexOf('>') != -1 ||
          value.indexOf('+') != -1
        ) {
          //文件名称不能包含特殊符号@#￥%&*<>+
          thatt.$message.warning('上传的文件名称不能包含特殊符号@#￥%&*<>+！');
        } else if (value1 != 'xls' && value1 != 'xlsx') {
          thatt.$message.warning('只能选择xls、xlsx格式的文件！');
          return false;
        } else {
          var txt = $('<span>' + value + '</span>');
          that.next('div').append(txt);
          //把文件传输到后台解析并返回到前端
          thatt.loadExcel(e, that);
        }
      };
      //之前初始化的给删除
      that.next('div').children().remove();
      that.next('div').append(input2);
      input2.click();
    },

importTable() {
    if(!this.input_ping){
                this.$message({
                    message: '请先选择文件！',
                    type: 'warning'
                });
            }else{
                this.tableShow = true;
                let thatt = this;
                thatt.successcount = 0;
                thatt.failcount = 0;
                let persons = thatt.excleData;
                let tmpArr = [];
                try{
                    for (let i = 2; i < persons.length; i++) {
                        let tmpdata = {};
                        let originalAttr = {};
                        if(this.groupId && this.treeId){
                            originalAttr.groupId = this.groupId;
                            originalAttr.treeId = this.treeId;
                        }
                        originalAttr.deviceType = persons[i].__EMPTY_2.trim();
                        originalAttr.model = persons[i].__EMPTY_4.trim();
                        originalAttr.installAddress = persons[i].__EMPTY_5.trim();
                        originalAttr.bandwidth = persons[i].__EMPTY_7.trim();
                        originalAttr.joinType = persons[i].__EMPTY_6.trim();
                        originalAttr.repairDeptName = persons[i].__EMPTY_8.trim();
                        originalAttr.protocol = 'ping';

                        tmpdata.name = persons[i].__EMPTY.trim();
                        tmpdata.ipv4 = persons[i].__EMPTY_1.trim();
                        tmpdata.manufacturer = persons[i].__EMPTY_3.trim().toLocaleUpperCase();
                        tmpdata.flagEnable = 1;
                        tmpdata.originalAttr = originalAttr;
//增加校验0421
                        let IP = /^([1-9]d?|1d{2}|2[01]d|22[0-3])(.([1-9]?d|1d{2}|2[0-4]d|25[0-5])){3}$/;
                        let NAME = /^[0-9a-zA-Zu4E00-u9FA5]+$/;
                        if(tmpdata.name == '' || tmpdata.name == null){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '设备名称为空，请重新选择文件！');
                            return;
                        }
                        if(tmpdata.manufacturer == '' || tmpdata.manufacturer == null){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '厂商为空，请重新选择文件！');
                            return;
                        }
                        if(tmpdata.name.length > 255){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '设备名称超过255字符，请重新选择文件！');
                            return;
                        }
                        if(tmpdata.manufacturer.length > 255){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '厂商超过255字符，请重新选择文件！');
                            return;
                        }
                        if(!NAME.test(tmpdata.name)){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '设备名称中有空格，请重新选择文件！');
                            return;
                        }
                        if(!NAME.test(tmpdata.manufacturer)){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '厂商中有空格，请重新选择文件！');
                            return;
                        }
                        
                        if(!IP.test(tmpdata.ipv4)){
                            this.$message.error('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '设备IP不正确，请重新选择文件！');
                            return;
                        }
                        
                        tmpArr.push(tmpdata);
                    }
                }catch(e){
                    this.$message({ 
                        message: '模板解析错误，请选择正确文件导入！',
                        type: 'error'
                    });
                }
                tmpArr.forEach(tmpdata => {
                    api.pingNetIn(tmpdata).then(res => { 
                        tmpdata.deviceType = tmpdata.originalAttr.deviceType;
                        if (res.code == 0) {
                            ++thatt.successcount;
                            this.$message.success('设备' + tmpdata.name + '(' + tmpdata.ipv4 + ')' + '入网成功！');
                        } else {
                            thatt.tableData.push(tmpdata);
                            ++thatt.failcount;
                            this.$message.error(res.message);
                        }
                        this.input_ping = null;
                    });
                });
            }
        },

导入前的表格：

 导入后的数据：