实现自定义计算引擎以扩展Aspose.Cells的默认计算引擎

实现自定义计算引擎

Aspose.Cells具有强大的计算引擎,可以计算几乎所有的Microsoft Excel公式。尽管如此,它还允许您扩展默认的计算引擎,从而为您提供更大的动力和灵活性。

在实现此功能中使用了以下属性和类。

以下代码实现了自定义计算引擎。它实现了 AbstractCalculationEngine 接口,该接口具有一个 Calculate(CalculationData data) 方法。该方法会针对所有公式进行调用。在这个方法中,我们捕获了 TODAY 函数,并向系统日期添加了一天。因此,如果当前日期是2023年7月27日,那么自定义引擎将计算TODAY()为2023年7月28日。

编程示例

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create a new class derived from AbstractCalculationEngine
class CustomEngine : AbstractCalculationEngine
{
// Override the Calculate method with custom logic
public override void Calculate(CalculationData data)
{
// Check the forumla name and change the implementation
if (data.FunctionName.ToUpper() == "TODAY")
{
// Assign the CalculationData.CalculatedValue: add one day offset for the date
data.CalculatedValue = CellsHelper.GetDoubleFromDateTime(DateTime.Today, false) + 1.0;
}
}
public override bool ProcessBuiltInFunctions { get { return true; } }
}
class ImplementCustomCalculationEngine
{
public static void Run()
{
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Access first Worksheet from the collection
Worksheet sheet = workbook.Worksheets[0];
// Access Cell A1 and put a formula to sum values of B1 to B2
Cell a1 = sheet.Cells["A1"];
Style style = a1.GetStyle();
style.Number = 14;
a1.SetStyle(style);
a1.Formula = "=TODAY()";
// Calculate all formulas in the Workbook
workbook.CalculateFormula();
// The result of A1 should be 20 as per default calculation engine
Console.WriteLine("The value of A1 with default calculation engine: " + a1.StringValue);
// Create an instance of CustomEngine
CustomEngine engine = new CustomEngine();
// Create an instance of CalculationOptions
CalculationOptions opts = new CalculationOptions();
// Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
opts.CustomEngine = engine;
// Recalculate all formulas in Workbook using the custom calculation engine
workbook.CalculateFormula(opts);
// The result of A1 will be 50 as per custom calculation engine
Console.WriteLine("The value of A1 with custom calculation engine: " + a1.StringValue);
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
}

结果

请检查上述示例代码的控制台输出,具有自定义引擎的 A1 的值(日期时间)应该比没有自定义引擎的结果晚一天。

相关文章