使用场景：前端下载表格中用户查看的数据。
1. 前端VUE+ElementUI导出复杂（多级表头、合并单元格）excel表格 el-table转为excel导出
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)">导出</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)
}
}
// 设置表格样式
setExlStyle(data) {
let borderAll = {
//单元格外侧框线
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 = {
// 设置边框
border: borderAll,
alignment: {
// 自动换行
wrapText: 1,
//水平居中对齐
horizontal: "center",
vertical: "center",
},
font: {
sz: 11,
},
bold: true,
// numFmt: 0
// 设置填充色
fill: {
patternType: 'solid',
fgColor: { theme: 3, tint: 0.3999755851924192, rgb: 'F5F7FA' },
bgColor: { theme: 7, tint: 0.3999755851924192, rgb: 'F5F7FA' },
},
};
// 设置列宽
data["!cols"].push({ wpx: 130 });
}
}
return data;
},
// 处理表格边框部分不显示
addRangeBorder(range, ws) {
// s:起始位置，e:结束位置
// 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 此时内容在A1 而range内获取到的是从0开始的，所以开始行数要+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'}]
},
效果图
注意：多级表头会出现边框不完整的情况
效果图
解决办法：
//手动补上标题栏的高度
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'}}}};
最终效果图
最终效果图
