以前一直是用C#直接操作Excel,但是发现性能无比低下,最近发现用Excel中的宏可以高速的完成批处理的功能,于是决定写一个用C#为Excel文件创建宏的程序,工程如下:
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
}
}
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
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
本文转自黄聪博客园博客,原文链接http://www.cnblogs.com/huangcong/archive/2010/07/11/1775193.html如需转载请自行联系原作者