Formula calculation using concatenate and vlookup bug

Aspose.Cells Product Family
1

I have a cell containing a formula concatenating the result of a vlookup and a string together and i have found a scenario where aspose and excel calculate its value differently. Consider the following example:

| A | B | C |
1 | 1 | | 1 |
2 | =CONCATENATE(VLOOKUP(1,myrange,1), " %") | | |
3 | | | |

the column c is in a named range called “myrange”.

Now, if i run the following example bit of code:

using System;
using System.Data;
using Aspose.Cells;

namespace ConsoleApplication1
{
///
/// Summary description for Class1.
///
class Class1
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main(string[] args)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“C:\Aspose.lic”);

Excel excel = new Excel();
excel.Open(“C:\basic.xls”);

string formula1 = excel.Worksheets[0].Cells[1,0].Value.ToString(); // this line returns ‘1 %’

excel.CalculateFormula(true);

string formula2 = excel.Worksheets[0].Cells[1,0].Value.ToString(); // this line returns ‘#value!’

Console.WriteLine(string.Format("{0} - {1}", formula1, formula2));
}
}
}

the line written to the console is:

"1 % - #VALUE!"

Why do i get the “#VALUE!”?

Interestingly, if i remove the concatenation from the formula it works. I’ve included my excel file if that helps.

paul

2

It’s a bug in Concatenate function. Please try this attached fix.

3

Cheers,

that did it.

paul