We use Spill Formula, but it doesn’t recover after RefreshDynamicArrayFormulas and CalculateFormula
We have two sheets:
Sheet3 → Contains table Table1 it contains the data
Sheet4 → User function =@MY_FUNC(1) which returns value All
Sheet4 → Custom entry in Name Manager test_name_range that references a cell with custom function
Sheet4 → Spill formula using =FILTER(…) based on Table1 and cell with test_name_range
public class MyEngine : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
switch (data.FunctionName)
{
case "MY_FUNC":
data.CalculatedValue = "All";
break;
}
}
}
var input = @"C:\input.xlsx";
var output = @"C:\output.xlsx";
var workbook = new Workbook(input);
// CHANGING DATA IN workbook.Worksheets[0]
// WE NEED TO USE THIS.
workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions { Recursive = true });
// WE NEED TO USE THIS
var opts = new CalculationOptions { CustomEngine = new MyEngine() };
workbook.CalculateFormula(opts);
workbook.Save(output);
Thanks for the template Excel file, sample code snippet and details.
After initial testing, I am able to reproduce the issue as you mentioned by using your template Excel file and sample code snippet. I found spilt array formulas are not restored after formula calculations.
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-58647
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.
Because the split array formula also references to your custom function/references, so please use CalculationOptions with custom engine too for Workbook.RefreshDynamicArrayFormulas():
// WE NEED TO USE THIS
var opts = new CalculationOptions { CustomEngine = new MyEngine() };
workbook.RefreshDynamicArrayFormulas(true, opts);
workbook.CalculateFormula(opts);
Are you suggesting to do RefreshDynamicArrayFormulas in the same Engine where we process custom functions? If so, we are already doing that.
We call RefreshDynamicArrayFormulas in Engine itself.
That is, we do this in our Engine:
var opts = new CalculationOptions
{
// Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
CustomEngine = this
};
_workbook.CalculateFormula(opts);
...
_workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions() { Recursive = true });
@aleksandr.l
Please refer to the following sample code.
var opts = new CalculationOptions
{
// Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
CustomEngine = this
};
_workbook.RefreshDynamicArrayFormulas(true, opts);
_workbook.CalculateFormula(opts);
We cannot swap the order of the calls _workbook.CalculateFormula(opts); and _workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions() { Recursive = true });
The thing is, we have a lot of logic between these two calls and the order is fixed: first CalculateFormula then RefreshDynamicArrayFormulas
Are you suggesting using a common CalculationOptions for these two calls?
RefreshDynamicArrayFormulas will not throw exception in your case if you pass CustomEngine = this to it?
I will check the method you suggested, but unfortunately we cannot change our flow.
Are there any other solutions, or is it possible to call RefreshDynamicArrayFormulas twice, first before CalculateFormula and then after?
The cause of the issue in your case is that there are custom functions which require the custom engine to calculate them correctly. So, custom calculation options with the custom engine is required anyways. For the calling of those methods, you are free to call them anywhere repeatedly if performance is not a problem for you. So, for your last question the answer is yes. However, to get the correct result, you should provide your custom engine when calling those methods.
Generally when you changed the data in the workbook(which may be used by some formulas so those formulas’ result may be changed too), those two methods need to be called. And because the referenced data changed, for dynamic array formula its spilled range may be changed too. For such situation RefreshDynamicArrayFormulas needs to be called so the formula can be spilled into the correct range. And because this operation(refreshing dynamic array formulas) itself also may changed the cells data in the workbook, so after this operation, other formulas also need to be re-calculated. That’s why Workbook.CalculateFormula() are required after RefreshDynamicArrayFormulas().
Thank you for explaining. We are trying to call RefreshDynamicArrayFormulas before CalculateFormulas and after run RefreshDynamicArrayFormulas and it works for us.
The solution has overhead costs, but it is a working option.
Thank you for your feedback. We are glad that your issue got solved. You can keep using this solution, and feel free to contact us when you get any problem with using apis of our component and we will be glad to provide assistance.