Hello Aspose team,
We identified an issue where a formula gets falsely identified as Array Formula using method Cell.IsArrayFormula.
You can replicate the issue using the attached sample project. Just replace “YOUR_PATH_HERE” in Program.cs with your local path to the sample workbook file and execute the project. It should display “The cell contains an array formula.” in the console.
AsposeTest.zip (24.9 KB)
Thanks for you assistance on this issue.
@paulo.coe
We got two “True” with the following codes:
Workbook w = new Workbook(dir + "SampleWorkbook.xlsx");
Cell cell = w.Worksheets[0].Cells["A1"];
Console.WriteLine(cell.IsArrayFormula);
Console.WriteLine(cell.IsDynamicArrayFormula);
@paulo.coe,
Moreover, using your exact project (with Aspose.Cells for .NET v24.7) it works as expected. Since the formula in Sheet1!A1 cell is an array formula so it should print “The cell contains an array formula” and rightly so. See the screenshot attached when running your project with your template Excel file.
sc_shot1.png (147.7 KB)
Thanks for the help amjad.sahi,
I’m still failing to understand why this formula is a array formula. Can you explain that to me, please?
@paulo.coe
It is defined as dynamic array formula(new array formula introduced by newer versions of ms office) in your template file. And you may re-save your file as xls format in ms excel, then re-open the xls file, you will find the formula has been shown as legacy array formula(xls does not support the new type of array formula, so ms excel just takes it as legacy array formula).
Thanks for the reply.
Can you share how you check in ms excel to identify the formula being defined as array?
I tried following the steps and re-saved the file to a xls file but couldn’t see the formula being changed? Should the formula be wrapped in brackets in the legacy format file?
Thanks
@paulo.coe,
Generally, in MS Excel, to identify an array formula in Excel, we look for curly braces (“{}”). Array formulas are enclosed within these curly brackets to distinguish them from regular formulas. We will get back to you and provide more details on it.
@paulo.coe
After re-saving the xlsx file to xls in ms excel, we re-open the xls file in ms excel, and the formula in A1 is shown as attached screenshot. It is just the format of legacy array formula.
SampleWorkbook_xls.png (5.7 KB)
I understand that now.
Thanks for the help confirming this information, Aspose team.
@paulo.coe
You are welcome. If you have any questions, please feel free to contact us at any time.