CalculateFormula does not calculate properly when formula points to a named range

Aspose.Cells Product Family
1

I have a named range called MyNamedRange which Refers to a string value and not an actual range on a spreadsheet. I.e. ="Test"

I have a formula in cell A1 that points to MyNamedRange. I.e. =MyNamedRange.
After I run CalculateFormula, the value of A1 is 0, when it should be the value of MyNamedRange which in this case is Test.

I have provided a sample workbook to illustrate my problem.

Is this a bug or is this by design? Can this be fixed?

Harry

2

Hi,

Thank you for considering Aspose.

Well, you can use workbook.ReCalcOnOpen = true; to calcualte the formula values on opening the work sheet.

Please see the following Sample code in this regard,
Sample Code:

Workbook workbook = new Workbook();

workbook.ReCalcOnOpen = true;

workbook.Open("C:\\NamedRangeFormula.xls");

Worksheet worksheet = workbook.Worksheets[0];

string a = worksheet.Cells[0].Value.ToString();

workbook.Save("C:\\Sample.xls");

Thank You & Best Regards,

3

workbook.ReCalcOnOpen did not work.

I’ve provided some sample code to explain my problem better.

Basically, I have 2 named ranges. One called MyNamedRange1 that refers to a number value of 5. The second one is called MyNamedRange2 that refers to a string value of Test.

I have 2 cells that have a formula that point to each one of those named ranges.

After I run CalculateFormula, the value in the first cell is 5 which is correct. The value of the second cell is 0 which is not correct. It should be Test.

Let me know if you have any more questions.

Thanks

Harry


Sample Code:
var workbook = new Workbook();

var nameIndex = workbook.Worksheets.Names.Add(“MyNamedRange1”);
var name = workbook.Worksheets.Names[nameIndex];
name.RefersTo = “=5”;
workbook.Worksheets[0].Cells[0, 0].Formula = “=MyNamedRange1”;

nameIndex = workbook.Worksheets.Names.Add(“MyNamedRange2”);
name = workbook.Worksheets.Names[nameIndex];
name.RefersTo = “=“Test””;
workbook.Worksheets[0].Cells[1, 0].Formula = “=MyNamedRange2”;

workbook.CalculateFormula();

Console.WriteLine(workbook.Worksheets[0].Cells[0, 0].Value.ToString());
Console.WriteLine(workbook.Worksheets[0].Cells[1, 0].Value.ToString());
Console.ReadLine();


4

Hi Harry,

Thank you for considering Aspose.

After an initial test, we have found the issue you have mentioned. We will figure it out soon.

Thank You & Best Regards,

5

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells for .NET. We have fixed the issue regarding calculating formulas. Please do let us know if you face any problem.

Thank You & Best Regards,

6

Thanks for the fix. It looks like it works however I think I found a problem that is indirectly related. If i run the following code, I get an exception when I save.

var workbook = new Workbook();

var nameIndex = workbook.Worksheets.Names.Add("Name1");
var name = workbook.Worksheets.Names[nameIndex];
name.RefersTo = "=5";
workbook.Worksheets[0].Cells[0, 0].Formula = "=Name1";

nameIndex = workbook.Worksheets.Names.Add("Name2");
name = workbook.Worksheets.Names[nameIndex];
name.RefersTo = "=\"Test\"";
workbook.Worksheets[0].Cells[1, 0].Formula = "=Name2";

workbook.CalculateFormula();

Console.WriteLine(workbook.Worksheets[0].Cells[0, 0].Value.ToString());
Console.WriteLine(workbook.Worksheets[0].Cells[1, 0].Value.ToString());
Console.ReadLine();

workbook.Save("NamedRangeFormula.AfterCalculate.xml", FileFormatType.SpreadsheetML);

The exception is "Invalid column expression: =Name1". It looks like having a formula point to a named range called Name1 is causing a problem. If I name the range something like MyNameRange, the exception does not happen.

7

Hi,

Thanks for providing us the sample code,

After an initial test, we found the issue you have described. We will figure it out soon.

Thank you.

8

Hi,

Please try the attached version. We have fixed this bug regarding NamedRanges for saving as SpeadsheetML file.

Thank you.

9

The fix appears to be working.

I will do some more thorough testing and let you know if there are any more problems.

Thank You.