table表格导出为excel文件并设置样式

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;
},