.Net Core NPOI 导出多级表头
想要导出这样的表格
数据准备格式
附上源码
1 usingNPOI.HSSF.UserModel;2 usingNPOI.SS.UserModel;3 usingNPOI.SS.Util;4 usingSystem.Data;5 usingSystem.Text.RegularExpressions;6 7 namespaceTestConsoleApp8 {9 /// <summary> 10 ///导出Excel11 /// </summary> 12 public static classExportHelper13 {14 public static voidExport()15 {16 var dt =CreteTable();17 var titles = GetExcelTitles(dt.Columns, out intmaxTitleLevel);18 19 HSSFWorkbook workbook = newHSSFWorkbook();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 intitles)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:\Drivers\Merge.xls");54 workbook.Write(stm);55 }56 57 private staticDataTable CreteTable()58 {59 DataTable dt = newDataTable();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 returndt;103 }104 105 106 private static List<ExcelTitle> GetExcelTitles(DataColumnCollection columns, out intmaxTitleLevel)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(newLevelExcelTitle()126 {127 Title =arr[i],128 LevelCode = string.Join("-", arr[..(i + 1)]),129 RowIndex =i,130 ColumnIndex =index,131 TotalLevel =arr.Length132 });133 }134 }135 136 var titleLevel =maxTitleLevel;137 var excelTitles =levelExcelTitles138 .GroupBy(b => new 139 {140 b.LevelCode,141 b.Title142 })143 .Select(b => newExcelTitle()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 returnexcelTitles;154 }155 }156 157 public classExcelTitle158 {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 classLevelExcelTitle187 {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 }