Does Aspose.cells java support Excel dynamic array formula calculations?

Aspose.Cells Product Family
,
1

Hi,
I am using aspose cell 23.8 for java. I am using Workbook.calculateFormula() to calculate all formulas in my workbook. I have this dynamic array formula:

=IFERROR(CHOOSECOLS(FILTER(Table1[[Scenario ID]:[KeyComment]],(Table1[Scenario ID]=J$3)(Table1[Excluded for all]=0)(Table1[Excluded]=1)),{2,6}),“”) in cell A1

Which basically filters Table1 based on certains conditions and an id and copies all rows from the Table1 that match the condition.
However when I generate the report from my code. Only one cell is populated, it is expected a whole array of table to be populated from cell A1. This formula works fine in Excel.

Does aspose support calculation of these dynamic array formulas? Let me know if you have any other solutions.

Thanks,
Vineet K.

2

@vineetskothari,

Yes, Aspose.Cells does support to set and calculate dynamic array formulas. Please call Workbook.refreshDynamicArrayFormulas method just before workbook.calculateFormula() to refresh/spill the dynamic formulas to proper cell ranges accordingly. Moreover, to set dynamic array formula in code via Aspose.Cells. please use Cell.setDynamicArrayFormula method to set it.

Let us know with details and sample files if you still find any issue or other queries.

1 Like
3

Thanks! that works!

4

@vineetskothari,

Good to know that it works for your needs. Feel free to write us back if you have further queries or comments, we will bee happy to assist you soon.