Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Mar 06, 2012 3:33 am

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

brian@softwords.com.au
 
Posts: 1
Joined: Sat Mar 03, 2012 8:35 am

Wed Mar 07, 2012 11:05 am

Hi Brian,

Thanks for evaluating Spire.XLS.

I am so sorry for that Spire.XlS doesn't support to use define name in formula. But you can use named ranges anywhere else but formula. I attached you a demo illustrates how to convert xls including formula to PDF and use named ranges in somewhere else. Please have a look.

Hoping this can be helpful for you.

In case of any ambiguity,please feel free to contact us.

Have a nice day.

BR
Suvi
e-iceblue support
Attachments
389NameRanges.zip
(44.95 KiB) Downloaded 529 times
User avatar

Suvi.Wu
 
Posts: 154
Joined: Thu Oct 20, 2011 2:53 am

Return to Spire.XLS

cron