Vue导入Excel

导入Excel

1. 定义 html元素（安装依赖 npm install xlsx --save）

   <input
          type="file"
          style="display:none"
          ref="file"
          @change="getFileData"
   />    
   <!--定义一个按钮 触发事件为 chooseFile-->
   <el-button type="primary" @click=“chooseFile”>导入</el-button>
   
   <!-- 记得导入xlsx-->
   import XLSX from "xlsx";
   

2. 定义方法

    //选择文件
   chooseFile() {
       this.$refs.file.dispatchEvent(new MouseEvent("click"));
   },
   //获取解析后的数据
   getFileData() {
       let _this = this;
       let params = [];
       const inputFile = this.$refs.file.files[0];
       if (inputFile) {
           if (
               inputFile.name.indexOf(".xlsx") == -1 &&
               inputFile.name.indexOf(".xls") == -1
           ) {
               this.$message.error("模板文件类型不正确！", 3);
               return;
           }
           const reader = new FileReader();
           reader.readAsBinaryString(inputFile);
           reader.onload = function(e) {
               var workbook = XLSX.read(e.target.result, { type: "binary" });
               for (let sheet in workbook.Sheets) {
                   if (workbook.Sheets.hasOwnProperty(sheet)) {
                       params = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
                       console.log("参数是神马？");
                       console.log(params);
                       break;
                   }
               }
               _this.$refs.file.value = null;
               _this.batchImport(_this.parseData())
           };
       }
   },
   // parseData方法功能：将excel中数据解析为一个数组
   // batchImport()  括号中的数据就是格式化好的数据list 方法中传给后台即可
   parseData() {
       let _this = this;
       params.shift();
       const data = [];
       params.forEach(item => {
           const obj = {};
           obj.itemName = _this.checkEmpty(item["测试一"],item["__rowNum__"] - 1,"测试一",1);
           obj.voltName = _this.checkEmpty(item["测试二"],item["__rowNum__"] - 1,"测试二",4); //表格中的展示的数据
           obj.voltLevel = importDataMatch.getDataKey(importDataMatch.importVoltList,item["测试二"]) || "";//给后台传编码
           obj.feaAppDInvest = item["测试二级表头"] || "";
           obj.sanctionLine = item["__EMPTY"] || "";
           obj.sanctionCapability = item["__EMPTY_1"] || "";
           obj.apprFileNo = item["__EMPTY_2"] || "";
           obj.feaAppDate = _this.checkDate(item["__EMPTY_3"],item["__rowNum__"] - 1,"时间1",18);
           obj.hzDInvest = item["测试二级表头"] || ""; 
           obj.hzLine = item["__EMPTY_4"] || "";
           obj.hzCapality = item["__EMPTY_5"] || "";
           obj.approvalReplyNum = item["__EMPTY_6"] || "";
           obj.hzReplyDate =  _this.checkDate(item["__EMPTY_7"],item["__rowNum__"] - 1,"时间2",23); 
           data.push(obj);
       });
       return data;
   },
   checkEmpty(data, index, name, column){
         if(data == undefined || (data+"").trim() == ""){
           this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】必填的字段未填,请重新设置！`)
           throw '导入失败！'
         }else {
           return (data+"").trim();
         }
   },
   //参数： 数据  行号 字段名称 列号 是否判空
   checkNum(num, index, name, column, emptyFlag) {
       if(emptyFlag){
           if(num == undefined){
               this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】必填的字段未填,请重新设置！`)
               throw '导入失败！'
           }
       }
       const reg = /[^\d\.]/;
       if (reg.test(num)) {
           this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】填写格式不正确,应该填写数字，请重新设置！`);
           throw '导入失败！'
       }
       const temp = parseFloat(num);
       if (isNaN(temp)) {
           this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】填写格式不正确,应该填写数字，请重新设置！`);
           throw '导入失败！'
       } else {
           return temp.toFixed(4);
       }
   },
   checkDate(date, index, name, column, emptyFlag) {
       if(emptyFlag){
           if(date == undefined){
               this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】必填的字段未填,请重新设置！`);
               throw '导入失败！'
           }
       }
       date = this.formatDate(date,'-')
       var DATE_FORMAT = /^[0-9]{4}-[0-1]?[0-9]{1}-[0-3]?[0-9]{1}$/; //判断是否是日期格式
       if(!DATE_FORMAT.test(date)){
           this.tipMessage(`导入失败<br><br>第[${index}]行，第[${column}]个单元格，【${name}】日期格式不正确,请重新设置！`);
           throw '导入失败！'
       } else {
           return date;
       }
   },
   // 格式化日期 读取excel中日期格式会变成一个数字 numb为读取到的值  format是格式样式 ‘-’或‘/’
   formatDate(numb, format) {
       const time = new Date((numb - 1) * 24 * 3600000 + 1)
       time.setYear(time.getFullYear() - 70)
       const year = time.getFullYear() + ''
       const month = time.getMonth() + 1 + ''
       const date = time.getDate() - 1 + ''
       if (format && format.length === 1) {
           return year + format + month + format + date
       }
       return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
   },　
   tipMessage(content){
       this.$messageElement({
           message: content,
           type: 'info',
           dangerouslyUseHTMLString: true,
       });
   },
   

3. 表格样式：