vue导入excel文件，vue导入多个sheets

水深无声 2023-03-13 06:57 6阅读 0赞

用input 导入 excel ，然后生成表格，然后调用接口上传到后台

### **html 导入按钮和选择文件的弹框** ###

![202005121743483.png][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlcm9fcm9uZw_size_16_color_FFFFFF_t_70][]

<el-button v-waves :loading='downloadLoading' class='filter-item' type='primary' icon='el-icon-download' @click='daoru'>
            导入
          </el-button>
          <el-dialog title="导入文件" :visible.sync="excelImportShow">
            <a href="javascript:;" class="file">
              <input id="upload" type="file" @change="importfxx(this)" accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
            </a>
            <div id="demo"></div>
            <div slot="footer" class="dialog-footer">
              <el-button @click.native="excelImportShow = false">取消</el-button>
              <el-button type="primary" @click.native="submit">提交</el-button>
            </div>
          </el-dialog>

### **js 重中之重 导入 excel 开始啦** ###

1.data里面的数据

excelImportShow: false,  //导入文件的弹框
            uploadArr: [],   // 上传给后台的数据
            realname: '',  // 以下都是我要转换的数据，因人而异
            gender: '',
            age: '',
            minzu: '',
            wenhua: '',
            shengfenzheng: '',
            job: '',
            mobile: '',
            mark: ''

2. 导入弹框

daoru() {
            this.excelImportShow = true
          },

3. 导入方法

![20200512181009688.png][]

// 导入
          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();
            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 = []
                let nameArr = outdata[1]
                for (let v in nameArr) {
                // 拿到 excel 里面的列名，根据列名得到里面的键名
                  let title = nameArr[v]
                  switch (title){
                    case '姓名':
                    console.log('这是姓名',nameArr[v]);
                    _this.realname = v
                    console.log('这是realname',v);
                      break;
                    case '性别':
                    console.log('这是性别',nameArr[v]);
                    _this.gender = v
                    console.log('这是gender',v);
                      break;
                    case '年龄':
                    console.log('这是年龄',nameArr[v]);
                    _this.age = v
                    console.log('这是age',v);
                      break;
                    case '民族':
                    console.log('这是民族',nameArr[v]);
                    _this.minzu = v
                    console.log('这是minzu',v);
                      break;
                    case '学历':
                    console.log('这是性别',nameArr[v]);
                    _this.wenhua = v
                    console.log('这是job',v);
                      break;
                    case '身份证号':
                    console.log('这是身份证号',nameArr[v]);
                    _this.shengfenzheng = v
                    console.log('这是shenfengzheng',v);
                      break;
                    case '工作单位/学校/社区':
                    console.log('"工作单位/学校/社区"',nameArr[v]);
                    _this.job = v
                    console.log('这是job',v);
                      break;
                    case '手机/电话':
                    console.log('这是手机/电话',nameArr[v]);
                    _this.mobile = v
                    console.log('这是mobile',v);
                      break;
                    case '备注':
                    console.log('这是备注',nameArr[v]);
                    _this.mark = v
                    console.log('这是mark',v);
                      break;
                    default:
                      break;
                  }
                }
                let obj
                let uploadArr = []
                // 这里是一个excel里面会有多个sheets，把里面的恶数据整个到这个数组里面一起上传
                let leng = Object.keys(wb.Sheets).length
                // 用for循环，leng 是sheets的个数，用来循环次数
                for (var i = 0; i < leng+1; i++) {
                  XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[i]])
                  let arrr = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[i]])
                  arrr = arrr.slice(2)        //如果从第三行开始是数据，那这里就是1，如果从第四行开始是数据，那这里就是2
                  uploadArr.push.apply(uploadArr,arrr)
                  _this.uploadArr = uploadArr
                }
                this.da.map(v => {
                  let obj = {}
                  obj.id = v.id
                  obj.status = v.status
                  arr.push(obj)
                })
                let para = {
                  withList: arr
                }
                _this.$message({
                  message: '请耐心等待导入成功',
                  type: 'success'
                });
              }
              reader.readAsArrayBuffer(f);
            }
            if (rABS) {
              reader.readAsArrayBuffer(f);
            } else {
              reader.readAsBinaryString(f);
            }
          },

4.确认导入 （其实这里就是上传到后台接口的，存到数据库里面才能保存）

// 确认导入
          submit() {
            let uploadArr = this.uploadArr
            this.excelImportShow = false
            let obj
            for (let v in uploadArr) {
              obj = {realname:uploadArr[v][""+this.realname+""],
              age:uploadArr[v][""+this.age+""],
              gender: uploadArr[v][""+this.gender+""]=='男'?'MALE':'FEMALE',
              minzu: uploadArr[v][""+this.minzu+""],
              wenhua: uploadArr[v][""+this.wenhua+""],
              shengfenzheng: uploadArr[v][""+this.shengfenzheng+""],
              job: uploadArr[v][""+this.job+""],
              wenhua: uploadArr[v][""+this.wenhua+""],
              mobile:uploadArr[v][""+this.mobile+""],
              mark:uploadArr[v][""+this.mark+""],
              place: "00"}
              // 这里的obj 就是后台需要的参数，用变量表示键名 [""+此处是你的变量+""]
              createUser(obj).then((res) => {
              这是后台添加的方法
                if (res.data.return_code === '200') {
                  this.dialogFormVisible = false
                } else {
                  this.$notify.error(res.data.return_msg)
                }
              })
              if (parseInt(v) === parseInt(uploadArr.length)-1) {
                console.log(v+'和'+uploadArr.length);
                setTimeout(()=>{
                  this.$notify({
                    title: '成功',
                    message: '创建成功',
                    type: 'success',
                    duration: 2000
                  })
                  this.getList({ page: 1,limit: 20 })
                  // 添加成功之后调用list 接口，展示所添加的数据
                },1500)
              }
            }
          },

才疏学浅，若有不足欢迎补充

[202005121743483.png]: https://img-blog.csdnimg.cn/202005121743483.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlcm9fcm9uZw_size_16_color_FFFFFF_t_70]: /images/20230312/d3ad0ebd5d1a43ec9b53a401438883a3.png
[20200512181009688.png]: /images/20230312/2f2db4c774e04e19a659afa3e734f18b.png