We’ve been having an issue with Shared Formulas when using a CustomEngine in CalculateFormulas. If there is a shared formula then the custom engine is not given an opportunity to calculate the formula. To the CustomEngine it’s as if the formula doesn’t exists.
For example: Column A 1 =@MyFormula(5) 2 =@MyFormula(5) 3 =@MyFormula(5) 4 =@MyFormula(5)
In this example, the cell A1 is the original formula that has been filled done to cell A4. Since Excel added the @ sign to this formula, the copies in A2:A4 all have IsSharedFormula = true. When run through a CustomEngine, only the formula in A1 will be called in CalculateFormula.
As a workaround we can scan the workbook for formulas with IsSharedFormul = true and then set the cell.Formula to itself, which negates the shared formula bit. However, on larger workbooks this causes a huge memory increase, so instead we’re only performing this workaround on cells that contain formulas that we want to make sure run through the custom calc engine.
Is there any other workarounds that we can use, or is there a way to instruct the CustomCalc engine that it prefers to handle shared formulas directly, even though they are designated as shared formulas?
Could you please provide a use case to demonstrate the issue? Please create a sample console demo app (source code without compilation errors) with sample file(s), zip the project with resource files, and provide it to us for reproducing the issue. We will evaluate and check your issue soon.
The attached SharedFormulas.xlsx file contains a shared formula that was entered into Cell A1 as: =RWSUPPRESS( True, "rows", "hidden")
Then this formulas was filled to cell A10 using the fill handle.
When run through the calculation and custom engine below you’ll see the behavior. The custom engine will log to the console each formula that is calculated in the engine, and will return the word “HIT”. You’ll see that all 10 cells will report the result “HIT” in the out.xlsx file, however only the first 2 cells (A1 and A2) were evaluated in the custom engine.
It may be that these shared formulas are optimized/cached so it’s unnecessary for the custom engine to be queried, however for our use case the custom engine is being used to find the formulas, not just calculate them. If this is expected behavior then I would like to request an override setting in the AbstractCalcuationEngine to permit the implementer the option to handle shared formulas explicitly.
for example: public override bool ProcessSharedFormulas => true;
[Test]
public void foo47()
{
var workbook = new Workbook(@"C:\Data\Temp\SharedFormulas.xlsx");
workbook.Settings.FormulaSettings.CalculationMode = CalcModeType.Manual;
workbook.CalculateFormula(new CalculationOptions()
{
CustomEngine = new MyCustomCalcEngine()
});
workbook.Save(@"C:\Data\Temp\out.xlsx");
}
public class MyCustomCalcEngine : AbstractCalculationEngine
{
public override bool ProcessBuiltInFunctions => true;
public override void Calculate(CalculationData data)
{
Console.Out.WriteLine($"{data.Cell.Name}={data.FunctionName}");
data.CalculatedValue = "HIT";
}
}
Thank you for providing the use-case and additional details.
Upon initial testing, I have been able to replicate the issue you described. It appears that shared formulas are being ignored in the custom calculation engine.
We need to conduct a comprehensive evaluation of this 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-55980
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.
@weissa
“It may be that these shared formulas are optimized/cached so it’s unnecessary for the custom engine to be queried…”
Yes, for performance consideration, if the inputs for one function are fixed values which will not change for different cells, we do not re-calculate the function while calculating shared formula for cells in the range.
We may consider to add some apis to force the re-calculation for such kind of special requirement later. As workaround, currently you may change your custom function a bit to make it work. For example, you may add one parameter of cell reference for the function:
=RWSUPPRESS( True, “rows”, “hidden”)
may be changed to
=RWSUPPRESS( True, “rows”, “hidden”,A1)
Now because the last parameter is variable for different cells, the custom function will be re-calculated always for them.
We were thinking that the behavior was intentional for performance reasons and that the workaround is helpful. Our current workaround is to scan the workbook for cell.IsSharedFormula= true where the cell.Formula contains custom functions that we always want to process, and then disabling the IsSharedFormula bit for those cells. We can continue to work around this with our unique requirements.
If you do choose to add functionality for processing all formulas (regardless of shared formulas), then perhaps the data.CalculateValue could be pre-set with the cached value in this case. Then the CustomEngine handler can check for data.CalculateValue != null to leverage the cached value while still performing any other logic that the custom engine desires.
Thank you for your input and recommendations. We have recorded them along with your current ticket in our system. We will assess and investigate them. We will keep you informed of any developments or updates (once available).
@weissa
We provide one new method ForceRecalculate(string functionName) for AbstractCalculationEngine to support this requirement. Your implementation may return true for RWSUPPRESS function to make it be recalculated always when calculating shared formulas. The new api will be included into next official version 24.7 which will be released soon.
The issues you have found earlier (filed as CELLSNET-55980) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi