数据xlsx表格导入和数据以xlsx表格导出

使用的是elementUi里的上传组件和xlsx库

//模板导入（xlsx表格形式）
  //选择模板
      upload(file, fileList) {
        console.log('file', file)
        console.log('fileList', fileList)
        let files = { 0: file.raw }
        this.readExcel1(files)
      },
    readExcel1(files) {
        //表格导入
        var that = this
        this.showConsole = false
        if (files.length <= 0) {
          //如果没有文件名
          return false
        } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
          this.$Message.error('上传格式不正确，请上传xls或者xlsx格式')
          return false
        }

        const fileReader = new FileReader()
        fileReader.onload =async ev => {
          try {
            const data = ev.target.result
            const workbook = XLSX.read(data, {
              type: 'binary'
            })
            const wsname = workbook.SheetNames[0] //取第一张表
            console.log(wsname)
            const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) //生成json表格内容
            console.log(ws)
            this.UpImportDeliver(ws)
            this.$refs.upload.value = ''
          } catch (e) {
            return false
          }
        }
        fileReader.readAsBinaryString(files[0])
      },
      async UpImportDeliver(ws){
            let sour = ws
            let length = sour.length
            let seccessIndex = 0
            let errIndex = 0
             this.writeLog(`共导入物流${length}单. `,true)
            for (let index = 0; index < length; index++) {
              const item = sour[index];
               let newdata = JSON.parse(
                JSON.stringify(item)
                  .replace('订单编号', 'orderSn')
                  .replace('物流公司', 'shippingName')
                  .replace('物流单号', 'trackingNo')
                  .replace('拍单平台（拼多多：1  淘宝：2  天猫：3  京东京喜：4  货老板：6  空包平台：50  其他：51  ）', 'purchasePlatformId')
                  .replace('拍单订单号', 'shotNo')
                  .replace('拍单价格', 'payAmount')
                  .replace('备注', 'note').replace(/\s|\n|\t/g,'')
              )
              let parma = {
                 orderSn:newdata.orderSn?newdata.orderSn:'',
                 trackingNo: newdata.trackingNo?newdata.trackingNo:'',
                  shippingName:newdata.shippingName,
                  shotAmount:newdata.payAmount&&Number(newdata.payAmount)>0?Number(newdata.payAmount):'',
                  shotOrderSn:newdata.shotNo&&newdata.shotNo.length>10?newdata.shotNo:'',
                  purchasePlatformId: newdata.purchasePlatformId?newdata.purchasePlatformId:4,
                  remark:newdata.note 
              }
              const { data } = await this.$api.importDeliver(
                    parma
                  )
                  if(data.status_code==200){
                    this.writeLog(`订单${parma.orderSn},导入物流完成.${index + 1}/${length} `,true)
                    seccessIndex++
                  }else{
                    errIndex++
                    this.writeLog(`订单${parma.orderSn},导入物流失败. ${data.message},${index + 1}/${length} `,false)
                  } 
                  if (seccessIndex+errIndex==length||index + 1==length) {
                    this.writeLog(`导入物流完成.共${length}单,成功${seccessIndex},失败${errIndex} `,true)
                  }  
            } 
      },

// 链接导入的方法
    importLinkValEvent() {
      if (!this.importLinkVal) {
        this.$message2.error('商品链接不能为空')
        return
      }
      this.linkDialogVisible = false
      this.impoStart('链接导入')
    },

数据以xlsx表格导出

