行业知识
适用于vue2和vue3的前端导出xlsx（包含合并单元格）：树形数据格式转换成二维数组后进行自定义合并单元格。
2026-11-15 10:17:31
来源：互联网转载
做项目的时候遇到了导出复杂表格，合并单元格没规则且无厘头，网上找了很久没找到一样的（最后找了一个很相似改成自己的需求，具体看vue：功能【xlsx】动态行内合并），后端不愿意做，争论了很久，最后的最后。。。他只愿意给树形数据加了一个字段N_Num用来统计子集总数。
最后实现的效果如下图
一、下载包: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 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 { 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行合并 ] 三、完整代码如下
导出 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 希望能帮助到您！有用的话点个三连（点赞，收藏，关注）吧！！！
参考文档
xlsx-js-style官网
vue：功能【xlsx】动态行内合并_vue 导出excel 合并单元格-CSDN博客
下一篇：怎么从网站入侵服务器?
