做项目的时候遇到了导出复杂表格，合并单元格没规则且无厘头，网上找了很久没找到一样的（最后找了一个很相似改成自己的需求，具体看vue：功能【xlsx】动态行内合并），后端不愿意做，争论了很久，最后的最后。。。他只愿意给树形数据加了一个字段N_Num用来统计子集总数。
最后实现的效果如下图
使用el-table展示：
（后记）适用于vue2和vue3的前端导出xlsx（包含合并单元格）~~~定义合并规则并使用el-table的span-method展示：树形数据格式转换成二维数组后进行自定义合并单元格。-CSDN博客
**一、下载包:xlsx ** xlsx-js-style file-saver这三个包
npm install file-saver --save
npm install xlsx --save
npm install -s xlsx-js-style
二、树形数据结构 N_Num是指第一列需要合并的总数
let treeD = [
{
"V_Room": "1#配电室",
"N_Num": 2,
"Child": [{
"V_Transformer": "1#TR1",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 1,
"Child": [
{
"V_Generatrix": "1#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}]
},
{
"V_Transformer": "1#TR2",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 1,
"Child": [{
"V_Generatrix": "2#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
}
]
},
{
"V_Room": "2#配电室",
"N_Num": 6,
"Child": [
{
"V_Transformer": "2#TR1",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 3,
"Child": [
{
"V_Generatrix": "3#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "4#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "5#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
},
{
"V_Transformer": "2#TR2",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 3,
"Child": [
{
"V_Generatrix": "6#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "7#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "8#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
}
]
},
]
下面是处理提前封装好的文件路径是src/utils/timi/outToExcelManySheet.ts,在ts中引入后会报错所以加了 // @ts-ignore
也可以用src/utils/timi/outToExcelManySheet.js；代码都相同
// @ts-ignore
import FileSaver from "file-saver";
// @ts-ignore
import XLSX from 'xlsx-js-style'
// import * as XLSX from "xlsx";
// 三个参数：sheetData、mergesHeader 和文件名。
export function exportSheetExcel(sheetData, mergerArr, fileName = 'karlaExport') {
const wb = XLSX.utils.book_new() // 创建一个新工作簿
for (let i = 0; i < sheetData.length; i++) {
const sheet = sheetData[i]
// 检查数据项是否存在
if (!sheet.data) {
continue // 如果数据项不存在，则跳过当前循环
}
const ws = XLSX.utils.aoa_to_sheet(sheet.data) // 将数据数组转换为工作表
// 设置合并单元格
ws['!merges'] = sheet.merges && sheet.merges.length > 0 ? [...sheet.merges, ...(mergerArr || [])] : mergerArr;
// 设置列宽为自适应
if (sheet.data.length > 0) {
ws['!cols'] = sheet.data[0].map((_, index) => ({ wch: 15 }))
}
// 设置行高
if (sheet.rowHeights && sheet.rowHeights.length > 0) {
ws['!rows'] = sheet.rowHeights.map((height) => ({ hpt: height, hpx: height }))
}
const borderAll = {
top: { style: 'thin' },
bottom: { style: 'thin' },
left: { style: 'thin' },
right: { style: 'thin' }
}
// 设置单元格样式
for (const key in ws) {
if (ws.hasOwnProperty(key)) {
const cell = ws[key]
if (typeof cell === 'object') {
cell.s = {
border: borderAll,
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true
},
font: {
sz: 12,
bold:false,
color: {
rgb: '000000'
}
},
numFmt: 'General',
fill: {
fgColor: { rgb: 'FFFFFF' }
}
}
}
}
}
//大标题加粗 不需要的可以注释掉
ws.A1.s.font.bold=true
ws.A1.s.border = {
top: { style: 'none' },
bottom: { style: 'none' },
left: { style: 'none' },
right: { style: 'none' }
}
// console.log(wb, )
XLSX.utils.book_append_sheet(wb, ws, sheet.name) // 将工作表添加到工作簿并指定名称
}
const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' }) // 将工作簿转换为数组
const file = new Blob([wbout], { type: 'application/octet-stream' }) // 创建Blob对象
FileSaver.saveAs(file, fileName+'.xlsx') // 下载文件
}
// 二维数组中空的数据设置为 0
function emptyValues(array, defaultValue) {
for (let i = 0; i < array.length; i++) {
for (let j = 0; j < array[i].length; j++) {
if (array[i][j] === null || array[i][j] === undefined || array[i][j] === '') {
array[i][j] = defaultValue
}
}
}
return array
}
// 生成excel列表数据
function handleExcelTable(columnHeader, list) {
if (list.length === 0) return []
// 表头
const tableColumn = Object.keys([columnHeader][0])
// 表格生成的数据
const sheet = [tableColumn]
list.forEach((item) => {
const row = tableColumn.map((column) => item[column])
sheet.push(row)
})
// 表头匹配对应的中文
const firstRow = sheet[0].map((column) => columnHeader[column])
sheet[0] = firstRow
return sheet || []
}
此处用的是vue3，在你的页引入方法
import {exportSheetExcel} from '/@/utils/timi/outToExcelManySheet.ts'
r 表示行索引，c 表示列索引
const mergesHeader = [
{s: {r: 0, c: 0}, e: {r: 0, c: 15}}, // 第0列的第0行和第15列的0行合并
// 行合并
// { s: { r: 0, c: 3 }, e: { r: 0, c: 5 } },
// { s: { r: 0, c: 6 }, e: { r: 0, c: 8 } },
// 列合并（r 表示行索引，c 表示列索引）
// { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // 第0列的第0行和第1行合并
// { s: { r: 0, c: 1 }, e: { r: 1, c: 1 } }, // 第1列的第0行和第1行合并
// { s: { r: 0, c: 2 }, e: { r: 1, c: 2 } }, // 第2列的第1行和第1行合并
]
三、完整代码如下
<template>
<div class="system-menu w-h-100">
<el-button @click="exportXLSx">导出</el-button>
</div>
</template>
<script lang="ts" setup>
import {nextTick, onMounted, reactive, ref, toRefs} from 'vue';
import {exportSheetExcel} from '/@/utils/timi/outToExcelManySheet.ts'
const exportXLSx = ()=>{
let treeD = [
{
"V_Room": "1#配电室",
"N_Num": 2,
"Child": [{
"V_Transformer": "1#TR1",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 1,
"Child": [
{
"V_Generatrix": "1#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}]
},
{
"V_Transformer": "1#TR2",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 1,
"Child": [{
"V_Generatrix": "2#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
}
]
},
{
"V_Room": "2#配电室",
"N_Num": 6,
"Child": [
{
"V_Transformer": "2#TR1",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 3,
"Child": [
{
"V_Generatrix": "3#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "4#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "5#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
},
{
"V_Transformer": "2#TR2",
"N_Capacity": 2000,
"N_TCmax": 120,
"N_TTmax": 36,
"N_TCavg": 30,
"N_TLmax": 50,
"N_TLavg": 45,
"N_Num": 3,
"Child": [
{
"V_Generatrix": "6#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "7#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
},
{
"V_Generatrix": "8#母线",
"V_MainLoad": "阴极模切2",
"N_RatedCurrent": 1000,
"N_GCmax": 120,
"N_GTmax": 36,
"N_GCavg": 30,
"N_GLmax": 50,
"N_GLavg": 45
}
]
}
]
},
]
console.log(treeD)
let DoubleArr = [];//存二维数组
let MergesListData = [];//存合并单元格数据
let FirstRow = 2//我是从第3行第0列开始的，excel的下标是从0开始，所以此处2对应excel的第三行；相当于起点，用来计算合并数量的变量
let SecondRow = 2//我是从第3行第0列开始的，excel的下标是从0开始，所以此处2对应excel的第三行
for (let i = 0; i < treeD.length; i++) { // 第一层循环，遍历树的根节点
let currentNode = treeD[i];
MergesListData.push(
{s: {r: FirstRow, c: 0}, e: {r: FirstRow + currentNode.N_Num - 1, c: 0}},
)
console.log(currentNode.N_Num)
FirstRow = FirstRow + currentNode.N_Num
for (let j = 0; j < currentNode.Child.length; j++) { // 第二层循环，遍历当前节点的子节点
let childNode = currentNode.Child[j];
MergesListData.push(
{s: {r: SecondRow, c: 1}, e: {r: SecondRow + childNode.N_Num - 1, c: 1}},
{s: {r: SecondRow, c: 2}, e: {r: SecondRow + childNode.N_Num - 1, c: 2}},
{s: {r: SecondRow, c: 3}, e: {r: SecondRow + childNode.N_Num - 1, c: 3}},
{s: {r: SecondRow, c: 4}, e: {r: SecondRow + childNode.N_Num - 1, c: 4}},
{s: {r: SecondRow, c: 5}, e: {r: SecondRow + childNode.N_Num - 1, c: 5}},
{s: {r: SecondRow, c: 6}, e: {r: SecondRow + childNode.N_Num - 1, c: 6}},
{s: {r: SecondRow, c: 7}, e: {r: SecondRow + childNode.N_Num - 1, c: 7}},
)
SecondRow = SecondRow + childNode.N_Num
for (let k = 0; k < childNode.Child.length; k++) {
// 第三层循环，遍历子节点的子节点（最深层的节点）
let deepestNode = childNode.Child[k];
DoubleArr.push([currentNode.V_Room,
childNode.V_Transformer,
childNode.N_Capacity,
childNode.N_TCmax,
childNode.N_TTmax,
childNode.N_TCavg,
childNode.N_TLmax,
childNode.N_TLavg,
deepestNode.V_Generatrix,
deepestNode.V_MainLoad,
deepestNode.N_RatedCurrent,
deepestNode.N_GCmax,
deepestNode.N_GTmax,
deepestNode.N_GCavg,
deepestNode.N_GLmax,
deepestNode.N_GLavg,
]);
}
}
}
console.log(DoubleArr, MergesListData)
let titleList = []
for (let i = 1; i < 17; i++) {
titleList.push('标题' + i)
}
const sheet1 = {
name: 'LeavePay',
// data: [header, ...OutExcelSheet.handleExcelTable(columnsHeader, list.value)], // 常规list数据用封装的方法处理二维数据
data: [['这是大标题'], titleList, ...DoubleArr], // 使用处理好的mock数据
merges: [],
rowHeights: [{hpx: 20}, {hpx: 20}]
}
sheet1.merges = MergesListData
let sheetData = [sheet1]
const mergesHeader = [
{s: {r: 0, c: 0}, e: {r: 0, c: 15}}, // 第0列的第0行和第15列的0行合并 "这是大标题"
// 行合并
// { s: { r: 0, c: 3 }, e: { r: 0, c: 5 } },
// { s: { r: 0, c: 6 }, e: { r: 0, c: 8 } },
// 列合并（r 表示行索引，c 表示列索引）
// { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // 第0列的第0行和第1行合并
// { s: { r: 0, c: 1 }, e: { r: 1, c: 1 } }, // 第1列的第0行和第1行合并
// { s: { r: 0, c: 2 }, e: { r: 1, c: 2 } }, // 第2列的第1行和第1行合并
]
exportSheetExcel(sheetData, mergesHeader, `导出`)
}
</script>
<style lang="scss" scoped>
//@import "./src/assets/styles/index.scss";
</style>
希望能帮助到您！有用的话点个三连（点赞，收藏，关注）吧！！！
参考文档
xlsx-js-style官网
vue：功能【xlsx】动态行内合并_vue 导出excel 合并单元格-CSDN博客
本文转载自: https://blog.csdn.net/Timi_666/article/details/138285009
版权归原作者 Timi_666 所有， 如有侵权，请联系我们删除。
版权归原作者 Timi_666 所有， 如有侵权，请联系我们删除。