Spilt array formulas are not restored

Hi,

We use Spill Formula, but it doesn’t recover after RefreshDynamicArrayFormulas and CalculateFormula

We have two sheets:

  1. Sheet3 → Contains table Table1 it contains the data
  2. Sheet4 → User function =@MY_FUNC(1) which returns value All
  3. Sheet4 → Custom entry in Name Manager test_name_range that references a cell with custom function
  4. Sheet4Spill 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);

Spilled formula:

=IF(C2=“All”,FILTER(Table1,Table1[Column6]=“empty”,“1”),FILTER(Table1,Table1[Column6]=C2,2))

C2 → is reference to custom name range test_name_range
test_name_range → is reference cell with custom formula =@MY_FUNC(1)
Table1[Column6] → the data

Cells with Spilled formula before calculations:

1 empty
2 empty
4 empty

Cells with Spilled formula after calculations:

1

We expect Spill formula to recover after CalculateFormula, but we also need to call RefreshDynamicArrayFormulas.

Our research:
If you open output.xlsx file and Sheet4!C2 select formula and press Enter, then the Spill formula will work and fill cells.

input.zip (11.0 KB)
output.zip (9.2 KB)

@aleksandr.l,

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.

1 Like

@aleksandr.l

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);

@amjad.sahi @johnson.shi
Is there a plan for a fix in the upcoming release?

@TsahiB.Datarails,

This is not the with the Aspose.Cells APIs. Please refer to our previous reply to fix the issue:

Let us know your feedback.

Hi @amjad.sahi

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 });

All this is within the same Engine
The advice came from here: UNIQUE Function in Aspsoe - #9 by orik

@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);

Let us know your feedback.

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?

@aleksandr.l

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().

1 Like

Hi @John.He

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.

@aleksandr.l

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.

1 Like