I am using “Aspose.Cells” (UserId: 754903 of the license) for excel related functionalities in .net. For example I am using “=SORT(FILTER(Heatmap!$A$2:$A$2000,(Heatmap!$R$2:$R$2000)=“Yes”),1)” and it is converting into “{=SORT(FILTER(Heatmap!$A$2:$A$2000,(Heatmap!$R$2:$R$2000)=“Yes”),1)}”. I have tried the formula from the template level and also from the code level using “SetArrayFormula”. Every time I have to select and press enter manually to get the result. I have also tried “CalculateFormula”, but no changes.
Please suggest me a way out so that every time the formula calculates automatically, no need to press enter manually.
@amjad.sahi Sorry, I won’t be able to share the actual template for security reasons. But I am sharing a dummy report with the same scenario (after generating). Please find the attachment and please refer to the A1 of “Filter” sheet. This is what I am getting after generating the report. I have tried the formula both from the template and code end, but the result is same. Also tried Workbook.RefreshDynamicArrayFormulas(true, CalculationOptions) as you mentioned. But still the result is not coming automatically, I have to select and press a enter manually to get the result.
As @johnson.shi replied earlier, you need to use Cell.SetDynamicArrayFormula to set the formula. We can obtain the correct results through the following code. Please refer to the attachment (9.1 KB).
The sample code as follows:
Workbook wb = new Workbook(filePath + "Test.xlsx_panditg_20230920201853.xlsx");
Cell cell = wb.Worksheets[0].Cells[0, 0];
cell.SetDynamicArrayFormula("=SORT(FILTER(Raw!$A$2:$A$2000,(Raw!$B$2:$B$2000)=\"Y\"),1)",
new FormulaParseOptions(), true);
wb.RefreshDynamicArrayFormulas(true, new CalculationOptions());
wb.CalculateFormula();
wb.Save(filePath + "out_net.xlsx");
You’re welcome. It’s nice to know that your issue has been sorted out. Please feel free to write us back if you have any further queries or comments. We’ll be happy to assist you soon.