C#調用Excel VBA宏

近日的一系列工作是做網站的營運維護，因此做了大量的支持工具。有Excel中寫VBA的，也有直接C#做的工具。有時需要在C#中執行Excel VBA宏，甚至有時還需要在執行了VBA宏之後，獲取返回值再進行相應的處理。為了使用方便，我寫了一個執行Excel VBA宏的幫助類 。放在博客裡做個備份也希望對有類似需求的朋友有所幫助。
幫助類僅提供了一個方法：RunExcelMacro 參數說明：         string         excelFilePath  Excel文件路徑                 string         macroName    宏名稱         object[]     parameters     宏參數組         out object  rtnValue         宏返回值         bool            isShowExcel   執行時是否顯示Excel

補充說明：VBA宏需如下圖寫在模塊中，才能被此方法識別。寫在ThisWorkBook中不能被識別。

執行Excel VBA宏幫助類，注釋比較詳細，不再累贅代碼過程。最核心部分其實就是通過反射方式調用Excel VBA宏,oBook.Save()這句話也很重要，否則即使執行了VBA宏調用，也不會保存Excel更改後的內容：

1.  1 using System;  
2.   2 using System.Collections.Generic;  
3.   3 using System.Text;  
4.   4 using Excel = Microsoft.Office.Interop.Excel;  
5.   5 using Microsoft.Office.Core;  
6.   6 using System.IO;  
7.   7   
8.   8 namespace DoVBAMacro  
9.   9 {  
10.  10     /// <summary>  
11.  11     /// 執行Excel VBA宏幫助類  
12.  12     /// </summary>  
13.  13     public class ExcelMacroHelper  
14.  14     {  
15.  15         /// <summary>  
16.  16         /// 執行Excel中的宏  
17.  17         /// </summary>  
18.  18         /// <param name="excelFilePath">Excel文件路徑</param>  
19.  19         /// <param name="macroName">宏名稱</param>  
20.  20         /// <param name="parameters">宏參數組</param>  
21.  21         /// <param name="rtnValue">宏返回值</param>  
22.  22         /// <param name="isShowExcel">執行時是否顯示Excel</param>  
23.  23         public void RunExcelMacro(  
24.  24                                             string excelFilePath,  
25.  25                                             string macroName,  
26.  26                                             object[] parameters,  
27.  27                                             out object rtnValue,  
28.  28                                             bool isShowExcel  
29.  29                                         )  
30.  30         {  
31.  31             try  
32.  32             {  
33.  33                 #region 檢查入參  
34.  34   
35.  35                 // 檢查文件是否存在  
36.  36                 if (!File.Exists(excelFilePath))  
37.  37                 {  
38.  38                     throw new System.Exception(excelFilePath + " 文件不存在");  
39.  39                 }  
40.  40   
41.  41                 // 檢查是否輸入宏名稱  
42.  42                 if (string.IsNullOrEmpty(macroName))  
43.  43                 {  
44.  44                     throw new System.Exception("請輸入宏的名稱");  
45.  45                 }  
46.  46   
47.  47                 #endregion  
48.  48   
49.  49                 #region 調用宏處理  
50.  50   
51.  51                 // 准備打開Excel文件時的缺省參數對象  
52.  52                 object oMissing = System.Reflection.Missing.Value;  
53.  53   
54.  54                 // 根據參數組是否為空，准備參數組對象  
55.  55                 object[] paraObjects;  
56.  56   
57.  57                 if (parameters == null)  
58.  58                 {  
59.  59                     paraObjects = new object[] { macroName };  
60.  60                 }  
61.  61                 else  
62.  62                 {  
63.  63                     // 宏參數組長度  
64.  64                     int paraLength = parameters.Length;  
65.  65   
66.  66                     paraObjects = new object[paraLength + 1];  
67.  67   
68.  68                     paraObjects[0] = macroName;  
69.  69                     for (int i = 0; i < paraLength; i++)  
70.  70                     {  
71.  71                         paraObjects[i + 1] = parameters[i];  
72.  72                     }  
73.  73                 }  
74.  74   
75.  75                 // 創建Excel對象示例  
76.  76                 Excel.ApplicationClass oExcel = new Excel.ApplicationClass();  
77.  77   
78.  78                 // 判斷是否要求執行時Excel可見  
79.  79                 if (isShowExcel)  
80.  80                 {  
81.  81                     // 使創建的對象可見  
82.  82                     oExcel.Visible = true;  
83.  83                 }  
84.  84   
85.  85                 // 創建Workbooks對象  
86.  86                 Excel.Workbooks oBooks = oExcel.Workbooks;  
87.  87   
88.  88                 // 創建Workbook對象  
89.  89                 Excel._Workbook oBook = null;  
90.  90   
91.  91                 // 打開指定的Excel文件  
92.  92                 oBook = oBooks.Open(  
93.  93                                         excelFilePath,  
94.  94                                         oMissing,  
95.  95                                         oMissing,  
96.  96                                         oMissing,  
97.  97                                         oMissing,  
98.  98                                         oMissing,  
99.  99                                         oMissing,  
100. 100                                         oMissing,  
101. 101                                         oMissing,  
102. 102                                         oMissing,  
103. 103                                         oMissing,  
104. 104                                         oMissing,  
105. 105                                         oMissing,  
106. 106                                         oMissing,  
107. 107                                         oMissing  
108. 108                                    );  
109. 109   
110. 110                 // 執行Excel中的宏  
111. 111                 rtnValue = this.RunMacro(oExcel, paraObjects);  
112. 112   
113. 113                 // 保存更改  
114. 114                 oBook.Save();  
115. 115   
116. 116                 // 退出Workbook  
117. 117                 oBook.Close(false, oMissing, oMissing);  
118. 118   
119. 119                 #endregion  
120. 120   
121. 121                 #region 釋放對象  
122. 122   
123. 123                 // 釋放Workbook對象  
124. 124                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);  
125. 125                 oBook = null;  
126. 126   
127. 127                 // 釋放Workbooks對象  
128. 128                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);  
129. 129                 oBooks = null;  
130. 130   
131. 131                 // 關閉Excel  
132. 132                 oExcel.Quit();  
133. 133   
134. 134                 // 釋放Excel對象  
135. 135                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);  
136. 136                 oExcel = null;  
137. 137   
138. 138                 // 調用垃圾回收  
139. 139                 GC.Collect();  
140. 140   
141. 141                 #endregion  
142. 142             }  
143. 143             catch (Exception ex)  
144. 144             {  
145. 145                 throw ex;  
146. 146             }  
147. 147         }  
148. 148   
149. 149         /// <summary>  
150. 150         /// 執行宏  
151. 151         /// </summary>  
152. 152         /// <param name="oApp">Excel對象</param>  
153. 153         /// <param name="oRunArgs">參數（第一個參數為指定宏名稱，後面為指定宏的參數值）</param>  
154. 154         /// <returns>宏返回值</returns>  
155. 155         private object RunMacro(object oApp, object[] oRunArgs)  
156. 156         {  
157. 157             try  
158. 158             {  
159. 159                 // 聲明一個返回對象  
160. 160                 object objRtn;  
161. 161                   
162. 162                 // 反射方式執行宏  
163. 163                 objRtn = oApp.GetType().InvokeMember(  
164. 164                                                         "Run",  
165. 165                                                         System.Reflection.BindingFlags.Default |  
166. 166                                                         System.Reflection.BindingFlags.InvokeMethod,  
167. 167                                                         null,  
168. 168                                                         oApp,  
169. 169                                                         oRunArgs  
170. 170                                                      );  
171. 171   
172. 172                 // 返回值  
173. 173                 return objRtn;  
174. 174   
175. 175             }  
176. 176             catch (Exception ex)  
177. 177             {  
178. 178                 // 如果有底層異常，拋出底層異常  
179. 179                 if (ex.InnerException.Message.ToString().Length > 0)  
180. 180                 {  
181. 181                     throw ex.InnerException;  
182. 182                 }  
183. 183                 else  
184. 184                 {  
185. 185                     throw ex;  
186. 186                 }  
187. 187             }  
188. 188         }  
189. 189     }  
190. 190 }  
191. 191   

