Sheetscope & Workbook scope Ranges

Aspose.Cells Product Family
1

Dear Team,

We have requirement like the workbook has a number of Sheetscope & Workbook scope named ranges. Now the requirement is we need to make a workbook scope range to sheet scope.

I tried renaming it to <>!RangeAddress but this is not working. Can you please suggest how this can be done using Aspose.cells.

Thanks
Anish

2

Hi,


I think you may remove your workbook scoped named range first (e.g by using NameCollection.Remove() method) and then recreate your worksheet scope named range accordingly for your needs. This process /behavior is same as MS Excel. For your information both workbook and worksheet scoped named ranges can exist with the same name in the workbook.

And see the topic on how to create different kinds of named ranges:
http://www.aspose.com/docs/display/cellsnet/Create+Workbook+%28Global%29++and++Worksheet+Scoped+Named+Ranges


Thank you.
3

Hi ,

Thanks for the reply. I need to know is it possible to find a specific workbook scope range and delete only that one.

one more question how can I identify a Range whether its having sheetscope or workbook scope. I am using Workbook.Worksheets.Names to get the names collection

I tried the following

workbook.Worksheets.remove("Test")

Range = workbook.Worksheets(0).cells.CreateRange("A10:B10")

Range.Name = workbook.Worksheets(0).Name + "!" + "Test"

Also if we remove the range will the formulas refering it get affected.

Thanks
Anish

4

Hi,


Please see the sample code with comments for your reference for your needs.

Sample code:
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(“e:\test2\sheetwb1.xlsx”);
//Get All Named RangeCollection in the workbook
Range[] range = workbook.Worksheets.GetNamedRanges();

//Note: If a range name cotains “!” sign, it means it is local or worksheet scoped range.
// If the name do not involve “!” sign, it will be global or workbook scoped range.
MessageBox.Show(range[0].Name);
MessageBox.Show(range[1].Name);

//This is also fine.
MessageBox.Show(workbook.Worksheets.Names[“wbsc1”].RefersTo);//Get the workbook scroped named range’s reference formula
MessageBox.Show(workbook.Worksheets.Names[“Sheet2!worksheetsc”].RefersTo);//Get the worksheet scroped named range’s reference formula

// want to list all the ranges
Range[] ranges = workbook.Worksheets.GetNamedRanges();
foreach (Range unRange in ranges)
{
Debug.WriteLine(unRange.Name);
}

// Use your own code to remove any range.


Regarding formula updation, Aspose.Cells should work the same way as MS Excel, if you find any difference, kindly let us know with details, sample code and Excel files here, we will check it soon.

Thank you.