c#调用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更改后的内容：

帮助类需添加引用：Microsoft Excel 11.0 Object Library

 

 using System;
  using System.Collections.Generic;
  using System.Text;
  using Excel = Microsoft.Office.Interop.Excel;
  using Microsoft.Office.Core;
  using System.IO;


namespace eLims.Core.DAL
{
    public class ExcelMacroHelper
    {

        public void RunExcelMacro(
                                             string excelFilePath,
                                             string macroName,
                                             object[] parameters,
                                             out object rtnValue,
                                             bool isShowExcel
                                         )
        {
            try
            {
                #region 检查入参

                // 检查文件是否存在
                if (!File.Exists(excelFilePath))
                {
                    throw new System.Exception(excelFilePath + " 文件不存在");
                }

                // 检查是否输入宏名称
                if (string.IsNullOrEmpty(macroName))
                {
                    throw new System.Exception("请输入宏的名称");
                }

                #endregion

                #region 调用宏处理

                // 准备打开Excel文件时的缺省参数对象
                object oMissing = System.Reflection.Missing.Value;

                // 根据参数组是否为空，准备参数组对象
                object[] paraObjects;

                if (parameters == null)
                {
                    paraObjects = new object[] { macroName };
                }
                else
                {
                    // 宏参数组长度
                    int paraLength = parameters.Length;

                    paraObjects = new object[paraLength + 1];

                    paraObjects[0] = macroName;
                    for (int i = 0; i < paraLength; i++)
                    {
                        paraObjects[i + 1] = parameters[i];
                    }
                }

                // 创建Excel对象示例
                Excel.ApplicationClass oExcel = new Excel.ApplicationClass();

                // 判断是否要求执行时Excel可见
                if (isShowExcel)
                {
                    // 使创建的对象可见
                    oExcel.Visible = false;
                }

                // 创建Workbooks对象
                Excel.Workbooks oBooks = oExcel.Workbooks;

                // 创建Workbook对象
                Excel._Workbook oBook = null;

                // 打开指定的Excel文件
                oBook = oBooks.Open(
                                        excelFilePath,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing
                                  );

                // 执行Excel中的宏
                rtnValue = this.RunMacro(oExcel, paraObjects);
                
                // 保存更改
                oBook.Save();

                // 退出Workbook
                oBook.Close(false, oMissing, oMissing);

                #endregion

                #region 释放对象

                // 释放Workbook对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                oBook = null;

                // 释放Workbooks对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                oBooks = null;

                // 关闭Excel
                oExcel.Quit();

                // 释放Excel对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                oExcel = null;

                // 调用垃圾回收
                GC.Collect();

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }







        private object RunMacro(object oApp, object[] oRunArgs)
         {
             try
             {
                 // 声明一个返回对象
                 object objRtn;
                 
                 // 反射方式执行宏
                 objRtn = oApp.GetType().InvokeMember(
                                                         "Run",
                                                         System.Reflection.BindingFlags.Default |
                                                         System.Reflection.BindingFlags.InvokeMethod,
                                                         null,
                                                         oApp,
                                                         oRunArgs
                                                     );
 
                 // 返回值
                 return objRtn;
 
             }
             catch (Exception ex)
             {
                // 如果有底层异常，抛出底层异常
                 if (ex.InnerException.Message.ToString().Length > 0)
                {
                     throw ex.InnerException;
                 }
                 else
                 {
                     throw ex;
                 }
             }
         }


    }

}

 

 

示例三个VBA宏方法：

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

 

 

对应的三个使用方法
1 不带参数的宏调用（兼演示执行过程显示Excel文件）
2 带参数的宏调用（兼演示执行过程不显示Excel文件）
3 有返回值的宏调用

 

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