Spire.XLS 16.3.6 支持操作 VBA 宏工程

sales@e-iceblue.com

028-81705109

2790765778

微信扫一扫

论坛

Spire.Cloud 纯前端文档控件

Spire.XLS 16.3.6 支持操作 VBA 宏工程

Spire.XLS 16.3.6 现已发布。该版本支持操作 VBA 宏工程并新增了对数据模拟分析功能的支持。此外，还修复了复制工作表时宏丢失的问题。详情如下。

新功能:

支持添加 VBA 宏工程。

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(outputFile_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 = ""Project "" & 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";

// Save the Excel file
workbook.SaveToFile(outputFile_Xls, FileFormat.Version97to2003);

支持读取VBA 宏工程。

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet ws = wb.Worksheets[0];
IVbaProject vbaProject = wb.VbaProject;

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";

IVbaModule mod = vbaProject.Modules.GetWorksheetModule(ws);
text += "VBA 模块接口：" + "\n";
text += "模块名称：" + mod.Name.ToString() + "\n";
text += "源代码：\n" + mod.SourceCode.ToString() + "\n";
text += "模块类型：" + mod.Type.ToString() + "\n";

File.WriteAllText(outputFile_TXT, text.ToString());
vbaProject.Modules.Clear();
wb.SaveToFile(outputFile);

支持编辑 VBA 宏工程。

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet ws = wb.Worksheets[0];

IVbaProject vbaProject = wb.VbaProject;
vbaProject.Password = "1234";
vbaProject.Name = "modify";
vbaProject.Description = "Description";
vbaProject.HelpFileName = "image1.png";
vbaProject.ConditionalCompilation = "DEBUG = 2";
vbaProject.LockProjectView = true;

IVbaModule mod = vbaProject.Modules.GetWorksheetModule(ws);
mod.Name = "IVbaModule";
mod.SourceCode = "Dim lRow As Long";
mod.Type = VbaModuleType.Module;

wb.SaveToFile(outputFile);

支持删除 VBA 宏工程。

Workbook wb1 = new Workbook();
wb1.LoadFromFile(inputFile_1);
IVbaProject vbaProject1 = wb1.VbaProject;

vbaProject1.Modules.Remove("SampleModule");
vbaProject1.Modules.RemoveAt(0);

wb1.SaveToFile(outputFile_1);

支持创建多个情景方案。

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet worksheet = wb.Worksheets[0];

// 访问工作表中的情景集合
XlsScenarioCollection scenarios = worksheet.Scenarios;

// 为情景初始化存储不同数值的列表对象
List<object> currentChangePercentage_Values = new List<object> { 0.23, 0.8, 1.1, 0.5, 0.35, 0.2 };
List<object> increasedChangePercentage_Values = new List<object> { 0.45, 0.56, 0.9, 0.5, 0.58, 0.43 };
List<object> decreasedChangePercentage_Values = new List<object> { 0.3, 0.2, 0.5, 0.3, 0.5, 0.23 };
List<object> currentQuantity_Values = new List<object> { 1500, 3000, 5000, 4000, 500, 4000 };
List<object> increasedQuantity_Values = new List<object> { 1000, 5000, 4500, 3900, 10000, 8900 };
List<object> decreasedQuantity_Values = new List<object> { 1000, 2000, 3000, 3000, 300, 4000 };

// 在工作表中，为同一组单元格添加携带不同数值的情景
scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values);
scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values);
scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values);
scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values);
scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values);
scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values);

// 保存工作簿
wb.SaveToFile(outputFile, ExcelVersion.Version2013);
wb.Dispose();

支持生成情景摘要报告

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet worksheet = wb.Worksheets[0];

// 访问工作表中的情景集合
XlsScenarioCollection scenarios = worksheet.Scenarios;

// 为情景初始化存储不同数值的列表对象
List<object> currentChangePercentage_Values = new List<object>{ 0.23, 0.8, 1.1, 0.5, 0.35, 0.2 };
List<object> increasedChangePercentage_Values = new List<object> { 0.45, 0.56, 0.9, 0.5, 0.58, 0.43 };
List<object> decreasedChangePercentage_Values = new List<object> { 0.3, 0.2, 0.5, 0.3, 0.5, 0.23 };
List<object> currentQuantity_Values = new List<object> { 1500, 3000, 5000, 4000, 500, 4000 };
List<object> increasedQuantity_Values = new List<object> { 1000, 5000, 4500, 3900, 10000, 8900 };
List<object> decreasedQuantity_Values = new List<object> { 1000, 2000, 3000, 3000, 300, 4000 };

// 在工作表中，为同一组单元格添加携带不同数值的情景
scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values);
scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values);
scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values);
scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values);
scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values);
scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values);

// 创建摘要汇总
worksheet.Scenarios.Summary(worksheet.Range["L7"]);

// 保存工作簿
wb.SaveToFile(outputFile, ExcelVersion.Version2013);
wb.Dispose();

支持编辑情景方案

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet worksheet = wb.Worksheets[0];

// 访问工作表中的情景集合
XlsScenarioCollection scenarios = worksheet.Scenarios;
XlsScenario scenario1 = scenarios[0];
XlsScenario scenario2 = scenarios[1];

// 修改情景方案
scenario1.SetVariableCells(worksheet.Range["A1:A5"], scenario2.Values);

CellRange sourceCell = worksheet.Range["B1:B5"];
scenario2.SetVariableCells(sourceCell, scenario2.Values);

scenario1.Show();
scenario2.Show();

// 保存工作簿
wb.SaveToFile(outputFile, ExcelVersion.Version2013);
wb.Dispose();

支持合并情景方案

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet worksheet1 = wb.Worksheets[0];
Worksheet worksheet2 = wb.Worksheets[1];

// 合并情景方案
worksheet1.Scenarios.Merge(worksheet2);

// 保存工作簿
wb.SaveToFile(outputFile, ExcelVersion.Version2013);
wb.Dispose();

支持删除情景方案

Workbook wb = new Workbook();
wb.LoadFromFile(inputFile);
Worksheet worksheet = wb.Worksheets[0];

// 访问工作表中的情景集合
XlsScenarioCollection scenarios = worksheet.Scenarios;

// 删除情景方案
scenarios.RemoveScenarioAt(0);
scenarios.RemoveScenarioByName("two");

string content = "";
content += "Count:" + scenarios.Count + "\n";
content += "ContainsScenario:" + scenarios.ContainsScenario("two").ToString() + "\n";
content += "ContainsScenario:" + scenarios.ContainsScenario("one").ToString() + "\n";
File.WriteAllText(outputFile, content.ToString());

// 保存工作簿
wb.SaveToFile(outputFile, ExcelVersion.Version2013);
wb.Dispose();

问题修复:

修复了复制工作表，宏丢失的问题。

下载Spire.XLS 16.3.6，请点击：

https://www.e-iceblue.cn/Downloads/Spire-XLS-NET.html

返回顶部

Spire.XLS 16.3.6 支持操作 VBA 宏工程

新闻类别