table表格导出为excel文件并设置样式
安装xlsx、xlsx-style-medalsoft 的 npm 包:
npm i xlsx xlsx-style-medalsoft
设置全局:
Vue.prototype.$XLSX = XLSX; // 设置全局
Vue.prototype.$XLSXStyle = XLSXStyle; // 设置全局
具体代码实现:
// 导出excel文件
exportFile() {
// 导出名称
let excelName = "file" + dateFormat(new Date(), "YYYY-MM-DD HH-mm-ss") + ".xlsx";
const xlsxParam = { raw: true }; // 转化成Excel使用原始格式
const table_book = this.$XLSX.utils.table_to_book(
document.querySelector("#table"),
xlsxParam
);
// console.log(table_book)
let wbs = table_book.Sheets.Sheet1;
// console.log(wbs)
let arrA = Object.keys(wbs);
arrA.forEach((item, index) => {
if (wbs[item].v == "") {
delete wbs[item];
}
});
// 设置列宽
this.column.forEach((item, i) => {
wbs["!cols"][i] = { wch: 16 };
});
// 设置行高
this.allTable.forEach((row, rowIndex) => {
wbs["!rows"][rowIndex + 1] = { hpt: 24 };
});
wbs["!rows"][0] = { hpt: 24 };
// 循环遍历每一个表格,设置样式
for (const key in wbs) {
if (key.indexOf("!") === -1) {
wbs[key].s = {
font: {
sz: 11, // 字体大小
bold: false, // 加粗
name: "宋体", // 字体
color: {
rgb: "000000", // 十六进制,不带#
},
},
alignment: {
// 文字居中
horizontal: "center",
vertical: "center",
wrapText: false, // 文本自动换行
},
border: {
// 设置边框
top: { style: "thin" },
bottom: { style: "thin" },
left: { style: "thin" },
right: { style: "thin" },
},
};
}
// 设置表头样式
if (
key == "A1" || key == "B1" || key == "C1" || key == "D1" || key == "E1"
) {
wbs[key].s.fill = {
bgColor: { indexed: 64 },
fgColor: { rgb: "95B3D7" },
};
}
}
let table_write = this.$XLSXStyle.write(table_book, {
bookType: "xlsx",
type: "buffer",
});
try {
this.$FileSaver.saveAs(
new Blob([table_write], { type: "application/octet-stream" }),
excelName
);
} catch (e) {
if (typeof console !== "undefined") {
// console.log(e, table_write);
}
}
return table_write;
},