Hello Aspose team,
We have faced a serious problem with calculation of custom functions in Aspose.Cells.
Method
Aspose.Cells.Cell.Calculate(bool ignoreError, ICustomFunction customFunction);
used to be non-recursive. Now it is recursive. This change practically hangs our application in some scenarios.
I know there is
public void Calculate(CalculationOptions options);
with CalculationOptions, but we need custom functions.
I know there is recursion option for worksheet calculation. But we need it for specific cells.
Is it possible to provide a version of Aspose.Cells.Cell.Calculate() for custom functions with ability to turn off recursion?
The behaviour was changed between Aspose versions 8.6.2 and 17.8.
@alex-rkn,
Thanks for some details.
Well, we need to evaluate your issue thoroughly. Please create two simple console demo applications (one with v17.8 and other with v8.6.2), zip the project(s) and provide us here to show the issue (you may even use some file sharing services (e.g dropbox and Google drive etc. to upload the sample projects and share the download link(s) here)), we will check it soon.
I’ll do shortly. However, if you can advise how to calculate a cell without triggering recursive calculation of all its precedents, that would be very helpful.
The demo showing the problem is attached.
Aspose Demo — Custom functions recursiveness.zip (46.5 KB)
After extra checks I can say it’s the regression from v. 8.7.1 to v. 8.7.2.
There is nothing in release notes about recursiveness.
Aspose lost the ability to detect that the worksheet is already calculated and no recursive calculation is needed.
As a fix I’d really happy to see Aspose.Cells.Cell.Calculate() with the option to skip recursive calculation.
Demo output using different Aspose versions:
Aspose: v. 8.7.1.0
Running…
E11: First calculation (4 calls expected)
=== calculating F1
=== calculating F2
=== calculating F3
=== calculating F4
E11: Second calculation (0 calls expected)
Ok
Aspose: v. 8.7.2.0
Running…
E11: First calculation (4 calls expected)
=== calculating F1
=== calculating F2
=== calculating F3
=== calculating F4
E11: Second calculation (0 calls expected)
=== calculating F1
=== calculating F2
=== calculating F3
=== calculating F4
Ok
@alex-rkn
For your trouble, we think you are using ReferredArea.GetValue()/GetValues() in the implementation of ICustomFunction or AbstractCalculationEngine. When there are formulas in the referred area and the formulas have not been calculated, then the formulas will be calculated before returning their values.
Two ways can be used to avoid the calculation for those referred formulas:
1 - You may get the cell values in the referred area manually by yourself.
CalculationOptions copts = new CalculationOptions();
copts.CustomEngine = new MyEngineForReferredArea1(wb);
//------------------------------------------
//------------------------------------------
//------------------------------------------
private class MyEngineForReferredArea1 : AbstractCalculationEngine
{
private Workbook _wb;
internal MyEngineForReferredArea1(Workbook wb)
{
_wb = wb;
}
public override void Calculate(CalculationData data)
{
object p = data.GetParamValue(0);
if (p is ReferredArea)
{
ProcessReference((ReferredArea)p);
}
data.CalculatedValue = "Success";
}
private void ProcessReference(ReferredArea ra)
{
if (ra.IsExternalLink)
{
Console.WriteLine("External link is not supported.");
return;
}
Cells cells = _wb.Worksheets[ra.SheetName].Cells;
int startRow = ra.StartRow;
int startCol = ra.StartColumn;
int endRow = ra.EndRow;
int endCol = ra.EndColumn;
for (int i = startRow; i <= endRow; i++)
{
for (int j = startCol; j <= endCol; j++)
{
Cell cell = cells.CheckCell(i, j);
if (cell == null)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, j) + ": null");
}
else
{
Console.WriteLine(cell.Name + ": " + cell.Value);
}
}
}
}//-----ProcessReference
}
2 - Set the Recursive flag to false when fetching values of the referred area:
CalculationOptions copts = new CalculationOptions();
copts.CustomEngine = new MyEngineForReferredArea2(copts);
//------------------------------------------
//------------------------------------------
//------------------------------------------
private class MyEngineForReferredArea2 : AbstractCalculationEngine
{
private CalculationOptions _copts;
internal MyEngineForReferredArea2(CalculationOptions copts)
{
_copts = copts;
}
public override void Calculate(CalculationData data)
{
object p = data.GetParamValue(0);
if (p is ReferredArea)
{
if (_copts != null && _copts.Recursive)
{
_copts.Recursive = false;
try
{
ProcessReference((ReferredArea)p);
}
finally
{
_copts.Recursive = true;
}
}
else
{
ProcessReference((ReferredArea)p);
}
}
data.CalculatedValue = "Success";
}//-----Calculate
private void ProcessReference(ReferredArea ra)
{
object vs = ra.GetValues();
if (vs is Array)
{
object[][] avs = (object[][])vs;
foreach (object[] rd in avs)
{
foreach (object v in rd)
{
Console.WriteLine(_copts == null ? "default:" + v : "norecursive: " + v);
}
}
}
}//-----ProcessReference
}
@alex-rkn
Thanks for providing the sample project and using Aspose APIs.
From the sample project you provided, we think you want to avoid the repeated calculation for the dependents between two invocations of Cell.Calculate() for one cell (in your sample is E11). However, in fact the behavior of 8.7.2 and later versions are right. Think of the scenario that cell F1 refers to another cell and this cell’s value be changed after the first invocation of Calculate() for E11, in such situation F1 should be re-calculated firstly while calculating E11 again. For versions prior to 8.7.2, this scenario cannot be handled correctly.
If you are sure that the dependents need not to be re-calculated with our recent versions it is also easy to calculate one cell without calculating its dependents recursively:
CalculationOptions copts = new CalculationOptions();
copts.Recursive = false;
cell.Calculate(copts);
Thank you, shakeel, I’ll check your ideas. However, you probably replied without seeing my last message with demo, where I showed different behaviour for Cell.Calculate() method in different Aspose versions.
Thank you for quick reply.
All what you say is right. And the solution to use CalculationOptions is what I need. If only your team could extend cell.Calculate() to include both CalculationOptions and ICustomFunction…
@alex-rkn
Thanks for your feedback and considering Aspose.Cells.
ICustomFunction also has been combined into CalculationOptions, you may learn the api reference or our online documents. And now we provide more flexible and powerful APIs for calculating custom functions to replace the ICustomFunction: AbstractCalculationEngine. The required implementation for this new class is similar with the old one in ICustomFunction so it is easy for user to migrate the implementation for ICustomFunction to AbstractCalculationEngine (Please see the example in our first reply where we show the usage of AbstractCalculationEngine with CalculationOptions).
Reference Link:
What you say sounds great! I’ll check it all tomorrow. Thanks.
Hi Shakeel,
Thank you for sharing knowledge on API improvements in .Cells. The functionality we need is there.
Issue solved.
@alex-rkn
Thanks for your feedback and using Aspose APIs.
We have marked this thread as resolved based on your feedback. If you find any other question, please feel free to let us know, we will be glad to help you further.