vue3使用 xlsx-js-style导出Exce 动态合并表头

安装插件
xlsx-js-style相当于xlsx的生基本，xlsx导出不能对单元格样式进行调整
安装了xlsx-js-style 就不需要安装 xlsx了

npm install xlsx-js-style

引入插件

import XLSXS from 'xlsx-js-style'

示例数据

  // 示例数据 dataconst data = [{ transactionDate: '2024-01-01', '17lowerPriceDisplay': 100, '17upperPriceDisplay': 150, '17averagePriceDisplay': 120, xxxxx2: '12', xxxxx1: '11' },{ transactionDate: '2024-01-02', '17lowerPriceDisplay': 110, '17upperPriceDisplay': 160, '17averagePriceDisplay': 125, xxxxx2: '12', xxxxx1: '11' },]
// 示例 表格列const columns = [{ title: '日期', dataIndex: 'transactionDate', width: 100, fixed: 'left' },{ title: 'ssss', dataIndex: '17lowerPriceDisplay', colSpan: 0, width: 120 },{ title: 'ssss', dataIndex: '17upperPriceDisplay', colSpan: 3, width: 120 },{ title: 'ssss', dataIndex: '17averagePriceDisplay', colSpan: 0, width: 120 },{ title: 'xxxxx', dataIndex: 'xxxxx1', colSpan: 0, width: 120 },{ title: 'xxxxx', dataIndex: 'xxxxx2', colSpan: 0, width: 120 },] 

表格头
表格头对应的key

 const headers = columns.map(item => { return item.title })  const fields = columns.map(item => { return item.dataIndex })

数据转换 二维数组

let dataCopy = data.map((obj) => {return fields.map((field) => {return obj[field]})})if (headers.length > 0) {dataCopy.splice(0, 0, headers)} else {dataCopy.splice(0, 0, fields)}

生成工作表

  const ws = XLSX.utils.aoa_to_sheet(dataCopy) // 创建工作表

单元格格式设置

// 设置列宽度自适应const colWidths = columns.map(col => col.width || 100);ws['!cols'] = colWidths.map(width => ({ wpx: width }));// 设置内容居中// 设置单元格公共样式let borderAll = { //单元格外侧框线top: {style: 'thin',},bottom: {style: 'thin',},left: {style: 'thin',},right: {style: 'thin',}};for (let key in ws) {// 单元格公共样式设置if (ws[key] instanceof Object) {ws[key].s = {border: borderAll,alignment: {horizontal: 'center', //水平居中对齐vertical: 'center',//垂直居中wrapText: 1,//自动换行},// fill: { //背景色//     fgColor: { rgb: 'dbf3f2' }// },font: {sz: 12,//单元格中字体的样式与颜色设置color: {rgb: '000000'}},bold: true,numFmt: 0}}}

动态合并列 导出excel

 // 记录合并单元格的信息const merges = [];let startCol = 0;let currentTitle = columns[0].title;// 确定合并区域columns.forEach((col, index) => {if (col.title === currentTitle) {// 继续合并} else {if (index - startCol > 1) {// 如果需要合并的区域大于1列merges.push({ s: { r: 0, c: startCol }, e: { r: 0, c: index - 1 } });}// 更新标题和起始列currentTitle = col.title;startCol = index;}});// 处理最后一个区域if (columns.length - startCol > 1) {merges.push({ s: { r: 0, c: startCol }, e: { r: 0, c: columns.length - 1 } });}// 应用合并区域到工作表ws['!merges'] = merges;// 创建工作簿const wb = XLSX.utils.book_new();console.log(wb, '创建工作簿')XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');// 导出 Excel 文件XLSX.writeFile(wb, 'export.xlsx');

完整代码 vue3

<template><div><button @click="exportExcel">导出 Excel</button></div>
</template><script>
// import * as XLSX from 'xlsx'
import XLSX from 'xlsx-js-style'
export default {methods: {exportExcel() {// 示例数据const data = [{ transactionDate: '2024-01-01', '17lowerPriceDisplay': 100, '17upperPriceDisplay': 150, '17averagePriceDisplay': 120, xxxxx2: '12', xxxxx1: '11' },{ transactionDate: '2024-01-02', '17lowerPriceDisplay': 110, '17upperPriceDisplay': 160, '17averagePriceDisplay': 125, xxxxx2: '12', xxxxx1: '11' },]// 列配置const columns = [{ title: '日期', dataIndex: 'transactionDate', width: 100, fixed: 'left' },{ title: 'ssss', dataIndex: '17lowerPriceDisplay', colSpan: 0, width: 120 },{ title: 'ssss', dataIndex: '17upperPriceDisplay', colSpan: 3, width: 120 },{ title: 'ssss', dataIndex: '17averagePriceDisplay', colSpan: 0, width: 120 },{ title: 'xxxxx', dataIndex: 'xxxxx1', colSpan: 0, width: 120 },{ title: 'xxxxx', dataIndex: 'xxxxx2', colSpan: 0, width: 120 },]const fields = columns.map(item => { return item.dataIndex })const headers = columns.map(item => { return item.title })let dataCopy = data.map((obj) => {return fields.map((field) => {return obj[field]})})if (headers.length > 0) {dataCopy.splice(0, 0, headers)} else {dataCopy.splice(0, 0, fields)}// 生成工作表// const ws = XLSX.utils.json_to_sheet(data, { header: columns.map(col => col.dataIndex) });const ws = XLSX.utils.aoa_to_sheet(dataCopy) // 创建工作表// 设置列宽度自适应const colWidths = columns.map(col => col.width || 100);ws['!cols'] = colWidths.map(width => ({ wpx: width }));// 设置内容居中// 设置单元格公共样式let borderAll = { //单元格外侧框线top: {style: 'thin',},bottom: {style: 'thin',},left: {style: 'thin',},right: {style: 'thin',}};for (let key in ws) {// 单元格公共样式设置if (ws[key] instanceof Object) {ws[key].s = {border: borderAll,alignment: {horizontal: 'center', //水平居中对齐vertical: 'center',//垂直居中wrapText: 1,//自动换行},// fill: { //背景色//     fgColor: { rgb: 'dbf3f2' }// },font: {sz: 12,//单元格中字体的样式与颜色设置color: {rgb: '000000'}},bold: true,numFmt: 0}}}// 记录合并单元格的信息const merges = [];let startCol = 0;let currentTitle = columns[0].title;// 确定合并区域columns.forEach((col, index) => {if (col.title === currentTitle) {// 继续合并} else {if (index - startCol > 1) {// 如果需要合并的区域大于1列merges.push({ s: { r: 0, c: startCol }, e: { r: 0, c: index - 1 } });}// 更新标题和起始列currentTitle = col.title;startCol = index;}});// 处理最后一个区域if (columns.length - startCol > 1) {merges.push({ s: { r: 0, c: startCol }, e: { r: 0, c: columns.length - 1 } });}// 应用合并区域到工作表ws['!merges'] = merges;// 创建工作簿const wb = XLSX.utils.book_new();console.log(wb, '创建工作簿')XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');// 导出 Excel 文件XLSX.writeFile(wb, 'export.xlsx');}}
};
</script>