c#树结构转npoi复杂表头
c#树结构转npoi复杂表头, Vue 前端框架框架中采用树结构打印表头为了前后端适配NPOI导出。
这里重点做树结构转换 NPOI 复杂表头的结构数据（ 跨行、跨列）
其它具体导出功能请参考 https://www.cnblogs.com/lwk9527/p/17374291.html
导出后实际效果
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)); // 递归调用 }
源数据 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":"数学"
}
]
