.Net Core NPOI 导出多级表头

老合 · 发表于 2024-11-6 15:49:53

想要导出这样的表格

数据准备格式

附上源码

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:\Drivers\Merge.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 }

复制代码

来源:https://www.cnblogs.com/Mrlvs5767/p/18529291/Mrlvs
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！

.Net Core NPOI 导出多级表头

本帖子中包含更多资源