Filter formula not updating

Aspose.Cells Product Family
1

I have a workbook that already has a formula in it like so: “=FILTER(A2:A100,A2:A100>5)”
I’m just adding a few bits of data to the file, calculating, and saving.
I expect that when I open “new.xlsx” that the “Values over 5” column show new data since I called the CalculateFormula method, but that doesn’t happen.

The small file I’m using to demonstrate this is attached inside sample.zip
The “actual” and “expected” are shown by the images.

var wb = new Workbook("sample.xlsx");
var ws = wb.Worksheets[0];
ws.Cells["A3"].PutValue(3);
ws.Cells["A4"].PutValue(7);
ws.Cells["A5"].PutValue(10);
wb.CalculateFormula();
wb.Save("new.xlsx");

sample.zip (8.1 KB)
actual.jpg (22.9 KB)
expected.jpg (23.5 KB)

Is there anything I can do to make Aspose.Cells calculate the formula correctly? Thank you.

2

@sngcdev,

Thanks for the template file and screenshots.

Please notice, after an initial test, I am able to reproduce the issue regarding FILTER formula as you mentioned by using your template file. I found Filter formula is not updated. I have logged a ticket with an id “CELLSNET-52090” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

1 Like
3

@sngcdev,

When you update those cells, the formula of FILTER function needs to be re-spilled to new range. For performance consideration, we do not check and do this work for Workbook.CalculateFormula(). Instead, we provide another method to do this separately: Workbook.RefreshDynamicArrayFormulas(). So please refresh the dynamic array formula before calculating and saving. Your code should be like:

ws.Cells["A3"].PutValue(3);
ws.Cells["A4"].PutValue(7);
ws.Cells["A5"].PutValue(10);
//refresh the dynamic array formulas when there are modified cells which may affect them
wb.RefreshDynamicArrayFormulas(true);
wb.CalculateFormula();
wb.Save("new.xlsx");