vue合并表格excel导出_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')

}

}

}

导出效果如下图:

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页