解決中文文件名保存Excel亂碼問題，主要是判斷火狐或者IE瀏覽器，然后做對應的判斷處理，核心代碼如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //設置下載的Excel文件名\ if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1) { //火狐瀏覽器 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?=")); } else{ //IE等瀏覽器 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8))); } |
廢話不多說，直接上類庫代碼，ATNPOIHelper.cs：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
|
using System; using System.Linq; using System.Web; using System.IO; using NPOI; using NPOI.SS.Util; using NPOI.HSSF.Util; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using System.Data; using System.Collections.Generic; using System.Text; namespace AT.Utility.DotNetFile { /* 導出Excel包含的功能： 1.多表頭導出最多支持到三行，表頭格式說明 相鄰父列頭之間用'#'分隔,父列頭與子列頭用空格(' ‘)分隔,相鄰子列頭用逗號分隔(‘,') 兩行：序號#分公司#組別#本日成功簽約單數 預警,續約,流失,合計#累計成功簽約單數 預警,續約,流失,合計#任務數#完成比例#排名 三行：等級#級別#上期結存 件數,重量,比例#本期調入 收購調入 件數,重量,比例#本期發出 車間投料 件數,重量,比例#本期發出 產品外銷百分比 件數,重量,比例#平均值 三行時請注意：列頭要重復 2.添加表頭標題功能 3.添加序號功能 4.根據數據設置列寬 缺陷： 數據內容不能合并列合并行 改進思路： 添加一屬性：設置要合并的列，為了實現多列合并可以這樣設置{“列1,列2”,”列4”} */ /// <summary> /// 利用NPOI實現導出Excel /// </summary> public class ATNPOIHelper { #region 初始化 /// <summary> /// 聲明 HSSFWorkbook 對象 /// </summary> private static HSSFWorkbook _workbook; /// <summary> /// 聲明 HSSFSheet 對象 /// </summary> private static HSSFSheet _sheet; #endregion #region Excel導出 /// <summary> /// Excel導出 /// </summary> /// <param name="fileName">文件名稱 如果為空或NULL，則默認“新建Excel.xls”</param> /// <param name="list"></param> /// <param name="ColMergeNum">合計：末行合計時，合并的列數</param> /// <param name="method">導出方式 1：WEB導出（默認）2：按文件路徑導出</param> /// <param name="filePath">文件路徑 如果WEB導出，則可以為空；如果按文件路徑導出，則默認桌面路徑</param> public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method = 1, string filePath = null) { // 文件名稱 if (!string.IsNullOrEmpty(fileName)) { if (fileName.IndexOf('.') == -1) { fileName += ".xls"; } else { fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls"; } } else { fileName = "新建Excel.xls"; } // 文件路徑 if (2 == method && string.IsNullOrEmpty(filePath)) { filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); } // 調用導出處理程序 Export(list, ColMergeNum); // WEB導出 if (1 == method) { System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //設置下載的Excel文件名\ if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1) { //火狐瀏覽器 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?=")); } else { //IE等瀏覽器 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8))); } using (MemoryStream ms = new MemoryStream()) { //將工作簿的內容放到內存流中 _workbook.Write(ms); //將內存流轉換成字節數組發送到客戶端 System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer()); System.Web.HttpContext.Current.Response.End(); _sheet = null; _workbook = null; } } else if (2 == method) { using (FileStream fs = File.Open(filePath, FileMode.Append)) { _workbook.Write(fs); _sheet = null; _workbook = null; } } } /// <summary> /// 導出方法實現 /// </summary> /// <param name="list"></param> private static void Export(IList<NPOIModel> list, int ColMergeNum) { #region 變量聲明 // 初始化 _workbook = new HSSFWorkbook(); // 聲明 Row 對象 IRow _row; // 聲明 Cell 對象 ICell _cell; // 總列數 int cols = 0; // 總行數 int rows = 0; // 行數計數器 int rowIndex = 0; // 單元格值 string drValue = null; #endregion foreach (NPOIModel model in list) { // 工作薄命名 if (model.sheetName != null) _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName); else _sheet = (HSSFSheet)_workbook.CreateSheet(); // 獲取數據源 DataTable dt = model.dataSource; // 初始化 rowIndex = 0; // 獲取總行數 rows = GetRowCount(model.headerName); // 獲取總列數 cols = GetColCount(model.headerName); //合計：合并表格末行N列,rows為表頭行數，dt.Rows.Count為數據行數 if (ColMergeNum > 1) { CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1); _sheet.AddMergedRegion(region_Merge); } ICellStyle myBodyStyle = bodyStyle; ICellStyle myTitleStyle = titleStyle; ICellStyle myDateStyle = dateStyle; ICellStyle myBodyRightStyle = bodyRightStyle; // 循環行數 foreach (DataRow row in dt.Rows) { #region 新建表，填充表頭，填充列頭，樣式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) _sheet = (HSSFSheet)_workbook.CreateSheet(); // 構建行 for (int i = 0; i < rows + model.isTitle; i++) { _row = _sheet.GetRow(i); // 創建行 if (_row == null) _row = _sheet.CreateRow(i); for (int j = 0; j < cols; j++) _row.CreateCell(j).CellStyle = myBodyStyle; } // 如果存在表標題 if (model.isTitle > 0) { // 獲取行 _row = _sheet.GetRow(0); // 合并單元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1)); _sheet.AddMergedRegion(region); // 填充值 _row.CreateCell(0).SetCellValue(model.tableTitle); // 設置樣式 _row.GetCell(0).CellStyle = myTitleStyle; // 設置行高 _row.HeightInPoints = 20; } // 取得上一個實體 NPOIHeader lastRow = null; IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle); // 創建表頭 foreach (NPOIHeader m in hList) { var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1); if (data.Count() > 0) { lastRow = data.First(); if (m.headerName == lastRow.headerName) m.firstCol = lastRow.firstCol; } // 獲取行 _row = _sheet.GetRow(m.firstRow); // 合并單元格 CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol); _sheet.AddMergedRegion(region); // 填充值 _row.CreateCell(m.firstCol).SetCellValue(m.headerName); } // 填充表頭樣式 for (int i = 0; i < rows + model.isTitle; i++) { _row = _sheet.GetRow(i); for (int j = 0; j < cols; j++) { _row.GetCell(j).CellStyle = myBodyStyle; //設置列寬 _sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 450); } } rowIndex = (rows + model.isTitle); } #endregion #region 填充內容 // 構建列 _row = _sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { // 添加序號列 if (1 == model.isOrderby && column.Ordinal == 0) { _cell = _row.CreateCell(0); _cell.SetCellValue(rowIndex - rows); _cell.CellStyle = myBodyStyle; } // 創建列 _cell = _row.CreateCell(column.Ordinal + model.isOrderby); // 獲取值 drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串類型 _cell.SetCellValue(drValue); _cell.CellStyle = myBodyStyle; break; case "System.DateTime"://日期類型 DateTime dateV; DateTime.TryParse(drValue, out dateV); _cell.SetCellValue(dateV); _cell.CellStyle = myDateStyle;//格式化顯示 break; case "System.Boolean"://布爾型 bool boolV = false; bool.TryParse(drValue, out boolV); _cell.SetCellValue(boolV); _cell.CellStyle = myBodyStyle; break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); _cell.SetCellValue(intV); _cell.CellStyle = myBodyRightStyle; break; case "System.Decimal"://浮點型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); _cell.SetCellValue(doubV.ToString("f2")); _cell.CellStyle = myBodyRightStyle; break; case "System.DBNull"://空值處理 _cell.SetCellValue(""); break; default: _cell.SetCellValue(""); break; } } #endregion rowIndex++; } } } #region 輔助方法 /// <summary> /// 表頭解析 /// </summary> /// <remarks> /// </remarks> /// <param name="header">表頭</param> /// <param name="rows">總行數</param> /// <param name="addRows">外加行</param> /// <param name="addCols">外加列</param> /// <returns></returns> private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows) { // 臨時表頭數組 string[] tempHeader; string[] tempHeader2; // 所跨列數 int colSpan = 0; // 所跨行數 int rowSpan = 0; // 單元格對象 NPOIHeader model = null; // 行數計數器 int rowIndex = 0; // 列數計數器 int colIndex = 0; // IList<NPOIHeader> list = new List<NPOIHeader>(); // 初步解析 string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries); // 表頭遍歷 for (int i = 0; i < headers.Length; i++) { // 行數計數器清零 rowIndex = 0; // 列數計數器清零 colIndex = 0; // 獲取所跨行數 rowSpan = GetRowSpan(headers[i], rows); // 獲取所跨列數 colSpan = GetColSpan(headers[i]); // 如果所跨行數與總行數相等，則不考慮是否合并單元格問題 if (rows == rowSpan) { colIndex = GetMaxCol(list); model = new NPOIHeader(headers[i], addRows, (rowSpan - 1 + addRows), colIndex, (colSpan - 1 + colIndex), addRows); list.Add(model); rowIndex += (rowSpan - 1) + addRows; } else { // 列索引 colIndex = GetMaxCol(list); // 如果所跨行數不相等，則考慮是否包含多行 tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries); for (int j = 0; j < tempHeader.Length; j++) { // 如果總行數=數組長度 if (1 == GetColSpan(tempHeader[j])) { if (j == tempHeader.Length - 1 && tempHeader.Length < rows) { model = new NPOIHeader(tempHeader[j], (j + addRows), (j + addRows) + (rows - tempHeader.Length), colIndex, (colIndex + colSpan - 1), addRows); list.Add(model); } else { model = new NPOIHeader(tempHeader[j], (j + addRows), (j + addRows), colIndex, (colIndex + colSpan - 1), addRows); list.Add(model); } } else { // 如果所跨列數不相等，則考慮是否包含多列 tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); for (int m = 0; m < tempHeader2.Length; m++) { // 列索引 colIndex = GetMaxCol(list) - colSpan + m; if (j == tempHeader.Length - 1 && tempHeader.Length < rows) { model = new NPOIHeader(tempHeader2[m], (j + addRows), (j + addRows) + (rows - tempHeader.Length), colIndex, colIndex, addRows); list.Add(model); } else { model = new NPOIHeader(tempHeader2[m], (j + addRows), (j + addRows), colIndex, colIndex, addRows); list.Add(model); } } } rowIndex += j + addRows; } } } return list; } /// <summary> /// 獲取最大列 /// </summary> /// <param name="list"></param> /// <returns></returns> private static int GetMaxCol(IList<NPOIHeader> list) { int maxCol = 0; if (list.Count > 0) { foreach (NPOIHeader model in list) { if (maxCol < model.lastCol) maxCol = model.lastCol; } maxCol += 1; } return maxCol; } /// <summary> /// 獲取表頭行數 /// </summary> /// <param name="newHeaders">表頭文字</param> /// <returns></returns> private static int GetRowCount(string newHeaders) { string[] ColumnNames = newHeaders.Split(new char[] { '@' }); int Count = 0; if (ColumnNames.Length <= 1) ColumnNames = newHeaders.Split(new char[] { '#' }); foreach (string name in ColumnNames) { int TempCount = name.Split(new char[] { ' ' }).Length; if (TempCount > Count) Count = TempCount; } return Count; } /// <summary> /// 獲取表頭列數 /// </summary> /// <param name="newHeaders">表頭文字</param> /// <returns></returns> private static int GetColCount(string newHeaders) { string[] ColumnNames = newHeaders.Split(new char[] { '@' }); int Count = 0; if (ColumnNames.Length <= 1) ColumnNames = newHeaders.Split(new char[] { '#' }); Count = ColumnNames.Length; foreach (string name in ColumnNames) { int TempCount = name.Split(new char[] { ',' }).Length; if (TempCount > 1) Count += TempCount - 1; } return Count; } /// <summary> /// 列頭跨列數 /// </summary> /// <remarks> /// </remarks> /// <param name="newHeaders">表頭文字</param> /// <returns></returns> private static int GetColSpan(string newHeaders) { return newHeaders.Split(',').Count(); } /// <summary> /// 列頭跨行數 /// </summary> /// <remarks> /// </remarks> /// <param name="newHeaders">列頭文本</param> /// <param name="rows">表頭總行數</param> /// <returns></returns> private static int GetRowSpan(string newHeaders, int rows) { int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length; // 如果總行數與當前表頭所擁有行數相等 if (rows == Count) Count = 1; else if (Count < rows) Count = 1 + (rows - Count); else throw new Exception("表頭格式不正確！"); return Count; } #endregion #region 單元格樣式 /// <summary> /// 數據單元格樣式 /// </summary> private static ICellStyle bodyStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //居中 style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 style.WrapText = true;//自動換行 // 邊框 style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; // 字體 //IFont font = _workbook.CreateFont(); //font.FontHeightInPoints = 10; //font.FontName = "宋體"; //style.SetFont(font); return style; } } /// <summary> /// 數據單元格樣式 /// </summary> private static ICellStyle bodyRightStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.RIGHT; //居中 style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 style.WrapText = true;//自動換行 // 邊框 style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; // 字體 //IFont font = _workbook.CreateFont(); //font.FontHeightInPoints = 10; //font.FontName = "宋體"; //style.SetFont(font); return style; } } /// <summary> /// 標題單元格樣式 /// </summary> private static ICellStyle titleStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //居中 style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 style.WrapText = true;//自動換行 //IFont font = _workbook.CreateFont(); //font.FontHeightInPoints = 14; //font.FontName = "宋體"; //font.Boldweight = (short)FontBoldWeight.BOLD; //style.SetFont(font); return style; } } /// <summary> /// 日期單元格樣式 /// </summary> private static ICellStyle dateStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //居中 style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 style.WrapText = true;//自動換行 // 邊框 style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; // 字體 //IFont font = _workbook.CreateFont(); //font.FontHeightInPoints = 10; //font.FontName = "宋體"; //style.SetFont(font); IDataFormat format = _workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("yyyy-MM-dd"); return style; } } #endregion #endregion } /// <summary> /// 實體類 /// </summary> public class NPOIModel { /// <summary> /// 數據源 /// </summary> public DataTable dataSource { get; private set; } /// <summary> /// 要導出的數據列數組 /// </summary> public string[] fileds { get; private set; } /// <summary> /// 工作薄名稱數組 /// </summary> public string sheetName { get; private set; } /// <summary> /// 表標題 /// </summary> public string tableTitle { get; private set; } /// <summary> /// 表標題是否存在 1：存在 0：不存在 /// </summary> public int isTitle { get; private set; } /// <summary> /// 是否添加序號 /// </summary> public int isOrderby { get; private set; } /// <summary> /// 表頭 /// </summary> public string headerName { get; private set; } /// <summary> /// 取得列寬 /// </summary> public int[] colWidths { get; private set; } /// <summary> /// 構造函數 /// </summary> /// <remarks> /// </remarks> /// <param name="dataSource">數據來源 DataTable</param> /// <param name="filed">要導出的字段，如果為空或NULL，則默認全部</param> /// <param name="sheetName">工作薄名稱</param> /// <param name="headerName">表頭名稱 如果為空或NULL，則默認數據列字段 /// 相鄰父列頭之間用'#'分隔,父列頭與子列頭用空格(' ')分隔,相鄰子列頭用逗號分隔(',') /// 兩行：序號#分公司#組別#本日成功簽約單數 預警,續約,流失,合計#累計成功簽約單數 預警,續約,流失,合計#任務數#完成比例#排名 /// 三行：等級#級別#上期結存 件數,重量,比例#本期調入 收購調入 件數,重量,比例#本期發出 車間投料 件數,重量,比例#本期發出 產品外銷百分比 件數,重量,比例#平均值 /// 三行時請注意：列頭要重復 /// </param> /// <param name="tableTitle">表標題</param> /// <param name="isOrderby">是否添加序號 0：不添加 1：添加</param> public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0) { if (!string.IsNullOrEmpty(filed)) { this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); // 移除多余數據列 for (int i = dataSource.Columns.Count - 1; i >= 0; i--) { DataColumn dc = dataSource.Columns[i]; if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper())) { dataSource.Columns.Remove(dataSource.Columns[i]); } } // 列索引 int colIndex = 0; // 循環排序 for (int i = 0; i < dataSource.Columns.Count; i++) { // 獲取索引 colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper()); // 設置下標 dataSource.Columns[i].SetOrdinal(colIndex); } } else { this.fileds = new string[dataSource.Columns.Count]; for (int i = 0; i < dataSource.Columns.Count; i++) { this.fileds[i] = dataSource.Columns[i].ColumnName; } } this.dataSource = dataSource; if (!string.IsNullOrEmpty(sheetName)) { this.sheetName = sheetName; } if (!string.IsNullOrEmpty(headerName)) { this.headerName = headerName; } else { this.headerName = string.Join("#", this.fileds); } if (!string.IsNullOrEmpty(tableTitle)) { this.tableTitle = tableTitle; this.isTitle = 1; } // 取得數據列寬 數據列寬可以和表頭列寬比較，采取最長寬度 colWidths = new int[this.dataSource.Columns.Count]; foreach (DataColumn item in this.dataSource.Columns) { colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } // 循環比較最大寬度 for (int i = 0; i < this.dataSource.Rows.Count; i++) { for (int j = 0; j < this.dataSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length; if (intTemp > colWidths[j]) { colWidths[j] = intTemp; } } } if (isOrderby > 0) { this.isOrderby = isOrderby; this.headerName = "序號#" + this.headerName; } } /// <summary> /// 獲取列名下標 /// </summary> /// <param name="colName">列名稱</param> /// <returns></returns> private int GetColIndex(string colName) { for (int i = 0; i < this.fileds.Length; i++) { if (colName == this.fileds[i]) return i; } return 0; } } /// <summary> /// 表頭構建類 /// </summary> public class NPOIHeader { /// <summary> /// 表頭 /// </summary> public string headerName { get; set; } /// <summary> /// 起始行 /// </summary> public int firstRow { get; set; } /// <summary> /// 結束行 /// </summary> public int lastRow { get; set; } /// <summary> /// 起始列 /// </summary> public int firstCol { get; set; } /// <summary> /// 結束列 /// </summary> public int lastCol { get; set; } /// <summary> /// 是否跨行 /// </summary> public int isRowSpan { get; private set; } /// <summary> /// 是否跨列 /// </summary> public int isColSpan { get; private set; } /// <summary> /// 外加行 /// </summary> public int rows { get; set; } public NPOIHeader() { } /// <summary> /// 構造函數 /// </summary> /// <param name="headerName">表頭</param> /// <param name="firstRow">起始行</param> /// <param name="lastRow">結束行</param> /// <param name="firstCol">起始列</param> /// <param name="lastCol">結束列</param> /// <param name="rows">外加行</param> /// <param name="cols">外加列</param> public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0) { this.headerName = headerName; this.firstRow = firstRow; this.lastRow = lastRow; this.firstCol = firstCol; this.lastCol = lastCol; // 是否跨行判斷 if (firstRow != lastRow) isRowSpan = 1; if (firstCol != lastCol) isColSpan = 1; this.rows = rows; } } } |
3、導出代碼示例如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
/// <summary> /// 導出測點列表表格 /// </summary> [HttpGet] [AllowAnonymous] public void ExportMeasurePointData(string TreeID, string TreeType) { DataTable dtResult = new DataTable(); DataTable dtExcel = new DataTable(); try { string sql = string.Format("EXEC P_GET_ZXJG_TagList '{0}','{1}'", TreeID, TreeType); dtResult = QuerySQL.GetDataTable(sql); dtExcel = dtResult.Copy(); dtExcel.Columns.Add("xuhao", typeof(string)); dtExcel.Columns.Add("StrValueTime", typeof(string)); dtExcel.Columns["xuhao"].SetOrdinal(0); dtExcel.Columns["StrValueTime"].SetOrdinal(2); for (int i = 0; i < dtResult.Rows.Count; i++) { dtExcel.Rows[i]["xuhao"] = (i + 1).ToString(); dtExcel.Rows[i]["StrValueTime"] = Convert.ToDateTime(dtResult.Rows[i]["F_ValueTime"]).ToString("yyyy-MM-dd HH:mm:ss"); } List<NPOIModel> list = new List<NPOIModel>(); list.Add(new NPOIModel(dtExcel, "xuhao;F_Description;StrValueTime;F_Value;F_Unit;F_AlmLow;F_AlmUp", "sheet", "序號#監測點#采集時間#當前數值#工程單位#報警下限#報警上限")); ATNPOIHelper.Export("測點列表", list, 0); } catch (Exception ex) { } } |
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://blog.csdn.net/zdw_wym/article/details/73608820?utm_source=tuicool&utm_medium=referral
