CalculateFormula is not working with TextJoin

Aspose.Cells Product Family
1

Have tried with Aspose.Cells 8.2.2 and 21.6.
I have a spreadsheet with a TextJoin formula in it
image.png (5.8 KB)
and others performing the same calculation in a more simple manner
image.png (5.9 KB)

I call worksheet.CalulateFormula(true) when reading the file.
The fields with TEXTJOIN in have an error, while the simple approach is calculated fine.

image.png (4.2 KB)

I have tried the “fix” in this post where someone appears to be having problems with CalculateFormula in Aspose 20 Aspose cell CalculateFormula of workbook not works - #44 by davidfalck without success.

2

@peter.purnell,

Thanks for the screenshots.

Please note, TEXTJOIN is a new function, which is only available in Excel 2019 and greater versions. That’s why you might not use/calculate the formula with older version of Aspose.Cells that you have. Please try to upgrade to newer versions of the APIs which does support the formula, see the supported formulas list for your reference. If you find any issue with latest version (Aspose.Cells v23.3), please share your sample Excel file, we will check it soon.

If you still want to stick with your older version(s), you may try to use CONCATENATE function or the “&” operator instead.

3

So it is expected that Aspose 21 does not support formulas in Excel 2019?
Here is a zip file containing the xlsx file I am trying to open
Test-Calc - Copy - Copy.zip (7.1 KB)

4

@peter.purnell,

Although we introduced TEXTJOIN formula support a couple of years ago, but we recommend to use latest version of the APIs. Also, we cannot evaluate your issue based on older version that you are using. All the enhancements and fixes are based on latest APIs set only. Kindly upgrade to and try using latest version. I tested your scenario/case using our latest version and it works fine. Here is sample code that I am using with latest version/fix for your reference:
e.g.
Sample code:

// Open the existing file.
Workbook workbook = new Workbook("g:\\test2\\Test-Calc - Copy - Copy.xlsx");

// Obtaining the reference of the first worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

worksheet.CalculateFormula(new CalculationOptions(), true);
Console.WriteLine(worksheet.Cells["E2"].StringValue);//1,2,3
Console.WriteLine(worksheet.Cells["E3"].StringValue);//Banana,1,12,12