公式与函数应用
基础公式操作
Excel公式是自动化处理的核心功能之一，MudTools.OfficeInterop.Excel提供了丰富的API来管理和操作Excel公式。
基本公式设置
csharp
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(" ✓ 文本函数公式创建完成");
}
}
}高级公式应用
数组公式应用
csharp
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集成
自定义函数应用
csharp
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(" ✓ 事件处理函数演示完成");
}
}
}公式错误处理与调试
公式错误处理
csharp
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace FormulaErrorHandlingExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 公式错误处理与调试示例 ===");
try
{
// 公式错误检测
FormulaErrorDetection();
// 公式调试技巧
FormulaDebuggingDemo();
// 错误处理策略
ErrorHandlingStrategies();
Console.WriteLine("\n✓ 公式错误处理示例完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 公式错误处理示例时出错: {ex.Message}");
}
}
static void FormulaErrorDetection()
{
Console.WriteLine("\n1. 公式错误检测：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "错误检测";
// 创建包含各种错误的公式
worksheet.Cells[1, 1].Value = "错误类型";
worksheet.Cells[1, 2].Value = "公式示例";
worksheet.Cells[1, 3].Value = "错误值";
worksheet.Cells[1, 4].Value = "错误说明";
// #DIV/0! 错误
worksheet.Cells[2, 1].Value = "除零错误";
worksheet.Cells[2, 2].Formula = "=1/0";
worksheet.Cells[2, 3].Formula = "=ISERROR(B2)";
worksheet.Cells[2, 4].Value = "除数为零";
// #N/A 错误
worksheet.Cells[3, 1].Value = "值不可用";
worksheet.Cells[3, 2].Formula = "=NA()";
worksheet.Cells[3, 3].Formula = "=ISNA(B3)";
worksheet.Cells[3, 4].Value = "数值不可用";
// #VALUE! 错误
worksheet.Cells[4, 1].Value = "值错误";
worksheet.Cells[4, 2].Formula = "=\"文本\"+1";
worksheet.Cells[4, 3].Formula = "=ISERROR(B4)";
worksheet.Cells[4, 4].Value = "数据类型不匹配";
// #REF! 错误
worksheet.Cells[5, 1].Value = "引用错误";
worksheet.Cells[5, 2].Formula = "=A100"; // 引用不存在的单元格
worksheet.Cells[5, 3].Formula = "=ISERROR(B5)";
worksheet.Cells[5, 4].Value = "无效的单元格引用";
// #NAME? 错误
worksheet.Cells[6, 1].Value = "名称错误";
worksheet.Cells[6, 2].Formula = "=UNKNOWNFUNCTION()";
worksheet.Cells[6, 3].Formula = "=ISERROR(B6)";
worksheet.Cells[6, 4].Value = "未知的函数名称";
// #NUM! 错误
worksheet.Cells[7, 1].Value = "数字错误";
worksheet.Cells[7, 2].Formula = "=SQRT(-1)";
worksheet.Cells[7, 3].Formula = "=ISERROR(B7)";
worksheet.Cells[7, 4].Value = "无效的数学运算";
// #NULL! 错误
worksheet.Cells[8, 1].Value = "空值错误";
worksheet.Cells[8, 2].Formula = "=A1 A2"; // 缺少运算符
worksheet.Cells[8, 3].Formula = "=ISERROR(B8)";
worksheet.Cells[8, 4].Value = "区域交集为空";
// 错误处理函数演示
worksheet.Cells[10, 1].Value = "错误处理函数:";
worksheet.Cells[10, 1].Font.Bold = true;
worksheet.Cells[11, 1].Value = "IFERROR示例:";
worksheet.Cells[11, 2].Formula = "=IFERROR(1/0,\"计算错误\")";
worksheet.Cells[12, 1].Value = "IFNA示例:";
worksheet.Cells[12, 2].Formula = "=IFNA(NA(),\"值不可用\")";
worksheet.Cells[13, 1].Value = "AGGREGATE示例:";
worksheet.Cells[13, 2].Formula = "=AGGREGATE(9,6,B2:B8)"; // 忽略错误值的求和
// 设置格式
var headerRange = worksheet.Range("A1:D1");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightGray;
var errorRange = worksheet.Range("A2:D8");
errorRange.Interior.Color = System.Drawing.Color.LightCoral;
var handlingRange = worksheet.Range("A10:B13");
handlingRange.Interior.Color = System.Drawing.Color.LightGreen;
worksheet.Columns["A:D"].AutoFit();
workbook.SaveAs("FormulaErrorDetection.xlsx");
Console.WriteLine(" ✓ 公式错误检测完成");
}
static void FormulaDebuggingDemo()
{
Console.WriteLine("\n2. 公式调试技巧：");
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;
// 创建测试数据
var salesData = new[]
{
new { Product = "A产品", Q1 = 100, Q2 = 120, Q3 = 90, Q4 = 110 },
new { Product = "B产品", Q1 = 80, Q2 = 95, Q3 = 105, Q4 = 120 },
new { Product = "C产品", Q1 = 150, Q2 = 140, Q3 = 160, Q4 = 170 }
};
// 写入数据
worksheet.Cells[3, 1].Value = "产品";
worksheet.Cells[3, 2].Value = "Q1";
worksheet.Cells[3, 3].Value = "Q2";
worksheet.Cells[3, 4].Value = "Q3";
worksheet.Cells[3, 5].Value = "Q4";
worksheet.Cells[3, 6].Value = "年度总和";
worksheet.Cells[3, 7].Value = "季度平均";
worksheet.Cells[3, 8].Value = "增长率";
for (int i = 0; i < salesData.Length; i++)
{
var data = salesData[i];
int row = i + 4;
worksheet.Cells[row, 1].Value = data.Product;
worksheet.Cells[row, 2].Value = data.Q1;
worksheet.Cells[row, 3].Value = data.Q2;
worksheet.Cells[row, 4].Value = data.Q3;
worksheet.Cells[row, 5].Value = data.Q4;
// 复杂公式：年度总和
worksheet.Cells[row, 6].Formula = $"=SUM(B{row}:E{row})";
// 复杂公式：季度平均
worksheet.Cells[row, 7].Formula = $"=AVERAGE(B{row}:E{row})";
// 复杂公式：增长率（(Q4-Q1)/Q1）
worksheet.Cells[row, 8].Formula = $"=(E{row}-B{row})/B{row}";
}
// 添加公式分解调试区域
worksheet.Cells[8, 10].Value = "公式分解调试:";
worksheet.Cells[8, 10].Font.Bold = true;
// 分解复杂公式进行调试
worksheet.Cells[9, 10].Value = "原始公式:";
worksheet.Cells[9, 11].Value = "=(E4-B4)/B4";
worksheet.Cells[10, 10].Value = "E4值:";
worksheet.Cells[10, 11].Formula = "=E4";
worksheet.Cells[11, 10].Value = "B4值:";
worksheet.Cells[11, 11].Formula = "=B4";
worksheet.Cells[12, 10].Value = "E4-B4:";
worksheet.Cells[12, 11].Formula = "=E4-B4";
worksheet.Cells[13, 10].Value = "最终结果:";
worksheet.Cells[13, 11].Formula = "=H4";
// 使用公式求值功能（模拟）
worksheet.Cells[15, 10].Value = "公式求值步骤:";
worksheet.Cells[15, 10].Font.Bold = true;
worksheet.Cells[16, 10].Value = "步骤1: E4 = 110";
worksheet.Cells[17, 10].Value = "步骤2: B4 = 100";
worksheet.Cells[18, 10].Value = "步骤3: 110-100 = 10";
worksheet.Cells[19, 10].Value = "步骤4: 10/100 = 0.1";
worksheet.Cells[20, 10].Value = "最终结果: 10%";
// 设置格式
var headerRange = worksheet.Range("A3:H3");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightBlue;
var dataRange = worksheet.Range("A4:H6");
dataRange.Interior.Color = System.Drawing.Color.WhiteSmoke;
var debugRange = worksheet.Range("J8:L20");
debugRange.Interior.Color = System.Drawing.Color.LightYellow;
worksheet.Range("H4:H6").NumberFormat = "0.00%";
worksheet.Range("G4:G6").NumberFormat = "#,##0.00";
worksheet.Columns["A:L"].AutoFit();
workbook.SaveAs("FormulaDebugging.xlsx");
Console.WriteLine(" ✓ 公式调试技巧演示完成");
}
static void ErrorHandlingStrategies()
{
Console.WriteLine("\n3. 错误处理策略：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "错误处理策略";
// 创建包含潜在错误的数据
var problematicData = new[]
{
new { Description = "正常数值", Value = "100" },
new { Description = "文本数值", Value = "一百" },
new { Description = "空值", Value = "" },
new { Description = "零值", Value = "0" },
new { Description = "负数", Value = "-50" },
new { Description = "小数", Value = "12.5" }
};
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 < problematicData.Length; i++)
{
var data = problematicData[i];
int row = i + 2;
worksheet.Cells[row, 1].Value = data.Description;
worksheet.Cells[row, 2].Value = data.Value;
// 策略1：安全数值转换
worksheet.Cells[row, 3].Formula = $"=IFERROR(VALUE(B{row}),0)";
// 策略2：错误处理
worksheet.Cells[row, 4].Formula = $"=IF(ISERROR(VALUE(B{row})),\"转换失败\",VALUE(B{row}))";
// 策略3：数据验证
worksheet.Cells[row, 5].Formula = $"=IF(AND(ISNUMBER(C{row}),C{row}>=0),\"有效\",\"无效\")";
}
// 高级错误处理策略
worksheet.Cells[9, 1].Value = "高级错误处理策略:";
worksheet.Cells[9, 1].Font.Bold = true;
worksheet.Cells[10, 1].Value = "策略1：嵌套IFERROR";
worksheet.Cells[10, 2].Formula = "=IFERROR(1/0,IFERROR(NA(),\"所有错误处理\"))"";
worksheet.Cells[11, 1].Value = "策略2：条件错误处理";
worksheet.Cells[11, 2].Formula = "=IF(ISNA(B3),\"N/A错误\",IF(ISERROR(B2),\"其他错误\",\"正常\"))"";
worksheet.Cells[12, 1].Value = "策略3：AGGREGATE函数";
worksheet.Cells[12, 2].Formula = "=AGGREGATE(1,6,B2:B7)"; // 平均值，忽略错误
worksheet.Cells[13, 1].Value = "策略4：自定义错误消息";
worksheet.Cells[13, 2].Formula = "=IF(B2=0,\"除零错误\",1/B2)";
// 设置格式
var headerRange = worksheet.Range("A1:E1");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightBlue;
var dataRange = worksheet.Range("A2:E7");
dataRange.Interior.Color = System.Drawing.Color.WhiteSmoke;
var strategyRange = worksheet.Range("A9:B13");
strategyRange.Interior.Color = System.Drawing.Color.LightGreen;
worksheet.Columns["A:E"].AutoFit();
workbook.SaveAs("ErrorHandlingStrategies.xlsx");
Console.WriteLine(" ✓ 错误处理策略演示完成");
}
}
}公式性能优化
公式计算优化策略
csharp
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace FormulaPerformanceExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 公式性能优化示例 ===");
try
{
// 公式计算模式优化
CalculationModeOptimization();
// 公式引用优化
FormulaReferenceOptimization();
// 数组公式优化
ArrayFormulaOptimization();
Console.WriteLine("
✓ 公式性能优化示例完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 公式性能优化示例时出错: {ex.Message}");
}
}
static void CalculationModeOptimization()
{
Console.WriteLine("
1. 公式计算模式优化：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 获取当前计算模式
var currentMode = excelApp.Calculation;
Console.WriteLine($"当前计算模式: {currentMode}");
// 设置手动计算模式以提高性能
excelApp.Calculation = XlCalculation.xlCalculationManual;
Console.WriteLine("已切换到手动计算模式");
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "性能优化";
// 创建大量公式数据
Console.WriteLine("正在创建大量公式数据...");
var random = new Random();
int rowCount = 1000;
int colCount = 10;
// 写入基础数据
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{
worksheet.Cells[row, col].Value = random.Next(1, 1000);
}
}
// 批量添加公式（在手动计算模式下更快）
for (int row = 1; row <= rowCount; row++)
{
// 汇总列
worksheet.Cells[row, colCount + 1].Formula = $"=SUM(A{row}:J{row})";
// 平均值列
worksheet.Cells[row, colCount + 2].Formula = $"=AVERAGE(A{row}:J{row})";
// 最大值列
worksheet.Cells[row, colCount + 3].Formula = $"=MAX(A{row}:J{row})";
// 最小值列
worksheet.Cells[row, colCount + 4].Formula = $"=MIN(A{row}:J{row})";
// 标准差列
worksheet.Cells[row, colCount + 5].Formula = $"=STDEV.S(A{row}:J{row})";
}
Console.WriteLine("公式创建完成，开始手动计算...");
// 手动触发计算
var startTime = DateTime.Now;
excelApp.Calculate();
var endTime = DateTime.Now;
Console.WriteLine($"计算完成，耗时: {(endTime - startTime).TotalMilliseconds} 毫秒");
// 恢复自动计算模式
excelApp.Calculation = XlCalculation.xlCalculationAutomatic;
Console.WriteLine("已恢复自动计算模式");
workbook.SaveAs("PerformanceOptimization.xlsx");
Console.WriteLine(" ✓ 计算模式优化完成");
}
static void FormulaReferenceOptimization()
{
Console.WriteLine("
2. 公式引用优化：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "引用优化";
// 创建大型数据集
var dataSize = 500;
var data = new double[dataSize];
var random = new Random();
for (int i = 0; i < dataSize; i++)
{
data[i] = random.NextDouble() * 1000;
worksheet.Cells[i + 1, 1].Value = data[i];
}
// 优化前：重复计算相同范围
worksheet.Cells[dataSize + 2, 1].Value = "优化前 - 重复引用:";
worksheet.Cells[dataSize + 2, 1].Font.Bold = true;
worksheet.Cells[dataSize + 3, 1].Value = "总和:";
worksheet.Cells[dataSize + 3, 2].Formula = "=SUM(A1:A500)";
worksheet.Cells[dataSize + 4, 1].Value = "平均值:";
worksheet.Cells[dataSize + 4, 2].Formula = "=AVERAGE(A1:A500)";
worksheet.Cells[dataSize + 5, 1].Value = "最大值:";
worksheet.Cells[dataSize + 5, 2].Formula = "=MAX(A1:A500)";
worksheet.Cells[dataSize + 6, 1].Value = "最小值:";
worksheet.Cells[dataSize + 6, 2].Formula = "=MIN(A1:A500)";
// 优化后：使用命名范围
var dataRange = worksheet.Range("A1:A500");
dataRange.Name = "DataRange";
worksheet.Cells[dataSize + 8, 1].Value = "优化后 - 命名引用:";
worksheet.Cells[dataSize + 8, 1].Font.Bold = true;
worksheet.Cells[dataSize + 9, 1].Value = "总和:";
worksheet.Cells[dataSize + 9, 2].Formula = "=SUM(DataRange)";
worksheet.Cells[dataSize + 10, 1].Value = "平均值:";
worksheet.Cells[dataSize + 10, 2].Formula = "=AVERAGE(DataRange)";
worksheet.Cells[dataSize + 11, 1].Value = "最大值:";
worksheet.Cells[dataSize + 11, 2].Formula = "=MAX(DataRange)";
worksheet.Cells[dataSize + 12, 1].Value = "最小值:";
worksheet.Cells[dataSize + 12, 2].Formula = "=MIN(DataRange)";
// 进一步优化：使用表结构
worksheet.Cells[dataSize + 14, 1].Value = "表结构优化:";
worksheet.Cells[dataSize + 14, 1].Font.Bold = true;
// 将数据转换为表（实际应用中需要VBA支持）
var tableRange = worksheet.Range("A1:A500");
// 表结构公式示例
worksheet.Cells[dataSize + 15, 1].Value = "表总和:";
worksheet.Cells[dataSize + 15, 2].Formula = "=SUM(Table1[Column1])"; // 假设表名为Table1
// 设置格式
var optimizationHeader = worksheet.Range($"A{dataSize+2}:A{dataSize+2}");
optimizationHeader.Font.Bold = true;
optimizationHeader.Font.Size = 12;
var optimizedHeader = worksheet.Range($"A{dataSize+8}:A{dataSize+8}");
optimizedHeader.Font.Bold = true;
optimizedHeader.Font.Size = 12;
var tableHeader = worksheet.Range($"A{dataSize+14}:A{dataSize+14}");
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 12;
worksheet.Columns["A:B"].AutoFit();
workbook.SaveAs("ReferenceOptimization.xlsx");
Console.WriteLine(" ✓ 引用优化完成");
}
static void ArrayFormulaOptimization()
{
Console.WriteLine("
3. 数组公式优化：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "数组优化";
// 创建大型矩阵数据
int matrixSize = 50;
var random = new Random();
// 矩阵A
for (int row = 1; row <= matrixSize; row++)
{
for (int col = 1; col <= matrixSize; col++)
{
worksheet.Cells[row, col].Value = random.NextDouble() * 100;
}
}
// 矩阵B
for (int row = 1; row <= matrixSize; row++)
{
for (int col = 1; col <= matrixSize; col++)
{
worksheet.Cells[row, col + matrixSize + 2].Value = random.NextDouble() * 100;
}
}
// 传统方法：逐个单元格计算
worksheet.Cells[matrixSize + 2, 1].Value = "传统方法 - 逐个计算:";
worksheet.Cells[matrixSize + 2, 1].Font.Bold = true;
var startTime = DateTime.Now;
for (int row = 1; row <= matrixSize; row++)
{
for (int col = 1; col <= matrixSize; col++)
{
// 矩阵加法（逐个单元格）
worksheet.Cells[row, col + (matrixSize + 2) * 2].Formula = $"=A{row}+{Convert.ToChar(64 + matrixSize + 2 + col)}{row}";
}
}
var endTime = DateTime.Now;
Console.WriteLine($"传统方法耗时: {(endTime - startTime).TotalMilliseconds} 毫秒");
// 数组公式方法：批量计算
worksheet.Cells[matrixSize + 4, 1].Value = "数组公式方法 - 批量计算:";
worksheet.Cells[matrixSize + 4, 1].Font.Bold = true;
startTime = DateTime.Now;
// 使用数组公式进行矩阵加法
var resultRange = worksheet.Range(
worksheet.Cells[1, (matrixSize + 2) * 3],
worksheet.Cells[matrixSize, (matrixSize + 2) * 3 + matrixSize - 1]
);
// 数组公式赋值（批量操作）
resultRange.FormulaArray = $"=A1:{Convert.ToChar(64 + matrixSize)}{matrixSize}+{Convert.ToChar(64 + matrixSize + 2)}{1}:{Convert.ToChar(64 + (matrixSize + 2) + matrixSize - 1)}{matrixSize}";
endTime = DateTime.Now;
Console.WriteLine($"数组公式方法耗时: {(endTime - startTime).TotalMilliseconds} 毫秒");
// 设置格式
var traditionalHeader = worksheet.Range($"A{matrixSize+2}:A{matrixSize+2}");
traditionalHeader.Font.Bold = true;
traditionalHeader.Interior.Color = System.Drawing.Color.LightCoral;
var arrayHeader = worksheet.Range($"A{matrixSize+4}:A{matrixSize+4}");
arrayHeader.Font.Bold = true;
arrayHeader.Interior.Color = System.Drawing.Color.LightGreen;
worksheet.Columns.AutoFit();
workbook.SaveAs("ArrayFormulaOptimization.xlsx");
Console.WriteLine(" ✓ 数组公式优化完成");
}
}
}实际应用案例
财务报表分析系统
csharp
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace FinancialReportExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 财务报表分析系统示例 ===");
try
{
// 创建财务报表
CreateFinancialReport();
// 财务比率分析
FinancialRatioAnalysis();
// 趋势分析
TrendAnalysis();
Console.WriteLine("
✓ 财务报表分析系统完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 财务报表分析时出错: {ex.Message}");
}
}
static void CreateFinancialReport()
{
Console.WriteLine("
1. 创建财务报表：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Name = "利润表";
// 创建利润表结构
worksheet.Cells[1, 1].Value = "XX公司利润表";
worksheet.Cells[1, 1].Font.Bold = true;
worksheet.Cells[1, 1].Font.Size = 16;
worksheet.Cells[3, 1].Value = "项目";
worksheet.Cells[3, 2].Value = "2023年";
worksheet.Cells[3, 3].Value = "2022年";
worksheet.Cells[3, 4].Value = "增减额";
worksheet.Cells[3, 5].Value = "增减率";
// 营业收入部分
worksheet.Cells[5, 1].Value = "一、营业收入";
worksheet.Cells[5, 1].Font.Bold = true;
worksheet.Cells[6, 1].Value = " 其中：主营业务收入";
worksheet.Cells[7, 1].Value = " 其他业务收入";
// 营业成本部分
worksheet.Cells[9, 1].Value = "二、营业成本";
worksheet.Cells[9, 1].Font.Bold = true;
worksheet.Cells[10, 1].Value = " 其中：主营业务成本";
worksheet.Cells[11, 1].Value = " 其他业务成本";
// 税金及附加
worksheet.Cells[13, 1].Value = "三、税金及附加";
// 期间费用
worksheet.Cells[15, 1].Value = "四、期间费用";
worksheet.Cells[15, 1].Font.Bold = true;
worksheet.Cells[16, 1].Value = " 销售费用";
worksheet.Cells[17, 1].Value = " 管理费用";
worksheet.Cells[18, 1].Value = " 财务费用";
// 营业利润
worksheet.Cells[20, 1].Value = "五、营业利润";
worksheet.Cells[20, 1].Font.Bold = true;
// 利润总额
worksheet.Cells[22, 1].Value = "六、利润总额";
worksheet.Cells[22, 1].Font.Bold = true;
// 净利润
worksheet.Cells[24, 1].Value = "七、净利润";
worksheet.Cells[24, 1].Font.Bold = true;
// 输入示例数据
var financialData = new[]
{
// 营业收入
new { Item = "营业收入", Year2023 = 10000000, Year2022 = 8500000 },
new { Item = "主营业务收入", Year2023 = 9500000, Year2022 = 8000000 },
new { Item = "其他业务收入", Year2023 = 500000, Year2022 = 500000 },
// 营业成本
new { Item = "营业成本", Year2023 = 6000000, Year2022 = 5200000 },
new { Item = "主营业务成本", Year2023 = 5700000, Year2022 = 4900000 },
new { Item = "其他业务成本", Year2023 = 300000, Year2022 = 300000 },
// 税金及附加
new { Item = "税金及附加", Year2023 = 300000, Year2022 = 250000 },
// 期间费用
new { Item = "销售费用", Year2023 = 800000, Year2022 = 700000 },
new { Item = "管理费用", Year2023 = 600000, Year2022 = 550000 },
new { Item = "财务费用", Year2023 = 200000, Year2022 = 180000 }
};
// 填入数据并计算
int currentRow = 5;
foreach (var item in financialData)
{
worksheet.Cells[currentRow, 2].Value = item.Year2023;
worksheet.Cells[currentRow, 3].Value = item.Year2022;
// 计算增减额
worksheet.Cells[currentRow, 4].Formula = $"=B{currentRow}-C{currentRow}";
// 计算增减率
worksheet.Cells[currentRow, 5].Formula = $"=D{currentRow}/C{currentRow}";
currentRow++;
}
// 计算营业利润
worksheet.Cells[20, 2].Formula = "=B5-B9-B13-SUM(B16:B18)";
worksheet.Cells[20, 3].Formula = "=C5-C9-C13-SUM(C16:C18)";
worksheet.Cells[20, 4].Formula = "=B20-C20";
worksheet.Cells[20, 5].Formula = "=D20/C20";
// 计算利润总额（假设营业外收支为0）
worksheet.Cells[22, 2].Formula = "=B20";
worksheet.Cells[22, 3].Formula = "=C20";
worksheet.Cells[22, 4].Formula = "=B22-C22";
worksheet.Cells[22, 5].Formula = "=D22/C22";
// 计算净利润（假设所得税率为25%）
worksheet.Cells[24, 2].Formula = "=B22*0.75";
worksheet.Cells[24, 3].Formula = "=C22*0.75";
worksheet.Cells[24, 4].Formula = "=B24-C24";
worksheet.Cells[24, 5].Formula = "=D24/C24";
// 设置格式
var titleRange = worksheet.Range("A1:E1");
titleRange.Merge();
titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
var headerRange = worksheet.Range("A3:E3");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightBlue;
var mainItemRange = worksheet.Range("A5:A5,A9:A9,A15:A15,A20:A20,A22:A22,A24:A24");
mainItemRange.Font.Bold = true;
mainItemRange.Interior.Color = System.Drawing.Color.LightYellow;
worksheet.Range("B4:E24").NumberFormat = "#,##0";
worksheet.Range("E5:E24").NumberFormat = "0.00%";
worksheet.Columns["A:E"].AutoFit();
workbook.SaveAs("FinancialReport.xlsx");
Console.WriteLine(" ✓ 财务报表创建完成");
}
static void FinancialRatioAnalysis()
{
Console.WriteLine("
2. 财务比率分析：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 创建财务比率分析工作表
var ratioWorksheet = workbook.Worksheets.Add();
ratioWorksheet.Name = "财务比率分析";
// 比率分析结构
ratioWorksheet.Cells[1, 1].Value = "财务比率分析";
ratioWorksheet.Cells[1, 1].Font.Bold = true;
ratioWorksheet.Cells[1, 1].Font.Size = 16;
ratioWorksheet.Cells[3, 1].Value = "比率类别";
ratioWorksheet.Cells[3, 2].Value = "比率名称";
ratioWorksheet.Cells[3, 3].Value = "计算公式";
ratioWorksheet.Cells[3, 4].Value = "2023年";
ratioWorksheet.Cells[3, 5].Value = "2022年";
ratioWorksheet.Cells[3, 6].Value = "行业标准";
ratioWorksheet.Cells[3, 7].Value = "评价";
// 盈利能力比率
ratioWorksheet.Cells[5, 1].Value = "盈利能力比率";
ratioWorksheet.Cells[5, 1].Font.Bold = true;
ratioWorksheet.Cells[6, 2].Value = "销售净利率";
ratioWorksheet.Cells[6, 3].Value = "净利润/营业收入";
ratioWorksheet.Cells[6, 4].Formula = "=利润表!B24/利润表!B5";
ratioWorksheet.Cells[6, 5].Formula = "=利润表!C24/利润表!C5";
ratioWorksheet.Cells[6, 6].Value = "0.08"; // 行业标准8%
ratioWorksheet.Cells[7, 2].Value = "资产收益率(ROA)";
ratioWorksheet.Cells[7, 3].Value = "净利润/平均总资产";
ratioWorksheet.Cells[7, 4].Formula = "=利润表!B24/5000000"; // 假设平均总资产500万
ratioWorksheet.Cells[7, 5].Formula = "=利润表!C24/4500000"; // 假设平均总资产450万
ratioWorksheet.Cells[7, 6].Value = "0.06"; // 行业标准6%
ratioWorksheet.Cells[8, 2].Value = "净资产收益率(ROE)";
ratioWorksheet.Cells[8, 3].Value = "净利润/平均净资产";
ratioWorksheet.Cells[8, 4].Formula = "=利润表!B24/3000000"; // 假设平均净资产300万
ratioWorksheet.Cells[8, 5].Formula = "=利润表!C24/2800000"; // 假设平均净资产280万
ratioWorksheet.Cells[8, 6].Value = "0.12"; // 行业标准12%
// 偿债能力比率
ratioWorksheet.Cells[10, 1].Value = "偿债能力比率";
ratioWorksheet.Cells[10, 1].Font.Bold = true;
ratioWorksheet.Cells[11, 2].Value = "流动比率";
ratioWorksheet.Cells[11, 3].Value = "流动资产/流动负债";
ratioWorksheet.Cells[11, 4].Formula = "=2000000/800000"; // 假设数据
ratioWorksheet.Cells[11, 5].Formula = "=1800000/750000"; // 假设数据
ratioWorksheet.Cells[11, 6].Value = "2.0"; // 行业标准2.0
ratioWorksheet.Cells[12, 2].Value = "速动比率";
ratioWorksheet.Cells[12, 3].Value = "(流动资产-存货)/流动负债";
ratioWorksheet.Cells[12, 4].Formula = "=(2000000-500000)/800000"; // 假设数据
ratioWorksheet.Cells[12, 5].Formula = "=(1800000-450000)/750000"; // 假设数据
ratioWorksheet.Cells[12, 6].Value = "1.0"; // 行业标准1.0
ratioWorksheet.Cells[13, 2].Value = "资产负债率";
ratioWorksheet.Cells[13, 3].Value = "总负债/总资产";
ratioWorksheet.Cells[13, 4].Formula = "=1500000/5000000"; // 假设数据
ratioWorksheet.Cells[13, 5].Formula = "=1400000/4500000"; // 假设数据
ratioWorksheet.Cells[13, 6].Value = "0.5"; // 行业标准50%
// 营运能力比率
ratioWorksheet.Cells[15, 1].Value = "营运能力比率";
ratioWorksheet.Cells[15, 1].Font.Bold = true;
ratioWorksheet.Cells[16, 2].Value = "应收账款周转率";
ratioWorksheet.Cells[16, 3].Value = "营业收入/平均应收账款";
ratioWorksheet.Cells[16, 4].Formula = "=利润表!B5/600000"; // 假设平均应收账款60万
ratioWorksheet.Cells[16, 5].Formula = "=利润表!C5/550000"; // 假设平均应收账款55万
ratioWorksheet.Cells[16, 6].Value = "8"; // 行业标准8次
ratioWorksheet.Cells[17, 2].Value = "存货周转率";
ratioWorksheet.Cells[17, 3].Value = "营业成本/平均存货";
ratioWorksheet.Cells[17, 4].Formula = "=利润表!B9/800000"; // 假设平均存货80万
ratioWorksheet.Cells[17, 5].Formula = "=利润表!C9/750000"; // 假设平均存货75万
ratioWorksheet.Cells[17, 6].Value = "6"; // 行业标准6次
// 评价公式
for (int row = 6; row <= 17; row++)
{
if (row == 5 || row == 10 || row == 15) continue;
ratioWorksheet.Cells[row, 7].Formula = $"=IF(D{row}>=F{row},\"良好\",IF(D{row}>=F{row}*0.8,\"一般\",\"较差\"))\";
}
// 设置格式
var ratioHeader = ratioWorksheet.Range("A3:G3");
ratioHeader.Font.Bold = true;
ratioHeader.Interior.Color = System.Drawing.Color.LightGray;
var categoryRange = ratioWorksheet.Range("A5:A5,A10:A10,A15:A15");
categoryRange.Font.Bold = true;
categoryRange.Interior.Color = System.Drawing.Color.LightBlue;
ratioWorksheet.Range("D4:G17").NumberFormat = "0.00";
ratioWorksheet.Columns["A:G"].AutoFit();
workbook.SaveAs("FinancialRatioAnalysis.xlsx");
Console.WriteLine(" ✓ 财务比率分析完成");
}
static void TrendAnalysis()
{
Console.WriteLine("
3. 趋势分析：");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 创建趋势分析工作表
var trendWorksheet = workbook.Worksheets.Add();
trendWorksheet.Name = "趋势分析";
// 趋势分析结构
trendWorksheet.Cells[1, 1].Value = "财务趋势分析";
trendWorksheet.Cells[1, 1].Font.Bold = true;
trendWorksheet.Cells[1, 1].Font.Size = 16;
// 创建5年历史数据
var years = new[] { "2019", "2020", "2021", "2022", "2023" };
var revenueData = new[] { 6000000, 6500000, 7500000, 8500000, 10000000 };
var profitData = new[] { 400000, 450000, 550000, 650000, 750000 };
var assetData = new[] { 3500000, 3800000, 4200000, 4500000, 5000000 };
// 写入历史数据
trendWorksheet.Cells[3, 1].Value = "年份";
for (int i = 0; i < years.Length; i++)
{
trendWorksheet.Cells[3, i + 2].Value = years[i];
}
trendWorksheet.Cells[4, 1].Value = "营业收入";
trendWorksheet.Cells[5, 1].Value = "净利润";
trendWorksheet.Cells[6, 1].Value = "总资产";
for (int i = 0; i < years.Length; i++)
{
trendWorksheet.Cells[4, i + 2].Value = revenueData[i];
trendWorksheet.Cells[5, i + 2].Value = profitData[i];
trendWorksheet.Cells[6, i + 2].Value = assetData[i];
}
// 计算增长率
trendWorksheet.Cells[8, 1].Value = "营业收入增长率";
trendWorksheet.Cells[9, 1].Value = "净利润增长率";
trendWorksheet.Cells[10, 1].Value = "资产增长率";
for (int i = 1; i < years.Length; i++)
{
trendWorksheet.Cells[8, i + 2].Formula = $"=(D4-C4)/C4";
trendWorksheet.Cells[9, i + 2].Formula = $"=(D5-C5)/C5";
trendWorksheet.Cells[10, i + 2].Formula = $"=(D6-C6)/C6";
}
// 复合增长率计算
trendWorksheet.Cells[12, 1].Value = "复合增长率(CAGR)";
trendWorksheet.Cells[12, 1].Font.Bold = true;
trendWorksheet.Cells[13, 1].Value = "营业收入CAGR";
trendWorksheet.Cells[13, 2].Formula = "=(F4/B4)^(1/4)-1"; // 4年复合增长
trendWorksheet.Cells[14, 1].Value = "净利润CAGR";
trendWorksheet.Cells[14, 2].Formula = "=(F5/B5)^(1/4)-1";
trendWorksheet.Cells[15, 1].Value = "资产CAGR";
trendWorksheet.Cells[15, 2].Formula = "=(F6/B6)^(1/4)-1";
// 趋势预测
trendWorksheet.Cells[17, 1].Value = "趋势预测(线性回归)";
trendWorksheet.Cells[17, 1].Font.Bold = true;
// 使用FORECAST.LINEAR函数进行预测
trendWorksheet.Cells[18, 1].Value = "2024年营业收入预测";
trendWorksheet.Cells[18, 2].Formula = "=FORECAST.LINEAR(6,B4:F4,{1,2,3,4,5})";
trendWorksheet.Cells[19, 1].Value = "2024年净利润预测";
trendWorksheet.Cells[19, 2].Formula = "=FORECAST.LINEAR(6,B5:F5,{1,2,3,4,5})";
// 设置格式
var headerRange = trendWorksheet.Range("A3:F3");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightBlue;
var dataRange = trendWorksheet.Range("A4:F6");
dataRange.Interior.Color = System.Drawing.Color.WhiteSmoke;
var growthRange = trendWorksheet.Range("A8:F10");
growthRange.Interior.Color = System.Drawing.Color.LightYellow;
var cagrRange = trendWorksheet.Range("A12:B15");
cagrRange.Interior.Color = System.Drawing.Color.LightGreen;
var forecastRange = trendWorksheet.Range("A17:B19");
forecastRange.Interior.Color = System.Drawing.Color.LightPink;
trendWorksheet.Range("B4:F6").NumberFormat = "#,##0";
trendWorksheet.Range("C8:F10").NumberFormat = "0.00%";
trendWorksheet.Range("B13:B15").NumberFormat = "0.00%";
trendWorksheet.Range("B18:B19").NumberFormat = "#,##0";
trendWorksheet.Columns["A:F"].AutoFit();
workbook.SaveAs("TrendAnalysis.xlsx");
Console.WriteLine(" ✓ 趋势分析完成");
}
}
}本章总结
本章详细介绍了MudTools.OfficeInterop.Excel库在公式与函数应用方面的强大功能。通过学习本章内容，您可以：
- 掌握基础公式操作：包括算术运算、统计函数、逻辑函数和文本函数
- 了解高级公式应用：特别是数组公式和动态数组公式的使用
- 学会自定义函数与VBA集成：实现复杂的业务逻辑
- 掌握公式错误处理与调试：确保公式的稳定性和正确性
- 了解公式性能优化：提升大型数据处理的效率
- 学习实际应用案例：财务报表分析等实际业务场景
这些功能在企业级Excel自动化应用中至关重要，可以帮助您构建复杂的数据处理和分析系统。通过本章的学习，您将能够：
- 熟练使用Excel公式进行各种数学运算和统计分析
- 掌握数组公式和动态数组公式的高级应用技巧
- 实现自定义业务逻辑和复杂的财务分析功能
- 优化公式性能，处理大规模数据集
- 构建完整的财务报表分析系统
MudTools.OfficeInterop.Excel库提供了强大的API支持，使得在.NET环境中进行Excel公式操作变得简单高效。无论是基础的单元格计算还是复杂的财务分析，该库都能提供完整的解决方案。