实现类似 Excel 的公式引擎

• 下载示例应用程序 - 228.4 KB
• 下载库和文档 - 387.1 KB
• 下载源代码 - 321.7 KB
• 从 SourceForge 下载最新版本

引言

FormulaEngine 是一个 .NET 程序集，可让您为应用程序添加公式支持。它负责解析和计算公式、跟踪它们的依赖关系以及按自然顺序重新计算。公式语法和引擎的大部分功能都是 Excel 的直接复制品，确保了用户较低的学习曲线。该库在 LGPL 许可下发布，并且该项目托管在 SourceForge 这里。

特点

以下是引擎主要功能的列表

• 解析和计算 Excel 风格的公式
• 已实现一百多个常用的 Excel 函数，并且可以轻松添加您自己的函数
• 自定义公式函数会自动验证参数类型和数量
• 支持带有可选参数和可变参数列表的自定义函数
• 支持命名引用、易失性函数和动态引用
• 支持多工作表的公式
• 通过接口与工作表交互，允许任何类用作工作表
• 跟踪和调整行/列插入/删除以及范围移动时的引用
• 管理公式依赖关系并按自然顺序重新计算
• 支持在没有任何工作表的情况下工作
• 区分文化的小数点和参数分隔符

动机

我编写这个库是出于以下原因

• 我需要一个业余项目在家工作，而这个项目似乎是一个挑战性和实用性平衡得当的想法
• 在工作中，我使用的是第三方组件供应商为其网格实现的公式引擎。我认为他们的实现非常糟糕（所有操作数都是字符串），任何有点本事的程序员都应该能做得更好；我决定付诸行动。

又一个表达式求值器？

考虑到表达式求值器在 CodeProject 上非常受欢迎，这款求值器有什么不同之处？两个主要区别是，这个库实现了 Excel 中的许多功能，并且它不仅仅是计算表达式。

概述

在本文中，我将简要概述该库允许您做的三件事

• 公式解析/计算
• 自然顺序重新计算
• 定义自定义函数以在公式中使用

公式解析和计算

该库允许您做的第一件事是计算公式表达式。支持的语法基于 Excel，95% 的现有公式应能无需修改即可使用。该引擎提供 `Formula` 类，它代表一个已编译的公式表达式。您调用引擎的 `CreateFormula` 方法并提供一个表达式，它将返回一个 `Formula` 实例，您可以对其进行计算。

' Create an instance of the engine
Dim engine As New FormulaEngine
' Create a formula
Dim f As Formula = engine.CreateFormula("=sqrt(3^2 + 4^2)")
' Evaluate the formula to get a result (5.0)
Dim result As Double = f.Evaluate()

如果无法从表达式创建公式，该方法将抛出 `InvalidFormulaException` 异常。这通常（但不仅限于）是由于表达式中的语法错误。抛出异常的内部异常将包含更多详细信息。

该引擎还具有非常流行的 `Evaluate` 方法，用于当您想快速计算表达式时。让我们尝试计算 此处 找到的“mega”公式。

