Spilt array formulas are not restored

Aspose.Cells Product Family
1

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

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)

2

@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