vue前端根据el-table导出excel

1.导入xlsx、xlsx-style、file-saver

npm install --save xlsx
npm install --save xlsx-style
npm install --save file-saver

2.防止xlsx-style报错 vue.config.js添加

chainWebpack(config) {
    config.externals({ "./cptable": "var cptable" }); // xlsx-style 
}

3.编写utils

import * as XLSX from "xlsx";
import XLSXStyle from "xlsx-style";
import { saveAs } from "file-saver";
/**
 *
 * @param elt 
 * @param sheetName 内部sheet名称
 * @param fileName 导出的文件名
 * @param titleNum  标题行数
 */
export function exportTable(elt, sheetName, fileName, titleNum) {
    const xlsxParam = { sheet: sheetName, raw: true };
    const fix = elt.querySelector(".el-table__fixed"); //如果是都给了fixed样式
    let wb;
    if (fix) {
        //判断要导出的节点中是否有fixed的表格，如果有，转换excel时先将该dom移除，然后append回去
        wb = XLSX.utils.table_to_book(elt.removeChild(fix), xlsxParam);
        elt.appendChild(fix);
    } else {
        wb = XLSX.utils.table_to_book(elt, xlsxParam);
    }
    // let wb = XLSX.utils.table_to_book(elt, { sheet: sheetName, raw: true });
    let range = XLSX.utils.decode_range(wb.Sheets[sheetName]["!ref"]);
    //单元格边框样式
    let borderStyle = {
        top: {
            style: "thin",
            color: { rgb: "000000" },
        },
        bottom: {
            style: "thin",
            color: { rgb: "000000" },
        },
        left: {
            style: "thin",
            color: { rgb: "000000" },
        },
        right: {
            style: "thin",
            color: { rgb: "000000" },
        },
    };
    let cWidth = [];
    for (let C = range.s.c; C <= range.e.c; ++C) {
        //SHEET列
        let len = 100; //默认列宽
        let len_max = 400; //最大列宽
        for (let R = range.s.r; R <= range.e.r; ++R) {
            //SHEET行
            let cell = { c: C, r: R }; //二维 列行确定一个单元格
            let cell_ref = XLSX.utils.encode_cell(cell); //单元格 A1、A2
            if (wb.Sheets[sheetName][cell_ref]) {
                // if (R == 0){
                if (R < titleNum) {
                    wb.Sheets[sheetName][cell_ref].s = {
                        //设置第一行单元格的样式 style
                        font: {
                            // sz: 15,
                            color: { rgb: "060B0E" },
                            bold: true,
                        },
                        alignment: {
                            horizontal: "center",
                            vertical: "center",
                        },
                        fill: {
                            fgColor: { rgb: "E4E4E4" },
                        },
                        border: borderStyle, //用上面定义好的边框样式
                    };
                } else {
                    wb.Sheets[sheetName][cell_ref].s = {
                        alignment: {
                            horizontal: "center",
                            vertical: "center",
                        },
                        border: borderStyle, //用上面定义好的边框样式
                    };
                }
                //动态自适应：计算列宽
                let va = JSON.parse(
                    JSON.stringify(wb.Sheets[sheetName][cell_ref].v)
                );
                var card1 = JSON.parse(JSON.stringify(va)).match(
                    /[\u4e00-\u9fa5]/g
                ); //匹配中文
                var card11 = "";
                if (card1) {
                    card11 = card1.join("");
                }
                var card2 = JSON.parse(JSON.stringify(va)).replace(
                    /([^\u0000-\u00FF])/g,
                    ""
                ); //剔除中文
                let st = 0;
                if (card11) {
                    // st += card11.length * 16  //中文字节码长度
                    st += card11.length * 20; //中文字节码长度
                }
                if (card2) {
                    // st += card2.length * 8  //非中文字节码长度
                    st += card2.length * 10; //非中文字节码长度
                }
                if (st > len) {
                    len = st;
                }
            }
        }
        if (len > len_max) {
            //最大宽度
            len = len_max;
        }
        cWidth.push({ wpx: len }); //列宽
    }
    wb.Sheets[sheetName]["!cols"] = cWidth;
    var wopts = { bookType: "xlsx", bookSST: false, type: "binary" };
    var wbout = XLSXStyle.write(wb, wopts); //一定要用XLSXStyle不要用XLSX，XLSX是没有格式的！
    saveAs(new Blob([s2ab(wbout)], { type: "" }), fileName + ".xlsx");
}
function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
}

4.引用

let elt = document.getElementById("educe-table");
exportTable(elt, "小题分统计", "小题分统计导出", 3)