C#+VBA 操作EXCEL（转）
Excel在日常办公中应用非常广泛，这次我接到一个案子，需要往Excel中写入数据并能够打印出固定格式，前前后后大概花了将近2星期才搞定，现在做一些分享。
一、关于操作Excel的几种方式
我们导出Excel，大抵是有两种方法，一种是在服务器端用一些类库或者COM组件直接生成Excel成品，其二是在后台只写入数据，而不写入具体格式，等用户下载完Excel之后再在客户端上利用vba生成Excel成品。
1.1使用“自动化”功能-后台生成成品
微软把后台使用COM组件称之为“自动化”，其实它本身是不建议这种用法，在257757 号文章（服务器端 Office 自动化注意事项）也明文标出“Microsoft 目前建议不要从任何无人参与的、非交互式客户端应用程序或组件（包括 ASP、DCOM 和 NT Service）中进行 Microsoft Office 应用程序的“自动化”，也不为此提供支持，因为 Office 在这种环境中运行时可能会出现不稳定的现象并且/或者会死锁。”
这种方法的优点是给用户更简洁的展现，毕竟原生的Excel成品比半成品来更容易接受，而且可以避免宏安全问题，万一客户端禁止了宏，就只能看到丑陋的模板界面+一坨数据。
缺点是需要服务端支持，编写成本也比较高（VBA有时候可以直接录制）。服务端支持体现在需要安装一些微软或者第三方的类库。我当时采用的是Office类库，也就是在安装有Office的机器上面使用Interop.Excel.dll这个中间动态链接库进行操作，需要麻烦的安全设置，效率低不说还会扯出Excel无法关闭的Bug。
关于效率低我深有体会，我使用这种方法生成5页数据大约2m，客户等的急死，其根本原因是由于COM组件在调用时候，每一个Range这种对象都会产生一个借口请求。
1.2、使用ADO.net传输数据+VBA控制模板和数据
这方法的优缺点正好与上文相反，由于只是写入数据，即通过ADO.net的连接方式INSERT一堆数据到Excel文件的隐藏Sheet里面去（别跟我说你不知道Excel可以隐藏某个Sheet），所以速度后台速度极快。前台Excel文件虽然需要VBA编程支持，但是在理解Excel模型之后也不是很难的事情。
1.3
选择哪种方式，取决于你的需求，如果你在Java平台下面并且输出文件页面格式不复杂，我推荐第一种，如果是.net平台又或者要处理复杂的页面样式，就选用第二种吧（我前期使用第一种，后来因为效率问题和无法关闭Excel的问题，重写逻辑，选用第二种）。
关于Excel导出方案的选择，微软官方也是不建议使用第一种方案，甚至不提供技术支持。它推荐了一些方案，包括使用报表导出Excel或者ADO.net方式导出（即第二种），具体文章见如何使用 Visual C# 2005 或 Visual C# .NET 向 Excel 工作簿传输数据。
二、Office Excel文档模型
在写操作Excel代码之前，需要了解一下Excel的文档模型，才能想当然的把代码写出来。
简单说来，我们只要了解Application 、Workbook 、Worksheet 、Range这四种类型，如果需要操作图像的话，还需要多了解一种Chart。
Application就是Excel实例，不仅仅是一个Excel文件，而是整体的Excel程序（Office都是MDI文档体系）。
WorkBook就是实质意义上的某个Excel文件，你可以进行保存操作等等。
Worksheet是某个工作簿类型。
Range是我们打交道最多的，可以理解成一个区域快，也就是常见的"A2:B5"这种表示方式。
了解这几种之后，我们就可以下手操作了。更详细的微软官方文档，可以在Excel 对象模型概述找到。
三、使用C#操作Excel
我虽然不推荐第一种，但是毕竟是一种解决方案。
需要使用的命名空间为using Excel = Microsoft.Office.Interop.Excel;（使用别名简化一下）
另外需要项目引用Office的类库，如果不是项目形式而是网站形式，则需要手动编译对应Interop.Excel.dll到网站bin目录下面，我使用Excel2007编译出这个链接库，版本为1.6.0.0，需要的可以点击Interop.Excel.dll下载。
3.1 编译Interop.Excel.dll
编译的方法出自于“使用Office组件读取Excel，引用Microsoft.Office.Interop.Excel出现的问题”
进入你的visual studio的sdk下的bin目录，找到TlbImp.exe文件，如果没有，请用光盘安装此文件，详细说明请参照MSDN。 命令行(cmd)进入bin目录，运行TlbImp /out:Interop.Excel.dll Office安装目录+Excel.exe 此时很可能会报错：TlbImp error: Unable to locate input type library: 'c:\program files\mcrosoft office\office\EXCEL.EXE'
此问题很有可能是TlbImp的bug，不支持空格式的路径；（具体原因不明）不要紧，将Excel.exe拷贝入bin目录，直接运行TlbImp /out:Interop.Excel.dll Excel.exe,提示“Type library imported to Interop.Excel.dll路径”
在bin目录下找到Interop.Excel.dll文件。在你的visual studio里将其引用即可。
3.2 封装的一个C#操作Excel类库
我把我之前封装的C#操作Excel类库分享一下
注：这个类的Dispose仍然没有解决Excel文件生成之后Excel进程无法正常关闭的问题，目前看来只有杀死进程方法才能起实质性作用。
其实我觉得这个类库除了让我熟悉一下Excel模型之外，并没派上实质的用场~
|
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
|
/// <summary>/// Excel帮助类/// Add by alswl 20101130/// </summary>public class ExcelHelper : IDisposable{ private Excel._Application excelApplication = null; public Excel._Workbook workbook = null; public Excel._Worksheet worksheet = null; private object missing = System.Reflection.Missing.Value; public ExcelHelper() { if (excelApplication == null) { excelApplication = new Excel.ApplicationClass(); } } /// <summary> /// ~ /// </summary> ~ExcelHelper() { if (excelApplication != null) excelApplication.Quit(); } /// <summary> /// 释放非托管资源 /// </summary> public void Dispose() { try { if (excelApplication != null) { if (!workbook.Saved) this.Close(false); excelApplication.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication); GC.Collect(System.GC.GetGeneration(worksheet)); GC.Collect(System.GC.GetGeneration(workbook)); GC.Collect(System.GC.GetGeneration(excelApplication)); if (excelApplication != null) { excelApplication = null; } GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } } catch { } finally { GC.Collect(); } } /// <summary> /// 设置当前工作表 /// </summary> public int CurrentWorksheetIndex { set { if (value <= 0 || value > workbook.Worksheets.Count) throw new ArgumentException("索引超出范围"); else { object index = value; worksheet = workbook.Worksheets[index] as Excel._Worksheet; } } } /// <summary> /// 打开一个Excel工作薄 /// </summary> /// <param name="fileName"></param> public void OpenWorkbook(string fileName) { workbook = excelApplication.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); if (workbook.Worksheets.Count > 0) { int index = 1; worksheet = workbook.Worksheets[index] as Excel._Worksheet; } } /// <summary> /// 添加一个工作表 /// </summary> /// <param name="SheetName"></param> /// <returns></returns> public Excel.Worksheet AddSheet(string sheetName) { Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; return worksheet; } /// <summary> /// 删除一个Sheet /// </summary> /// <param name="sheetName"></param> public void DeleteSheet(string sheetName) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; excelApplication.DisplayAlerts = false; sheet.Delete(); excelApplication.DisplayAlerts = true; } /// <summary> /// 保存数据 /// </summary> public void Save() { if (workbook != null) { workbook.Save(); } } /// <summary> /// 关闭文档 /// </summary> /// <param name="isSave"></param> public void Close(bool isSave) { object obj_Save = isSave; workbook.Close(obj_Save, missing, missing); } /// <summary> /// 设置当前工作表中某单元格的值 /// </summary> /// <param name="range"></param> /// <param name="value"></param> public void SetRangeValue(string range, object value) { SetRangeValue(worksheet.Name, range, value); } /// <summary> /// 设置工作表中某单元格的值 /// </summary> /// <param name="sheetName"></param> /// <param name="range"></param> /// <param name="value"></param> public void SetRangeValue(string sheetName, string range, object value) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(range, missing).Value2 = value; } /// <summary> /// 删除某个Range，右侧左移 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> public void DeleteRange(string sheetName, string rangeStr) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(rangeStr, missing).Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft); } /// <summary> /// 删除某个Range，右侧左移 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> public void DeleteRange(string rangeStr) { DeleteRange(worksheet.Name, rangeStr); } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheetName"></param> /// <param name="range1"></param> /// <param name="range2"></param> public void Merge(string sheetName, string range1, string range2) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = worksheet.get_Range(range1, range2); range.Merge(true); } /// <summary> /// 合并单元格 /// </summary> /// <param name="range1"></param> /// <param name="range2"></param> public void Merge(string range1, string range2) { Merge(worksheet.Name, range1, range2); } /// <summary> /// 设置一个单元格的属性 /// </summary> /// <param name="sheetName"></param> /// <param name="range"></param> /// <param name="size">大小</param> /// <param name="name">字体</param> /// <param name="color">颜色</param> /// <param name="HorizontalAlignment">对齐方式</param> public void SetCellProperty(string sheetName, string rangeStr, double fontSize, string fontName, double height, Excel.Constants horizontalStyle) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.Font.Size = fontSize; range.Font.Name = fontName; //range.Font.Color = fontColor; range.RowHeight = height; range.HorizontalAlignment = horizontalStyle; } /// <summary> /// 设置一个单元格的属性 /// </summary> /// <param name="range"></param> /// <param name="fontSize"></param> /// <param name="fontName"></param> /// <param name="fontColor"></param> /// <param name="horizontalStyle"></param> public void SetCellProperty(string range, double fontSize, string fontName, double height, Excel.Constants horizontalStyle) { SetCellProperty(worksheet.Name, range, fontSize, fontName, height, horizontalStyle); } /// <summary> /// 设定Range的边框格式 /// </summary> /// <param name="rangeStart"></param> /// <param name="rangeEnd"></param> /// <param name="topStyle"></param> /// <param name="rightStyle"></param> /// <param name="bottomStyle"></param> /// <param name="leftStyle"></param> public void SetRangeBorderStyle(string sheetName, string rangeStr, Excel.XlLineStyle topStyle, Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = topStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = rightStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = bottomStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = leftStyle; } /// <summary> /// 设定Range的边框格式 /// </summary> /// <param name="rangeStr"></param> /// <param name="topStyle"></param> /// <param name="rightStyle"></param> /// <param name="bottomStyle"></param> /// <param name="leftStyle"></param> public void SetRangeBorderStyle(string rangeStr, Excel.XlLineStyle topStyle, Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle) { SetRangeBorderStyle(worksheet.Name, rangeStr, topStyle, rightStyle, bottomStyle, leftStyle); } /// <summary> /// 设定Range数字格式 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> /// <param name="format"></param> public void SetRangeNumberFormat(string sheetName, string rangeStr, string format) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.NumberFormat = format;//0% } /// <summary> /// 设定Range数字格式 /// </summary> /// <param name="rangeStr"></param> /// <param name="format"></param> public void SetRangeNumberFormat(string rangeStr, string format) { SetRangeNumberFormat(worksheet.Name, rangeStr, format); } /// <summary> /// 将数据表格添加到Excel指定工作表的指定位置 /// </summary> /// <param name="dt"></param> /// <param name="ws"></param> /// <param name="startX"></param> /// <param name="startY"></param> public void AddTable(System.Data.DataTable dt, int startX, int startY) { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { worksheet.Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } /// <summary> /// 加入分页符 /// </summary> /// <param name="rangeStr"></param> /// <param name="lineNumber"></param> public void AddPageBreak(string rangeStr, int lineNumber) { Excel.Range range = worksheet.get_Range("A" + lineNumber.ToString(), missing); worksheet.HPageBreaks.Add(range); } /// <summary> /// 加入分页符 /// </summary> /// <param name="lineNumber"></param> public void AddPageBreak(int lineNumber) { AddPageBreak(worksheet.Name, lineNumber); } /// <summary> /// 从当前工作表数据区域复制数据到另一个区域 /// </summary> /// <param name="sheetFromRange"></param> /// <param name="sheetToRange"></param> public void CopyRange2Range(string sheetFromRange, string sheetToRange) { CopyRange2Range(worksheet.Name, worksheet.Name, sheetFromRange, sheetToRange); } /// <summary> /// 从一个表的某个数据区域复制数据到另一个表的某个区域 /// </summary> /// <param name="sheetFromName"></param> /// <param name="sheetToName"></param> /// <param name="sheetFromRange"></param> /// <param name="sheetToRange"></param> public void CopyRange2Range(string sheetFromName, string sheetToName, string sheetFromRange, string sheetToRange) { Excel.Worksheet sheetFrom = workbook.Worksheets[sheetFromName] as Excel.Worksheet; Excel.Worksheet sheetTo = workbook.Worksheets[sheetToName] as Excel.Worksheet; sheetFrom.get_Range(sheetFromRange, missing).Copy(sheetTo.get_Range(sheetToRange, missing)); } /// <summary> /// 移动Range到另一个Range /// </summary> /// <param name="FromRange"></param> /// <param name="ToRange"></param> public void MoveRange2Range(string FromRange, string ToRange) { MoveRange2Range(worksheet.Name, FromRange, ToRange); } /// <summary> /// 移动Range到另一个Range /// </summary> /// <param name="sheetName"></param> /// <param name="FromRange"></param> /// <param name="ToRange"></param> public void MoveRange2Range(string sheetName, string FromRange, string ToRange) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(FromRange, missing).Cut(sheet.get_Range(ToRange, missing)); } /// <summary> /// 重设当前工作目录的打印区域 /// </summary> public void ResetPrintArea() { SetPrintArea(worksheet.Name, ""); } /// <summary> /// 重设工作目录的打印区域 /// </summary> public void ResetPrintArea(string sheetName) { SetPrintArea(sheetName, ""); } /// <summary> /// 设定工作目录的打印区域 /// </summary> /// <param name="area"></param> public void SetPrintArea(string area) { SetPrintArea(worksheet.Name, area); } /// <summary> /// 设定工作目录的打印区域 /// </summary> /// <param name="sheetName"></param> /// <param name="area"></param> public void SetPrintArea(string sheetName, string area) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.PageSetup.PrintArea = area; } /// <summary> /// 将当前工作表中的表格数据复制到剪切板 /// </summary> public void Copy() { if (worksheet != null) { try { worksheet.UsedRange.Select(); } catch { } worksheet.UsedRange.Copy(missing); } }} |
四、使用ADO.net+VBA操作Excel
我手头的这个案子在用第一种方法撰写一个星期之后遇到效率瓶颈，无奈改为第二种方法，由于对Excel的文档模型有了较全面的认识，几个主流函数也很清楚，所以写的速度很快。
这里我还是要佩服一下微软，曾经我对Office很不屑，后来看完《Word排版艺术》才开始认识Office，再之后深入PPT和Excel，更是感觉文档模型了不起。。。咳咳咳，扯远了~
4.1 使用ADO.net写入数据到Excel文件
废话不说了，上核心代码
|
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
|
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destFileName + ";Extended Properties=Excel 8.0;");objConn.Open();System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();objCmd.Connection = objConn;foreach (DataRow row in dt.Rows){ StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat("INSERT INTO [{0}$] (", sheetName); for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) stringBuilder.Append(dt.Columns[i].ColumnName + ","); else stringBuilder.Append(dt.Columns[i].ColumnName + ") VALUES ("); } for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) stringBuilder.Append("@" + dt.Columns[i].ColumnName + ","); else stringBuilder.Append("@" + dt.Columns[i].ColumnName + ");"); } objCmd.Parameters.Clear(); for (int i = 0; i < dt.Columns.Count; i++) { objCmd.Parameters.Add(dt.Columns[i].ColumnName, row[i]); } objCmd.CommandText = stringBuilder.ToString(); objCmd.ExecuteNonQuery();} |
注意点就是INSERT的表明就是Sheet的名字加上中括号和$符号。
4.2 使用VBA操作模板和数据
使用VBA操作模板和数据的过程是一个assign的过程，就是把数据和模板杂糅起来，类似于MVC中C控制M输出到V中，我建议针对不同类型的页面制作多个模板，而不要使用VBA进行各种样式修改操作，减少代码量。我这个案子中使用了8个不同类型模板，毕竟直接使用鼠标拖拽出一个模板比用VBA代码修改处一个模板简单的多。
Excel2007的VBA界面在“开发工具”-“Visual Basic”中打开，千万不要以为写VBA就是录制宏呀~另外附赠一个小技巧，在VBA编辑界面里面使用Ctrl+J可以自动补全，相当实用。
我把项目分成5个模块：Golbal 、Init 、Insert 、Finalize 、Utils，看名字就知道意思了，其中Utils包含一个中文大写数字转换的函数，供前面使用。
我们需要在Excel自动打开时候进行操作，需要使用函数Workbook_Open，另有Auto_Open，两个有不同之处，可以Google之。
启用代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
'开启工作簿时候动作Private Sub Workbook_Open() MAIN_SHEET = "报价模板" For Each Sheet In Sheets If Sheet.Name = MAIN_SHEET Then Exit Sub End If Next If Sheets("OPTION").Range("B2").Value = "" Then '如果不存在DH行数，则退出 Exit Sub End If MsgBox ("初始化数据，请稍等片刻！") Call Run Call DeleteSheets 'Call ActiveWorkbook.Save MsgBox ("初始化数据完成，请保存Excel文件！")End Sub |
核心代码的话。。。其实没什么核心代码，就是频繁的使用赋值和Copy函数。。。。
五、相关资料链接
检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005
PS:8000401a的错误会在服务器上出现，不会在xp上出现，原因是交互式用户也没有Excel操作权限，这时候要给Excel手动制动一个有Excel操作权限用户，比如administrator。
好了，这就是我这次Excel学习的分享，谢谢大家耐着性子看完~
版权所有 © 2010 转载本站文章请注明： 转载自Log4D 原文链接: http://dddspace.com/2010/12/csharp-vba-excel.html 您可以随意地转载本站的文章，但是必须在醒目位置注明来源及本站链接，不可以将本站文章商业化使用，或者修改、转换或者以本作品为基础进行创作。
