在C#代码中编写宏操作Excel

Workbook oBook; Application oXlsApp; private void OpenExcelConnection()//创建workBook对象 { oXlsApp = new Application(); oXlsApp.Visible = false; // Excel表示有無 oXlsApp.DisplayAlerts = false; // Excelの確認ダイアログ表示有無 oBook = (oXlsApp.Workbooks.Open( excelFileName, // Filename Type.Missing, // UpdateLinks Type.Missing, // ReadOnly Type.Missing, // Format Type.Missing, // Password Type.Missing, // WriteResPassword Type.Missing, // IgnoreReadOnlyRecommended Type.Missing, // Origin Type.Missing, // Delimiter Type.Missing, // Editable Type.Missing, // Notify Type.Missing, // Converter Type.Missing, // AddToMru Type.Missing, // Local Type.Missing // CorruptLoad )); oSheet = (Worksheet)oBook.ActiveSheet; } private void CreateMacro()//创建宏 { try { VBIDE.VBComponent oModule; oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule); string sCode = @" Sub StyleChange() Application.DisplayAlerts = False Dim i, j, k, str, Sheet, sht Sheet = Worksheets.Count() For sht = 1 To Sheet With Worksheets(sht) .Cells(1, 1).Interior.ColorIndex = 3 .Cells(1, 2).Interior.ColorIndex = 3 .Cells(1, 3).Interior.ColorIndex = 3 .Cells(1, 4).Interior.ColorIndex = 3 .Cells(1, 5).Interior.ColorIndex = 3 .Cells(1, 6).Interior.ColorIndex = 3 .Cells(1, 7).Interior.ColorIndex = 3 .Cells(1, 8).Interior.ColorIndex = 3 End With Next Application.DisplayAlerts = True End Sub "; oModule.CodeModule.AddFromString(sCode); Console.WriteLine("success"); } catch (Exception ex) { throw ex; } } /// <summary> /// 运行宏 /// </summary> /// <param name="macroName">宏名</param> /// <param name="proObject">处理对象</param> /// <returns></returns> private object RunMacro(string macroName, out object proObject) { try { //宏执行时Excel不可见 oXlsApp.Visible = false; oXlsApp.DisplayAlerts = false; oXlsApp.ScreenUpdating = false; //准备打开Excel文件时的缺省参数对象 object oMissing = Missing.Value; //反射方式执行宏 proObject = oXlsApp.GetType().InvokeMember( "Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, oXlsApp, new object[] { macroName } ); oBook.RemovePersonalInformation = false; oBook.Save(); oXlsApp.DisplayAlerts = true; oXlsApp.ScreenUpdating = true; //返回执行对象 return proObject; } catch (Exception ex) { Console.WriteLine(ex.StackTrace+ex.Message); proObject = null; return null; } finally { if (oXlsApp != null) { //断开连接 oBook.Close(false, Type.Missing, Type.Missing); oXlsApp.DisplayAlerts = false; oXlsApp.Quit(); oBook = null; oXlsApp = null; GC.Collect(); } } }