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