C#实现方式创建、修改和删除Excel中的VBA宏实用指南

Install-Package Spire.XLS

dotnet add package Spire.XLS

using Spire.Xls;
using System.IO;

namespace CreateExcelVBA
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 创建新的 Excel 工作簿
            Workbook workbook = new Workbook();
            
            // 添加 VBA 项目到文档
            IVbaProject vbaProject = workbook.VbaProject;
            vbaProject.Name = "SampleVBAMacro";
            
            // 记录编码设置信息（中文支持）
            string text = "编码修改前: " + vbaProject.CodePage.ToString() + "n";
            vbaProject.CodePage = 936; // 设置为简体中文编码
            text += "编码修改后: " + vbaProject.CodePage.ToString() + "n";
            File.WriteAllText("encoding_info.txt", text);
            
            // 添加 VBA 模块到项目
            IVbaModule vbaModule = vbaProject.Modules.Add("SampleModule", VbaModuleType.Module);
            
            // 设置 VBA 宏源代码
            vbaModule.SourceCode = @"
Sub ExampleMacro()
    ' 声明变量
    Dim ws As Worksheet
    Dim i As Integer
    
    ' 设置对活动工作表的引用
    Set ws = ActiveSheet 
    
    ' 清除工作表内容（可选）
    ws.Cells.Clear
    
    ' 填充示例数据
    With ws
        ' 写入表头行
        .Range(""A1:C1"").Value = Array(""序号"", ""项目名称"", ""金额"")
        
        ' 循环填充10行数据
        For i = 1 To 10
            .Cells(i + 1, 1).Value = i  ' 序号列
            .Cells(i + 1, 2).Value = ""项目 "" & i  ' 项目名称列
            .Cells(i + 1, 3).Value = i * 100  ' 金额列（示例计算）
        Next i
        
        ' 自动调整列宽
        .Columns(""A:C"").AutoFit
        
        ' 格式化表头行
        With .Range(""A1:C1"")
            .Font.Bold = True
            .Interior.Color = RGB(200, 220, 255)  ' 浅蓝色背景
        End With
        
        ' 格式化金额列
        .Range(""C2:C11"").NumberFormat = ""$#,##0.00""
    End With
    
    ' 显示完成消息
    MsgBox ""数据填充完成！"", vbInformation, ""操作提示""
End Sub";
            
            // 保存 Excel 文件（97-2003 格式）
            workbook.SaveToFile("SampleWithMacro.xls", FileFormat.Version97to2003);
            Console.WriteLine("VBA 宏创建成功！");
        }
    }
}

using Spire.Xls;
using System.IO;

namespace ReadExcelVBA
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 加载包含 VBA 项目的 Excel 文件
            Workbook wb = new Workbook();
            wb.LoadFromFile("SampleWithMacro.xls");
            
            // 获取 VBA 项目
            IVbaProject vbaProject = wb.VbaProject;
            
            // 读取 VBA 项目基本信息
            string text = "是否受保护：" + vbaProject.IsProtected + "n";
            text += "项目名称：" + vbaProject.Name + "n";
            text += "项目描述：" + vbaProject.Description + "n";
            text += "帮助文件名：" + vbaProject.HelpFileName + "n";
            text += "条件编译：" + vbaProject.ConditionalCompilation + "n";
            text += "锁定项目视图：" + vbaProject.LockProjectView + "n";
            text += "密码：" + vbaProject.Password + "n";
            text += "代码页：" + vbaProject.CodePage + "n";
            
            // 获取工作表模块
            Worksheet ws = wb.Worksheets[0];
            IVbaModule mod = vbaProject.Modules.GetWorksheetModule(ws);
            
            text += "nVBA 模块信息：n";
            text += "模块名称：" + mod.Name + "n";
            text += "模块类型：" + mod.Type + "n";
            text += "源代码：n" + mod.SourceCode + "n";
            
            // 保存到文本文件
            File.WriteAllText("vba_project_info.txt", text);
            Console.WriteLine("VBA 项目信息读取完成！");
        }
    }
}

using Spire.Xls;

namespace ModifyExcelVBA
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 加载 Excel 文件
            Workbook wb = new Workbook();
            wb.LoadFromFile("SampleWithMacro.xls");
            
            // 获取 VBA 项目
            IVbaProject vbaProject = wb.VbaProject;
            
            // 修改 VBA 项目属性
            vbaProject.Password = "1234";                    // 设置项目密码
            vbaProject.Name = "ModifiedProject";             // 修改项目名称
            vbaProject.Description = "这是一个修改后的项目";  // 添加描述
            vbaProject.HelpFileName = "help.png";            // 设置帮助文件
            vbaProject.ConditionalCompilation = "DEBUG = 2"; // 设置条件编译
            vbaProject.LockProjectView = true;               // 锁定项目视图
            
            // 修改工作表模块
            Worksheet ws = wb.Worksheets[0];
            IVbaModule mod = vbaProject.Modules.GetWorksheetModule(ws);
            mod.Name = "ModifiedModule";                     // 修改模块名称
            mod.SourceCode = "Dim lRow As Long";             // 修改源代码
            mod.Type = VbaModuleType.Module;                 // 修改模块类型
            
            // 保存修改
            wb.SaveToFile("ModifiedMacro.xls");
            Console.WriteLine("VBA 项目修改成功！");
        }
    }
}

using Spire.Xls;

namespace RemoveExcelVBA
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 加载 Excel 文件
            Workbook wb = new Workbook();
            wb.LoadFromFile("SampleWithMacro.xls");
            
            // 获取 VBA 项目
            IVbaProject vbaProject = wb.VbaProject;
            
            // 删除特定模块（通过名称）
            vbaProject.Modules.Remove("SampleModule");
            
            // 删除第一个模块（通过索引）
            vbaProject.Modules.RemoveAt(0);
            
            // 或者清空所有模块
            // vbaProject.Modules.Clear();
            
            // 保存修改
            wb.SaveToFile("MacroRemoved.xls");
            Console.WriteLine("VBA 模块删除成功！");
        }
    }
}

try
{
    Workbook wb = new Workbook();
    wb.LoadFromFile("file.xls");
    
    if (wb.VbaProject != null)
    {
        // 安全地操作 VBA 项目
        IVbaProject vbaProject = wb.VbaProject;
        // ... 操作代码
    }
    else
    {
        Console.WriteLine("该文件不包含 VBA 项目");
    }
}
catch (Exception ex)
{
    Console.WriteLine("操作失败: " + ex.Message);
}

vbaProject.CodePage = 936;  // 简体中文

// vbaProject.CodePage = 950;  // 繁体中文