.Net Core NPOI 导出多级表头

  1 using NPOI.HSSF.UserModel;   2 using NPOI.SS.UserModel;   3 using NPOI.SS.Util;   4 using System.Data;   5 using System.Text.RegularExpressions;   6    7 namespace TestConsoleApp   8 {   9     /// <summary>  10     /// 导出Excel  11     /// </summary>  12     public static class ExportHelper  13     {  14         public static void Export()  15         {  16             var dt = CreteTable();  17             var titles = GetExcelTitles(dt.Columns, out int maxTitleLevel);  18   19             HSSFWorkbook workbook = new HSSFWorkbook();  20             ISheet sheet = workbook.CreateSheet("Sheet1");  21   22             var allRowCount = dt.Rows.Count + maxTitleLevel;  23             //创建所有单元格  24             for (int i = 0; i < allRowCount; i++)  25             {  26                 var row = sheet.CreateRow(i);  27                 for (int j = 0; j < dt.Columns.Count; j++)  28                 {  29                     row.CreateCell(j);  30                 }  31             }  32   33             //合并创建表头  34             foreach (var tit in titles)  35             {  36                 sheet.GetRow(tit.StartRow).GetCell(tit.StartColumn).SetCellValue(tit.Title);  37                 if (tit.MergeColumnCount + tit.MergeRowCount > 0)  38                 {  39                     sheet.AddMergedRegion(new CellRangeAddress(tit.StartRow, tit.StartRow + tit.MergeRowCount, tit.StartColumn, tit.StartColumn + tit.MergeColumnCount));  40                 }  41             }  42   43             //生成数据行   44             for (int i = 0; i < dt.Rows.Count; i++)  45             {  46                 for (int j = 0; j < dt.Columns.Count; j++)  47                 {  48                     string cellValue = dt.Rows[i][j].ToString();  49                    sheet.GetRow(maxTitleLevel + i).Cells[j].SetCellValue(cellValue);  50                 }  51             }  52   53             using FileStream stm = File.OpenWrite(@"D:DriversMerge.xls");  54             workbook.Write(stm);  55         }  56   57         private static DataTable CreteTable()  58         {  59             DataTable dt = new DataTable();  60             dt.Columns.Add("编号");  61             dt.Columns.Add("收入-线上采购-数量");  62             dt.Columns.Add("收入-线上采购-金额");  63   64             dt.Columns.Add("收入-线下采购-数量");  65             dt.Columns.Add("收入-线下采购-金额");  66   67             dt.Columns.Add("回收-数量");  68             dt.Columns.Add("回收-金额");  69   70             dt.Columns.Add("支出-测试01-数量");  71             dt.Columns.Add("支出-测试01-金额");  72   73             dt.Columns.Add("支出-测试02-数量");  74             dt.Columns.Add("支出-测试02-金额");  75   76             dt.Columns.Add("其它-数量");  77             dt.Columns.Add("其它-金额");  78   79             dt.Columns.Add("备注");  80   81             for (int i = 1; i <= 100; i++)  82             {  83                 var row = dt.NewRow();  84   85                 row["编号"] = "编号" + i;  86                 row["收入-线上采购-数量"] = i;  87                 row["收入-线上采购-金额"] = i;  88                 row["收入-线下采购-数量"] = i;  89                 row["收入-线下采购-金额"] = i;  90                 row["回收-数量"] = i;  91                 row["回收-金额"] = i;  92                 row["支出-测试01-数量"] = i;  93                 row["支出-测试01-金额"] = i;  94                 row["支出-测试02-数量"] = i;  95                 row["支出-测试02-金额"] = i;  96                 row["其它-数量"] = i;  97                 row["其它-金额"] = i;  98                 row["备注"] = i;  99                 dt.Rows.Add(row); 100             } 101  102             return dt; 103         } 104  105  106         private static List<ExcelTitle> GetExcelTitles(DataColumnCollection columns, out int maxTitleLevel) 107         { 108             maxTitleLevel = 0; 109             List<LevelExcelTitle> levelExcelTitles = new List<LevelExcelTitle>(); 110  111             for (var index = 0; index < columns.Count; index++) 112             { 113                 var column = columns[index].ToString(); 114  115                 var arr = column.Split("-"); 116  117  118                 if (maxTitleLevel < arr.Length) 119                 { 120                     maxTitleLevel = arr.Length; 121                 } 122  123                 for (int i = 0; i < arr.Length; i++) 124                 { 125                     levelExcelTitles.Add(new LevelExcelTitle() 126                     { 127                         Title = arr[i], 128                         LevelCode = string.Join("-", arr[..(i + 1)]), 129                         RowIndex = i, 130                         ColumnIndex = index, 131                         TotalLevel = arr.Length 132                     }); 133                 } 134             } 135  136             var titleLevel = maxTitleLevel; 137             var excelTitles = levelExcelTitles 138                 .GroupBy(b => new 139                 { 140                     b.LevelCode, 141                     b.Title 142                 }) 143                .Select(b => new ExcelTitle() 144                { 145                    Title = b.Key.Title, 146                    StartRow = b.Min(c => c.RowIndex), 147                    MergeRowCount = b.Min(c => c.RowIndex) + 1 == b.Max(c => c.TotalLevel) ? titleLevel - b.Max(c => c.TotalLevel) : 0, 148  149                    StartColumn = b.Min(c => c.ColumnIndex), 150                    MergeColumnCount = b.Count() - 1,//排除自身 151                }).ToList(); 152  153             return excelTitles; 154         } 155     } 156  157     public class ExcelTitle 158     { 159         /// <summary> 160         /// 标题 161         /// </summary> 162         public string Title { get; set; } 163  164         /// <summary> 165         /// 开始行 166         /// </summary> 167         public int StartRow { get; set; } 168  169         /// <summary> 170         /// 合并行 171         /// </summary> 172         public int MergeRowCount { get; set; } 173  174  175         /// <summary> 176         /// 开始列 177         /// </summary> 178         public int StartColumn { get; set; } 179  180         /// <summary> 181         /// 合并列 182         /// </summary> 183         public int MergeColumnCount { get; set; } 184     } 185  186     public class LevelExcelTitle 187     { 188         /// <summary> 189         /// 标题 190         /// </summary> 191         public string Title { get; set; } 192  193         public string LevelCode { get; set; } 194  195         /// <summary> 196         /// 第几行 197         /// </summary> 198         public int RowIndex { get; set; } 199  200         /// <summary> 201         /// 第几列 202         /// </summary> 203         public int ColumnIndex { get; set; } 204  205         /// <summary> 206         /// 总层 207         /// </summary> 208         public int TotalLevel { get; set; } 209     } 210 }