CONCAT() formula does not evaluate

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

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.