c#树结构转npoi复杂表头

  • c#树结构转npoi复杂表头已关闭评论
  • 71 次浏览
  • A+
所属分类:.NET技术
摘要

Vue 前端框架框架中采用树结构打印表头,为了前后端适配NPOI导出。这里重点做树结构转换 NPOI 复杂表头的结构数据( 跨行、跨列),其它具体导出功能请参考  https://www.cnblogs.com/lwk9527/p/17374291.html

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

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

 

导出后实际效果

c#树结构转npoi复杂表头

 源数据 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)); // 递归调用           }