C# .netcore NPOI库 实现报表的列自适应删减
实际需求：业务上的一个需求，数据库表A中的B字段存放的是该条数据的一些标签，标签存在两级【即一级标签和二级标签】， 现在要是实现将这些标签统计到报表中，一级标签作为表头，二级标签作为填充值。
由于之前的报表每增加一个列都需要去数据库表中增加这个字段名称，然后代码中写统计逻辑，这样才能实现， 这样做的好处是只需要后端就可以完成报表的一个字段的增加，不需要等待前端去增加这了列名了， 后来的低代码就是这样的逻辑，可以简单的编写sql，去实现统计逻辑。实现快速开发，节省开发时间。
回归正题，由于每个数据的标签是不确定的，那就导致了每个数据要统计的列是不一样的，这里的做法是将全部的标签都进行增加统计【用户不需要的话还有个功能取消掉某列的统计就可以了】，当用户新增一个标签的时候，只要去查看报表，检测到这个标签的名称不在列统计当中，就会自动的去新增一条列统计字段，每条数据循环的时候要将给其赋值，有对应的二级标签那就展示二级标签名称，没有的话那也要新增一个列，但是数据填写空字符串。【这一步主要是防止在下载报表的时候报错，以防列数对应不上而报错】。
有两个核心的点，第一个：统计的列名为什么还要在数据库表中再存一份？ 其实也可以将要统计的列名放入代码中写死，同样可以实现报表数据的展示，但是所有的客户都会展示这么多的列统计，没有办法支持各个客户对统计列的筛选【这个就是原因】。
第二个：代码中的列数据如何和数据库表中的列名对应上的？ 数据库表中存入统计列的列中文名和列英文名， 在代码中一个字段的定义肯定是英文的，这样就可以知道如何赋值了，【列中文名是给客户展示的】。
这里是说了一下实现报表的列自适应原理， 至于报表的操作直接去nuget管理中下载NPOI库，对于报表的一些操作什么的可以直接网查，封装起来使用就好了。
下边附上实现的代码，供大家参考：
自己封装的NPOI帮助类， 大家可以根据自己的实际需要去封装：
点击查看代码
public class NPOIHelper
{
/// <summary>
/// 1. 创建workbook
/// 1.1创建详细信息DocumentSummaryInformation和SummaryInformation
/// 2. 创建工作簿CreateSheet
/// 3. 表头设置
/// 4. 生成字节数组
/// </summary>
/// <param name="excelSheets"></param>
public static byte[] CreateExcel<T>(Dictionary<string, List<T>> excelSheets)
{
HSSFWorkbook workbook = new HSSFWorkbook(); //创建workbook
SetSummaryInformation(workbook);
foreach (var origin in excelSheets)
{
CreateSheet(workbook, origin.Value, origin.Key); //创建工作簿
}
byte[] data = null; //生成字节数组
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.ToArray();
}
return data;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K">扩展列的值，定保跟进历史</typeparam>
/// <param name="excelSheets"></param>
/// <param name="extendKV">key是扩展列的列名</param>
/// <returns></returns>
public static byte[] CreateExcelExtend<T, K>(Dictionary<string, List<T>> excelSheets, Dictionary<string, int> extendKV)
{
HSSFWorkbook workbook = new HSSFWorkbook(); //创建workbook
SetSummaryInformation(workbook);
foreach (var origin in excelSheets)
{
CreateSheetExtend<T, K>(workbook, origin.Value, extendKV, origin.Key); //创建工作簿
}
byte[] data = null; //生成字节数组
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.ToArray();
}
return data;
}
/// <summary>
/// 创建excel
/// </summary>
/// <returns></returns>
public static IWorkbook CreateExcelOnly()
{
HSSFWorkbook workbook = new HSSFWorkbook(); //创建workbook
SetSummaryInformation(workbook);
return workbook;
}
/// <summary>
/// 追加页签
/// </summary>
/// <typeparam name="T">数据实体类型</typeparam>
/// <param name="workbook">excel</param>
/// <param name="sheetVlaue"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static IWorkbook AppendSheet<T>(IWorkbook workbook, IEnumerable<T> sheetVlaue, string sheetName = null)
{
CreateSheet(workbook, sheetVlaue, sheetName);
return workbook;
}
/// <summary>
/// 获取excel字节
/// </summary>
/// <param name="workbook">excel</param>
/// <returns></returns>
public static byte[] GetExcelData(IWorkbook workbook)
{
byte[] data = null; //生成字节数组
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.ToArray();
}
return data;
}
/// <summary>
/// 1. 创建workbook
/// 1.1创建详细信息DocumentSummaryInformation和SummaryInformation
/// 2. 创建工作簿CreateSheet
/// 3. 表头设置
/// 4. 生成字节数组
/// </summary>
/// <param name="excelSheets"></param>
public static byte[] AppendExcel<T>(IWorkbook workbook, Dictionary<string, List<T>> excelSheets)
{
foreach (var origin in excelSheets)
{
CreateSheet(workbook, origin.Value, origin.Key); //创建工作簿
}
byte[] data = null; //生成字节数组
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.ToArray();
}
return data;
}
/// <summary>
/// 详细信息
/// </summary>
/// <param name="workbook"></param>
public static void SetSummaryInformation(HSSFWorkbook workbook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //创建详细信息：属性、来源等
dsi.Company = "xxxxxx有限公司";
dsi.Category = "xx科技";
dsi.Manager = "xx科技";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "xx科技";
si.Title = "xx科技";
si.ApplicationName = "xx科技";
si.Author = "xx科技";
si.LastAuthor = "xx科技";
si.Comments = "xx科技";
si.CreateDateTime = DateTime.Now.AddMonths(-2);
//创建好的对象赋给hssfWorkbook,这样才能保证这些信息被写入文件
workbook.SummaryInformation = si;
}
/// <summary>
/// 创建工作簿
/// </summary>
/// <param name="workbook"></param>
/// <param name="rowList"></param>
/// <param name="sheetName"></param>
public static void CreateSheet<T>(IWorkbook workbook, IEnumerable<T> rowList, string sheetName = null)
{
var newname = NewSheetName(workbook, sheetName);
ISheet sheet = workbook.CreateSheet(newname);//创建工作簿
Type tp = typeof(T);
var proInfos = tp.GetProperties();//获得实体对象的属性集合
SetRowHead(sheet, proInfos);
if (rowList == null || rowList.Count() == 0)
{
return;
}
int i = 1;
foreach (var row in rowList)
{
CreateRow(row, proInfos, sheet, i++);
}
}
/// <summary>
/// 创建工作簿
/// </summary>
/// <param name="workbook"></param>
/// <param name="rowList"></param>
/// <param name="sheetName"></param>
/// <param name="dics"></param>
public static void CreateSheetClue<T>(IWorkbook workbook, IEnumerable<T> rowList, Dictionary<string, string> dics, string sheetName = null)
{
var newname = NewSheetName(workbook, sheetName);
ISheet sheet = workbook.CreateSheet(newname);//创建工作簿
Type tp = typeof(T);
var proInfos = tp.GetProperties();//获得实体对象的属性集合
SetRowHeadClue(sheet, dics, proInfos);
if (rowList == null || rowList.Count() == 0)
{
return;
}
int i = 1;
foreach (var row in rowList)
{
CreateRowClue(row, sheet, i++, dics);
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K">扩展列的值，跟进历史</typeparam>
/// <param name="workbook"></param>
/// <param name="rowList"></param>
/// <param name="extendKV">key为扩展列的列名</param>
/// <param name="sheetName">工作簿的名称</param>
private static void CreateSheetExtend<T, K>(IWorkbook workbook, IEnumerable<T> rowList, Dictionary<string, int> extendKV, string sheetName = null)
{
var newname = NewSheetName(workbook, sheetName);
ISheet sheet = workbook.CreateSheet(newname);//创建工作簿
Type tp = typeof(T);
var proInfos = tp.GetProperties();//获得实体对象的属性集合
SetRowHeadExtend(sheet, proInfos, extendKV);
if (rowList == null || rowList.Count() == 0)
{
return;
}
int i = 1;
Type ktp = typeof(K);//扩展列的属性
var kProInfos = ktp.GetProperties();
var extendCount = extendKV == null ? 0 : extendKV.Count();//扩展列的总数量
foreach (var row in rowList)
{
CreateRowExtend<T, K>(row, proInfos, sheet, i++, kProInfos, extendCount);
}
}
/// <summary>
/// 获取新页签名防止重复
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
private static string NewSheetName(IWorkbook workbook, string sheetName = null)
{
if (string.IsNullOrWhiteSpace(sheetName))
{
sheetName = "Sheet";
}
if (workbook.NumberOfSheets == 0)
{
return sheetName;
}
List<string> nameList = new List<string>();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
nameList.Add(workbook.GetSheetName(i));
}
var newname = sheetName;
int endidx = 0;
while (nameList.Contains(newname, StringComparer.OrdinalIgnoreCase))
{
newname = newname + endidx;
}
return newname;
}
/// <summary>
/// 创建单元格
/// </summary>
/// <param name="origin"></param>
/// <param name="proInfos"></param>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
private static void CreateRow<T>(T origin, PropertyInfo[] proInfos, ISheet sheet, int rowIndex)
{
IRow row = sheet.CreateRow(rowIndex);//创建行
for (int j = 0; j < proInfos.Length; j++)
{
var cell = row.CreateCell(j);
var proInfo = proInfos[j];
//CellStyleAlignment(cell, proInfo);//设置水平对齐方式
object val = proInfo.GetValue(origin);//获取该属性的Value值
if (val == null)
{
continue;
}
var cellValue = val == null ? "" : val.ToString();
cell.SetCellValue(cellValue);//创建单元格、并且赋值
}
}
/// <summary>
/// 创建单元格
/// </summary>
/// <param name="origin"></param>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
private static void CreateRowClue<T>(T origin, ISheet sheet, int rowIndex, Dictionary<string, string> dics)
{
IRow row = sheet.CreateRow(rowIndex);//创建行
int i = 0;
Dictionary<string, string> pairs = JsonConvert.DeserializeObject<Dictionary<string, string>>(origin.ToJson());
foreach (var item in dics)
{
var cell = row.CreateCell(i);
string result = item.Key.Substring(0, 1).ToLower() + item.Key.Substring(1, item.Key.Length - 1);
object val = pairs[$"{result}"];
var cellValue = val == null ? "" : val.ToString();
cell.SetCellValue(cellValue);//创建单元格、并且赋值
i++;
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K"></typeparam>
/// <param name="origin"></param>
/// <param name="proInfos"></param>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="kProInfos">扩展列的属性</param>
/// <param name="extendCount">扩展列的总数量</param>
private static void CreateRowExtend<T, K>(T origin, PropertyInfo[] proInfos, ISheet sheet, int rowIndex, PropertyInfo[] kProInfos, int extendCount)
{
IRow row = sheet.CreateRow(rowIndex);//创建行
for (int j = 0; j < proInfos.Length; j++)
{
var proInfo = proInfos[j];
object val = proInfo.GetValue(origin);//获取该属性的Value值
if (val != null && typeof(List<K>) == val.GetType())
{
SetExtendCellValue<K>(row, val, kProInfos, j, extendCount); //扩展列的类型
continue;
}
var cellValue = val == null ? "" : val.ToString();
var cell = row.CreateCell(j);
cell.SetCellValue(cellValue);//创建单元格、并且赋值
}
}
private static void SetExtendCellValue<K>(IRow row, object val, PropertyInfo[] kProInfos, int j, int extendCount)
{
if (val == null || extendCount == 0)
{
return;//没有值，则不创建单元格
}
var kk = (List<K>)val;
foreach (var kkk in kk)
{
for (int i = 0; i < kProInfos.Length; i++, j++)
{
var cellK = row.CreateCell(j);
var proInfoK = kProInfos[i];
var valK = proInfoK.GetValue(kkk);
var cellValueK = valK == null ? "" : valK.ToString();
cellK.SetCellValue(cellValueK);//创建单元格、并且赋值
}
}
//不满足数量的也不创建单元格
}
/// <summary>
/// 设置单元格的对齐方式
/// </summary>
private static void CellStyleAlignment(ICell cell, PropertyInfo proInfo)
{
if (proInfo.Name == "FollowUpHistory" || proInfo.Name == "MaintainFollowUpHistory")
{
cell.CellStyle.Alignment = HorizontalAlignment.Fill;//水平对齐方式：填充
}
//该方法设置填充，耗时较长
//Type attType = typeof(HorizontalAlignmentAttribute);
//var attr = Attribute.GetCustomAttribute(proInfo, attType);
//if (attr == null)
//{
// return;
//}
//var haAttr = (HorizontalAlignmentAttribute)attr;
//if (haAttr==null)
//{
// return;
//}
//if (haAttr.Fill)
//{
// cell.CellStyle.Alignment = HorizontalAlignment.Fill;//水平对齐方式：填充
//}
}
/// <summary>
/// 设置表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="proInfos"></param>
private static void SetRowHead(ISheet sheet, PropertyInfo[] proInfos)
{
IRow rowHead = sheet.CreateRow(0);
for (int k = 0; k < proInfos.Length; k++)
{
var proInfo = proInfos[k];
string name = proInfo.Name; //获取T的字段名称
//string d=proInfo.Attributes.Description
string des = ((DescriptionAttribute)Attribute.GetCustomAttribute(proInfo, typeof(DescriptionAttribute))).Description; //获取T的字段名称的描述
rowHead.CreateCell(k).SetCellValue(des);//创建单元格、并且赋值
}
}
/// <summary>
/// 设置表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="proInfos"></param>
private static void SetRowHeadClue(ISheet sheet, Dictionary<string, string> dics, PropertyInfo[] proInfos)
{
IRow rowHead = sheet.CreateRow(0);
int i = 0;
foreach (var item in dics)
{
string name = item.Key; //获取T的字段名称
string des = item.Value; //获取T的字段名称的描述
rowHead.CreateCell(i).SetCellValue(des);//创建单元格、并且赋值
i++;
}
//for (int k = 0; k < dics.Count; k++)
//{
// var proInfo = proInfos[k];
// string name = dics[k].Key; //获取T的字段名称
// //string d=proInfo.Attributes.Description
// string des = ((DescriptionAttribute)Attribute.GetCustomAttribute(proInfo, typeof(DescriptionAttribute))).Description; //获取T的字段名称的描述
// rowHead.CreateCell(k).SetCellValue(des);//创建单元格、并且赋值
//}
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="proInfos"></param>
/// <param name="extendProInfos">扩展列</param>
private static void SetRowHeadExtend(ISheet sheet, PropertyInfo[] proInfos, Dictionary<string, int> extendProInfos)
{
IRow rowHead = sheet.CreateRow(0);
for (int k = 0; k < proInfos.Length; k++)
{
var proInfo = proInfos[k];
string name = proInfo.Name; //获取T的字段名称
var attr = Attribute.GetCustomAttribute(proInfo, typeof(DescriptionAttribute));
if (attr == null)
{
continue;//没有DescriptionAttribute标签，则不生成列
}
var desAttr = (DescriptionAttribute)attr;
if (desAttr == null)
{
continue;//没有DescriptionAttribute标签，则不生成列
}
string des = desAttr.Description; //获取T的字段名称的描述
rowHead.CreateCell(k).SetCellValue(des);//创建单元格、并且赋值
}
//增加扩展列（随着extendProInfos的大小动态生成，列名为extendProInfos的key）
if (extendProInfos == null || !extendProInfos.Any())
{
return;//不包含扩展列
}
var index = rowHead.Count();
foreach (var kv in extendProInfos)
{
if (string.IsNullOrWhiteSpace(kv.Key))
{
continue;
}
rowHead.CreateCell(index).SetCellValue(kv.Key);
index += 1;
}
}
}
下边是下载报表字段对应的代码：
点击查看代码
List<Dictionary<string, string>> CompanyAccidentDatas = new List<Dictionary<string, string>>();
CompanyAccidentDatas = companyAccident.Data.CompanyAccidentDatas;//自己要填充的数据源
List<ExpandoObject> dynamiccompanyAccidents = new List<ExpandoObject>();
foreach (var x in CompanyAccidentDatas)
{
dynamic dynamiccompanyAccident = new ExpandoObject();
foreach (var item in x)
{
((IDictionary<string, object>)dynamiccompanyAccident)[item.Key] = item.Value;
}
dynamiccompanyAccidents.Add(dynamiccompanyAccident);
}
if (dynamiccompanyAccidents != null && dynamiccompanyAccidents.Count > 0)
{
string token = _httpContext.HttpContext.Request.Headers["Authorization"].ToString().Replace("Bearer ", "");
var jwtToken = new JwtSecurityTokenHandler().ReadJwtToken(token);
string Employeed = await Task.Run(() =>
{
return jwtToken.Claims.FirstOrDefault(x => x.Type == "employeeId")?.Value;
});
long employeeId = Convert.ToInt64(Employeed);
List<LxbTableHeader> tableHeaders = new List<LxbTableHeader>();
using (var dbContext = new ClueContext(CompId))
{
tableHeaders = dbContext.LxbTableHeader.Where(x => x.IsDel == 0 && x.CompId == CompId && x.EmployeeId == employeeId && x.TableName == "xx报表" && x.IsShow == true).OrderBy(x => x.Sort).ToList();
}
Dictionary<string, string> dics = new Dictionary<string, string>();
if (tableHeaders != null && tableHeaders.Count > 0)
{
tableHeaders.ForEach(item =>
{
dics.Add(item.ColumnEName, item.ColumnCName);
});
}
NPOIHelper.CreateSheetClue(workbook, dynamiccompanyAccidents, dics, "报表xx");
}
}
下边是获取一条数据标记的标签
点击查看代码
if (tempClue != null && !string.IsNullOrEmpty(tempClue.Label))
{
List<string> tempLabels = tempClue.Label.Split(',').ToList();
List<string> secondLabelIdsStr = secondLabelIds.Select(g => g.ToString()).ToList();
List<string> interLabelIdsStr = secondLabelIdsStr.Intersect(tempLabels).ToList();
if (interLabelIdsStr != null && interLabelIdsStr.Count > 0)
{
List<int> interLabelIds = interLabelIdsStr.Select(g => Convert.ToInt32(g)).ToList();
interLabelIds.ForEach(async g =>
{
LxbLabelConfig tempsecond = labels.Where(x => x.Id == g).FirstOrDefault();
LxbLabelConfig tempfirst = labels.Where(x => x.Id == tempsecond.GroupId).FirstOrDefault();
LxbTableHeader tempTable = new LxbTableHeader();
using (var dbContext = new ClueContext(CompId))
{
tempTable = dbContext.LxbTableHeader.Where(x => x.ColumnCName == tempfirst.LabelName && x.CompId == CompId && x.EmployeeId == employeeId && x.IsDel == 0).FirstOrDefault();
}
if (tempTable != null)
{
if (dic.Keys.Contains(tempTable.ColumnEName))
{
dic[tempTable.ColumnEName] = $"{dic[tempTable.ColumnEName]},{tempsecond.LabelName}";
}
else
dic.Add(tempTable.ColumnEName, tempsecond.LabelName);
}
});
}
}
if (customLabels != null && customLabels.Count > 0)
{
customLabels.ForEach(async g =>
{
LxbTableHeader tempTable = new LxbTableHeader();
using (var dbContext = new ClueContext(CompId))
{
tempTable = dbContext.LxbTableHeader.Where(x => x.ColumnCName == g.LabelName && x.CompId == CompId && x.EmployeeId == employeeId && x.IsDel == 0).FirstOrDefault();
}
if (tempTable != null)
{
if (!dic.Keys.Contains(tempTable.ColumnEName))
dic.Add(tempTable.ColumnEName, "");
}
});
}
几个主要的逻辑代码，这里已经附上，有疑问的可以私信探讨，共同进步ヽ(ｰ)人(ｰ)ノ
