执行VBA的C#底层引擎编写

VBA执行分几种：

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;
using Microsoft.Vbe.Interop;
using Microsoft.Office.Interop.Excel;
using System.Text;
using VBIDE = Microsoft.Vbe.Interop;


namespace ExcelVBA
{
    public class VBAHelper
    {
        /// <summary>
        /// 执行Excel中的宏
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <param name="macroName">宏名称</param>
        /// <param name="parameters">宏参数组</param>
        /// <param name="rtnValue">宏返回值</param>
        /// <param name="isShowExcel">执行时是否显示Excel</param>
        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 = true;
                }

                // 创建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;
            }
        }

        /// <summary>
        /// 执行宏
        /// </summary>
        /// <param name="oApp">Excel对象</param>
        /// <param name="oRunArgs">参数（第一个参数为指定宏名称，后面为指定宏的参数值）</param>
        /// <returns>宏返回值</returns>
        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;
                }
            }
        }

        public void saveExcel()
        {
            string filePath = System.Reflection.Assembly.GetExecutingAssembly().Location;
            //string directoryPath = System.IO.Path.GetDirectoryName(filePath);
            //string excelPath = System.IO.Path.Combine(directoryPath, "ExcelFile.xlsx");

            Excel.Application excelApp = new Excel.Application();
            Workbook workbook = excelApp.ActiveWorkbook;
            workbook.SaveAs("C:\\Users\\username\\Documents\\test.xlsm", XlFileFormat.xlOpenXMLWorkbookMacroEnabled);

        }
        //添加宏，执行宏
        public void AddMacro()
        {
            try
            {

                // open excel file 
                const string excelFile = @"C:\Users\Desktop\vba\test1.xlsm";
                var excelApplication = new Excel.Application { Visible = true };
                var targetExcelFile = excelApplication.Workbooks.Open(excelFile);

                // add standart module to file
                var newStandardModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                var codeModule = newStandardModule.CodeModule;

                // add vba code to module
                var lineNum = codeModule.CountOfLines + 1;
                var macroName = "Button1_Click";
                var codeText = "Public Sub " + macroName + "()" + "\r\n";
                codeText += "  MsgBox \"Hi from Excel\"" + "\r\n";
                codeText += "End Sub";

                codeModule.InsertLines(lineNum, codeText);
                targetExcelFile.Save();

                // run the macro
                var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newStandardModule.Name, macroName);
                excelApplication.Run(macro);

                excelApplication.Quit();

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }
        }

        //添加透视表选定宏，执行宏
        public void AddMacroWatcher()
        {
            try
            {

                // open excel file 
                const string excelFile = @"C:\Users\Desktop\vba\test2.xlsm";
                var excelApplication = new Excel.Application { Visible = true };
                var targetExcelFile = excelApplication.Workbooks.Open(excelFile);

                // add standart module to file
                var newStandardModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                //var newworksheetModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_Document);
                //var newworksheetModule = targetExcelFile.VBProject.VBComponents.Item("Sheet1");
                var codeModule = newStandardModule.CodeModule;
                // var codeModule = newworksheetModule.CodeModule;
                /****
                 * 
                 * Private Sub Worksheet_SelectionChange(ByVal Target As Range)

                    Open "C:\Users\leo_b\Desktop\testvba3.txt" For Output As #1
                    Dim cell As Range
                     Dim str As String
                      For Each cell In Selection
                       str = str & cell.Value & vbNewLine
                      Next cell
                         Print #1, str
                        Close #1
                      MsgBox str + "写入完成"
                    End Sub
                 * 
                 ***/

                // add vba code to module
                var lineNum = codeModule.CountOfLines + 1;
                var macroName = "Worksheet_SelectionChange";
                var codeText = "Private Sub " + macroName + "(ByVal Target As Range)" + "\r\n";
                // codeText += " Open \"C:\\Users\\leo_b\\Desktop\\testvba3.txt\" For Output As #1" + "\r\n";
                codeText += " Open \"" + excelFile + "\" For Output As #1" + "\r\n";
                codeText += "   Dim cell As Range" + "\r\n";
                codeText += "     Dim str As String" + "\r\n";
                codeText += "      For Each cell In Selection" + "\r\n";
                codeText += "    str = str & cell.Value & vbNewLine" + "\r\n";
                codeText += "    Next cell" + "\r\n";
                codeText += "    Print #1, str" + "\r\n";
                codeText += "    Close #1" + "\r\n";
                codeText += "  MsgBox \"写入完成1\"" + "\r\n";
                codeText += "End Sub";

                codeModule.InsertLines(lineNum, codeText);
                targetExcelFile.Save();

                //  excelApplication.SheetSelectionChange += Application_SheetSelectionChange;

                // run the macro
                var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newStandardModule.Name, macroName);
                // var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newworksheetModule.Name, macroName);
                excelApplication.Run(macro);

                excelApplication.Quit();

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }
        }
        private void Application_SheetSelectionChange(Object sh, Range Target)
        {
            // Target.Value = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss");
            // xlRange = (Globals.ThisAddIn.Application.ActiveCell as Excel.Range).CurrentRegion;
            // 在selectchange事件中运行VBA代码
            // ThisWorkbook.Application.Run("VBA代码名称");

        }

        public void vbarun()
        {
            //获取当前工作表
            // Excel.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
            //创建Excel Application对象
            Excel.Application excelApp = new Excel.Application(); //隐藏Excel窗口 
            excelApp.Visible = false; //打开Excel文件
            Workbook workbook = excelApp.Workbooks.Open("C:\\Users\\leo_b\\Desktop\\vba\\test3.xlsm");
            //saveExcel();
            string excelFile = "C:\\Users\\leo_b\\Desktop\\testvba3.txt";
            //获取第一个工作表 
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
            // 定义VBA代码
            //string vbaCode = "Sub HelloWorld() \n" + "MsgBox "Hello, World!" \n" + "End Sub";
            var macroName = "Worksheet_SelectionChange";
            var vbaCode = "Private Sub " + macroName + "(ByVal Target As Range)" + "\r\n";
            // codeText += " Open \"C:\\Users\\leo_b\\Desktop\\testvba3.txt\" For Output As #1" + "\r\n";
            vbaCode += " Open \"" + excelFile + "\" For Output As #1" + "\r\n";
            vbaCode += "   Dim cell As Range" + "\r\n";
            vbaCode += "     Dim str As String" + "\r\n";
            vbaCode += "      For Each cell In Selection" + "\r\n";
            vbaCode += "    str = str & cell.Value & vbNewLine" + "\r\n";
            vbaCode += "    Next cell" + "\r\n";
            vbaCode += "    Print #1, str" + "\r\n";
            vbaCode += "    Close #1" + "\r\n";
            vbaCode += "  MsgBox \"写入完成1\"" + "\r\n";
            vbaCode += "End Sub";
            // 运行VBA代码
            worksheet.Parent.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, worksheet.Parent, new object[] { vbaCode });

        }

        public string Reader(string readfile)
        {
            //string ExcelPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
            //int last = ExcelPath.LastIndexOf("\\");
            //ExcelPath = ExcelPath.Substring(0, last);
            //TODO 20230522 合并透视表的数据和具体字段定义信息
            try
            {
                //data
                string readfiled = "";
                readfiled = readfile + "\\testvba2.txt";
                StreamReader sr = new StreamReader(readfiled, Encoding.UTF8);
                String line;
                string text = "";
                while ((line = sr.ReadLine()) != null)
                {
                    text += line.ToString();
                }
                //toushibiao
                string readfilet = "";
                readfilet = readfile + "\\testvba3.txt";
                StreamReader srp = new StreamReader(readfilet, Encoding.UTF8);
                String linep;
                string textp = "";
                while ((linep = srp.ReadLine()) != null)
                {
                    textp += linep.ToString();
                }

                return text + "," + textp;


            }
            catch (Exception ex)
            {
                return "";
            }
        }

        /// <summary>
        /// 取得语句中的VBA
        /// </summary>
        /// <param name="vba">整个语句</param>
        /// <returns>标准的VBA</returns>
        public string SubstringVBA(string vba)
        {
            int sub = vba.IndexOf("```");//vba语句开始索引
            int endsub = vba.LastIndexOf("```");//vba语句结束索引
            vba = vba.Remove(endsub);//移除vba尾部不相关语句
            sub = vba.IndexOf("```");//重新计算vba开头
            vba = vba.Substring(sub + 3);//从vba开头拿vba语句
            return vba;
        }
        /// <summary>
        /// 执行VBA
        /// </summary>
        /// <param name="filePath">路径</param>
        /// <param name="componentName">模块名称</param>
        /// <param name="procedureName">事件过程名称</param>
        /// <param name="vbaCode">vba代码</param>
        public void ExecuteVbaCode(string filePath, string componentName, string procedureName, string vbaCode)
        {
            //componentName = "模块1";
            //procedureName = "Button1_Click";//事件过程名称
            //vbaCode vba代码
            // vbaCode = "Public Sub " + procedureName + "()" + "\r\n";
            //vbaCode += "  MsgBox \"Hi from Excel of vba\"" + "\r\n";
            //vbaCode += "End Sub";
            //filePath = @"C:\Users\Desktop\vba\test1.xlsm";
            try
            {
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;  //不显示Excel应用程序

                Excel.Workbook workbook = excelApp.Workbooks.Open(filePath);

                VBIDE.VBE vbe = workbook.VBProject.VBE;
                //新建模块名，不用参数名
                var newStandardModule = workbook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                componentName = newStandardModule.Name;
                //
                VBIDE.VBComponent vbComponent = vbe.ActiveVBProject.VBComponents.Item(componentName); //此处为代码模块名称

                VBIDE.CodeModule code = vbComponent.CodeModule;
                var lineNum = code.CountOfLines + 1;
                code.InsertLines(lineNum + 1, vbaCode);  //此处为要插入执行的VBA代码

                excelApp.Run(componentName + "." + procedureName);  //执行指定代码模块的过程或函数, 此处为代码模块名称和过程或事件名称

                workbook.Close(false);
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
            catch (Exception ex)
            {
                //Common.SaveLog("Run VBA Exception:", ex.Message);
            }

        }
        //Sub PrintRangeCoordinateInfo()
        /// <summary>
        /// 取得语句中的ProcedureName
        /// </summary>
        /// <param name="vbaS">整个语句</param>
        /// <returns>标准的VBA</returns>
        public string SubsProcedureName(string vbaS)
        {
            //vbaS = "Sub PrintRangeCoordinateInfo()";
            int sub = vbaS.IndexOf("Sub");//sub语句开始索引
            int endsub = vbaS.IndexOf("()");//ProcedureName语句结束索引
            vbaS = vbaS.Remove(endsub);//移除vba尾部不相关语句
            sub = vbaS.IndexOf("Sub");//重新计算vba开头
            vbaS = vbaS.Substring(sub + 3);//从vba开头拿sub开头语句
            return vbaS;
        }
    }
}