|
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
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000 |
using
System;using
System.Collections.Generic;using
System.Linq;using
System.Text;using
System.Runtime.InteropServices;using
System.IO; //Fileusing
System.Diagnostics;//Processusing
System.Reflection;//引用这个才能使用Missing字段using
Excel = Microsoft.Office.Interop.Excel; //操作Excel using
System.Data;//DataTableusing
System.Data.SqlClient; //sqlserver数据库连接using
System.Data.OleDb;using
System.Text.RegularExpressions; //Regexusing
System.Data.OleDb; //OleDbDataAdapternamespace
MyExcel{ public
class ExcelHelper { #region 变量 private
Excel.Application ExApp = null; //引擎 private
Excel.Workbook wb = null; //工作薄 private
Excel.Workbooks wbs = null; //工作薄(集合) private
Excel.Worksheet ws = null; //工作表 private
Excel.Worksheets wss = null; //工作表(集合) private
int workSheetCount = 0; //WorkSheet数量 private
object missing = Missing.Value; //使用 Missing 类的此实例来表示缺少的值 private
Excel.Range getRange; //代表一个Excel单元格 private
Excel.Range changeRange; /// <summary> /// 代表一个Excel单元格 /// </summary> public
Excel.Range ChangeRange { get
{ return
changeRange; } set
{ changeRange = value; } } /// <summary> /// 代表一个Excel单元格 /// </summary> public
Excel.Range GetRange { get
{ return
getRange; } set
{ getRange = value; } } private
string inputPath; //输入文件路径 public
string InputPath { get
{ return
inputPath; } set
{ inputPath = value; } } string
outputPath; //文件输出路径 public
string OutputPath { get
{ return
outputPath; } set
{ outputPath = value; } } DateTime beforeTime;//Excel启动之前时间 DateTime afterTime; //Excel启动之后时间 private
string[] arry = new
string[] { }; //数组 /// <summary> /// 数组 /// </summary> public
string[] Arry { get
{ return
arry; } set
{ arry = value; } } #endregion /// <summary> /// 构造函数 /// </summary> public
ExcelHelper() {} /// <summary> /// 初始化 /// </summary> /// <param name="inputFilePath">Excel模板路径</param> public
ExcelHelper(string
inputFilePath) { if
(string.IsNullOrEmpty(inputFilePath)) throw
new Exception("Excel模板文件路径不能为空！"); if
(!File.Exists(inputFilePath)) throw
new Exception("指定路径的Excel模板文件不存在！"); beforeTime = DateTime.Now; //启动时间 try { ExApp = new
Excel.Application(); } catch { throw
new Exception("先要安装office,才能把数据保存到Excel"); } afterTime = DateTime.Now; //启动结束 try { wb = (Excel.Workbook)ExApp.Workbooks.Open(inputFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch { throw
new Exception("请先关闭Excel模板"); } try { ws = (Excel.Worksheet)wb.Worksheets.get_Item(1); } catch { throw
new Exception("该Excel模板已被损坏"); } workSheetCount = wb.Worksheets.Count; this.inputPath = inputFilePath; } /// <summary> /// 初始化 /// </summary> /// <param name="inputFilePath">Excel模板路径</param> /// <param name="outPutFilePath">保存路径</param> public
ExcelHelper(string
inputFilePath, string
outPutFilePath) { if
(string.IsNullOrEmpty(inputFilePath)) throw
new Exception("Excel模板文件路径不能为空！"); if
(string.IsNullOrEmpty(outPutFilePath)) throw
new Exception("输出Excel文件路径不能为空！"); if
(!File.Exists(inputFilePath)) throw
new Exception("指定路径的Excel模板文件不存在！"); beforeTime = DateTime.Now; //启动时间 try { ExApp = new
Excel.Application(); } catch { throw
new Exception("先要安装office,才能把数据保存到Excel"); } afterTime = DateTime.Now; //启动结束 try { wb = (Excel.Workbook)ExApp.Workbooks.Open(inputFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch { throw
new Exception("请先关闭Excel模板"); } try { ws = (Excel.Worksheet)wb.Worksheets.get_Item(1); } catch { throw
new Exception("该Excel模板已被损坏"); } workSheetCount = wb.Worksheets.Count; this.inputPath = inputFilePath; this.outputPath = outPutFilePath; } #region 公用方法 /// <summary> /// 创建工作薄 /// </summary> public
void CreateWorkbooks() { ExApp = new
Excel.Application(); ExApp.Workbooks.Add(true); //ExApp.Worksheets.Add(missing, missing, 2, true); ExApp.Visible = true; } /// <summary> /// 添加工作薄 /// </summary> /// <param name="count">个数</param> public
void AddWorkbooks(int
count) { wb.Worksheets.Add(missing, missing, count, true); } /// <summary> /// 设置值 /// </summary> /// <param name="area">区域: A2</param> /// <param name="value">值</param> public
void SetRandValue(string
area, string
value) { Excel.Range cells = ws.get_Range(area); cells.Value = value; } /// <summary> /// 设置值 /// </summary> /// <param name="row">行</param> /// <param name="col">列</param> /// <param name="value">值</param> public
void SetRandValue(int
row, int
col, string
value) { ws = (Excel.Worksheet)wb.Sheets.get_Item(1); ws.Cells[row, col] = value; } /// <summary> /// 设置值 /// </summary> /// <param name="wsName">工作薄名称</param> /// <param name="row">行</param> /// <param name="col">列</param> /// <param name="value">值</param> public
void SetRandValue(string
wsName, int
row, int
col, string
value) { ws = (Excel.Worksheet)wb.Sheets[wsName]; ws.Cells[row, col] = value; } /// <summary> /// 获取某个工作薄中的某个单元格的值 /// </summary> /// <param name="wsName">工作薄的名称(比如:"Sheet1")</param> /// <param name="range">单元格(比如:D4)</param> /// <returns></returns> public
string GetRangValue(string
wsName, string
range) { ws = (Excel.Worksheet)wb.Sheets[wsName]; getRange = ws.get_Range(range, Type.Missing); return
getRange.Text.ToString(); } /// <summary> /// 获取某个工作薄中的某个单元格的值 /// </summary> /// <param name="wsName">工作薄的名称(比如:"Sheet1")</param> /// <param name="row">第几行</param> /// <param name="col">第几列</param> /// <returns></returns> public
string GetRangValue(string
wsName,int
row,int
col) { ws = (Excel.Worksheet)wb.Sheets[wsName]; getRange = ws.Cells[row, col]; return
getRange.Text.ToString(); } /// <summary> /// 将DataTable填充到Excel中 /// </summary> /// <param name="ShetName">工作薄的名称</param> /// <param name="row">开始行</param> /// <param name="col">开始列</param> /// <param name="dt">DataTable</param> public
void PaddingRange(string
ShetName, int
row, int
col,DataTable dt) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; string
rowCode = IndexToColumn(col); string
colCode = IndexToColumn(dt.Columns.Count+row); string
area = rowCode + row + ":"
+ colCode + (dt.Rows.Count + row - 1); object[,] arr = DataTable_To_Array(dt); Excel.Range range = ws.get_Range(area); range.Value2 = arr; } /// <summary> /// 将DataTable填充到Excel中 /// </summary> /// <param name="dt"></param> public
void PaddingRange(DataTable dt) { ws = (Excel.Worksheet)wb.Sheets.get_Item(1); string
colCode = IndexToColumn(dt.Columns.Count); string
area = "A1:"
+ colCode + dt.Rows.Count; object[,] arr = DataTable_To_Array(dt); Excel.Range range = ws.get_Range(area); range.Value2 = arr; } /// <summary> /// 清空 sheet /// </summary> /// <param name="ShetName"></param> public
void ClearSheet(string
ShetName) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; ws.ClearArrows(); } /// <summary> /// 删除 sheet /// </summary> /// <param name="ShetName"></param> public
void DeleteSheet(string
ShetName) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; ws.Delete(); } /// <summary> /// 将 DataTable 转化为二维数组 /// </summary> /// <param name="table">DataTable</param> /// <returns></returns> public
object[,] DataTable_To_Array(DataTable table) { int
row = table.Rows.Count; int
col = table.Columns.Count; object[,] arr = new
object[row, col]; for
(int
i = 0; i < col; i++) { for
(int
j = 0; j < row; j++) { arr[j, i] = table.Rows[j][i]; } } return
arr; } /// <summary> /// 将 DataRow 转化为二维数组 /// </summary> /// <param name="rows">DataRow</param> /// <returns></returns> public
object[,] DataTable_To_Array(DataRow[] rows) { int
row = rows.Length; int
col = 3; object[,] arr = new
object[row, col]; for
(int
i = 0; i < col; i++) { for
(int
j = 0; j < row; j++) { arr[j, i] = rows[j][i + 1].ToString(); } } return
arr; } /// <summary> /// 将二维数组 转化为 Range /// </summary> /// <param name="arr"></param> /// <param name="area">区域 如 "B5:E40" 区域必须和数组对应</param> /// <returns></returns> public
Excel.Range Array_To_Range(object[,] arr, string
area) { Excel.Range cells = ws.get_Range(area); cells.Value2 = arr; return
cells; } /// <summary> /// 删除列 /// </summary> /// <param name="column">列数</param> /// <returns></returns> public
bool DelectCol(int
column) { bool
flg = false; if
(!flg) { ws = (Excel.Worksheet)wb.Sheets[1]; ws.Columns.Delete(column); flg = true; } return
flg; } /// <summary> /// 删除列 /// </summary> /// <param name="ShetName">工作薄名称</param> /// <param name="column">列数</param> /// <returns></returns> public
bool DelectCol(string
ShetName, int
column) { bool
flg = false; if
(!flg) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; ws.Columns.Delete(column); flg = true; } return
flg; } /// <summary> /// 删除列 /// </summary> /// <param name="ColNum">第几列</param> /// <returns></returns> public
bool DeleteCol(int
ColNum) { ((Excel.Range)ws.Cells[1, ColNum]).Select(); ((Excel.Range)ws.Cells[1, ColNum]).EntireColumn.Delete(0); return
true; } /// <summary> /// 用于excel表格中列号字母转成列索引，从1对应A开始 /// </summary> /// <param name="column">列号</param> /// <returns>列索引</returns> public
int ColumnToIndex(string
column) { if
(!Regex.IsMatch(column.ToUpper(), @"[A-Z]+")) { throw
new Exception("Invalid parameter"); } int
index = 0; char[] chars = column.ToUpper().ToCharArray(); for
(int
i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)‘A‘
+ 1) * (int)Math.Pow(26, chars.Length - i - 1); } return
index; } /// <summary> /// 用于将excel表格中列索引转成列号字母，从A对应1开始 /// </summary> /// <param name="index">列索引</param> /// <returns>列号</returns> public
string IndexToColumn(int
index) { if
(index <= 0) { throw
new Exception("Invalid parameter"); } index--; string
column = string.Empty; do { if
(column.Length > 0) { index--; } column = ((char)(index % 26 + (int)‘A‘)).ToString() + column; index = (int)((index - index % 26) / 26); } while
(index > 0); return
column; } /// <summary> /// 水平自动递增 例如：从第7行第2列开始增加到100 ("sheet1",7,2,100) /// </summary> /// <param name="ShetName">sheet名称</param> /// <param name="bRow">开始行</param> /// <param name="bColumn">开始列</param> /// <param name="index">增加长度</param> public
void AutoHorizontalIndex(string
ShetName, int
row, int
col, int
num) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; string
colCode = IndexToColumn(col+num-1); string
rowCode = IndexToColumn(col); string
area = rowCode + row + ":"
+ colCode + row; object[,] arr = new
object[1, num]; for
(int
i = 0; i < num; i++) { arr[0,i] = i + 1; } Excel.Range range = ws.get_Range(area); range.Value2 = arr; } /// <summary> /// 竖直自动递增 例如：从第7行第2列开始 自动增加到100 (“Sheet1”,7,2,100) /// </summary> /// <param name="ShetName">sheet名称</param> /// <param name="bRow">开始行</param> /// <param name="bColumn">开始列</param> /// <param name="num">增加长度</param> public
void AutoVerticalIndex(string
ShetName, int
row, int
col, int
num) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; string
colCode = IndexToColumn(col); string
area = colCode + row + ":"
+ colCode + (row + num -1); object[,] arr = new
object[num,1]; for
(int
i = 0; i < num; i++) { arr[i, 0] = i + 1; } Excel.Range range = ws.get_Range(area); range.Value2 = arr; } /// <summary> /// 黏贴模板 /// </summary> /// <param name="ShetName">sheet名称</param> /// <param name="area">开始区域 如"A2"</param> /// <param name="area2">结束区域 如 "G42"</param> /// <param name="range">模板</param> public
void Paste_Range_Model(string
ShetName, string
area, string
area2, Excel.Range rangeModel) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; Excel.Range range = rangeModel; //黏贴区域 Excel.Range newRange = ws.get_Range(area, area2); range.Copy(); ws.Paste(newRange, missing); } /// <summary> /// 拷贝模板 /// </summary> /// <param name="ShetName">sheet名称</param> /// <param name="area">开始区域 如"A2"</param> /// <param name="area2">结束区域 如 "G42"</param> public
Excel.Range Copy_Range_Model(string
ShetName, string
area, string
area2) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; Excel.Range range = ws.get_Range(area, area2); return
range; } /// <summary> /// 插入行 /// </summary> /// <param name="ShetName">工作薄的名称</param> /// <param name="rowIndex">从第几行开始索引（第一行是1）</param> /// <param name="count">插入几行</param> public
void InsertExcelHeader(string
ShetName, int
rowIndex, int
count) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; Excel.Range range = (Excel.Range)ws.Rows[rowIndex, missing]; for
(int
i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown); } } /// <summary> /// 合并单元格 /// </summary> /// <param name="ShetName">sheet名称</param> /// <param name="area">区域 例如（A2:G2）</param> public
void MergeExcelRange(string
ShetName, string
area) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; ws.get_Range(area, missing).Merge(0); } /// <summary> /// 显示sheet /// </summary> /// <param name="ShetName">sheet名称</param> public
void ShowWorkSheet(string
ShetName) { try { ws = (Excel.Worksheet)wb.Sheets[ShetName]; } catch { throw
new Exception("不存在该Sheet"); } ws.Visible = Excel.XlSheetVisibility.xlSheetHidden; } /// <summary> /// 隐藏sheet /// </summary> /// <param name="ShetName">sheet名称</param> public
void HideWorkSheet(string
ShetName) { try { ws = (Excel.Worksheet)wb.Sheets[ShetName]; } catch { throw
new Exception("不存在该Sheet"); } ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden; } /// <summary> /// 检索值个数 (查询某个工作薄) /// </summary> /// <param name="strValue">查询值</param> /// <param name="sheetIndex">工作薄名称</param> public
int SearchValueCount(string
strValue, string
sheetIndex) { //使用行 int
indxRow = ws.UsedRange.Rows.Count; //使用列 int
indxCol = ws.UsedRange.Columns.Count; int
count = 0; string
str = null; for
(int
i = 1; i < indxRow; i++) { for
(int
j = 1; j < indxCol; j++) { str = GetRangValue(sheetIndex, i, j); str = str.Trim().ToLower(); strValue = strValue.Trim().ToLower(); if
(strValue == str) { count++; } } } return
count; } /// <summary> /// 检索值(精确) /// </summary> /// <param name="strValue">查询值</param> /// <param name="sheetIndex">工作薄名称</param> /// <returns></returns> public
string SearchValue(string
strValue, string
sheetIndex) { //使用行 int
indxRow = ws.UsedRange.Rows.Count; //使用列 int
indxCol = ws.UsedRange.Columns.Count; //存储行和列 Dictionary<int, int> dic = new
Dictionary<int, int>(); int
count = 0; string
str = null; for
(int
i = 1; i < indxRow; i++) { for
(int
j = 1; j < indxCol; j++) { str = GetRangValue(sheetIndex, i, j); str = str.Trim().ToLower(); strValue = strValue.Trim().ToLower(); if
(strValue == str) { dic.Add(i, j); count++; } } } return
""; } /// <summary> /// 将图片插入到指定的单元格位置。 /// 注意：图片必须是绝对物理路径 /// </summary> /// <param name="RangeName">单元格名称，例如：B4</param> /// <param name="PicturePath">要插入图片的绝对路径。</param> public
void InsertPicture(string
ShetName, string
area, string
PicturePath) { ws = (Excel.Worksheet)wb.Sheets[ShetName]; getRange = ws.get_Range(area,missing); getRange.Select(); Excel.Pictures pics = (Excel.Pictures)ws.Pictures(missing); pics.Insert(PicturePath, missing); } /// <summary> /// 将Excel导入到 DataTable /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="sheetName">工作薄名称</param> /// <returns></returns> public
DataTable ExcelToDataTable(string
filePath, string
sheetName) { OleDbDataAdapter oda = new
OleDbDataAdapter(); OleDbConnection oleCon = new
OleDbConnection(); string
strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@filePath;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1‘"; strCon = strCon.Replace("@filePath", filePath); DataTable dt = new
DataTable(); oleCon.ConnectionString = strCon; oleCon.Open(); DataTable table = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //string sheetName = table.Rows[0][2].ToString(); string
sql = "select * from [@sheetName$]"; sql = sql.Replace("@sheetName", sheetName); oda = new
System.Data.OleDb.OleDbDataAdapter(sql, oleCon); oda.Fill(dt); oleCon.Close(); return
dt; } #endregion #region Excel样式 /// <summary> /// 常用颜色定义,对就Excel中颜色名 /// </summary> public
enum ColorIndex { 无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49, 深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10, 青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43, 海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7, 金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15, 玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39, 白色 = 2 } /// <summary> /// 下划线方式 /// </summary> public
enum UnderlineStyle { 无下划线 = -4142, 双线 = -4119, 双线充满全格 = 5, 单线 = 2, 单线充满全格 = 4 } /// <summary> /// 单元格填充方式 /// </summary> public
enum Pattern { Automatic = -4105, Checker = 9, CrissCross = 16, Down = -4121, Gray16 = 17, Gray25 = -4124, Gray50 = -4125, Gray75 = -4126, Gray8 = 18, Grid = 15, Horizontal = -4128, LightDown = 13, LightHorizontal = 11, LightUp = 14, LightVertical = 12, None = -4142, SemiGray75 = 10, Solid = 1, Up = -4162, Vertical = -4166 } /// <summary> /// 垂直对齐方式 /// </summary> public
enum ExcelVAlign { 靠上 = 1, 居中, 靠下, 两端对齐, 分散对齐 } /// <summary> /// 线粗 /// </summary> public
enum BorderWeight { 极细 = 1, 细 = 2, 粗 = -4138, 极粗 = 4 } /// <summary> /// 线样式 /// </summary> public
enum LineStyle { 连续直线 = 1, 短线 = -4115, 线点相间 = 4, 短线间两点 = 5, 点 = -4118, 双线 = -4119, 无 = -4142, 少量倾斜点 = 13 } /// <summary> /// 水平对齐方式 /// </summary> public
enum ExcelHAlign { 常规 = 1, 靠左, 居中, 靠右, 填充, 两端对齐, 跨列居中, 分散对齐 } /// <summary> /// 自动调整行高 /// </summary> /// <param name="columnNum">行号</param> public
void RowAutoFit(int
rowNum) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":"
+ rowNum.ToString(), System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 设置列宽 /// </summary> /// <param name="startColumn">起始列(列对应的字母)</param> /// <param name="endColumn">结束列(列对应的字母)</param> /// <param name="width"></param> public
void SetColumnWidth(string
startColumn, string
endColumn, int
width) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":"
+ endColumn, System.Type.Missing]; range.ColumnWidth = width; } /// <summary> /// 设置列宽 /// </summary> /// <param name="startColumn">起始列</param> /// <param name="endColumn">结束列</param> /// <param name="width"></param> public
void SetColumnWidth(int
startColumn, int
endColumn, int
width) { string
strStartColumn = IndexToColumn(startColumn); string
strEndColumn = IndexToColumn(endColumn); //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":"
+ strEndColumn, System.Type.Missing]; range.ColumnWidth = width; } /// <summary> /// 自动调整列宽 /// </summary> /// <param name="columnNum">列号</param> public
void ColumnAutoFit(string
column) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[column + ":"
+ column, System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 自动调整列宽 /// </summary> /// <param name="columnNum">列号</param> public
void ColumnAutoFit(int
columnNum) { string
strcolumnNum = IndexToColumn(columnNum); //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":"
+ strcolumnNum, System.Type.Missing]; range.EntireColumn.AutoFit(); } /// <summary> /// 字体颜色 /// </summary> /// <param name="indexRow">开始单元格 例如 A1</param> /// <param name="indexCol">结束单元格 例如 C5</param> /// <param name="color">颜色索引</param> public
void FontColor(string
indexRow, string
indexCol, ColorIndex color) { Excel.Range range = ExApp.get_Range(indexRow, indexCol); range.Font.ColorIndex = color; } /// <summary> /// 字体样式(加粗,斜体,下划线) /// </summary> /// <param name="indexRow">开始单元格 例如 A1</param> /// <param name="indexCol">结束单元格 例如 C5</param> /// <param name="isBold">是否加粗</param> /// <param name="isItalic">是否斜体</param> /// <param name="underline">下划线类型</param> public
void FontStyle(string
indexRow, string
indexCol, bool
isBold, bool
isItalic, UnderlineStyle underline) { Excel.Range range = ExApp.get_Range(indexRow, indexCol); range.Font.Bold = isBold; range.Font.Underline = underline; range.Font.Italic = isItalic; } /// <summary> /// 单元格字体及大小 /// </summary> /// <param name="indexRow">开始单元格 例如 A1</param> /// <param name="indexCol">结束单元格 例如 C5</param> /// <param name="fontName">字体名称</param> /// <param name="fontSize">字体大小</param> public
void FontNameSize(string
indexRow, string
indexCol, string
fontName, int
fontSize) { Excel.Range range = ExApp.get_Range(indexRow, indexCol); range.Font.Name = fontName; range.Font.Size = fontSize; } /// <summary> /// 单元格背景色及填充方式 /// </summary> /// <param name="indexRow">开始单元格 例如 A1</param> /// <param name="indexCol">结束单元格 例如 C5</param> /// <param name="color">颜色</param> public
void CellsBackColor(string
indexRow, string
indexCol, ColorIndex color) { Excel.Range range = ExApp.get_Range(indexRow, indexCol); range.Interior.ColorIndex = color; range.Interior.Pattern = Pattern.Solid; } /// <summary> /// 单元格背景色及填充方式 /// </summary> /// <param name="indexRow">开始单元格 例如 A1</param> /// <param name="indexCol">结束单元格 例如 C5</param> /// <param name="color">颜色</param> /// <param name="pattern">图案样品</param> public
void CellsBackColor(string
indexRow, string
indexCol, ColorIndex color, Pattern pattern) { Excel.Range range = ExApp.get_Range(indexRow, indexCol); range.Interior.ColorIndex = color; range.Interior.Pattern = pattern; } /// <summary> /// 设置行高 /// </summary> /// <param name="startRow">起始行</param> /// <param name="endRow">结束行</param> /// <param name="height">行高</param> public
void SetRowHeight(int
startRow, int
endRow, int
height) { //获取当前正在使用的工作表 Excel.Worksheet worksheet = (Excel.Worksheet)ExApp.ActiveSheet; Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":"
+ endRow.ToString(), System.Type.Missing]; range.RowHeight = height; } #endregion /// <summary> /// 另存文件 /// </summary> public
void SaveAs() { wb.SaveAs(this.outputPath, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); } /// <summary> /// 保存 /// </summary> public
void Save() { wb.Save(); } #region 注销 /// <summary> /// 结束Excel进程 /// </summary> public
void KillExcelProcess() { Process[] arrProcess = Process.GetProcessesByName("Excel"); DateTime startTime; foreach
(Process p in
arrProcess) { startTime = p.StartTime; if
(startTime > beforeTime && startTime < afterTime) p.Kill(); } } /// <summary> /// 注销 /// </summary> public
void Dispose() { if
(ExApp != null) { ExApp.Workbooks.Close(); ExApp.Quit(); KillExcelProcess(); GC.Collect(); } } //[DllImport("User32.dll", CharSet = CharSet.Auto)] //public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); //public void Dispose(bool bl) //{ // ExApp.Workbooks.Close(); // ExApp.Quit(); // if (ExApp != null) // { // IntPtr t = new IntPtr(ExApp.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 // int k = 0; // GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k // System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 // p.Kill(); //关闭进程k // } // GC.Collect(); //} #endregion }} |
原文：http://www.cnblogs.com/2zhyi/p/3640846.html