CONCAT() formula does not evaluate

[sigisaac]

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

[shakeel.faiz]

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.

• Supported Formula Functions