How to find the scope of a named range?

Aspose.Cells Product Family
1

Hi,

I am using Aspose cells 8.1.0
Is there any property in com.aspose.cells.Range that returns me the scope of that named range.
I want to categorize named ranges on the basis of scope.

Thanks,
Jaspreet
2

Hi Jaspreet,

Thanks for your posting and using Aspose.Cells for Java.

Please use Name.getSheetIndex() method to determine if the named range is in Workbook Scope or Worksheet Scope.

If it is 0, then it means it is in Workbook scope. If it is 1, then it means it has a first sheet scope. Similarly, if it is 2 then it means it has second sheet scope and so on.

Please see the following code and its console output. Please check the source Excel file used in this code for your reference.

Java


Workbook workbook = new Workbook(“source.xlsx”);


int count = workbook.getWorksheets().getNames().getCount();


for (int i = 0; i < count; i++)

{

Name name = workbook.getWorksheets().getNames().get(i);


System.out.println("Range Name: " + name.getText());

System.out.println("Workbook Scope: " + (name.getSheetIndex() == 0));

}

Console Output:
Range Name: Sheet1SheetScope
Workbook Scope: false
Range Name: Sheet1WorkbookScope
Workbook Scope: true
Range Name: Sheet2SheetScope
Workbook Scope: false
Range Name: Sheet2WorkbookScope
Workbook Scope: true
Range Name: Sheet3SheetScope
Workbook Scope: false
Range Name: Sheet3WorkbookScope
Workbook Scope: true


3

Hi,


Moreover, please see the following sample code to evaluate other way for workbook scoped and worksheet scoped named ranges in the workbook, you may refer to it for your requirements.
e.g
Sample code:

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Get Worksheets collection
WorksheetCollection worksheets = workbook.getWorksheets();

//Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);

//Get worksheet Cells collection
Cells cells = sheet.getCells();

//Creating a workbook scope named range
Range namedRange = cells.createRange(“A1”, “C10”);
namedRange.setName(“workbookScope”);
//Creating a worksheet scope named range
Range namedRange1 = cells.createRange(“D1”, “F10”);
namedRange1.setName(“Sheet1!local”);
Range [] ranges = worksheets.getNamedRanges();
System.out.println(ranges.length);
for(int i = 0; i<ranges.length; i++)
{
Range range = ranges[i];
if(range.getName().contains("!"))
{
System.out.println("Worksheet scoped named range: " + range.getName());
}
else
{
System.out.println("Workbook scoped named range: " + range.getName());
}
}
//Saving the modified Excel file in default format
workbook.save(“f:\files\out1.xlsx”);


Hope, this helps a bit.

Thank you.