' Create an instance of the engine
Dim engine As New FormulaEngine
' Assume cell A1 contains "http://j-walk.com/ss/books"
' Call Evaluate to get a result: "books"
Dim result As String = engine.Evaluate("=RIGHT(A1,LEN(A1)-FIND(CHAR(1),_
                                       SUBSTITUTE(A1,""/"",CHAR(1)" _
                              & ",LEN(A1)-LEN(SUBSTITUTE(A1,""/"","""")))))")

数据类型和计算

在计算表达式时，引擎支持以下数据类型：整数、双精度浮点数、字符串、布尔值、日期时间、Null、错误值和引用。就像 Excel 一样，操作数是松散类型的，意味着只要数据类型可以转换为所需的数据类型，任何数据类型都是有效的。例如：表达式 `="123" + 10` 是有效的，因为字符串 "123" 可以转换为数字。与 Excel 的一个主要区别是，日期时间值 **不** 被视为数字。如果您想加/减日期，您需要使用函数。

当公式计算期间遇到错误时，将返回 `ErrorValueWrapper` 实例。此类包装了七个 Excel 错误值之一，并允许您获取特定错误以及格式化它。

公式和结果类型属性

公式类有一个名为 `ResultType` 的属性，允许您指定公式结果所需的类型。当表达式为 `=A1` 时，这很有用，它可以有效地计算为单元格 A1 的内容或对其的引用。通过设置结果类型，您可以控制您获得的两个结果中的哪个。公式将尝试将其结果转换为指定的类型。如果转换不可行，则会返回 #VALUE! 错误。

Dim f As Formula = engine.CreateFormula("=A1")
' Make the formula evaluate to any value except a reference
f.ResultType = OperandType.Primitive
' result will be the contents of cell A1
Dim result As Object = f.Evaluate()
' Make the formula evaluate to a sheet reference
f.ResultType = OperandType.SheetReference
' result will be a reference to cell A1
result = f.Evaluate()

自然顺序重新计算

该库允许您做的第二件事是自然顺序重新计算。对于不熟悉此术语的人来说，按自然顺序重新计算意味着公式将在其依赖的任何公式之后进行重新计算。考虑一个包含以下值和公式的工作表

(A1): 15
(B2): =A1 + 10
(C1): =A1 + B2
(D2): =C1 * 2

当单元格 A1 的内容发生变化时，需要重新计算三个公式。B2 的公式必须首先重新计算，因为它只依赖于 A1。C1 的公式在第二个重新计算，因为它依赖于 B2 的值。最后，D2 的公式最后重新计算，因为它依赖于 C1。

为了让引擎能够按自然顺序重新计算，它必须跟踪公式之间的依赖关系。它通过充当公式的容器来实现这一点。当公式被添加到引擎时，它们的依赖关系会被分析并构建一个依赖关系图。然后，您告诉引擎重新计算，它将使用该图构建一个计算列表，按自然顺序对其进行排序，并重新计算每个公式。

参考文献

引擎用于跟踪依赖关系的基本单位是引用。存在各种类型的引用，它们都实现 `IReference` 接口，并且 `ReferenceFactory` 类创建所有这些引用。当您将公式添加到引擎时，您需要指定公式将绑定的引用。然后公式将“位于”该引用处。通过更改绑定公式的引用的类型，您可以更改其他公式如何引用该公式。例如：通过将公式绑定到命名引用，您可以允许其他公式通过名称引用它。

' Associate the name Root2 with a formula
engine.AddFormula("=sqrt(2)", engine.ReferenceFactory.Named("Root2"))
' Use the name in an expression (result is 2.0)
Dim result As Double = engine.Evaluate("=root2 ^ 2")

既然我们已经了解了引擎如何跟踪依赖关系，让我们看看上面的基本示例将如何使用代码设置

' Assume we've already added a worksheet to the engine
' Add a formula at B2
engine.AddFormula("=A1 + 10", engine.ReferenceFactory.Cell(2, 2))
' Add a formula at C1
engine.AddFormula("=A1 + B2", engine.ReferenceFactory.Parse("C1"))
' Add a formula at D2
engine.AddFormula("=C1 * 2", engine.ReferenceFactory.Parse("D2"))

我们的引擎现在包含 3 个公式和描述它们依赖关系的图。我们只需要告诉引擎一个引用已更改，并且其所有依赖项都需要重新计算。我们使用 `Recalculate` 方法来完成此操作。

' Create a reference to cell A1
Dim a1Ref As ISheetReference = engine.ReferenceFactory.Parse("A1")
' Recalculate all dependents of A1
engine.Recalculate(a1Ref)

自定义函数

引擎允许您做的最后一件事是定义自己的函数以在公式中使用。要做到这一点，我们必须使用 `FunctionLibrary` 类，该类可通过引擎上的属性访问。我使用的扩展机制基于委托。我认为这使得添加许多函数更加容易，因为您不必为每个函数定义一个新类，这与基于接口/子类的替代机制不同。它还允许引擎使用反射批量添加类的所有方法。定义自定义函数需要三个步骤

• 定义一个与 `FormulaFunctionCall` 委托具有相同签名的函数
• 标记该函数，使用 `FixedArgumentFormulaFunction` 或 `VariableArgumentFormulaFunction` 属性
• 将其添加到函数库

让我们定义一个给定其他两个边长的函数，它返回斜边的长度：首先，我们必须定义一个具有正确签名的函数。

Public Sub Hypotenuse(ByVal args() As Argument, ByVal result As FunctionResult,_
                      ByVal engine As FormulaEngine)

End Sub

三个参数的说明

• 我们的函数参数是 `Argument` 实例的数组
• 我们将存储函数返回值的 `FunctionResult` 类的实例
• 公式引擎的实例

第二，我们必须用适当的属性来装饰我们的函数，以便函数库可以识别它

<FixedArgumentFormulaFunction(2, New OperandType() {OperandType.Double, _
 OperandType.Double})> _
Public Sub Hypotenuse(ByVal args() As Argument, ByVal result As FunctionResult,_
                      ByVal engine As FormulaEngine)

End Sub

我们现在已将我们的函数声明为需要 2 个参数，都为 `Double` 类型。仅当公式中指定了恰好 2 个参数并且两个参数都可以转换为 `Double` 时，引擎才会调用我们的函数。这使我们不必为我们想实现的每个函数编写参数验证代码。

最后，我们必须编写函数的实际实现

<FixedArgumentFormulaFunction(2, New OperandType() {OperandType.Double, _
 OperandType.Double})> _
Public Sub Hypotenuse(ByVal args() As Argument, ByVal result As FunctionResult,_
                      ByVal engine As FormulaEngine)
    ' Get the value of the first argument as a double
    Dim a As Double = args(0).ValueAsDouble
    ' Get the value of the second argument as a double
    Dim b As Double = args(1).ValueAsDouble
    ' Compute the hypotenuse
    Dim hyp As Double = System.Math.Sqrt(a ^ 2 + b ^ 2)
    ' Set the function's result
    result.SetValue(hyp)
End Sub

我们将每个参数的值作为双精度浮点数获取，计算斜边，并将值设置到 `FunctionResult` 中。

接下来我们将自定义函数添加到库中

engine.FunctionLibrary.AddFunction(AddressOf Hypotenuse)

现在我们可以在函数中使用它了

dim result as Double = engine.Evaluate("=10 + Hypotenuse(3, 4)")

请注意，所有函数都必须返回值，并且在定义公式时不能定义/取消定义函数。

演示应用程序

演示应用程序是 Excel 的简陋版本。它旨在作为参考实现，展示如何使用引擎的所有功能。它展示了以下内容

• 多个工作表和跨工作表引用
• 命名引用
• 通过行/列插入/删除和范围移动进行引用跟踪
• 剪切/复制/粘贴和向右/向下填充支持
• 绝对/相对引用
• 公式引擎和工作表的保存和加载
• 不在任何工作表上的公式，并且“监视”工作表值（转到“插入”->“图表”）

实现细节

公式解析

为了实现公式的解析，我使用了优秀的 Grammatica 解析器生成器。我编写了一个描述公式语法的语法，让 Grammatica 生成一个解析器。然后我让解析器进行解析，监听回调，并用我自己的对象填充解析树。最后，我有一个代表整个公式解析树的根元素。我将树重新排列成后缀形式，并将其保存到公式实例中。计算公式然后简单地迭代每个元素，让它在堆栈上推送/弹出值。最后，堆栈上应该只剩下一个值，这就是公式的结果。

我选择 Grammatica 是因为它在语法和解析器代码之间有清晰的分离，具有易于使用的语法，并且它输出 VB .NET 代码。该项目已经有一段时间没有活动了，但它并没有死，即使我使用的版本是 alpha 版，我也觉得它非常稳定：没有崩溃，也没有不正确的功能。如果您和我一样是解析器和语法的新手，我强烈推荐它。

此外，由于 grammatica 解析器是在运行时创建的，因此可以非常轻松地动态更改小数点和参数分隔符模式，以使用当前文化的相应值。这意味着，在爱沙尼亚的用户可以输入 `=1,2 + sum(1;2;3)` 而不是 `=1.2 + sum(1,2,3)`，并且可以将其作为有效公式。

自然顺序重新计算

引擎维护所有公式的依赖关系图。当需要重新计算时，会从重新计算的根节点开始构建一个临时图。一旦消除了所有循环引用，就会在图上执行 拓扑排序，以获得按自然顺序排列的公式列表。有了这个计算列表，只需迭代它并重新计算每个公式即可。

未实现

以下内容未实现，因为它们是晦涩或高级的功能，大多数人可能不知道或发现它们有用

• 数组公式
• 范围、联合和交集运算符
• 3D 引用

如果需求足够，我一定会考虑实现它们。

结论

我发现编码这个项目让我对 Excel 的工作方式有了很多了解。不得不实现 100 多个 Excel 函数，会让您非常熟悉它们的所有细微之处。例如：在单元格 A1 中输入的公式 `=Offset(A1,1,1)`，尽管它依赖于自身的单元格，但 Excel 并不将其视为循环引用。另一个例子是，连接函数不适用于非单元格范围。虽然您可以说 `=Sum(A1:B2)`，但您不能说 `=Concatenate(A1:B2)`。

该项目目前处于 alpha 阶段，因为它还没有经过实际的测试/使用。如引言中所述，该项目托管在 SourceForge 上。任何错误或功能请求都应使用提供的工具在此处报告。任何新版本也将在此处发布。

好吧，我希望大家觉得这个项目很有用！

历史

• 2007 年 3 月 4 日
  • 初始发布
• 2007 年 3 月 6 日
  • 已从 Dundas 图表切换到 ZedGraph，因为它占用的空间更小
  • 源代码发布现在包括图表程序集
• 2007 年 3 月 17 日
  • 修复了命名引用未重新计算的 bug
  • 添加了 Variable 类，以便更轻松地使用命名常量值