//先去调数据接口
// 导出
    exportSearchBtn() {
      if (this.listQuery.total && this.listQuery.total.length >= 30000) {
        this.$message.warning('单次导出订单数量请控制在3万条以下')
        return false
      }

      if (this.selection.length) {
        this.$confirm(
          `即将实时导出您勾选的${this.selection.length}条订单数据，是否导出？`,
          '提示',
          {
            confirmButtonText: '确定',
            cancelButtonText: '取消',
            type: 'warning'
          }
        )
          .then(() => {
            this.importOrder(this.selection)
          })
          .catch(() => {
            this.$message({
              type: 'info',
              message: '已取消'
            })
          })
        console.log(this.selection, 'this.selection======')
        return
      }
      this.$confirm(
        `即将实时导出${this.listQuery.total}条订单数据，是否导出？`,
        '提示',
        {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'warning'
        }
      )
        .then(() => {
          this.exportSearch()
        })
        .catch(() => {
          this.$message({
            type: 'info',
            message: '已取消'
          })
        })
    },
    
   exportSearch(page = 1) {
      this.isExport = true
      const _params = {
        countries:
          (this.form.countries && this.form.countries.toString()) || '',
        sysMallId:
          (this.form.sysMallId && this.form.sysMallId.toString()) || '',
        abnormalStatus: this.form.abnormalStatus || '',
        orderStatus: this.form.orderStatus || '',
        sourcePlatformIds: this.form.sourcePlatformIds || '',
        timeType: this.form.timeType || '',
        page,
        perPage: this.listQuery.limit || '',
        otherTime:
          (this.otherTime &&
            this.otherTime.length &&
            this.otherTime[0] + '/' + this.otherTime[1]) ||
          '',
        createTime:
          (this.createTime &&
            this.createTime.length &&
            this.createTime[0] + '/' + this.createTime[1]) ||
          '',
        expressLineCode: this.form.expressLineCode || '',
        trackingStatus: this.form.shotStatus
      }
      if (this.isWaitStatus) {
        _params.orderStatus = 14
        _params.wait_status = this.form.waitStatus
      } else {
        _params.orderStatus = this.form.orderStatus || ''
      }
      if (this.ailas.label && this.ailas.value) {
        _params[this.ailas.label] = this.ailas.value
      }
      _params.page = page
      orderManageList(_params).then((res) => {
        const { data } = res
        if (data.code === 200) {
          // this.abnormalOrders = data.data.data
          const newData = data.data.data
          this.listQuery.total = data.data.total
          this.exportList.push(...newData)
          if (this.exportList.length >= this.listQuery.total) {
            this.isExport = false
            this.importOrder(this.exportList)
          } else {
            this.exportSearch(_params.page + 1)
          }
        } else {
          this.isExport = false
          this.$message.error(data.message)
        }
      })
    },
    // 导出
    importOrder(jsonData) {
      // 要导出的json数据
      // const jsonData = this.orders
      let str =
        '<tr><td>平台名称</td><td>店铺名称</td><td>支付授权码</td><td>订单收入</td><td>退款金额</td><td>买家姓名</td><td>买家邮箱</td><td>买家付款金额</td><td>预估运费</td><td>预估毛利</td><td>包裹重量</td><td>平台费用</td><td>财务费用</td><td>实际总运费</td><td>商品数量</td><td>订单编号</td><td>订单成本</td><td>订单补寄</td><td>渠道线路</td><td>支付方式</td><td>订单创建时间</td><td>订单更新时间</td><td>订单同步时间</td><td>订单推送状态</td><td>发货时间</td><td>订单状态</td><td>妥投状态</td><td>发货失败原因</td><td>异常备注</td><td>毛利</td><td>备注</td><td>国家</td><td>客户留言</td><td>物流单号</td><td>收件人电话</td><td>收件人城市</td><td>收件人省份</td><td>收件人大州</td><td>收件人邮编</td><td>收件人地址</td><td>发货方式</td></tr>'
      jsonData.forEach((item) => {
        str += `<tr>
        <td>${
  this.formData(item.source_platform_id, 'platformList')
    ? this.formData(item.source_platform_id, 'platformList')
    : '' + '\t'
}</td>
        <td>${
  item.mall_info && item.mall_info.platform_mall_name
    ? item.mall_info.platform_mall_name
    : '' + '\t'
}</td> 
        <td>${item.authorization_key ? item.authorization_key : '' + '\t'}</td>
        <td>${item.escrow_amount ? item.escrow_amount : '' + '\t'}</td>
        <td>${
  item.part_return_amount ? item.part_return_amount : '' + '\t'
}</td>
        <td>${item.receiver_name ? item.receiver_name : '' + '\t'}</td>
        <td>${item.receiver_email ? item.receiver_email : '' + '\t'}</td>
        <td>${item.escrow_amount ? item.escrow_amount : '' + '\t'}</td>
        <td>${item.total_amount ? item.total_amount : '' + '\t'}</td>
        <td>${
  item.buyer_shipping_fee ? item.buyer_shipping_fee : '' + '\t'
}</td>
        <td>${
  item.estimate_gross_profit ? item.estimate_gross_profit : '' + '\t'
}</td>
        <td>${item.package_weight ? item.package_weight : '' + '\t'}</td>
        <td>${
  item.total_financial_fee ? item.total_platform_fee : '' + '\t'
}</td>
        <td>${
  item.total_shipping_fee ? item.total_shipping_fee : '' + '\t'
}</td>
        <td>${item.goods_count ? item.goods_count : '' + '\t'}</td> // 商品数量
        <td>${item.order_sn ? item.order_sn : '' + '\t'}</td>
        <td>${item.order_cost ? item.order_cost : '' + '\t'}</td>
        <td>${'--' + '\t'}</td> // 订单补寄
        <td>${item.express_line_name ? item.express_line_name : '' + '\t'}</td>
        <td>${item.payment_method ? item.payment_method : '' + '\t'}</td>
        <td>${item.created_time ? item.created_time : '' + '\t'}</td>
        <td>${item.update_time ? item.update_time : '' + '\t'}</td>
        <td>${item.created_at ? item.created_at : '' + '\t'}</td>
        <td>${
  this.formData(item.push_xzy_status, 'pushStatus')
    ? this.formData(item.push_xzy_status, 'pushStatus')
    : '' + '\t'
}</td>
        <td>${item.delivery_time ? item.delivery_time : '' + '\t'}</td>
        <td>${
  this.formData(item.order_status, 'orderStatus')
    ? this.formData(item.order_status, 'orderStatus')
    : '' + '\t'
}</td>
        <td>${
  this.formData(item.tracking_status, 'trackingStatus')
    ? this.formData(item.tracking_status, 'trackingStatus')
    : '' + '\t'
}</td>
        <td>${
  this.formData(item.delivery_sync_status, 'deliverySyncStatus')
    ? this.formData(item.delivery_sync_status, 'deliverySyncStatus')
    : '' + '\t'
}</td>
        <td>${item.abnormal_remark ? item.abnormal_remark : '' + '\t'}</td>
        <td>${item.real_gross_profit ? item.real_gross_profit : '' + '\t'}</td>
        <td>${item.note ? item.note : '' + '\t'}</td>
        <td>${
  this.formData1(item.country, 'countryList')
    ? this.formData1(item.country, 'countryList')
    : '' + '\t'
}</td>tracking_no
        <td>${item.customer_remark ? item.customer_remark : '' + '\t'}</td>
        <td>${item.tracking_no ? item.tracking_no : '' + '\t'}</td>
        <td>${item.phone ? item.phone : '' + '\t'}</td>
        <td>${item.city ? item.city : '' + '\t'}</td>
        <td>${item.province ? item.province : '' + '\t'}</td>
        <td>${item.state ? item.state : '' + '\t'}</td>
        <td>${item.zipcode ? item.zipcode : '' + '\t'}</td>
        <td>${item.address ? item.address : '' + '\t'}</td>
        <td>${item.express_line_name ? item.express_line_name : '' + '\t'}</td>
        </tr>`
      })
      // Worksheet名
      const worksheet = '订单管理信息'
      // const uri = 'data:application/vnd.ms-excel;base64,'

      // 下载的表格模板数据
      const template = `<html xmlns:o="urn:schemas-microsoft-com:office:office"
             xmlns:x="urn:schemas-microsoft-com:office:excel"
             xmlns="http://www.w3.org/TR/REC-html40">
             <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
               <x:Name>${worksheet}</x:Name>
               <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
               </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
               </head><body><table>${str}</table></body></html>`
      // 下载模板
      const blob = new Blob([template], { type: 'html', name: worksheet })
      const a = document.createElement('a')
      document.body.appendChild(a)
      // a.href = uri + this.base64(template)
      a.href = URL.createObjectURL(blob)
      a.download = `订单管理信息${new Date(Date.now() + 8 * 3600 * 1000)
        .toISOString()
        .slice(0, 10)}.xlsx`
      a.click()
      document.body.removeChild(a)
    }