Formula is incorrectly modified with @ symbol when using TEXTJOIN & IF with named ranges or sheet references

Aspose.Cells Product Family
1

When saving an Excel file the formula is modified with an @ symbol for TEXTJOIN and IF (not sure if it is isolated to just IF or not).

An example of this is:

“=TEXTJOIN(” “,TRUE,IF(A1=List!$A$1:$A$10000,List!$B$1:$B$10000,”"))"
changes to
“=TEXTJOIN(” “,TRUE,IF(A1=@List!$A$1:$A$10000,List!$B$1:$B$10000,”"))"

I have created a working test case that can reproduce this on the latest version of Aspose.Cells for .NET.

var workbook = new Workbook();
workbook.Worksheets.Add(“List”);

var listSheet = workbook.Worksheets[“List”];

listSheet.Cells[0, 0].PutValue(“AAA”);
listSheet.Cells[0, 1].PutValue(“Value 1”);

listSheet.Cells[1, 0].PutValue(“AAA”);
listSheet.Cells[1, 1].PutValue(“Value 2”);

listSheet.Cells[2, 0].PutValue(“BBB”);
listSheet.Cells[2, 1].PutValue(“Value 3”);

var firstSheet = workbook.Worksheets[“Sheet1”];

firstSheet.Cells[0, 0].PutValue(“AAA”);
firstSheet.Cells[0, 1].Formula = “=TEXTJOIN(” “, TRUE, IF(A1=List!$A$1:$A$10000, List!$B$1:$B$10000, “”))”;

firstSheet.Cells[1, 0].PutValue(“BBB”);
firstSheet.Cells[1, 1].Formula = “=TEXTJOIN(” “, TRUE, IF(A2=List!$A$1:$A$10000, List!$B$1:$B$10000, “”))”;

var savePath = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName, “Temp”, “TextJoin.xlsx”);

workbook.Save(savePath, Aspose.Cells.SaveFormat.Xlsx);

Can you let me know if this is a bug or if there is a valid workaround available?

Thanks

2

@waynegilbert,

Please try our latest version/fix Aspose.Cells for .NET v21.5.6.
Aspose.Cells21.5.6 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.5.6 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.5.6 For .NetStandard20.Zip (5.5 MB)

I have tested using the exact code (see following sample code) and it works fine and the formulas are not changed:
e.g.
Sample code:

var workbook = new Workbook();
        workbook.Worksheets.Add("List");
        
        var listSheet = workbook.Worksheets["List"];

        listSheet.Cells[0, 0].PutValue("AAA");
        listSheet.Cells[0, 1].PutValue("Value 1");

        listSheet.Cells[1, 0].PutValue("AAA");
        listSheet.Cells[1, 1].PutValue("Value 2");

        listSheet.Cells[2, 0].PutValue("BBB");
        listSheet.Cells[2, 1].PutValue("Value 3");

        var firstSheet = workbook.Worksheets["Sheet1"];

        firstSheet.Cells[0, 0].PutValue("AAA");
        firstSheet.Cells[0, 1].Formula = "=TEXTJOIN(\" \", TRUE, IF(A1=List!$A$1:$A$10000, List!$B$1:$B$10000, \"\"))";

        firstSheet.Cells[1, 0].PutValue("BBB");
        firstSheet.Cells[1, 1].Formula = "=TEXTJOIN(\" \", TRUE, IF(A2=List!$A$1:$A$10000, List!$B$1:$B$10000, \"\"))";

        workbook.Save("e:\\test2\\out.xlsx", Aspose.Cells.SaveFormat.Xlsx);

Please find attached the output file as well.

If you still find the issue with latest fix/version, let us know with output file for reference.
files1.zip (7.4 KB)

3

Thanks for getting back to me. I opened your file and it too has the same issue. I have also tried using SaveFormat.PDF in case it was my version of Excel. It did not work with the PDF SaveFormat.

Attached is a screenshot when I open your file in Excel straight away (after allowing editing in Excel).

TextJoin.PNG (42.4 KB)

4

@waynegilbert
It is the behavior of new version of ms excel. Please see the document at Implicit intersection operator: @ - Microsoft Support.
By our test, if set the formula as yours in ms excel manually, ms excel will set it as array formula automatically. So, please set it as array formula by Cell.SetArrayFormula() instead of Cell.Formula.

5

Thanks. That has resolved the issue. I was not aware of the SetArrayFormula method.

For anyone interested, the solution is to set it like this below rather than using the setter Formula:

firstSheet.Cells[0, 1].SetArrayFormula("=TEXTJOIN(" “, TRUE, IF(A1=List!$A$1:$A$10000, List!$B$1:$B$10000, “”))”, 1, 1);