高级公式与 VBA 集成

本页是公式与函数应用的续篇，可结合前文一起阅读。

高级公式应用

数组公式应用

using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;

namespace ArrayFormulaExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("=== 数组公式应用示例 ===");
            
            try
            {
                // 基本数组公式
                BasicArrayFormulaDemo();
                
                // 动态数组公式
                DynamicArrayFormulaDemo();
                
                // 矩阵运算
                MatrixFormulaDemo();
                
                Console.WriteLine("\n✓ 数组公式示例完成");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"✗ 数组公式示例时出错: {ex.Message}");
            }
        }
        
        static void BasicArrayFormulaDemo()
        {
            Console.WriteLine("\n1. 基本数组公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "基本数组公式";
            
            // 创建测试数据
            var salesData = new double[,]
            {
                { 1200, 1500, 1800, 2000 },
                { 900, 1100, 1300, 1600 },
                { 800, 950, 1200, 1400 },
                { 1500, 1700, 1900, 2100 }
            };
            
            // 写入销售数据
            for (int row = 0; row < salesData.GetLength(0); row++)
            {
                for (int col = 0; col < salesData.GetLength(1); col++)
                {
                    worksheet.Cells[row + 1, col + 1].Value = salesData[row, col];
                }
            }
            
            // 添加季度标签
            string[] quarters = { "Q1", "Q2", "Q3", "Q4" };
            for (int i = 0; i < quarters.Length; i++)
            {
                worksheet.Cells[6, i + 1].Value = quarters[i] + "总和";
            }
            
            // 使用数组公式计算季度总和
            for (int col = 1; col <= 4; col++)
            {
                // 传统数组公式（需要Ctrl+Shift+Enter）
                worksheet.Cells[6, col].FormulaArray = $"=SUM(A1:A4)";
                
                // 现代Excel中的动态数组公式
                worksheet.Cells[7, col].Formula = $"=SUM(A1:A4)";
            }
            
            // 数组公式：计算每个季度的平均值
            worksheet.Cells[8, 1].Value = "季度平均值:";
            for (int col = 1; col <= 4; col++)
            {
                worksheet.Cells[8, col + 1].FormulaArray = $"=AVERAGE(A1:A4)";
            }
            
            // 设置格式
            var dataRange = worksheet.Range("A1:D4");
            dataRange.NumberFormat = "#,##0";
            
            var totalRange = worksheet.Range("A6:D7");
            totalRange.Font.Bold = true;
            totalRange.Interior.Color = System.Drawing.Color.LightBlue;
            
            var averageRange = worksheet.Range("B8:E8");
            averageRange.Font.Bold = true;
            averageRange.Interior.Color = System.Drawing.Color.LightGreen;
            
            worksheet.Columns["A:E"].AutoFit();
            
            workbook.SaveAs("BasicArrayFormulas.xlsx");
            Console.WriteLine("  ✓ 基本数组公式创建完成");
        }
        
        static void DynamicArrayFormulaDemo()
        {
            Console.WriteLine("\n2. 动态数组公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "动态数组公式";
            
            // 创建员工数据
            var employees = new[]
            {
                new { Name = "张三", Department = "销售部", Salary = 12000, Bonus = 3000 },
                new { Name = "李四", Department = "技术部", Salary = 15000, Bonus = 4000 },
                new { Name = "王五", Department = "财务部", Salary = 13000, Bonus = 3500 },
                new { Name = "赵六", Department = "销售部", Salary = 11000, Bonus = 2800 },
                new { Name = "钱七", Department = "技术部", Salary = 16000, Bonus = 4500 }
            };
            
            // 写入数据
            worksheet.Cells[1, 1].Value = "姓名";
            worksheet.Cells[1, 2].Value = "部门";
            worksheet.Cells[1, 3].Value = "基本工资";
            worksheet.Cells[1, 4].Value = "奖金";
            worksheet.Cells[1, 5].Value = "总收入";
            
            for (int i = 0; i < employees.Length; i++)
            {
                worksheet.Cells[i + 2, 1].Value = employees[i].Name;
                worksheet.Cells[i + 2, 2].Value = employees[i].Department;
                worksheet.Cells[i + 2, 3].Value = employees[i].Salary;
                worksheet.Cells[i + 2, 4].Value = employees[i].Bonus;
                worksheet.Cells[i + 2, 5].Formula = $"=C{i+2}+D{i+2}";
            }
            
            // 动态数组公式：筛选销售部员工
            worksheet.Cells[8, 1].Value = "销售部员工:";
            worksheet.Cells[8, 1].Font.Bold = true;
            
            // 使用FILTER函数（现代Excel）
            worksheet.Cells[9, 1].Formula = "=FILTER(A2:E6,B2:B6=\"销售部\")";
            
            // 动态数组公式：排序总收入
            worksheet.Cells[8, 7].Value = "按总收入排序:";
            worksheet.Cells[8, 7].Font.Bold = true;
            
            // 使用SORT函数
            worksheet.Cells[9, 7].Formula = "=SORT(A2:E6,5,-1)"; // 按第5列降序排序
            
            // 动态数组公式：唯一部门列表
            worksheet.Cells[8, 12].Value = "唯一部门:";
            worksheet.Cells[8, 12].Font.Bold = true;
            
            worksheet.Cells[9, 12].Formula = "=UNIQUE(B2:B6)";
            
            // 设置格式
            var headerRange = worksheet.Range("A1:E1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightGray;
            
            worksheet.Range("C2:E7").NumberFormat = "#,##0";
            
            var dynamicHeader = worksheet.Range("A8:A8,G8:G8,L8:L8");
            dynamicHeader.Font.Bold = true;
            dynamicHeader.Font.Size = 11;
            
            worksheet.Columns["A:M"].AutoFit();
            
            workbook.SaveAs("DynamicArrayFormulas.xlsx");
            Console.WriteLine("  ✓ 动态数组公式创建完成");
        }
        
        static void MatrixFormulaDemo()
        {
            Console.WriteLine("\n3. 矩阵运算公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "矩阵运算";
            
            // 创建两个矩阵
            var matrixA = new double[,]
            {
                { 1, 2, 3 },
                { 4, 5, 6 },
                { 7, 8, 9 }
            };
            
            var matrixB = new double[,]
            {
                { 9, 8, 7 },
                { 6, 5, 4 },
                { 3, 2, 1 }
            };
            
            // 写入矩阵A
            worksheet.Cells[1, 1].Value = "矩阵A:";
            worksheet.Cells[1, 1].Font.Bold = true;
            
            for (int row = 0; row < 3; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    worksheet.Cells[row + 2, col + 1].Value = matrixA[row, col];
                }
            }
            
            // 写入矩阵B
            worksheet.Cells[1, 5].Value = "矩阵B:";
            worksheet.Cells[1, 5].Font.Bold = true;
            
            for (int row = 0; row < 3; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    worksheet.Cells[row + 2, col + 5].Value = matrixB[row, col];
                }
            }
            
            // 矩阵加法
            worksheet.Cells[6, 1].Value = "矩阵加法 (A+B):";
            worksheet.Cells[6, 1].Font.Bold = true;
            
            for (int row = 0; row < 3; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    worksheet.Cells[row + 7, col + 1].Formula = $"=A{row+2}+E{row+2}";
                }
            }
            
            // 矩阵乘法
            worksheet.Cells[6, 5].Value = "矩阵乘法 (A*B):";
            worksheet.Cells[6, 5].Font.Bold = true;
            
            // 使用MMULT函数进行矩阵乘法
            for (int row = 0; row < 3; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    // 矩阵乘法公式
                    worksheet.Cells[row + 7, col + 5].FormulaArray = $"=MMULT(A2:C4,E2:G4)";
                }
            }
            
            // 矩阵转置
            worksheet.Cells[11, 1].Value = "矩阵A转置:";
            worksheet.Cells[11, 1].Font.Bold = true;
            
            for (int row = 0; row < 3; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    worksheet.Cells[row + 12, col + 1].FormulaArray = $"=TRANSPOSE(A2:C4)";
                }
            }
            
            // 矩阵行列式
            worksheet.Cells[11, 5].Value = "矩阵A行列式:";
            worksheet.Cells[11, 5].Font.Bold = true;
            
            worksheet.Cells[12, 5].FormulaArray = "=MDETERM(A2:C4)";
            
            // 设置格式
            var matrixARange = worksheet.Range("A2:C4");
            var matrixBRange = worksheet.Range("E2:G4");
            
            matrixARange.Interior.Color = System.Drawing.Color.LightBlue;
            matrixBRange.Interior.Color = System.Drawing.Color.LightGreen;
            
            var resultRange1 = worksheet.Range("A7:C9");
            var resultRange2 = worksheet.Range("E7:G9");
            var resultRange3 = worksheet.Range("A12:C14");
            
            resultRange1.Interior.Color = System.Drawing.Color.LightYellow;
            resultRange2.Interior.Color = System.Drawing.Color.LightPink;
            resultRange3.Interior.Color = System.Drawing.Color.LightCyan;
            
            worksheet.Columns["A:G"].AutoFit();
            
            workbook.SaveAs("MatrixFormulas.xlsx");
            Console.WriteLine("  ✓ 矩阵运算公式创建完成");
        }
    }
}

