Override TODAY() function or set custom time context for Aspose.Cells

Aspose.Cells Product Family
1

Hello!
Is there a way to override the =TODAY() formula result in the calculated worksheet?

We are uploading the excel files on a server app, where they are calculated, but the problem is that the built-in TODAY() function result on the server might differ, depending on the time zone difference.
For example if I am in UTC+3 time zone and it is 01:00 local time then today() on my machine will result in 25.07.2023, but at the same time it will be calculated as 24.07.2023 on the server app (It is 22:00 on the previous day).
I am using Aspose.Cell 22.3
Has someone come across this use-case?

Any help will be greatly appreciated!

2

@bboyadzhiev,

I think you may try to extend the default calculation engine by Aspose.Cells and override the results of “TODAY” formula/function by yourselves for your requirements. See the example code in the document on how to implement custom calculation engine to extend the default calculation engine of Aspose.Cells. The custom calculation engine implements the interface AbstractCalculationEngine which has a Calculate(CalculationData data) method. This method is called against all of your formulas. Inside this method, you may capture “TODAY” formula and increases/decreases its value by 3 hours or so. You can add/subtract 3 hours (represented by TIME(3, 0, 0)) from the current date and time returned by the TODAY() function, so you could get resultant new date and time value that is three hours after/earlier. See the sample Excel formula expression for your reference.
=TODAY() + TIME(3, 0, 0)

Hope, this helps a bit.

3

@bboyadzhiev,
As @amjad.sahi replied, you can extend the default calculation engine by Aspose.Cells. When implementing a custom formula calculation engine, please override the property ProcessBuiltInFunctions.
The sample code as follows:

public void RunTest()
{
    // 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 a1Style = a1.GetStyle();
    a1Style.Number = 14;
    a1.SetStyle(a1Style);
    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
    CustomFormulaEngine engine = new CustomFormulaEngine();

    // 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();
}


class CustomFormulaEngine : 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")
        {
            double val = (double)data.CalculatedValue;
            val = val + 300;

            // Assign the CalculationData.CalculatedValue
            data.CalculatedValue = val;
        }
    }
    
    // Override ProcessBuiltInFunctions property
    public override bool ProcessBuiltInFunctions
    {
        get
        {
            return true;
        }
    }
}

Hope helps a bit.

1 Like
4

The ProcessBuiltInFunctions property was what I missed!
Thank you, John.He!

5

@bboyadzhiev,

It is nice to know that you have sorted it out now. Feel free to write us back if you have further queries or comments, we will be happy to assist you soon.

1 Like