报表数据导出excel

实现目标：1、多张报表，导出在同一个sheet页；2、多张报表导出多个sheet页；3、多表头表格自动合并单元格
使用插件：exceljs

// 列项配置
const options = {
    titleField: 'text', // 名称字段
    keyField: 'id', // 数据字段
    childrenField: 'children', // 子级字段
};
// 单元格样式
const excelCellStyle = {
    border: {
        top: {
            style: 'thin',
            color: {
                argb: '808080',
            },
        },
        left: {
            style: 'thin',
            color: {
                argb: '808080',
            },
        },
        bottom: {
            style: 'thin',
            color: {
                argb: '808080',
            },
        },
        right: {
            style: 'thin',
            color: {
                argb: '808080',
            },
        },
    },
    protection: {
        locked: false,
    },
};
/**
 * @desc 报表导出：多sheet页，多报表
 * @param sheets Array [
                    {
                        tables: [
                            {
                                columns:[], Array //列项：传进来的列项都会被导出。如果有不需要导出的列，过滤之后再传进来
                                datas:[],  Array //数据
                                title:String,// 报表名称 '研发支出明细表',
                                columnOption: { }, // 列项配置
                            },
                        ],
                       sheetName: '我是sheet页名称',
                    },
                ];
 * @param fileName String 文件名称
 */
export default function exportExcel({ sheets, fileName = new Date().valueOf() }) {
    const ExcelJS = require('exceljs');
    // 创建新工作簿对象
    const workbook = new ExcelJS.Workbook();

    // 循环sheet页
    for (let s = 0; s < sheets.length; s++) {
        let sheet = sheets[s]; // 单sheet页
        let sheetName = sheet.sheetName || `Sheet${s + 1}`; // sheet页名称

        // 创建新工作表
        const worksheet = workbook.addWorksheet(sheetName);

        worksheet.properties.defaultRowHeight = 24; // 行高
        worksheet.properties.defaultColWidth = 20; // 列宽
        for (let t = 0; t < sheet.tables.length; t++) {
            if (t != 0) {
                // 空两行
                worksheet.addRows([[], []]);
            }
            let table = sheet.tables[t];
            let columns = table.columns; // 表头
            // 列项配置
            let columnOption = table.columnOption || options;
            const { titleField, keyField, childrenField } = columnOption;
            const { header, mergeRecord, floorCols } = getExcelTableColumn(columns, titleField, childrenField);
            // 表格标题
            worksheet.addRow([table.title]);
            //  当前sheet页总行数
            let rowCount = worksheet.rowCount;

            // 表格标题合并单元格
            worksheet.mergeCells(rowCount, 1, rowCount, floorCols.length);
            // 表格标题样式
            setExcelTableHeaderStyle(worksheet.getRow(rowCount));
            // 添加表头
            worksheet.addRows(header);
            // 表头合并单元格
            mergeRecord.forEach((row) => {
                let endR = row.e.r + rowCount + 1;
                let endC = row.e.c + 1;
                let startR = row.s.r + rowCount + 1;
                let startC = row.s.c + 1;
                //  按开始行，开始列，结束行，结束列合并（worksheet.mergeCells(10,11,12,13); 相当于 K10:M12）
                if (!(endR == startR && endC == startC)) {
                    worksheet.mergeCells(startR, startC, endR, endC);
                }
            });

            // 循环表头设置样式
            for (let i = rowCount + 1; i <= rowCount + header.length; i++) {
                const excelRow = worksheet.getRow(i);
                setExcelTableHeaderStyle(excelRow);
            }
            let datas = table.datas; // 表格数据
            // 列字段
            let fields = floorCols.map((r) => r[keyField]);
            const excelDatas = getExcelTableData(datas, fields);
            worksheet.addRows(excelDatas);

            // 当前sheet页总行数 worksheet.rowCount
            let count = 1;
            for (let i = rowCount + header.length + 1; i <= worksheet.rowCount; i++) {
                const excelRow = worksheet.getRow(i);
                // 设置表格数据样式
                count++;
                let isStripes = count % 2; // 斑马线
                setExcelTableBodyStyle(excelRow, floorCols, isStripes);
            }
        }
    }
    // 保存工作簿
    workbook.xlsx.writeBuffer().then((buffer) => {
        const blob = new Blob([buffer], { type: 'application/octet-stream' });
        // 导出 xlsx
        downloadFile(blob, fileName);
    });
}
// 设置表头样式
function setExcelTableHeaderStyle(excelRow) {
    excelRow.eachCell({ includeEmpty: true }, (excelCell) => {
        Object.assign(excelCell, excelCellStyle, {
            font: {
                bold: true,
                color: {
                    argb: '606266',
                },
            },
            fill: {
                type: 'pattern',
                pattern: 'solid',
                fgColor: {
                    argb: 'E2E2E2',
                },
            },
            alignment: {
                vertical: 'middle', // 垂直
                horizontal: 'center', // 水平
                wrapText: true, // 自动换行
            },
        });
    });
}
// 设置表格数据样式
function setExcelTableBodyStyle(excelRow, floorCols, isStripes) {
    excelRow.eachCell({ includeEmpty: true }, (excelCell, index) => {
        let column = floorCols[index - 1];
        let isDecimal = column.type == 'decimal';
        let customFormatter = column.customFormatter;
        // 千分位两位小数数值格式：'#,##0.00_ '
        // 千分位'#,##'
        let defaultNumFmt = '#,##0.00'; // 保留两位小数，千分位
        let numFmt = '';
        if (customFormatter) {
            if (customFormatter.millesimal != false) numFmt += '#,##0';
            if (typeof customFormatter.digit != 'undefined' && !isNaN(customFormatter.digit)) {
                for (let i = 0; i < customFormatter.digit; i++) {
                    if (!i) numFmt += '.';
                    numFmt += '0';
                }
            } else numFmt += '.00';
        }
        if (isDecimal) {
            // 最后加上 '_ ': 加上就是数值格式，不加就是货币格式
            excelCell.numFmt = (numFmt || defaultNumFmt) + '_ ';
        }
        Object.assign(excelCell, excelCellStyle, {
            alignment: {
                vertical: 'middle', // 垂直
                horizontal: isDecimal ? 'right' : 'left', // 水平
                // wrapText: isDecimal ? false : true, // 自动换行
            },
        });
        if (isStripes) {
            // 斑马线
            excelCell.fill = {
                type: 'pattern',
                pattern: 'solid',
                fgColor: {
                    argb: 'f8f8f9',
                },
            };
        }
    });
}

