is array formula slowing down the aspose.cells calculation?
Formula like
(A1=sheet2![col-1])*(A2=sheet2![col-2])
Using array formulas in Aspose.Cells can potentially slow down calculations, especially if they are dynamic and depend on other cells or ranges. When you perform operations like inserting or deleting ranges, the library does not automatically refresh dynamic array formulas, which can lead to performance issues if these operations are frequent.
To manage this, you can manually refresh dynamic array formulas after such operations using the RefreshDynamicArrayFormulas method. This method allows you to control when the formulas are recalculated, which can help improve performance. For example, instead of calling CalculateFormula() directly after making changes, you can use:
workbook.RefreshDynamicArrayFormulas(true);
workbook.Save(fileName);
This approach ensures that only the necessary formulas are recalculated, rather than recalculating all formulas in the workbook, which can be resource-intensive.
If you need to ensure that all dependent formulas are recalculated, you can use the overloaded version of RefreshDynamicArrayFormulas that accepts CalculationOptions, where you can set the Recursive property to true. This will ensure that all related formulas are recalculated as needed.
In summary, while array formulas can slow down calculations, managing their refresh behavior can help mitigate performance issues.
Sources:
[1]: UNIQUE Function in Aspsoe
Generally the normal array formula(legacy array formula) should not cause performance issue when comparing with normal formula, unless the array formula requires complex(such as recurse references) calculations with large dataset. If you are facing performance issue for array formula, please provide us your template file and runnable code to reproduce the issue, we will look into it and try to provide possible solution.