Hello!
I have a custom implementation of the TODAY() and NOW() built-in formulas for the purpose of overriding the calculation of an excel worksheet for a specific date/time.
I have created a custom AbstractCalculationEngine implementation that overrides the TODAY() and NOW() built-in formulas (with the ProcessBuiltInFunctions set to true) , as well as some custom formulas for our application.
I have been able to successfully override the date/time, except for a specific use case.
There is a case when these functions are referenced from a upper part of the sheet…
For example if I have a =ROUNDDOWN(A20,0) function in A10 that is using the result of a =NOW() formula in A20 (placed “below” the rounddown), then overriding of the NOW() with a specific date/time is not taken into consideration and returns the server/process actual data/time.
However if the =NOW() is located “above” the =ROUNDDOWN() it works, for example if =NOW() is in A1 and =ROUNDDOWN(A1,0) is in A10.
Is there some dependency I am missing here?
I would not like to force the users to place the TODAY and NOW formulas always at the top of the sheet…
I am using Aspose.Cells 23.10.0 on .NET6
Any help will be greatly appreciated!
@bboyadzhiev,
To accurately evaluate your issue, we need to examine how you are utilizing custom implementations of the TODAY() and NOW() built-in formulas. Could you please provide a sample (runnable) code for your test case or preferably a console (demo) application? Additionally, please zip the project along with the template Excel file (if applicable) so that we can reproduce the issue on our end. We will promptly investigate it for you.
Sure!
There it is:
public class TargetDatePrecalculationEngine : AbstractCalculationEngine
{
private DateTime _dateTime;
public void SetTime(DateTime dateTime)
{
_dateTime = dateTime;
}
public override void Calculate(CalculationData data)
{
if (data.FunctionName.ToUpper() == "TODAY")
{
data.CalculatedValue = _dateTime.Date;
return;
}
else if (data.FunctionName.ToUpper() == "NOW")
{
data.CalculatedValue = _dateTime;
return;
}
else if (data.FunctionName.ToLower() == "CustomFunction1")
{
// custom calcualtion code
}
else .......
}
public override bool ProcessBuiltInFunctions
{
get
{
return true;
}
}
}
This is the prepared file:
Now and Today Bug.zip (6.8 KB)
Here is a screenshot of the result of the same file with set date/time of 31-10-2023 after calculation:
image.png (13.3 KB)
Here is a sample of the TargetDatePrecalculationEngine usage.
Workbook _workbook;
TargetDatePrecalculationEngine _targetDatePrecalculationEngine;
----
CalculationOptions _preCalculationOptions = new CalculationOptions
{
CustomEngine = _targetDatePrecalculationEngine,
IgnoreError = false
};
----
_targetDatePrecalculationEngine.SetTime(targetDateTime);
_workbook.CalculateFormula(_preCalculationOptions);
@bboyadzhiev,
I tested your scenario/case using Aspose.Cells for .NET v23.11 (.NET6) with your template Excel file and it works fine and I got expected results dynamically and in the output Excel file. Here is my sample code for your reference.
e.g.
Sample code:
Workbook workbook = new Workbook("g:\\test2\\Now and Today Bug.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Cell b2= sheet.Cells["B2"];
workbook.CalculateFormula();
Console.WriteLine("The value of B2 with default calculation engine: " + b2.StringValue);//12/1/2023 0:00
// Create an instance of CustomEngine
TargetDatePrecalculationEngine engine = new TargetDatePrecalculationEngine();
DateTime oldDate = new DateTime(2023, 10, 31, 0, 0, 0);
engine.SetTime(oldDate);
// Create an instance of CalculationOptions
CalculationOptions opts = new CalculationOptions()
{
CustomEngine = engine,
IgnoreError = false
};
// Recalculate all formulas in Workbook using the custom calculation engine
workbook.CalculateFormula(opts);
sheet.AutoFitColumns();
Console.WriteLine("The value of b2 with default calculation engine: " + b2.StringValue);//10/31/2023 0:00
workbook.Save("g:\\test2\\out1.xlsx");
........
public class TargetDatePrecalculationEngine : AbstractCalculationEngine
{
private DateTime _dateTime;
public void SetTime(DateTime dateTime)
{
_dateTime = dateTime;
}
public override void Calculate(CalculationData data)
{
if (data.FunctionName.ToUpper() == "TODAY")
{
data.CalculatedValue = _dateTime.Date;
return;
}
else if (data.FunctionName.ToUpper() == "NOW")
{
data.CalculatedValue = _dateTime;
return;
}
else if (data.FunctionName.ToLower() == "CustomFunction1")
{
// custom calcualtion code
}
//else// .......
}
public override bool ProcessBuiltInFunctions
{
get
{
return true;
}
}
}
Please find attached the output Excel file for your reference.
out1.zip (7.1 KB)
Let us know with more details if you still find the issue with latest version (Aspose.Cells for .NET v23.11 (.NET6)) of the APIs.