/**
 * 把表头转换成excel单元格数据，并获取表头的合并单元格及最低层列
 * @param {*} columns 列项
 * @param {*} titleField  列名称字段
 * @param {*} childrenField  列数据字段
 * @returns
 */
function getExcelTableColumn(columns, titleField, childrenField) {
    // 过滤不需要导出的列，及最低层列
    const { newColumns, floorCols } = transformColumn(columns, childrenField);

    // 表头占几行
    let headerH = columnHeight(newColumns);
    // 表头占几列
    let headerW = columnWidth(newColumns);
    // 表头二维数组
    let header = [];
    for (let rowNum = 0; rowNum < headerH; rowNum++) {
        header[rowNum] = [];
        for (let colNum = 0; colNum < headerW; colNum++) {
            header[rowNum][colNum] = '';
        }
    }
    // 偏移量
    let offset = 0;
    // 合并单元格位置
    let mergeRecord = [];

    for (let item of newColumns) {
        generateExcelColumn(header, 0, offset, item, mergeRecord, titleField, childrenField);
        offset += treeWidth(item);
    }

    return {
        header,
        mergeRecord,
        floorCols,
    };
}
// 表个数据转换成单元格数据
function getExcelTableData(datas, fields) {
    let excelDatas = [];

    datas.forEach((row, index) => {
        let newRow = [];
        fields.forEach((field) => {
            let cellValue = row[field] || '';
            newRow.push(cellValue);
        });
        excelDatas.push(newRow);
    });
    return excelDatas;
}
/**
 * 过滤不需要导出的列项，生生新列
 * @param {*} columns
 * @param {*} childrenField
 * @param {*} floorCols 最底层列 （不用传参）
 */
