I’ve implemented an AbstractCalculationEngine and the CalculationData object being passed into the Calculate override method does not properly reflect dependent changes to values in the workbook. As a result the custom functions are being calculating using old data. The only way to “fix” this is to calculate formulas twice. And when EnableCalculationChain = true, calculating twice doesn’t recalculate the cells a second time (which makes sense) resulting in incorrect calculation results. The main issue is the dependency tree itself. All of the custom function dependencies need to be recalculated before being fed into the CalculationData object.
As a result of this issue, I am definitely unable to use EnableCalculationChain = true and I am forced to call the CalculateWorkbook method multiple times leading to poor performance. Please address this Aspose.Cells bug in a future release.
Thank you.
@mattgabriel,
Could you please provide us with a sample (console) application or a complete (runnable) sample code, along with any necessary resource files, to help us reproduce the issue on our end? We will then be able to check your issue soon. Please remember to zip the project and resource files before attaching them.
@mattgabriel
And for performance consideration, we do not re-calculate custom functions when it is in shared formulas and values of its parameters do not change for different cells. Please see the api reference of AbstractCalculationEngine.ForceRecalculate(string) and check whether it is your situation. If yes, please override this method in your custom engine to make it return true.
If it is not your situation, as we have said, we need the runnbale application with the required resources such as template files to reproduce the issue so we can figure the issue out.
Hi- I was able to build a console application and sample workbook that replicates the issue to prove that it’s a bug. In fact, I found that the position of the formula relative to its dependencies affects the issue which proves that it’s a dependency tree issue. In my sample, you will see that the custom function has parameters that are below the call to the custom formula. This situation replicates the issue. However, if I move the call to the custom function below its parameters it works as expected. I can’t always guarantee that the formulas will be written with the custom function below its parameters.
The attached zip file contains the entire console app as well as the sample workbook.
Please let me know if anything else is needed.
Thank you.
ConsoleAppForAsposeReview.zip (57.7 KB)
Here’s another version that executes a second calculation that shows that the results are correct for a second calculation. That’s not ideal, of course.
ConsoleAppForAsposeReview.zip (5.9 MB)
@mattgabriel,
Thanks for the template file and sample apps.
After initial testing, I was able to reproduce the issue as you mentioned by using your sample project with your template Excel file. I found AbstractCalculationEngine dependency tree is not working properly which does not properly reflect dependent changes to values in the workbook. As a result the custom functions are being calculating using older data as I noticed.
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-56959
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@mattgabriel
We investigated your case and found the issue was caused by the code in your implementation of the custom engine. In your CustomFunction() function, you just fetch range values directly, which ignores all calculations for those formulas in the range. Here you should use CalculationData.GetParamValue() instead, this api will calculate all formulas in the required range when needed.
So, the corresponding code:
private static String CustomFunction(Aspose.Cells.CalculationData data)
{
...
rateArray = (object[,])AsposeGetRange(data.Workbook, data.Worksheet, data.GetParamText(0)).Value;
}
should be changed to:
private static String CustomFunction(Aspose.Cells.CalculationData data)
{
...
object[][] o = (object[][])((ReferredArea)data.GetParamValue(0)).GetValues(); //for this simple case, the parameter is just the range reference, so we cast it directly. For complex cases, here should check the parameter value type and process it accordingly.
for (int i = 0; i < 3; i++)
{
rateArray[i, 0] = o[i][0];
}
}