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         }