function transformColumn(columns, childrenField = 'children', floorCols = []) {
    let newCols = []; // 新生成的列项
    for (let i = 0; i < columns.length; i++) {
        let col = columns[i];
        // hidden：true不显示
        if (col.hidden) {
            // 跳过
            break;
        }
        if (col[childrenField] && col[childrenField].length) {
            // 有子级
            let childs = transformColumn(col[childrenField], childrenField, floorCols);
            if (childs.length) {
                col[childrenField] = childs;
            }
        } else {
            // 没有子级
            floorCols.push(col);
        }
        newCols.push(col);
    }
    return {
        newColumns: newCols,
        floorCols,
    };
}
/**
 * 生成单元格列
 * @param {*} columnTable Array 表头二维数组
 * @param {*} rowOffset Number 行偏移
 * @param {*} colOffset Number 列偏移
 * @param {*} columnDefine Object 列项
 * @param {*} mergeRecord Array 合并单元格数组
 */
function generateExcelColumn(columnTable, rowOffset, colOffset, columnDefine, mergeRecord, titleField, childrenField) {
    let columnWidth = treeWidth(columnDefine);
    // 名称
    columnTable[rowOffset][colOffset] = columnDefine[titleField];
    // 存在子级
    if (columnDefine[childrenField]) {
        mergeRecord.push({ s: { r: rowOffset, c: colOffset }, e: { r: rowOffset, c: colOffset + columnWidth - 1 } });
        let tempOffSet = colOffset;
        // 循环子级
        for (let child of columnDefine[childrenField]) {
            generateExcelColumn(columnTable, rowOffset + 1, tempOffSet, child, mergeRecord, titleField, childrenField);
            tempOffSet += treeWidth(child);
        }
    } else {
        if (rowOffset !== columnTable.length - 1) mergeRecord.push({ s: { r: rowOffset, c: colOffset }, e: { r: columnTable.length - 1, c: colOffset } });
    }
}
/**
 * 数组高度（表头占几行）
 * @param {*} column  Array
 * @returns
 */
function columnHeight(column) {
    let height = 0;
    for (let item of column) {
        height = Math.max(treeHeight(item), height);
    }
    return height;
}
/**
 * 数组宽度（表头占几列）
 * @param {*} column  Array
 * @returns
 */
function columnWidth(column) {
    let width = 0;
    for (let item of column) {
        width += treeWidth(item);
    }
    return width;
}
/**
 * 获取高度
 * @param {*} root Object
 * @returns
 */
function treeHeight(root) {
    if (root) {
        if (root.children && root.children.length !== 0) {
            let maxChildrenLen = 0;
            for (let child of root.children) {
                maxChildrenLen = Math.max(maxChildrenLen, treeHeight(child));
            }
            return 1 + maxChildrenLen;
        } else {
            return 1;
        }
    } else {
        return 0;
    }
}
/**
 * 获取宽度
 * @param {*} root Object
 * @returns
 */
function treeWidth(root) {
    if (!root) return 0;
    if (!root.children || root.children.length === 0) return 1;
    let width = 0;
    for (let child of root.children) {
        width += treeWidth(child);
    }
    return width;
}

/**
 * 下载文件
 * @param {*} blob
 * @param {*} filename
 */
function downloadFile(blob, filename) {
    const type = 'xlsx';
    if (window.Blob) {
        if (navigator.msSaveBlob) {
            navigator.msSaveBlob(blob, `${filename}.${type}`);
        } else {
            const linkElem = document.createElement('a');
            linkElem.target = '_blank';
            linkElem.download = `${filename}.${type}`;
            linkElem.href = URL.createObjectURL(blob);
            document.body.appendChild(linkElem);
            linkElem.click();
            document.body.removeChild(linkElem);
        }
    }
}