CostAccountingSys/src/utils/tableToMultiHeaderExcel.ts

//exportTableToMultiExcel

import * as XLSX from 'xlsx';
import { saveAs } from 'file-saver';


type DataType = { [key: string]: string | number | null };

type Column = {
    title: string;
    dataIndex: string;
    key: string;
    children?: Column[];
};

type Merge = {
    s: { r: number; c: number };
    e: { r: number; c: number };
};



function getMaxDepth(columns: Column[]): number {
    let maxDepth = 0;
    for (const column of columns) {
        if (column.children) {
            // 如果有子列，递归计算子列的深度
            const depth = 1 + getMaxDepth(column.children);
            maxDepth = Math.max(maxDepth, depth);
        } else {
            // 如果没有子列，深度为1
            maxDepth = Math.max(maxDepth, 1);
        }
    }
    return maxDepth;
}




function processColumns(columns: Column[], startRow = 0, startCol = 0, maxDepth = 0,colLength=0): { headers: any[][], merges: Merge[] } {

    let headers: any[][] = Array.from({ length: maxDepth }, () => Array(colLength).fill(''));

    let merges: Merge[] = [];
    let colIndex = startCol;

    // console.log({columns});

    for (const column of columns) {
        let endRow = startRow;
        let endCol = colIndex;

        if (column.children) {

            const result = processColumns(column.children, startRow + 1, colIndex, maxDepth,colLength);

            headers = headers.map((row, i) => {
                
                if (i === startRow) {
                    return [...row.slice(0, colIndex), ...result.headers[i], ...row.slice(colIndex + result.headers[i].length)];
                } else {
                    return [...row.slice(0, colIndex), ...result.headers[i].slice(colIndex, result.headers[i].length), ...row.slice(colIndex + result.headers[i].length)];
                }
            });

            merges = [...merges, ...result.merges];
            endCol += getAllColumns(column.children).length - 1;  // 根据所有子列的总数来更新 endCol
            endRow = startRow;  // 父列的标题应该在子列标题的上一行

        } else {
            endRow = maxDepth - 1;
        }

        for (let i = startRow; i <= endRow; i++) {
            headers[i][colIndex] = i === startRow ? column.title : '';
        }
        
        merges.push({ s: { r: startRow, c: colIndex }, e: { r: endRow, c: endCol } });
        colIndex = endCol + 1;
    }
     
    return { headers, merges };
}


function exportTableToMultiExcel(tableData: DataType[], columns: Column[], fileName = 'data', needSummary = false) {
    const allColumns = getAllColumns(columns);  // 获取所有列（包括子列）

    // 计算总结信息
    const summary: DataType = {};
    if (needSummary) {
        allColumns.forEach(column => {
            summary[column.dataIndex] = tableData.reduce((total, row) => {
                if (typeof row[column.dataIndex] === 'number') {
                    return total + (row[column.dataIndex] as number);
                }
                return total;
            }, 0);
        });
        summary[allColumns[0].dataIndex] = '总计';  // 设置第一列的值为'总计'
    }

    const dataValues = [...tableData.map(row => {
        const rowData = [];
        for (const column of allColumns) {
            rowData.push(row[column.dataIndex]);
        }
        return rowData;
    }), needSummary ? Object.values(summary) : []];  // 如果需要汇总，那么在数据数组的末尾添加总结信息

    const maxDepth = getMaxDepth(columns);
    const { headers, merges } = processColumns(columns, 0, 0, maxDepth,columns.length);

    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.aoa_to_sheet([...headers, ...dataValues]);

    // 计算每一列的最大字符长度
    const colWidths = allColumns.map((column, i) => {
        let maxLength = column.title.length * 2; // 初始值设为列标题的长度的两倍
        dataValues.forEach(row => {
            const cellValue = row[i];
            if (cellValue) {
                const cellLength = cellValue.toString().length;
                // 对于宽字符，使用较大的系数；对于窄字符，使用较小的系数
                const cellWidth = /[\u4e00-\u9fa5]/.test(cellValue.toString()) ? cellLength * 2 : cellLength;
                maxLength = Math.max(maxLength, cellWidth);
            }
        });
        return { wch: maxLength };
    });

    // 设置每一列的宽度
    ws['!cols'] = colWidths;

    // 设置单元格样式为水平和垂直居中
    const range = XLSX.utils.decode_range(ws['!ref'] as string);
    for (let R = range.s.r; R <= range.e.r; ++R) {
        for (let C = range.s.c; C <= range.e.c; ++C) {
            const cell_address = { c: C, r: R };
            const cell_ref = XLSX.utils.encode_cell(cell_address);
            if (!ws[cell_ref]) ws[cell_ref] = {};
            ws[cell_ref].s = {
                alignment: {
                    horizontal: 'center',
                    vertical: 'center'
                }
            };
        }
    }

    // 设置表头的背景色
    for (let r = 0; r < maxDepth; r++) {
        for (let c = 0; c < headers[r].length; c++) {
            if (!ws[XLSX.utils.encode_cell({ r, c })]) {
                ws[XLSX.utils.encode_cell({ r, c })] = {};
            }
            ws[XLSX.utils.encode_cell({ r, c })].s = {
                fill: {
                    fgColor: { rgb: "FFFF00" }  // 设置为黄色
                },
                alignment: {
                    horizontal: 'center',
                    vertical: 'center'
                }
            };
        }
    }

    ws['!merges'] = merges;
    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
    const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    const blob = new Blob([wbout], { type: 'application/octet-stream' });
    saveAs(blob, `${fileName}.xlsx`);
}




function getAllColumns(columns: Column[]): Column[] {
    let allColumns: Column[] = [];
    for (const column of columns) {
        if (column.children) {
            allColumns = [...allColumns, ...getAllColumns(column.children)];
        } else {
            allColumns.push(column);
        }
    }
    return allColumns;
}

export default exportTableToMultiExcel;



const columns = [
    {
        dataIndex: "unitName",
        ellipsis: true,
        fixed: "left",
        key: "unitName",
        title: "核算单元",
        width: 140
    },
    {
        dataIndex: "key-23",
        ellipsis: true,
        key: "key-23",
        title: "单元管理绩效",
        width: 200
    },
    {
        dataIndex: "key-1679019958216040448",
        ellipsis: true,
        key: "key-1679019958216040448",
        title: "临床诊察积分奖金",
        width: 200,
        children: [
            {
                dataIndex: "key-6",
                ellipsis: true,
                key: "key-6",
                title: "临床诊察积分-节假日奖金",
                width: 200,
                children: [
                    {
                        dataIndex: "key-4",
                        ellipsis: true,
                        key: "key-4",
                        title: "临床诊察积分-节假日",
                        width: 200
                    },
                    {
                        dataIndex: "key-5",
                        ellipsis: true,
                        key: "key-5",
                        title: "临床诊察积分-节假日点值",
                        width: 200
                    }
                ]
            },
            {
                dataIndex: "key-3",
                ellipsis: true,
                key: "key-3",
                title: "临床诊察积分-工作日奖金",
                width: 200,
                children: [
                    {
                        dataIndex: "key-1",
                        ellipsis: true,
                        key: "key-1",
                        title: "临床诊察积分-工作日",
                        width: 200
                    },
                    {
                        dataIndex: "key-2",
                        ellipsis: true,
                        key: "key-2",
                        title: "临床诊察积分-工作日点值",
                        width: 200
                    }
                ]
            }
        ]
    },
    {
        dataIndex: "totalScore",
        ellipsis: true,
        fixed: "right",
        key: "totalScore",
        title: "总奖金",
        width: 140
    }
]