CONCAT() formula does not evaluate

Aspose.Cells Product Family
1
Hello Aspose,

I'm working with a spreadsheet that uses the =CONCAT() function as in
=CONCAT("'",A1,"',",A2,"'")

However the cell that uses this formula does not get evaluated by Workbook.CalculateFormula(). Other functions such as =SUM() don't seem to have this problem.

Here is a short test program to demonstrate this:

private void TestConcatenate()
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

workbook.Worksheets.Add();
var sheet = workbook.Worksheets[0];

// This works
sheet.Cells[0, 0].Value = 1;
sheet.Cells[1, 0].Value = 2;
sheet.Cells[2, 0].Formula = "=SUM(A1:A2)";
workbook.CalculateFormula();
string result = sheet.Cells[2, 0].Value.ToString(); // result is "3"
System.Diagnostics.Debug.Assert(result == "3");

// This does NOT work
sheet.Cells[0, 0].Value = "Dogs";
sheet.Cells[1, 0].Value = "Cats";
sheet.Cells[2, 0].Formula = "=CONCAT(A1,\" and \",A2)";
workbook.CalculateFormula();
result = sheet.Cells[2, 0].Value.ToString(); // result is "#NAME?"
System.Diagnostics.Debug.Assert(result == "Dogs and Cats");
}

Is there a reason that this does not work and is there a simple workaround? More generally, is there a list of Excel functions that do not get evaluated by Aspose?

Many thanks,
Sig Isaac
2

Hi,


Thanks for your posting and using Aspose.Cells.

Please use CONCATENATE instead of CONCAT. The following line of code works fine.

sheet.Cells[2, 0].Formula = “=CONCATENATE(A1," and ",A2)”;

Please check this document for supported list of functions by Aspose.Cells.