c#树结构转npoi复杂表头

Vue 前端框架框架中采用树结构打印表头，为了前后端适配NPOI导出。

这里重点做树结构转换 NPOI 复杂表头的结构数据（ 跨行、跨列）,其它具体导出功能请参考  https://www.cnblogs.com/lwk9527/p/17374291.html

导出后实际效果

 源数据 json 版

[
    {
        "Title":"账号",
        "Childrens":null
    },
    {
        "Title":"姓名",
        "Childrens":null
    },
    {
        "Title":"语文",
        "Childrens":[
            {
                "Title":"成绩",
                "Childrens":null
            },
            {
                "Title":"用时",
                "Childrens":null
            },
            {
                "Title":"完成次数",
                "Childrens":null
            }
        ]
    },
    {
        "Title":"数学",
        "Childrens":[
            {
                "Title":"成绩",
                "Childrens":null
            },
            {
                "Title":"用时",
                "Childrens":null
            },
            {
                "Title":"完成次数",
                "Childrens":null
            }
        ]
    }
]

转换后的到的数据 json 版

[
    {
        "FirstRow":0,
        "LastRow":1,
        "FirstCol":0,
        "LastCol":0,
        "Value":"账号"
    },
    {
        "FirstRow":0,
        "LastRow":1,
        "FirstCol":1,
        "LastCol":1,
        "Value":"姓名"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":2,
        "LastCol":2,
        "Value":"成绩"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":3,
        "LastCol":3,
        "Value":"用时"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":4,
        "LastCol":4,
        "Value":"完成次数"
    },
    {
        "FirstRow":0,
        "LastRow":0,
        "FirstCol":2,
        "LastCol":4,
        "Value":"语文"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":5,
        "LastCol":5,
        "Value":"成绩"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":6,
        "LastCol":6,
        "Value":"用时"
    },
    {
        "FirstRow":1,
        "LastRow":1,
        "FirstCol":7,
        "LastCol":7,
        "Value":"完成次数"
    },
    {
        "FirstRow":0,
        "LastRow":0,
        "FirstCol":5,
        "LastCol":7,
        "Value":"数学"
    }
]

c# 案例

public void TestMethod0()
        {
            //源数据
            List<HeadData> headDatas = new List<HeadData>();
            headDatas.Add(new HeadData() { Title= "账号" });
            headDatas.Add(new HeadData() { Title = "姓名" });
            List<HeadData> subHeadDatas = new List<HeadData>();
            subHeadDatas.Add(new HeadData() { Title = "成绩" });
            subHeadDatas.Add(new HeadData() { Title = "用时" });
            subHeadDatas.Add(new HeadData() { Title = "完成次数"});
            headDatas.Add(new HeadData() { Title = "语文",Childrens= subHeadDatas });
            List<HeadData> subHeadDatas2 = new List<HeadData>();
            subHeadDatas2.Add(new HeadData() { Title = "成绩" });
            subHeadDatas2.Add(new HeadData() { Title = "用时" });
            subHeadDatas2.Add(new HeadData() { Title = "完成次数" });
            headDatas.Add(new HeadData() { Title = "数学", Childrens = subHeadDatas2});
            //转换后数据集
            List<ExcelHeader> headers = new List<ExcelHeader>();
            int firstRow = 0;
            int firstCol = 0;
            //获取树最大层次深度 （用于计算跨行）
            int maxLevel= CalculateMaxLevel(headDatas);
            DataConvert(headDatas, headers, firstRow, firstCol, maxLevel);//初始化
            IWorkbook workbook = new HSSFWorkbook();
            //工作簿
            ISheet sheetTable = workbook.CreateSheet();
            //生成表头
            foreach (var item in headers)
            {
                IRow headerRow = sheetTable.GetRow(item.FirstRow);
                if (headerRow == null)
                {
                    headerRow = sheetTable.CreateRow(item.FirstRow);
                    //行高，避免自动换行的内容将行高撑开
                    headerRow.HeightInPoints = 24;
                }
                ICell headerCell = headerRow.CreateCell(item.FirstCol);
                headerCell.SetCellValue(item.Value);
                //设置跨行
                if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol)
                {
                    //CellRangeAddress(开始行,结束行,开始列,结束列)
                    //行列索引由0开始
                    var region = new CellRangeAddress(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol);
                    sheetTable.AddMergedRegion(region);
                }
              // headerCell.CellStyle = HeaderStyle(workbook);
            }


            // 创建一个工作表
            ISheet sheet = workbook.CreateSheet("ComplexHeader");

            // 将工作簿写入文件
            using (var fileStream = new System.IO.FileStream("ComplexHeader.xlsx", System.IO.FileMode.Create, System.IO.FileAccess.Write))
            {
                workbook.Write(fileStream, true);
            }
        }
        /// <summary>
        /// 数据转换
        /// </summary>
        /// <param name="HeadDatas"></param>
        /// <param name="headers"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="maxLevel"></param>
        /// <param name="level"></param>
        public void DataConvert(List<HeadData> HeadDatas, List<ExcelHeader> headers,int startRow, int startCol,int maxLevel=2,int level=0)
        {
            foreach (var head in HeadDatas)
            {
                int LastCol = startCol;
                int LastRow = startRow;
               
                if (head.Childrens != null && head.Childrens.Count > 0)
                {
                    DataConvert(head.Childrens, headers, startRow + 1, startCol, maxLevel,level+1);
                }
                else
                {
                    LastRow =startRow+(maxLevel - level);
                }
                int lastCol = startCol;
                int colLength = GetTotalChildrenCount(head);
                if (colLength > 0)
                {
                    lastCol= startCol + colLength-1;
                }
                headers.Add(new ExcelHeader() { FirstRow = startRow, LastRow = LastRow, FirstCol = startCol, LastCol = lastCol, Value = head.Title });

                startCol= lastCol+1;
            }
        }
        /// <summary>
        /// 获取树结构最大深度
        /// </summary>
        /// <param name="nodes"></param>
        /// <param name="level"></param>
        /// <returns></returns>
        static int CalculateMaxLevel(List<HeadData> nodes, int level=0)
        {
            int maxLevel = level; // 初始级别为当前级别  
            foreach (var node in nodes)
            {
                if (node.Childrens != null) // 检查是否有子节点  
                {
                    int childLevel = CalculateMaxLevel(node.Childrens, level + 1); // 递归调用以获取子节点的最大级别  
                    maxLevel = Math.Max(maxLevel, childLevel); // 更新最大级别值  
                }
            }
            return maxLevel;
        }
        /// <summary>
        /// 获取所有子节点数量
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        public static int GetTotalChildrenCount(HeadData node)
        {
            if (node == null) return 0; // 基础情况：节点为空，返回0  
            if (node.Childrens == null) return 0; // 基础情况：子节点列表为空，返回0  
            return node.Childrens.Count + node.Childrens.Sum(child => GetTotalChildrenCount(child)); // 递归调用  
        }