Direct calculation of custom function without writing it in a worksheet

Direct calculation of custom function without writing it in a worksheet

This topic explains how you can directly calculate your custom functions without first writing them in a worksheet. Please use the Worksheet.CalculateFormula(string formula, CalculationOptions opts) method for this purpose.

Please see the following sample code that illustrates the usage of this method. We have used a custom function named MyCompany.CustomFunction() and we calculate its value as “Aspose.Cells.” by ourselves and then this value is automatically concatenated with the value of cell A1 which is “Welcome to " by the calculation engine and the final calculated value returns as “Welcome to Aspose.Cells."”. As you can see in a code that we have not written our custom function anywhere in a worksheet and it is calculated directly by our own custom logic.

Programming Sample

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
class ICustomEngine : AbstractCalculationEngine
{
    // Override the Calculate method with custom logic
    public override void Calculate(CalculationData data)
    {
        // Check the forumla name and calculate it yourself
        if (data.FunctionName == "MyCompany.CustomFunction")
        {
            // This is our calculated value
            data.CalculatedValue = "Aspose.Cells.";
        }
    }
}
class ImplementDirectCalculationOfCustomFunction
{
    public static void Run()
    {
        // Create a workbook
        Workbook wb = new Workbook();

        // Accesss first worksheet
        Worksheet ws = wb.Worksheets[0];

        // Add some text in cell A1
        ws.Cells["A1"].PutValue("Welcome to ");

        // Create a calculation options with custom engine
        CalculationOptions opts = new CalculationOptions();
        opts.CustomEngine = new ICustomEngine();

        // This line shows how you can call your own custom function without
        // a need to write it in any worksheet cell
        // After the execution of this line, it will return
        // Welcome to Aspose.Cells.
        object ret = ws.CalculateFormula("=A1 & MyCompany.CustomFunction()", opts);

        // Print the calculated value on Console
        Console.WriteLine("Calculated Value: " + ret);
    }
}

Console Output

Below is the console output of the above sample code.

Calculated Value: Welcome to Aspose.Cells.

Related Article

Implement Custom Calculation Engine to extend the Default Calculation Engine of Aspose.Cells