vue 导出多级表头table 数据到excel

注意：在开始之前，先进行插件安装，参考：https://www.cnblogs.com/zhoulifeng/p/9974735.html

第二步：更改export2Excel.js里面的源码，本文示例为3级表头，可以根据需求，自行更改

//此处源码已被修改，autoWidt和bookType参数，先默认，（可以通过传参修改）

export function export_json_to_excel(excelData, autoWidth = true, bookType = 'xlsx') {

/* original data */

let multiHeader = excelData.multiHeader; // 第一级标题

let multiHeader2 = excelData.multiHeader2; // 第二级标题

let header = excelData.tHeader; // 第三级标题

let data = excelData.transData; // table数据

let defaultTitle = excelData.defaultTitle; // excel表的标题

let merges = excelData.merges; // excel表合并单元格

// console.log(multiHeader);

// console.log(multiHeader2);

// console.log(header);

// console.log(data);

// console.log(defaultTitle);

// console.log(merges);

data.unshift(header);

//此处是第二行表头

for (let i = multiHeader2.length - 1; i > -1; i--) {

data.unshift(multiHeader2[i])

}

//此处是第一行行表头

for (let i = multiHeader.length - 1; i > -1; i--) {

data.unshift(multiHeader[i])

}

var ws_name = "SheetJS";

var wb = new Workbook(),

ws = sheet_from_array_of_arrays(data);

//合并excel单元格和设置表格宽度

if (merges.length > 0) {

if (!ws['!merges']) ws['!merges'] = [];

merges.forEach(item => {

ws['!merges'].push(XLSX.utils.decode_range(item))

})

}

if (autoWidth) {

/*设置worksheet每列的最大宽度*/

const colWidth = data.map(row => row.map(val => {

/*先判断是否为null/undefined*/

if (val == null) {

return {

'wch': 10

};

}

/*再判断是否为中文*/

else if (val.toString().charCodeAt(0) > 255) {

return {

'wch': val.toString().length * 2

};

} else {

return {

'wch': val.toString().length

};

}

}))

/*以第一行为初始值*/

let result = colWidth[0];

for (let i = 1; i < colWidth.length; i++) {

for (let j = 0; j < colWidth[i].length; j++) {

if (result[j]['wch'] < colWidth[i][j]['wch']) {

result[j]['wch'] = colWidth[i][j]['wch'];

}

ws['!cols'] = result;

}

//--------------------------------------------------------

/* add worksheet to workbook */

wb.SheetNames.push(ws_name);

wb.Sheets[ws_name] = ws;

var wbout = XLSX.write(wb, {

bookType: 'xlsx',

bookSST: false,

type: 'binary'

});

var title = defaultTitle || '列表'

saveAs(new Blob([s2ab(wbout)], {

type: "application/octet-stream"

}), title + ".xlsx")

}

第三步：元素，及页面method里添加的方法

exportmulti() {

import("@/exportExcel/Export2Excel2").then(excel => {

const multiHeader = [

[

"企业名称",

"电子收费金额",

"",

"应退款金额",

"",

"电子收费资金明细分类",

"",

]

];

const multiHeader2 = [

[

"",

"微信公众商户平台",

"微信开放商户平台",

"支付宝",

"合计",

"2016.9.1前",

"",

"2016.9.1-2017.12.31",

"",

"2018.1.1-2019.12.31",

"",

"2020.1.1后",

"",

"合计",

"2016.9.1前",

"",

"2016.9.1-2017.12.31",

"",

"2018.1.1-2019.12.31",

"",

"实收",

"",

"合计"

]

];

const tHeader = [

"",

"微信",

"支付宝",

"微信",

"支付宝",

"微信",

"支付宝",

"微信",

"支付宝",

"",

"微信",

"支付宝",

"微信",

"支付宝",

"微信",

"支付宝",

"微信",

"支付宝",

];

// table对应导入字段

const filterVal = [

"companyName",

"eWeChatMountApp",

"eAlipay",

"eTotal",

"reWeChatBefore",

"reAlipayBefore",

"reWeChatBe",

"reAlipayBe",

"reWeChatTe",

"reAlipayTe",

"reWeChatAf",

"reAlipayAf",

"reTotal",

"deWeChatBefore",

"deAlipayBefore",

"deWeChatBe",

"deAlipayBe",

"deWeChatTe",

"deAlipayTe",

"deWeChatAf",

"deAlipayAf",

"deTotal"

];

let list = this.capitalStatementData;

// table数据转换

const data = this.formatJson(filterVal, list);

//进行所有表头的单元格合并，建议一行一行来，不然容易整乱

const merges = [

"A1:A3",

"B1:E1",

"B2:B3",

"C2:C3",

"D2:D3",

"E2:E3",

"F1:N1",

"F2:G2",

"H2:I2",

"J2:K2",

"L2:M2",

"N2:N3",

"O1:W1",

"O2:P2",

"Q2:R2",

"S2:T2",

"U2:V2",

"W2:W3"

];

// 导出excel表，的标题

const defaultTitle = "各企业资金统计";

// 按照顺序传参

excel.export_json_to_excel({

multiHeader, //这里是第一行的表头

multiHeader2, //这里是第二行的表头

tHeader, //这里应该是算第三行的表头

data, // 表格数据

defaultTitle, // excel表的名称

merges // 合并excel单元格

});

/* 转换table数据，以便正确导出数据

-------------------------------------------------------------------*/

formatJson(filterVal, jsonData) {

return jsonData.map(v =>

filterVal.map(j => {

if (j !== "companyName") {

return parseTime(v[j]);

} else {

return v[j];

}

})

);