自定义函数与VBA集成

自定义函数应用

using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;

namespace CustomFunctionExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("=== 自定义函数与VBA集成示例 ===");
            
            try
            {
                // 自定义函数应用
                CustomFunctionDemo();
                
                // VBA宏集成
                VbaIntegrationDemo();
                
                // 事件处理函数
                EventHandlerDemo();
                
                Console.WriteLine("\n✓ 自定义函数示例完成");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"✗ 自定义函数示例时出错: {ex.Message}");
            }
        }
        
        static void CustomFunctionDemo()
        {
            Console.WriteLine("\n1. 自定义函数应用：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "自定义函数";
            
            // 创建业务数据
            var businessData = new[]
            {
                new { Product = "笔记本电脑", Cost = 4000, Price = 5999, Quantity = 50 },
                new { Product = "智能手机", Cost = 2500, Price = 3999, Quantity = 100 },
                new { Product = "打印机", Cost = 800, Price = 1299, Quantity = 30 },
                new { Product = "办公桌", Cost = 600, Price = 899, Quantity = 20 }
            };
            
            // 写入数据
            worksheet.Cells[1, 1].Value = "产品名称";
            worksheet.Cells[1, 2].Value = "成本";
            worksheet.Cells[1, 3].Value = "售价";
            worksheet.Cells[1, 4].Value = "数量";
            worksheet.Cells[1, 5].Value = "毛利率";
            worksheet.Cells[1, 6].Value = "总利润";
            worksheet.Cells[1, 7].Value = "利润率";
            
            for (int i = 0; i < businessData.Length; i++)
            {
                var data = businessData[i];
                int row = i + 2;
                
                worksheet.Cells[row, 1].Value = data.Product;
                worksheet.Cells[row, 2].Value = data.Cost;
                worksheet.Cells[row, 3].Value = data.Price;
                worksheet.Cells[row, 4].Value = data.Quantity;
                
                // 使用自定义公式计算业务指标
                worksheet.Cells[row, 5].Formula = $"=({data.Price}-{data.Cost})/{data.Price}"; // 毛利率
                worksheet.Cells[row, 6].Formula = $"=({data.Price}-{data.Cost})*{data.Quantity}"; // 总利润
                worksheet.Cells[row, 7].Formula = $"=({data.Price}-{data.Cost})/{data.Cost}"; // 利润率
            }
            
            // 添加汇总行
            int lastRow = businessData.Length + 2;
            worksheet.Cells[lastRow, 1].Value = "汇总:";
            worksheet.Cells[lastRow, 1].Font.Bold = true;
            
            worksheet.Cells[lastRow, 2].Formula = "=SUM(B2:B5)"; // 总成本
            worksheet.Cells[lastRow, 3].Formula = "=SUM(C2:C5)"; // 总售价
            worksheet.Cells[lastRow, 4].Formula = "=SUM(D2:D5)"; // 总数量
            worksheet.Cells[lastRow, 6].Formula = "=SUM(F2:F5)"; // 总利润
            
            // 添加高级业务指标
            worksheet.Cells[lastRow + 2, 1].Value = "业务指标:";
            worksheet.Cells[lastRow + 2, 1].Font.Bold = true;
            
            worksheet.Cells[lastRow + 3, 1].Value = "平均利润率:";
            worksheet.Cells[lastRow + 3, 2].Formula = "=AVERAGE(G2:G5)";
            
            worksheet.Cells[lastRow + 4, 1].Value = "最高利润率产品:";
            worksheet.Cells[lastRow + 4, 2].Formula = "=INDEX(A2:A5,MATCH(MAX(G2:G5),G2:G5,0))";
            
            worksheet.Cells[lastRow + 5, 1].Value = "利润贡献率:";
            for (int i = 0; i < businessData.Length; i++)
            {
                worksheet.Cells[lastRow + 5 + i, 1].Value = businessData[i].Product + "贡献率:";
                worksheet.Cells[lastRow + 5 + i, 2].Formula = $"=F{i+2}/F{lastRow}";
            }
            
            // 设置格式
            var headerRange = worksheet.Range("A1:G1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.SteelBlue;
            headerRange.Font.Color = System.Drawing.Color.White;
            
            worksheet.Range("B2:G" + lastRow).NumberFormat = "#,##0.00";
            worksheet.Range("E2:G5").NumberFormat = "0.00%"; // 百分比格式
            
            var summaryRange = worksheet.Range($"A{lastRow}:G{lastRow}");
            summaryRange.Font.Bold = true;
            summaryRange.Interior.Color = System.Drawing.Color.LightGreen;
            
            var metricsRange = worksheet.Range($"A{lastRow+2}:B{lastRow+8}");
            metricsRange.Font.Bold = true;
            metricsRange.Interior.Color = System.Drawing.Color.LightYellow;
            
            worksheet.Columns["A:G"].AutoFit();
            
            workbook.SaveAs("CustomFunctions.xlsx");
            Console.WriteLine("  ✓ 自定义函数应用完成");
        }
        
        static void VbaIntegrationDemo()
        {
            Console.WriteLine("\n2. VBA宏集成：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "VBA集成";
            
            // 创建需要VBA处理的数据
            var salesData = new[]
            {
                new { Region = "华北", Month = "1月", Sales = 120000, Target = 100000 },
                new { Region = "华北", Month = "2月", Sales = 135000, Target = 110000 },
                new { Region = "华北", Month = "3月", Sales = 150000, Target = 120000 },
                new { Region = "华东", Month = "1月", Sales = 180000, Target = 150000 },
                new { Region = "华东", Month = "2月", Sales = 165000, Target = 160000 },
                new { Region = "华东", Month = "3月", Sales = 195000, Target = 170000 }
            };
            
            // 写入数据
            worksheet.Cells[1, 1].Value = "区域";
            worksheet.Cells[1, 2].Value = "月份";
            worksheet.Cells[1, 3].Value = "实际销售额";
            worksheet.Cells[1, 4].Value = "目标销售额";
            worksheet.Cells[1, 5].Value = "完成率";
            worksheet.Cells[1, 6].Value = "超额金额";
            
            for (int i = 0; i < salesData.Length; i++)
            {
                var data = salesData[i];
                int row = i + 2;
                
                worksheet.Cells[row, 1].Value = data.Region;
                worksheet.Cells[row, 2].Value = data.Month;
                worksheet.Cells[row, 3].Value = data.Sales;
                worksheet.Cells[row, 4].Value = data.Target;
                worksheet.Cells[row, 5].Formula = $"=C{row}/D{row}"; // 完成率
                worksheet.Cells[row, 6].Formula = $"=C{row}-D{row}"; // 超额金额
            }
            
            // 添加VBA相关功能
            worksheet.Cells[10, 1].Value = "VBA功能演示:";
            worksheet.Cells[10, 1].Font.Bold = true;
            
            // 创建按钮调用VBA宏（需要实际VBA代码支持）
            var button = worksheet.Buttons.Add(100, 100, 120, 30);
            button.Text = "计算汇总";
            // button.OnAction = "CalculateSummary"; // 实际应用中需要设置VBA宏名称
            
            // 添加数据验证（需要VBA支持）
            var validationRange = worksheet.Range("A2:A7");
            // 实际应用中可以使用VBA添加数据验证规则
            
            // 设置条件格式（可以通过VBA增强）
            var completionRange = worksheet.Range("E2:E7");
            
            // 完成率低于80% - 红色
            var lowCondition = completionRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, 
                XlFormatConditionOperator.xlLess, 0.8);
            lowCondition.Interior.Color = System.Drawing.Color.LightCoral;
            
            // 完成率80%-100% - 黄色
            var mediumCondition = completionRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, 
                XlFormatConditionOperator.xlBetween, 0.8, 1.0);
            mediumCondition.Interior.Color = System.Drawing.Color.LightYellow;
            
            // 完成率超过100% - 绿色
            var highCondition = completionRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, 
                XlFormatConditionOperator.xlGreaterEqual, 1.0);
            highCondition.Interior.Color = System.Drawing.Color.LightGreen;
            
            // 设置格式
            var headerRange = worksheet.Range("A1:F1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightBlue;
            
            worksheet.Range("C2:F7").NumberFormat = "#,##0";
            worksheet.Range("E2:E7").NumberFormat = "0.00%";
            
            worksheet.Columns["A:F"].AutoFit();
            
            workbook.SaveAs("VbaIntegration.xlsx");
            Console.WriteLine("  ✓ VBA集成演示完成");
        }
        
        static void EventHandlerDemo()
        {
            Console.WriteLine("\n3. 事件处理函数：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "事件处理";
            
            // 创建交互式数据表
            worksheet.Cells[1, 1].Value = "动态计算器";
            worksheet.Cells[1, 1].Font.Bold = true;
            worksheet.Cells[1, 1].Font.Size = 14;
            
            worksheet.Cells[3, 1].Value = "输入数值1:";
            worksheet.Cells[3, 2].Value = 100;
            
            worksheet.Cells[4, 1].Value = "输入数值2:";
            worksheet.Cells[4, 2].Value = 50;
            
            worksheet.Cells[5, 1].Value = "运算类型:";
            worksheet.Cells[5, 2].Value = "加法";
            
            // 创建下拉列表（实际应用中需要数据验证）
            worksheet.Cells[6, 1].Value = "选择运算:";
            worksheet.Cells[6, 2].Value = "加法";
            worksheet.Cells[7, 2].Value = "减法";
            worksheet.Cells[8, 2].Value = "乘法";
            worksheet.Cells[9, 2].Value = "除法";
            
            // 动态计算结果
            worksheet.Cells[11, 1].Value = "计算结果:";
            worksheet.Cells[11, 1].Font.Bold = true;
            
            // 使用CHOOSE和MATCH函数实现动态计算
            worksheet.Cells[12, 1].Formula = $"=CHOOSE(MATCH(B6,B7:B10,0),B3+B4,B3-B4,B3*B4,B3/B4)";
            
            // 添加解释说明
            worksheet.Cells[14, 1].Value = "说明:";
            worksheet.Cells[14, 1].Font.Bold = true;
            
            worksheet.Cells[15, 1].Value = "修改B3、B4单元格的值或B6单元格的运算类型，结果将自动更新";
            worksheet.Cells[15, 1].Font.Italic = true;
            
            // 创建实时监控区域
            worksheet.Cells[17, 1].Value = "实时监控:";
            worksheet.Cells[17, 1].Font.Bold = true;
            
            worksheet.Cells[18, 1].Value = "当前时间:";
            worksheet.Cells[18, 2].Formula = "=NOW()";
            
            worksheet.Cells[19, 1].Value = "计算次数:";
            worksheet.Cells[19, 2].Value = 1;
            
            worksheet.Cells[20, 1].Value = "最后计算时间:";
            worksheet.Cells[20, 2].Formula = "=NOW()";
            
            // 设置格式
            var inputRange = worksheet.Range("A3:B5");
            inputRange.Interior.Color = System.Drawing.Color.LightBlue;
            
            var selectionRange = worksheet.Range("A6:B9");
            selectionRange.Interior.Color = System.Drawing.Color.LightGreen;
            
            var resultRange = worksheet.Range("A12:B12");
            resultRange.Font.Bold = true;
            resultRange.Font.Size = 16;
            resultRange.Interior.Color = System.Drawing.Color.Gold;
            
            var monitorRange = worksheet.Range("A18:B20");
            monitorRange.Interior.Color = System.Drawing.Color.LightYellow;
            
            worksheet.Range("B18:B18").NumberFormat = "yyyy-mm-dd hh:mm:ss";
            worksheet.Range("B20:B20").NumberFormat = "yyyy-mm-dd hh:mm:ss";
            
            worksheet.Columns["A:B"].AutoFit();
            
            workbook.SaveAs("EventHandler.xlsx");
            Console.WriteLine("  ✓ 事件处理函数演示完成");
        }
    }
}