想要导出这样的表格

数据准备格式

附上源码

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 }

标签: none

添加新评论