Hello;
is there any sample of code to create a range name at the workbook level.
I am trying to use
workbook.NameRanges.Add(...)
but finding various problems with each override of this method.
Also, can you show an example of using a range name in a formula? Both a name defined globally, and a name local to the sheet.
I have tried modifying your WriteFormula sample like this:
CellRange r = sheet.Range["$B$3:$D$3"];
sheet.Names.Add("MyRange", r); //this works
Core.INamedRange nr = workbook.NameRanges.Add("MyGlobal");
nr.RefersToRange = sheet.Range[2, 2, 5, 5];
currentFormula = "=SUM(MyRange)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(Sheet1!MyRange)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(MyGlobal)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula; // this line gives error: 'MyGlobal is not a valid named range'
workbook.CalculateAllValue();
workbook.SaveToFile(@"c:\files\CalculatedFile.xlsx", ExcelVersion.Version2010);
If I continue to save the workbook in xlsx format, and examine the xml in the OOXML content is see this:
<c r="B50" t="str"><f ca="1">SUM(MyRange)</f><v>#NAME?</v></c>
If I reopen the book in Excel, of course Excel recalculates this function - but it seems the RecalculateAllValue in Spire.Xls does not reclculate the range reference correctly.
My aim is to be able to:
1) push some values into named ranges in a workbook
2) Recalculate, so that other values dependent on those names are refreshed
3) export to PDF
So, in this scenario, I must rely on the Spire Calculation engine to update the values correctly ... can you advise or give examples on how best to do this?
with thanks
Brian