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)); // 递归调用
}<br><br>
来源:https://www.cnblogs.com/xiaobao6652/p/17997229
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]