页面数据如下图：
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 + 1hdMergeObj['e']['c'] = hdMergeObj['s']['c'] + 2wsMerge.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'] = wsMergeconsole.log(wsMerge)XLSX.writeFile(wb, '库存报表按SKU.xlsx')}}}
