做项目的时候遇到了导出复杂表格，合并单元格没规则且无厘头，网上找了很久没找到一样的（最后找了一个很相似改成自己的需求，具体看vue：功能【xlsx】动态行内合并），后端不愿意做，争论了很久，最后的最后。。。他只愿意给树形数据加了一个字段N_Num用来统计子集总数。

最后实现的效果如下图

使用el-table展示：

（后记）适用于vue2和vue3的前端导出xlsx（包含合并单元格）~~~定义合并规则并使用el-table的span-method展示：树形数据格式转换成二维数组后进行自定义合并单元格。-CSDN博客

一、下载包:xlsx   xlsx-js-style  file-saver这三个包

npm install file-saver --savenpm install xlsx --savenpm 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-ignoreimport FileSaver from "file-saver";// @ts-ignoreimport 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') // 下载文件 } // 二维数组中空的数据设置为 0function 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博客