Data XLSX table import and data export as XLSX table

You use the upload component in elementUi and the XLSX library

// Import template (XLSX table)
  // Select the template
      upload(file, fileList) {
        console.log('file', file)
        console.log('fileList', fileList)
        let files = { 0: file.raw }
        this.readExcel1(files)
      },
    readExcel1(files) {
        // Table import
        var that = this
        this.showConsole = false
        if (files.length = 0) {
          // If there is no file name
          return false
        } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
          this.$Message.error('Upload format is not correct, please upload XLS or XLSX format')
          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] // select the first table
            console.log(wsname)
            const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // Generate json table content
            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('Total import logistics${length}Single. `.true)
            for (let index = 0; index  length; index++) {
              const item = sour[index];
               let newdata = JSON.parse(
                JSON.stringify(item)
                  .replace('Order Number'.'orderSn')
                  .replace('Logistics Company'.'shippingName')
                  .replace('Tracking Number'.'trackingNo')
                  .replace('Clap single platform (Pindongduo: 1 Taobao: 2 Tmall: 3 Beijing Tokyo Xi: 4 Goods boss: 6 Empty package platform: 50 Others: 51)'.'purchasePlatformId')
                  .replace('Order number'.'shotNo')
                  .replace('Bid price'.'payAmount')
                  .replace('note'.'note').replace(/\s|\n|\t/g.' '))let parma = {
                 orderSn:newdata.orderSn? newdata.orderSn:' '.trackingNo: newdata.trackingNo? newdata.trackingNo:' '.shippingName:newdata.shippingName,
                  shotAmount:newdata.payAmountNumber(newdata.payAmount)0?Number(newdata.payAmount):' '.shotOrderSn:newdata.shotNonewdata.shotNo.length10? 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(` orders${parma.orderSn}, import logistics completed.${index + 1}/${length} `.true)
                    seccessIndex++
                  }else{
                    errIndex++
                    this.writeLog(` orders${parma.orderSn}Failed to import logistics.${data.message}.${index + 1}/${length} `.false)}if (seccessIndex+errIndex==length||index + 1==length) {
                    this.writeLog(Import logistics completed. A total of${length}Single, successful${seccessIndex}, failure${errIndex} `.true)}}},Copy the code

// Link import method
    importLinkValEvent() {
      if (!this.importLinkVal) {
        this.$message2.error('Product link cannot be empty')
        return
      }
      this.linkDialogVisible = false
      this.impoStart('Link import')},Copy the code

The data is exported in XLSX tables

// Go to the data interface
/ / export
    exportSearchBtn() {
      if (this.listQuery.total  this.listQuery.total.length = 30000) {
        this.$message.warning('Please limit the number of exported orders to less than 30,000 pieces at a time')
        return false
      }

      if (this.selection.length) {
        this.$confirm(
          'About to export in real time what you checkedThe ${this.selection.length}Item order data, export or not? `.'tip',
          {
            confirmButtonText: 'sure'.cancelButtonText: 'cancel'.type: 'warning'
          }
        )
          .then(() =  {
            this.importOrder(this.selection)
          })
          .catch(() =  {
            this.$message({
              type: 'info'.message: 'Cancelled'})})console.log(this.selection, 'this.selection======')
        return
      }
      this.$confirm(
        'Soon to export in real timeThe ${this.listQuery.total}Item order data, export or not? `.'tip',
        {
          confirmButtonText: 'sure'.cancelButtonText: 'cancel'.type: 'warning'
        }
      )
        .then(() =  {
          this.exportSearch()
        })
        .catch(() =  {
          this.$message({
            type: 'info'.message: 'Cancelled'})})},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)
        }
      })
    },
    / / export
    importOrder(jsonData) {
      // The json data to export
      // const jsonData = this.orders
      let str =
        Platform name ' tr   td   / td   td  store name  / td   td  payment authorization code  / td   td  order income  / td   td  the refund amount  / td   td  buyers name  / td   td  buyer email  / td   td  buyer payment amount  / td   td  estimated shipping charges Projected gross margin  / td   td   / td   td  parcel weight  / td   td  platform cost  / td   / td   td  financial costs the total freight  / td   td   td  quantity  / td   td  order no.  / td   td  order cost  / td   td  mail order  / Channels of td   td  line  / td   td  payment  / td   td  order creation time  / td   td  update order time  / td   td  orders synchronization time  / td   td  order delivery status  / td   td  delivery time  / td   td  order status  / td   td  Join state  / td   / td   td  delivery failure reasons abnormal note  / td   td   td  gross  / td   td  note  / td   td  state  / td   td  customer message  / td   td  logistics single number  / td   td  the recipient phone  / td   td   recipient city / Td   td  recipient provinces  / td   td   / td  recipient states  td  recipient zip  / td   td  recipient address  / td   td  shipping method  / td   / tr  '
      jsonData.forEach((item) =  {
        str += `tr
        tdThe ${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${item.order_sn ? item.order_sn : ' ' + '\t'}/td
        td${item.order_cost ? item.order_cost : ' ' + '\t'}/td
        tdThe ${The '-' + '\t'}/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
        tdThe ${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
        tdThe ${this.formData(item.order_status, 'orderStatus')?this.formData(item.order_status, 'orderStatus')
    : ' ' + '\t'
}/td
        tdThe ${this.formData(item.tracking_status, 'trackingStatus')?this.formData(item.tracking_status, 'trackingStatus')
    : ' ' + '\t'
}/td
        tdThe ${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
        tdThe ${this.formData1(item.country, 'countryList')?this.formData1(item.country, 'countryList')
    : ' ' + '\t'
}/tdtracking_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 = 'Order Management Information'
      // const uri = 'data:application/vnd.ms-excel; base64,'

      // Download the table template data
      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]xmlx:ExcelWorkbookx:ExcelWorksheetsx:ExcelWorksheet x:Name${worksheet}/x:Name x:WorksheetOptionsx:DisplayGridlines//x:WorksheetOptions/x:ExcelWorksheet /x:ExcelWorksheets/x:ExcelWorkbook/xml! [endif]-- /headbodytable${str}/table/body/html`
      // Download the template
      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 = 'Order management informationThe ${new Date(Date.now() + 8 * 3600 * 1000)
        .toISOString()
        .slice(0.10)}.xlsx`
      a.click()
      document.body.removeChild(a)
    }
Copy the code