Access List of Named Ranges[] in Sheet Scope in Excel file using C#.NET

Aspose.Cells Product Family
1

Hi,

How to access the List of Named Ranges[] in sheet scope.

Please provide me some sample code.

Thanks & Regards,
Saravanan Mani

2

In Aspose 8.3.2 version

3

Hi,


Well, you may try to use Name.getFullText() method to evaluate if it is workbook scoped or worksheet scoped named range. Generally the name of a worksheet is attached to the named range’s label that is worksheet scoped with a “!” character, so you may check if the name contains “!” in its full name, see the sample code below for your reference:
e.g
Sample code:

String filePath = “bk_accesslist1.xlsx”;


Workbook workbook = new Workbook(filePath);


NameCollection col = workbook.getWorksheets().getNames();


for (int i = 0; i < col.getCount(); i++)

{

Name nm = col.get(i);

if(nm.getFullText().indexOf("!")!=-1)
{
System.out.println(“Worksheet Scoped Named Range”);
System.out.println("Name: " + nm.getText());
System.out.println("Refers To: " +nm.getRefersTo());

}


}
Hope, this helps a bit.

Thank you.
4

Hi,

Currently i am having one workbook in that workbook i am adding worksheet.

Example:

workbook: Arrival and depature Two worksheet are present. While adding the sheet i need to fetch depature sheet scope Name ranges[].



// adding a new sheet
Worksheet tempSheet = worksheets.add(sheetName);
try
{
tempSheet1.copy(this.fetchTemplateSheet(factorLevel));
}
catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
// fetching the named ranges in created sheets for renaming
Range[] namedRanges = tempSheet.getWorkbook().getWorksheets().getNamedRanges();
//Above mentioned code is bringing all the ranges present in the workbook. but i need specfic sheet named ranges.


for (Range namedRange : namedRanges)
{
// renaming ranges as __
namedRange.setName(NameResolver.getPhysicalRangeName(sheetName, namedRange.getName()));
// namedRange
}

return tempSheet;
Please help me on this. Please provide me contact number. so that i can explain my secanrio.


Thanks & Regards,
Saravanan Mani



5
Hi,

saravanan mani:

..............
Example:

workbook: Arrival and depature Two worksheet are present. While adding the sheet i need to fetch depature sheet scope Name ranges[].
........


It is easy to browse a specific worksheet (departure in your case) scoped named ranges, see the updated sample code below for your reference:

e.g
Sample code:

String filePath = "Book1.xlsx";


Workbook workbook = new Workbook(filePath);


NameCollection col = workbook.getWorksheets().getNames();


for (int i = 0; i < col.getCount(); i++)

{

Name nm = col.get(i);

if(nm.getFullText().indexOf("!")!=-1)
{
//Retrieve the departure (sheet) scoped named ranges only
if(nm.getFullText().toLowerCase().contains("departure"))
{
System.out.println("Worksheet Scoped Named Range");
System.out.println("Name: " + nm.getText());
System.out.println("Refers To: " +nm.getRefersTo());
}
}


}