基于exceljs的前端多级表头的导出

磨了两天半磨出来的功能

exceljs官网

https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md

贴个demo图

 

上代码：

引入：

import ExcelJS from 'ExcelJS';

 

数据：

 data() {
        return {
            tableColumn: [
                { prop: 'a', label: 'pos码' },
                { prop: 'b', label: '门店' },
                { prop: 'c', label: '日期' },
                {
                    prop: 'd', label: '类型', children: [
                        { prop: 'x', label: '类型1' },
                        { prop: 'x', label: '类型2' },
                    ]
                },
                { prop: 'e', label: '分类' },
                {
                    prop: 'f', label: '总金额', children: [
                        { prop: 'g', label: '支付宝' },
                        { prop: 'h', label: '微信' },
                        { prop: 'i', label: '银行卡' },
                        { prop: 'j', label: '挂账', },
                    ]
                },
            ],
            list: [
                { a: '233', b: '毁三代点点', c: '2024-02-02', d: '果蔬类', e: '大类', f: '500', g: 100, h: 200, i: 250, k: 230, l: 80 },
                { a: '233', b: '毁三代点点', c: '2024-02-02', d: '果蔬类', e: '大类', f: '500' },
            ],
            letterMap: ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'],
        }
    },

方法：

methods: {
        async exportExcel() {
            /* eslint-disable */
            const workbook = new ExcelJS.Workbook();
            const sheet = workbook.addWorksheet("sheet1");  // 新建工作表
            let deepLength = 0;
            const columnsList: Array<{ header: string, key: string, width?: number }> = [];
            const header = [];
            const checkedLength = (data) => {
                if (data.children) {
                    deepLength += 1;
                    header.push([]);
                    data.children.map((x) => {
                        checkedLength(x);
                    });
                } else {
                    columnsList.push({ header: data.label, key: data.prop });
                }
            }
            this.tableColumn.map((x) => {
                checkedLength(x);
            });
            this.sheet.columns = columnsList; // 设置表头

            const setHeader = (data, deepNumber) => {
                if (data.children) {
                    deepNumber++;
                    data.children.map((x) => {
                        setHeader(x, deepNumber);
                    });
                    const titleLength = header[deepNumber - 1].length - JSON.parse(JSON.stringify(header[deepNumber - 1])).reverse().findIndex((x) => x);
                    header[deepNumber - 1][titleLength] = data.label;
                } else {
                    for (let i in header) {
                        if (i == deepNumber) {
                            header[i].push(data.label);
                        } else {
                            header[i].push('');
                        }
                    }
                }
            };
            this.tableColumn.map((x) => {
                let deepNumber = 0;
                setHeader(x, deepNumber);
            });
            header.map((x, index) => {
                sheet.getRow(index + 1).values = x;
            })
            header.map((x, index) => {  // 合并表头
                x.map((y, indexY) => {
                    if (y) {
                        // 横向合并
                        let endNumber = index;
                        for (let i = index; i < deepLength; i++) {
                            if (header[i + 1] && header[i + 1][indexY]) break;
                            endNumber = i + 1;
                        }
                        if ((endNumber - index) >= 1) {
                            try {
                                sheet.mergeCells(`${this.letterMap[indexY]}${index + 1}:${this.letterMap[indexY]}${endNumber + 1}`);
                                sheet.getCell(`${this.letterMap[indexY]}${index + 1}`).alignment = { vertical: 'middle' };
                            } catch (error) {
                                // console.log(error);
                            }
                        }
                        // 纵向合并
                        let rightNumber = indexY;
                        for (let i = rightNumber + 1; i < x.length; i++) {
                            if (x[i]) break;
                            rightNumber = i;
                        }
                        if ((rightNumber - indexY) >= 1) {
                            try {
                                sheet.mergeCells(`${this.letterMap[indexY]}${index + 1}:${this.letterMap[rightNumber]}${index + 1}`);
                                sheet.getCell(`${this.letterMap[indexY]}${index + 1}`).alignment = { vertical: 'middle' };
                            } catch (error) {
                                // console.log(error);
                            }
                        }
                    }
                });
            })
            this.list.map((x) => {
                sheet.addRow(x);  // 写入数据
            });
            await workbook.xlsx.writeBuffer().then((data) => {
                const blob = new Blob([data], { type: 'application/vnd.ms-excel' });
                const link = document.createElement("a"); // a标签下载
                link.href = window.URL.createObjectURL(blob); // href属性指定下载链接
                link.download = "导出.xlsx"; // dowload属性指定文件名
                link.click(); // click()事件触发下载
                window.URL.revokeObjectURL(link.href); // 释放内存
            });
        }

 

demo就这样子，正式版加了些功能，按需添加

 

参考博客

https://www.cnblogs.com/yeminglong/p/18079930