表格导出使用xlsx-style-vite修改导出样式

最近项目需求让封装一个公共表格导出组件，项目是vue3+vite+ts搭建的，最初下载xlsx-style总是报错 this relative module was not found: ./cptable and ./node_modules/xlsx-style，查到有让改依赖解决报错问题的感觉不实用后续再次下载依赖还会出问题，困扰了一天没有解决问题，后续突然发现了xlsx-style-vite完美解决导出样式问题。

1.下载依赖：npm install xlsx-style-vite

2.在需要的页面引用 import * as XLSX_STYLE from 'xlsx-style-vite

下面是样式设置的代码：

//设置公共样式
const setPubExcel = (data) => {
    data["!cols"] = [];
    const excludes = ['!cols', '!fullref', '!merges', '!ref', '!rows'];
    for (let key in data) {
        if (data.hasOwnProperty(key)) {
            if (!excludes.includes(key)) {
                data[key].s = {
                    border: {
                        top: { style: 'thin', color: { rgb: '000000' } },
                        bottom: { style: 'thin', color: { rgb: '000000' } },
                        left: { style: 'thin', color: { rgb: '000000' } },
                        right: { style: 'thin', color: { rgb: '000000' } },
                    },
                    alignment: {
                        horizontal: 'center',//水平居中对齐
                       vertical: 'center',//垂直居中
                        wrapText: true,//是否换行
                    }
                     fill: {
                         fgColor: { rgb: '0070C0' },
                    },
                }
            }
        }
    }
};

// 合并行列样式
const mergeCell = (range, ws) => {
    let cols = ['A', 'B', 'C', 'D', 'E', 'F'];
    range.forEach(item => {
        let style = {
            s: {
                border: {
                    top: { style: 'thin', color: { rgb: '000000' } },
                    bottom: { style: 'thin', color: { rgb: '000000' } },
                    left: { style: 'thin', color: { rgb: '000000' } },
                    right: { style: 'thin', color: { rgb: '000000' } },
                },
                alignment: {
                    horizontal: 'center',//水平居中对齐
                    vertical: 'center',//垂直居中
                    wrapText: true,//自动换行
                },
            }
        };
        // 处理合并行
        for (let i = item.s.c; i <= item.e.c; i++) {
            ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style;

            // 处理合并列
            for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
                ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
            }
        }
    })
    return ws
};

然后就是在导出方法中调用上述方法就可以实现对导出样式的修改                                                  

 let workbook = XLSX.utils.book_new();
    let table = document.querySelector('#' + tableId);
    let wb = XLSX.utils.table_to_sheet(table);
    XLSX.utils.book_append_sheet(workbook, wb, name);
    mergeCell(wb['!merges'], wb)
    setPubExcel(wb);

 完整代码如下：

<script lang="ts"  setup>
import * as XLSX from 'xlsx';
import * as XLSX_STYLE from 'xlsx-style-vite';
import FileSaver from 'file-saver';

//设置公共样式
const setPubExcel = (data) => {
    data["!cols"] = [];
    const excludes = ['!cols', '!fullref', '!merges', '!ref', '!rows'];
    for (let key in data) {
        if (data.hasOwnProperty(key)) {
            if (!excludes.includes(key)) {
                data[key].s = {
                    border: {
                        top: { style: 'thin', color: { rgb: '000000' } },
                        bottom: { style: 'thin', color: { rgb: '000000' } },
                        left: { style: 'thin', color: { rgb: '000000' } },
                        right: { style: 'thin', color: { rgb: '000000' } },
                    },
                    alignment: {
                        horizontal: 'center',//水平居中对齐
                       vertical: 'center',//垂直居中
                        wrapText: true,//是否换行
                    }
                     fill: {
                         fgColor: { rgb: '0070C0' },
                    },
                }
            }
        }
    }
};

// 合并行列样式
const mergeCell = (range, ws) => {
    let cols = ['A', 'B', 'C', 'D', 'E', 'F'];
    range.forEach(item => {
        let style = {
            s: {
                border: {
                    top: { style: 'thin', color: { rgb: '000000' } },
                    bottom: { style: 'thin', color: { rgb: '000000' } },
                    left: { style: 'thin', color: { rgb: '000000' } },
                    right: { style: 'thin', color: { rgb: '000000' } },
                },
                alignment: {
                    horizontal: 'center',//水平居中对齐
                    vertical: 'center',//垂直居中
                    wrapText: true,//自动换行
                },
            }
        };
        // 处理合并行
        for (let i = item.s.c; i <= item.e.c; i++) {
            ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style;

            // 处理合并列
            for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
                ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
            }
        }
    })
    return ws
};

const s2ab = (s) => {
    const buf = new ArrayBuffer(s.length);
    const view = new Uint8Array(buf);
    for (let i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xff;
    }
    return buf;
};

//导出公共方法
const exportExcel = async (filename, tableId, name) => {
    //let xlsxParam = { raw: true };// 导出的内容只做解析，不进行格式转换
    let workbook = XLSX.utils.book_new();
    let table = document.querySelector('#' + tableId);
    let wb = XLSX.utils.table_to_sheet(table);
    XLSX.utils.book_append_sheet(workbook, wb, name);
    mergeCell (wb['!merges'], wb)
    setPubExcel(wb);

    let wbout = XLSX_STYLE.write(workbook, { bookType: 'xlsx', bookSST: false, type: 'binary', });
    try {
        FileSaver.saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream;charset=utf-8"' }), filename + '.xlsx')
    } catch (e) {
        if (typeof console !== 'undefined') {
            console.log(e, wbout)
        }
    }
    return wbout
};
</script>

完美解决问题 ！！