C#调用Excel的宏

最新推荐文章于 2020-07-10 11:14:57 发布

weixin_30871701

最新推荐文章于 2020-07-10 11:14:57 发布

阅读量141

点赞数

原文链接：http://www.cnblogs.com/trampt/archive/2009/04/03/1428732.html

版权

用到了三个组件

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的格式写我们必须考虑回车换行再者假如文件中存在双引号,我们还要特殊处理

Code
StreamReader reader=new StreamReader("c:\\code.txt",System.Text.Encoding.GetEncoding("gb2312"));

            string str = reader.ReadLine();
            string result = "\"" + str + "\\r\\n\"+\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
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();

转载于:https://www.cnblogs.com/trampt/archive/2009/04/03/1428732.html