示例三個VBA宏方法：

1. 1 Sub getTime()  
2.  2   
3.  3     Sheet1.Cells(1, 1) = Now  
4.  4   
5.  5 End Sub  
6.  6   
7.  7   
8.  8 Sub getTime2(title As String)  
9.  9   
10. 10     Sheet1.Cells(2, 1) = title & " : " & Now  
11. 11   
12. 12 End Sub  
13. 13   
14. 14 Function getTime3(title As String)  As String  
15. 15   
16. 16     getTime3 = title & " : " & Now  
17. 17   
18. 18 End Function  
19. 19   

對應的三個使用方法 1 不帶參數的宏調用（兼演示執行過程顯示Excel文件） 2 帶參數的宏調用（兼演示執行過程不顯示Excel文件） 3 有返回值的宏調用

1.  1         private void btnExe_Click(object sender, EventArgs e)  
2.  2         {  
3.  3             try  
4.  4             {  
5.  5                 // 返回對象  
6.  6                 object objRtn = new object();  
7.  7   
8.  8                 // 獲得一個ExcelMacroHelper對象  
9.  9                 ExcelMacroHelper excelMacroHelper = new ExcelMacroHelper();  
10. 10   
11. 11                 // 執行指定Excel中的宏，執行時顯示Excel  
12. 12                 excelMacroHelper.RunExcelMacro(  
13. 13                                                     @"E:\csharp_study\DoVBAMacro\test.xls",  
14. 14                                                     "getTime2",  
15. 15                                                     new Object[] { "現在時刻" },  
16. 16                                                     out objRtn,  
17. 17                                                     true  
18. 18                                               );  
19. 19   
20. 20                 // 執行指定Excel中的宏，執行時不顯示Excel  
21. 21                 excelMacroHelper.RunExcelMacro(  
22. 22                                                     @"E:\csharp_study\DoVBAMacro\test.xls",  
23. 23                                                     "getTime2",  
24. 24                                                     new Object[] { "現在時刻" },  
25. 25                                                     out objRtn,  
26. 26                                                     false  
27. 27                                                );  
28. 28   
29. 29                 // 執行指定Excel中的宏，執行時顯示Excel，有返回值  
30. 30                 excelMacroHelper.RunExcelMacro(  
31. 31                                                     @"E:\csharp_study\DoVBAMacro\test.xls",  
32. 32                                                     "getTime3",  
33. 33                                                     new Object[] { "現在時刻" },  
34. 34                                                     out objRtn,  
35. 35                                                     true  
36. 36                                                );  
37. 37   
38. 38                 MessageBox.Show((string)objRtn);  
39. 39   
40. 40             }  
41. 41             catch(System.Exception ex)  
42. 42             {  
43. 43                 MessageBox.Show(ex.Message);  
44. 44             }  
45. 45         }  

 示例工程下載