公式与函数应用

基础公式操作

Excel公式是自动化处理的核心功能之一，MudTools.OfficeInterop.Excel提供了丰富的API来管理和操作Excel公式。

基本公式设置

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

namespace BasicFormulaExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("=== 基础公式操作示例 ===");
            
            try
            {
                // 算术运算公式
                ArithmeticFormulaDemo();
                
                // 统计函数公式
                StatisticalFormulaDemo();
                
                // 逻辑函数公式
                LogicalFormulaDemo();
                
                // 文本函数公式
                TextFormulaDemo();
                
                Console.WriteLine("\n✓ 基础公式示例完成");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"✗ 基础公式示例时出错: {ex.Message}");
            }
        }
        
        static void ArithmeticFormulaDemo()
        {
            Console.WriteLine("\n1. 算术运算公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "算术运算";
            
            // 设置基本数据
            worksheet.Cells[1, 1].Value = "数值A";
            worksheet.Cells[1, 2].Value = "数值B";
            worksheet.Cells[1, 3].Value = "运算结果";
            worksheet.Cells[1, 4].Value = "公式";
            
            // 测试数据
            var testData = new[]
            {
                new { A = 10, B = 5 },
                new { A = 25, B = 8 },
                new { A = 100, B = 3 },
                new { A = 7, B = 2 },
                new { A = 15, B = 4 }
            };
            
            int row = 2;
            foreach (var data in testData)
            {
                worksheet.Cells[row, 1].Value = data.A;
                worksheet.Cells[row, 2].Value = data.B;
                
                // 加法
                worksheet.Cells[row, 3].Formula = $"=A{row}+B{row}";
                worksheet.Cells[row, 4].Value = "加法";
                row++;
                
                // 减法
                worksheet.Cells[row, 3].Formula = $"=A{row-1}-B{row-1}";
                worksheet.Cells[row, 4].Value = "减法";
                row++;
                
                // 乘法
                worksheet.Cells[row, 3].Formula = $"=A{row-2}*B{row-2}";
                worksheet.Cells[row, 4].Value = "乘法";
                row++;
                
                // 除法
                worksheet.Cells[row, 3].Formula = $"=A{row-3}/B{row-3}";
                worksheet.Cells[row, 4].Value = "除法";
                row++;
                
                // 幂运算
                worksheet.Cells[row, 3].Formula = $"=A{row-4}^B{row-4}";
                worksheet.Cells[row, 4].Value = "幂运算";
                row++;
                
                // 求余
                worksheet.Cells[row, 3].Formula = $"=MOD(A{row-5},B{row-5})";
                worksheet.Cells[row, 4].Value = "求余";
                row++;
            }
            
            // 设置格式
            var headerRange = worksheet.Range("A1:D1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightBlue;
            
            worksheet.Range("A2:A" + (row-1)).NumberFormat = "#,##0";
            worksheet.Range("B2:B" + (row-1)).NumberFormat = "#,##0";
            worksheet.Range("C2:C" + (row-1)).NumberFormat = "#,##0.00";
            
            worksheet.Columns["A:D"].AutoFit();
            
            workbook.SaveAs("ArithmeticFormulas.xlsx");
            Console.WriteLine("  ✓ 算术运算公式创建完成");
        }
        
        static void StatisticalFormulaDemo()
        {
            Console.WriteLine("\n2. 统计函数公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "统计函数";
            
            // 生成随机测试数据
            var random = new Random();
            var data = new double[50];
            
            for (int i = 0; i < 50; i++)
            {
                data[i] = random.Next(100, 1000);
                worksheet.Cells[i + 1, 1].Value = data[i];
            }
            
            // 添加统计函数
            worksheet.Cells[52, 1].Value = "统计结果:";
            worksheet.Cells[52, 1].Font.Bold = true;
            
            worksheet.Cells[53, 1].Value = "平均值:";
            worksheet.Cells[53, 2].Formula = "=AVERAGE(A1:A50)";
            
            worksheet.Cells[54, 1].Value = "总和:";
            worksheet.Cells[54, 2].Formula = "=SUM(A1:A50)";
            
            worksheet.Cells[55, 1].Value = "最大值:";
            worksheet.Cells[55, 2].Formula = "=MAX(A1:A50)";
            
            worksheet.Cells[56, 1].Value = "最小值:";
            worksheet.Cells[56, 2].Formula = "=MIN(A1:A50)";
            
            worksheet.Cells[57, 1].Value = "中位数:";
            worksheet.Cells[57, 2].Formula = "=MEDIAN(A1:A50)";
            
            worksheet.Cells[58, 1].Value = "标准差:";
            worksheet.Cells[58, 2].Formula = "=STDEV.S(A1:A50)";
            
            worksheet.Cells[59, 1].Value = "方差:";
            worksheet.Cells[59, 2].Formula = "=VAR.S(A1:A50)";
            
            worksheet.Cells[60, 1].Value = "计数:";
            worksheet.Cells[60, 2].Formula = "=COUNT(A1:A50)";
            
            worksheet.Cells[61, 1].Value = "非空计数:";
            worksheet.Cells[61, 2].Formula = "=COUNTA(A1:A50)";
            
            // 设置格式
            worksheet.Range("A1:A50").NumberFormat = "#,##0";
            worksheet.Range("B53:B61").NumberFormat = "#,##0.00";
            
            var statHeader = worksheet.Range("A52:A52");
            statHeader.Font.Bold = true;
            statHeader.Font.Size = 12;
            
            var statLabels = worksheet.Range("A53:A61");
            statLabels.Font.Bold = true;
            
            var statValues = worksheet.Range("B53:B61");
            statValues.Interior.Color = System.Drawing.Color.LightGreen;
            
            worksheet.Columns["A:B"].AutoFit();
            
            workbook.SaveAs("StatisticalFormulas.xlsx");
            Console.WriteLine("  ✓ 统计函数公式创建完成");
        }
        
        static void LogicalFormulaDemo()
        {
            Console.WriteLine("\n3. 逻辑函数公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "逻辑函数";
            
            // 设置测试数据
            worksheet.Cells[1, 1].Value = "条件A";
            worksheet.Cells[1, 2].Value = "条件B";
            worksheet.Cells[1, 3].Value = "逻辑结果";
            worksheet.Cells[1, 4].Value = "说明";
            
            var testCases = new[]
            {
                new { A = true, B = true, Description = "TRUE AND TRUE" },
                new { A = true, B = false, Description = "TRUE AND FALSE" },
                new { A = false, B = true, Description = "FALSE AND TRUE" },
                new { A = false, B = false, Description = "FALSE AND FALSE" }
            };
            
            int row = 2;
            foreach (var testCase in testCases)
            {
                worksheet.Cells[row, 1].Value = testCase.A;
                worksheet.Cells[row, 2].Value = testCase.B;
                
                // AND 函数
                worksheet.Cells[row, 3].Formula = $"=AND(A{row},B{row})";
                worksheet.Cells[row, 4].Value = $"AND: {testCase.Description}";
                row++;
                
                // OR 函数
                worksheet.Cells[row, 3].Formula = $"=OR(A{row-1},B{row-1})";
                worksheet.Cells[row, 4].Value = $"OR: {testCase.Description}";
                row++;
                
                // NOT 函数
                worksheet.Cells[row, 3].Formula = $"=NOT(A{row-2})";
                worksheet.Cells[row, 4].Value = $"NOT: {testCase.Description}";
                row++;
                
                // XOR 函数
                worksheet.Cells[row, 3].Formula = $"=XOR(A{row-3},B{row-3})";
                worksheet.Cells[row, 4].Value = $"XOR: {testCase.Description}";
                row++;
                
                // IF 函数
                worksheet.Cells[row, 3].Formula = $"=IF(A{row-4},\"条件A为真\",\"条件A为假\")";
                worksheet.Cells[row, 4].Value = $"IF: {testCase.Description}";
                row++;
            }
            
            // 复杂IF函数示例
            worksheet.Cells[row, 1].Value = "成绩";
            worksheet.Cells[row, 2].Value = "等级";
            worksheet.Cells[row, 3].Value = "公式";
            row++;
            
            var scores = new[] { 95, 85, 75, 65, 55, 45 };
            foreach (var score in scores)
            {
                worksheet.Cells[row, 1].Value = score;
                worksheet.Cells[row, 2].Formula = $"=IF(A{row}>=90,\"优秀\",IF(A{row}>=80,\"良好\",IF(A{row}>=70,\"中等\",IF(A{row}>=60,\"及格\",\"不及格\")))";
                worksheet.Cells[row, 3].Value = "嵌套IF函数";
                row++;
            }
            
            // 设置格式
            var headerRange = worksheet.Range("A1:D1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightYellow;
            
            var scoreHeader = worksheet.Range($"A{row-6}:C{row-6}");
            scoreHeader.Font.Bold = true;
            scoreHeader.Interior.Color = System.Drawing.Color.LightCyan;
            
            worksheet.Columns["A:D"].AutoFit();
            
            workbook.SaveAs("LogicalFormulas.xlsx");
            Console.WriteLine("  ✓ 逻辑函数公式创建完成");
        }
        
        static void TextFormulaDemo()
        {
            Console.WriteLine("\n4. 文本函数公式：");
            
            using var excelApp = ExcelFactory.BlankWorkbook();
            var workbook = excelApp.ActiveWorkbook;
            var worksheet = workbook.ActiveSheetWrap;
            
            worksheet.Name = "文本函数";
            
            // 设置测试文本
            var testTexts = new[]
            {
                "Hello World",
                "excel automation",
                "123 Main Street",
                "中国北京",
                "2023-12-25",
                "Excel2021"
            };
            
            worksheet.Cells[1, 1].Value = "原始文本";
            worksheet.Cells[1, 2].Value = "函数类型";
            worksheet.Cells[1, 3].Value = "结果";
            worksheet.Cells[1, 4].Value = "公式";
            
            int row = 2;
            foreach (var text in testTexts)
            {
                worksheet.Cells[row, 1].Value = text;
                
                // LEN 函数
                worksheet.Cells[row, 3].Formula = $"=LEN(A{row})";
                worksheet.Cells[row, 2].Value = "长度";
                worksheet.Cells[row, 4].Value = $"=LEN(A{row})";
                row++;
                
                // UPPER 函数
                worksheet.Cells[row, 3].Formula = $"=UPPER(A{row-1})";
                worksheet.Cells[row, 2].Value = "大写";
                worksheet.Cells[row, 4].Value = $"=UPPER(A{row-1})";
                row++;
                
                // LOWER 函数
                worksheet.Cells[row, 3].Formula = $"=LOWER(A{row-2})";
                worksheet.Cells[row, 2].Value = "小写";
                worksheet.Cells[row, 4].Value = $"=LOWER(A{row-2})";
                row++;
                
                // PROPER 函数
                worksheet.Cells[row, 3].Formula = $"=PROPER(A{row-3})";
                worksheet.Cells[row, 2].Value = "首字母大写";
                worksheet.Cells[row, 4].Value = $"=PROPER(A{row-3})";
                row++;
                
                // LEFT 函数
                worksheet.Cells[row, 3].Formula = $"=LEFT(A{row-4},5)";
                worksheet.Cells[row, 2].Value = "左截取";
                worksheet.Cells[row, 4].Value = $"=LEFT(A{row-4},5)";
                row++;
                
                // RIGHT 函数
                worksheet.Cells[row, 3].Formula = $"=RIGHT(A{row-5},5)";
                worksheet.Cells[row, 2].Value = "右截取";
                worksheet.Cells[row, 4].Value = $"=RIGHT(A{row-5},5)";
                row++;
                
                // MID 函数
                worksheet.Cells[row, 3].Formula = $"=MID(A{row-6},3,4)";
                worksheet.Cells[row, 2].Value = "中间截取";
                worksheet.Cells[row, 4].Value = $"=MID(A{row-6},3,4)";
                row++;
                
                // FIND 函数
                worksheet.Cells[row, 3].Formula = $"=FIND(\"e\",A{row-7})";
                worksheet.Cells[row, 2].Value = "查找位置";
                worksheet.Cells[row, 4].Value = $"=FIND(\"e\",A{row-7})";
                row++;
                
                // SUBSTITUTE 函数
                worksheet.Cells[row, 3].Formula = $"=SUBSTITUTE(A{row-8},\"e\",\"E\")";
                worksheet.Cells[row, 2].Value = "替换";
                worksheet.Cells[row, 4].Value = $"=SUBSTITUTE(A{row-8},\"e\",\"E\")";
                row++;
                
                // TRIM 函数
                worksheet.Cells[row, 3].Formula = $"=TRIM(A{row-9})";
                worksheet.Cells[row, 2].Value = "去空格";
                worksheet.Cells[row, 4].Value = $"=TRIM(A{row-9})";
                row++;
                
                // 添加分隔行
                worksheet.Cells[row, 1].Value = "---";
                worksheet.Cells[row, 2].Value = "---";
                worksheet.Cells[row, 3].Value = "---";
                worksheet.Cells[row, 4].Value = "---";
                row++;
            }
            
            // 设置格式
            var headerRange = worksheet.Range("A1:D1");
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightPink;
            
            worksheet.Columns["A:D"].AutoFit();
            
            workbook.SaveAs("TextFormulas.xlsx");
            Console.WriteLine("  ✓ 文本函数公式创建完成");
        }
    }
}

相关章节

• 高级公式与 VBA 集成
• 公式调试、性能优化与实战