使用場景：前端下載表格中用戶查看的數(shù)據(jù)。
1. 前端VUE+ElementUI導(dǎo)出復(fù)雜（多級表頭、合并單元格）excel表格 el-table轉(zhuǎn)為excel導(dǎo)出
3. 安裝xlsx-style報錯./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js
部分偽代碼如下：
# 1. 安裝
npm i file-saver
npm i xlsx
npm i xlsx-style
# 2. 引入：
import FileSaver from 'file-saver';
import XLSX from 'xlsx/dist/xlsx.core.min';
import XLSXS from "xlsx-style";
# 3. 使用
<el-button @click="exportExcel(123)">導(dǎo)出</el-button>
exportExcel(excelName) {
try {
const $e = this.$refs['report-table'].$el
let $table = $e.querySelector('.el-table__fixed')
if(!$table) {
$table = $e
}
const wb = XLSX.utils.table_to_book($table, {raw:true})
const wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST:true, type: 'array'})
FileSaver.saveAs(
new Blob([wbout],{type: 'application/octet-stream'}),
`${excelName}.xlsx`,
)
} catch (e) {
if (typeof console !== 'undefined') console.error(e)
}
}
// 設(shè)置表格樣式
setExlStyle(data) {
let borderAll = {
//單元格外側(cè)框線
top: {style: "thin"},
bottom: {style: "thin"},
left: {style: "thin"},
right: {style: "thin"}
};
data["!cols"] = [];
for (let key in data) {
if (data[key] instanceof Object) {
data[key].s = {
// 設(shè)置邊框
border: borderAll,
alignment: {
// 自動換行
wrapText: 1,
//水平居中對齊
horizontal: "center",
vertical: "center",
},
font: {
sz: 11,
},
bold: true,
// numFmt: 0
// 設(shè)置填充色
fill: {
patternType: 'solid',
fgColor: { theme: 3, tint: 0.3999755851924192, rgb: 'F5F7FA' },
bgColor: { theme: 7, tint: 0.3999755851924192, rgb: 'F5F7FA' },
},
};
// 設(shè)置列寬
data["!cols"].push({ wpx: 130 });
}
}
return data;
},
// 處理表格邊框部分不顯示
addRangeBorder(range, ws) {
// s:起始位置，e:結(jié)束位置
// let arr =["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
let arr =["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z", "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"];
range.forEach((item) => {
let startRowNumber = Number(item.s.r), startColumnNumber = Number(item.s.c),endColumnNumber = Number(item.e.c),
endRowNumber = Number(item.e.r);
// 合并單元格時會丟失邊框樣式都伪，例如A1->A4 此時內(nèi)容在A1 而range內(nèi)獲取到的是從0開始的，所以開始行數(shù)要+2
for (let i = startColumnNumber; i <= endColumnNumber; i++) {
for(let j = startRowNumber + 2 ; j <= endRowNumber + 1 ; j++) {
ws[arr[i] + j] = {
s: {
border: {
top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" },
},
},
};
}
}
});
return ws;
}
若 xlsx-style報“./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js”，解決辦法如下：
在vue.config.js中：
configureWebpack: config => {
config.externals = [ {'./cptable': 'var cptable'}]
},
效果圖
注意：多級表頭會出現(xiàn)邊框不完整的情況
效果圖
解決辦法：
//手動補上標題欄的高度
ws['A2']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['B1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['C1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['D1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
最終效果圖
最終效果圖
