用到了三个组件
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using VBIDE = Microsoft.Vbe.Interop;
因为涉及到大批量的Excel文件的处理
首先 先要把文本文件中保存的vba函数倒入到Excel中 手动倒入,假设有几千个Excel 将会窒息
假设 "1.txt"文件内容为
Sub DeleteLink()
Cells.Hyperlinks.Delete
End Sub
将文本文件写到一个str中 此str比较特殊,因为要按照vba的格式写 我们必须考虑回车换行 再者 假如文件中存在双引号,我们还要特殊处理
StreamReader reader=new StreamReader("c:\\code.txt",System.Text.Encoding.GetEncoding("gb2312"));
string str = reader.ReadLine();
string result = str + "\r\n";
while ((str=reader.ReadLine())!=null)
{
if (str != "")
{
str = "\"" + str + "\\r\\n\"+\r\n";
}
result = result + str;
}
textBox1.Text = result;
//str.Replace("\r", "\"+");
reader.Close();
处理完后 就要倒入宏,然后执行了
Code
Excel.Application app = null;
Excel.Workbook workBook = null;
VBIDE.VBComponent vbComponent = null;
string strCode;
Object oMissing = System.Reflection.Missing.Value;
Object missing = System.Reflection.Missing.Value;
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = app.Workbooks._Open("c:\\aaaa.xls", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
vbComponent = workBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
#region
strCode = "Sub ceshi()\r\n" +
"Cells.Hyperlinks.Delet\r\n"+
"End Sub";
#endregion
vbComponent.CodeModule.AddFromString(strCode);
try
{
app.Run("ceshi", missing, missing,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
}
catch
{
GC.Collect();
}
app.Visible = true;
app.UserControl = true;
vbComponent = null;
workBook = null;
app = null;
GC.Collect();