黄聪：用C#为Excel创建个人宏工作薄(原创)

以前一直是用C#直接操作Excel,但是发现性能无比低下,最近发现用Excel中的宏可以高速的完成批处理的功能,于是决定写一个用C#为Excel文件创建宏的程序,工程如下:

using System;
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;


using Office = Microsoft.Office.Core;
using VBDE = Microsoft.Vbe.Interop;
using Excel = Microsoft.Office.Interop.Excel;

 
namespace ConsoleApplication1
{
 
    class Class1
    {
 
        [STAThread]
        static void Main(string[] args)
        {
            string MyFile = Path.GetFullPath(".") + @"\sample.xls";
            CreateWorkbook(MyFile,GetMacro());  
            Console.WriteLine("File Saved to " + MyFile);
            Console.ReadLine();
        }

        #region Get Macro
        private static string GetMacro()
        {
           StringBuilder sb = new StringBuilder();
          
           sb.Append("Sub FormatSheet()" + "\n");
           sb.Append("   msgbox \"http://www.cnblogs.com/huangcong/\"\r\n");
           sb.Append("End Sub");

           return sb.ToString();
        }
        #endregion

        #region Create Workbook
        private static void CreateWorkbook(string FileName,string Macro)
        {
             
            Excel.Application xl = null;
            Excel._Workbook wb = null;
            Excel._Worksheet sheet = null;
            VBDE.VBComponent module = null;
            bool SaveChanges = false;
 

            try
            {

                if (File.Exists(FileName)) { File.Delete(FileName); }
  
                GC.Collect(); 

                xl = new Excel.Application();
                xl.Visible = false;
             
                wb = (Excel._Workbook)(xl.Workbooks.Add( Missing.Value ));
                sheet = (Excel._Worksheet)wb.ActiveSheet;
                
                module = wb.VBProject.VBComponents.Add(VBDE.vbext_ComponentType.vbext_ct_StdModule);
                module.CodeModule.AddFromString(Macro);

                xl.Visible = false;
                xl.UserControl = false;
                SaveChanges = true;

                wb.SaveAs(FileName,Excel.XlFileFormat.xlWorkbookNormal,
                    null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,
                    false,false,null,null,null);
                 
            }
            catch( Exception theException ) 
            {
                String msg;
                msg = "Error: ";
                msg = String.Concat( msg, theException.Message );
                msg = String.Concat( msg, " Line: " );
                msg = String.Concat( msg, theException.Source ); 
                Console.WriteLine(msg);
            }
            finally
            {

                try
                {
                    xl.Visible = false;
                    xl.UserControl = false;
                    wb.Close(SaveChanges,null,null);
                    xl.Workbooks.Close();
                }
                catch { }

                xl.Quit();
                
                if (module != null)  { Marshal.ReleaseComObject (module); }
                if (sheet !=null) { Marshal.ReleaseComObject (sheet); }
                if (wb !=null)    { Marshal.ReleaseComObject (wb); }
                if (xl !=null)    { Marshal.ReleaseComObject (xl); }
                
                module = null;
                sheet=null;
                wb=null;
                xl = null;
                GC.Collect(); 
            }

        }
        #endregion
    }
}

运行结果:

打开添加了宏的Excel文件,找到刚才创建好的宏:

运行查看效果:

工程下载: 创建宏.rar

 

出处：http://www.cnblogs.com/huangcong/archive/2010/07/11/1775193.html 请输

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。