Hi Aspose Team, We are working on a project where we need to call Workbook.CalculateFormula() multiple times as some addin functions we are supporting need to request data from a database which will then be set as the result of the formula on the next call of workbook.CalculateFormula().
If a Different formula tries to calculate the result of one of these formulas which haven’t received their data yet we throw an exception.
One issue we have encountered with doing this is that if we set IgnoreError to false any exception that is thrown due to the data not being received from the database yet causes the rest of the workbook to stop calculating, but if we set ignore error to true any formulas calculated from the default calculation engine that get thrown this exception for not having the data yet will instead try and calculate using the current value in the cell (which is likely old/bad data). This can end up causing problems if another formula is dependent on one of these formulas that calculated using old/bad data, especially if the formulas are one of the formulas that receive data from our database.
We are wondering if there is there some way we can use Workbook.CalculateFormula but have it so if an Exception is thrown when calculating a formula it will not attempt to calculate any of its dependent formulas.
@ecarrillo,
It is complicated for your requirement. Even though the exception can be transferred in the calculation chain, it can only process one situation such as:
A1 references to A2, when the calculation starts from A1, then A2 is found that it needs to be calculated firstly. If receiving data fails for A2, then the exception can be caught by A1 and the calculation may be interrupted.
However, if A2 references to A1, the calculation starts from A1 and receiving data fails for A1, then the calculation finished. So when calculating A2, it will be unknown whether A1 has been calculated successfully or not.
So, we think you need one special value as the calculated result to denote the calculation’s failure. We think you are using custom calculation engine(for such as the processing addin function to receive data), here we create one demo to set special value for the formula so the calculation for one cell can be marked as failure and other formulas may recognize it. Hope it can fit your requirement:
private class BreakingCalcChain : AbstractCalculationEngine
{
internal const string Indicator = "SPECIAL_FLAG_BREAK";
private bool IsBreak(object v)
{
return v is string && (string)v == Indicator;
}
public override void Calculate(CalculationData data)
{
if (data.FunctionName == "MYFUNC")
{
data.CalculatedValue = Indicator;
}
else
{
for (int i = data.ParamCount - 1; i > -1; i--)
{
object v = data.GetParamValue(i);
bool isBreak = false;
if (v is object[][])
{
foreach (object[] vr in (object[][])v)
{
foreach (object vi in vr)
{
if (IsBreak(vi))
{
isBreak = true;
break;
}
}
if (isBreak)
{
break;
}
}
}
else if (v is ReferredArea)
{
ReferredArea ra = (ReferredArea)v;
int rc = ra.EndRow - ra.StartRow + 1;
int cc = ra.EndColumn - ra.StartColumn + 1;
for (int r = 0; r < rc; r++)
{
for (int c = 0; c < cc; c++)
{
object vi = ra.GetValue(r, c);
if (IsBreak(vi))
{
isBreak = true;
break;
}
}
if (isBreak)
{
break;
}
}
}
else
{
isBreak = IsBreak(v);
}
if (isBreak)
{
data.CalculatedValue = Indicator;
break;
}
}
}
}
public override bool ProcessBuiltInFunctions { get { return true; } }
}