Excel 文件导出 js-xlsx合并单元格的实现 （vue）

页面数据如下图：

export default {
 data() {
    return {
	tableData: [],
	colData: [],
	 originData: [{"skuId":"00001","skuName":"nameSku0001","w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":50,"lockQuantity":5,"availableQuantity":45},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":40,"lockQuantity":1,"availableQuantity":39}},{"skuId":"00002","skuName":"nameSku0002","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}},{"skuId":"00003","skuName":"nameSku0003","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}}]
	}
 }
 methods: {
  initData() {
      this.tableData = this.originData.map(res => {
        res.warehouseItem.forEach(item => {
          res[item.warehouseId] = item
        })
        return res
      })
      this.originData[0].warehouseItem.forEach((res) => {
        this.colData.push({
          dataItem: res.warehouseId,
          dataName: res.warehouseName
        })
      })
    },
 exportList() {
      const wscols = [
        { wpx: 220 } // 第一列宽度设置单位px
      ]
      /**
       * 合并单元格元素(decode_range方法解析数据格式)
       {
          s: { //s start 开始
            c: 1,//cols 开始列
            r: 0 //rows 开始行
          },
          e: {//e end  结束
            c: 4,//cols 结束列
            r: 0 //rows 结束行
          }
        }
       */
      const wsMerge = [XLSX.utils.decode_range('A1:A2')]
      /** 头部-行列信息*/
      const header1 = this.colData.map(res => res.dataName).concat('合计')

      header1.map((res, idx, array) => {
        const hdMergeObj = {
          s: { r: 0 },
          e: { r: 0 }
        }
        if ((idx + 2) % 1 === 0) {
          array.splice(3 * idx + 1, 0, '', '')
          hdMergeObj['s']['c'] = 3 * idx + 1
          hdMergeObj['e']['c'] = hdMergeObj['s']['c'] + 2
          wsMerge.push(hdMergeObj)
        }
        return res
      })
      const header2 = this.colData.map(() => '库存数量').concat('库存数量')
      header2.map((res, idx, array) => {
        if ((idx + 2) % 1 === 0) {
          array.splice(3 * idx + 1, 0, '锁定数量', '可用数量')
        }
        return res
      })
      const ws = XLSX.utils.aoa_to_sheet([
        ['商品信息'].concat(header1),
        [''].concat(header2)
      ])
      // console.log('header1', ['商品信息'].concat(header1))
      // console.log(header2)
      /** 商品信息-行列信息*/
      const skuInfo = this.tableData.map(res => res.skuId + '\r\n' + res.skuName)
      XLSX.utils.sheet_add_aoa(ws, skuInfo.map(res => [res]), { origin: 'A3' })
      /** 仓库-行列信息（库存数量，锁定数量，可用数量）*/
      const unAllowed = ['skuId', 'skuName', 'warehouseItem']
      const sheetData = this.tableData.map((data) => {
        const filterItem = Object.assign({}, data)
        Object.keys(filterItem)
          .filter(key => unAllowed.includes(key))
          .forEach(key => delete filterItem[key])
        // 重构完数据
        let warehouseDatas = []
        Object.values(filterItem).forEach(res => {
          warehouseDatas = warehouseDatas.concat([res.quantity]).concat([res.lockQuantity]).concat([res.availableQuantity])
          return warehouseDatas
        })
        return warehouseDatas
      })
      // console.log('sheetData', sheetData)
      XLSX.utils.sheet_add_aoa(ws, sheetData, { origin: { r: 2, c: 1 }})
      /** 合计-行列信息*/
      const totalData = this.tableData.map(row => {
        const totlal_quantity = Object.values(row.warehouseItem).map(res => res.quantity).reduce((accumulator, currentValue) => accumulator + currentValue)
        const totlal_lockQuantity = Object.values(row.warehouseItem).map(res => res.lockQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)
        const totlal_availableQuantity = Object.values(row.warehouseItem).map(res => res.availableQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)
        return [totlal_quantity, totlal_lockQuantity, totlal_availableQuantity]
      })
      // console.log('total', totalData, this.colData.length * 3 + 1)
      XLSX.utils.sheet_add_aoa(ws, totalData, { origin: { r: 2, c: this.colData.length * 3 + 1 }})
      // 导出
      const wb = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
      ws['!cols'] = wscols
      // wsMerge.push(XLSX.utils.decode_range('B1:D1')) // 测试数据 仓库1模拟数据
      ws['!merges'] = wsMerge
      console.log(wsMerge)
      XLSX.writeFile(wb, '库存报表按SKU.xlsx')
    }
 }
}

导出效果如下图：

转载于:https://my.oschina.net/u/3734107/blog/3072645