'getNamedRanges' API doesn't return all named ranges

Aspose.Cells Product Family
1

Hello,


I am using Aspose.Cells v17.5 to retrieve all named-ranges within the attached excel file using ‘getNamedRanges’ API. This API doesn’t return all named-ranges. There is a named-range - ‘Print_Area’ in the excel but it isn’t present in ranges returned by this API.

Following code is used to retrieve the named-ranges.

Workbook workbook = new Workbook(“test.xlsx”);
WorksheetCollection worksheetCollection = workbook.getWorksheets();
Range[] rangeCollection = worksheetCollection.getNamedRanges();

Can you please look into this.

Thanks,
Neha
2

Hi,


Thanks for your posting and using Aspose.Cells.

When you do not get expected results from getNamedRanges() method, you should try the getNames() method.

Please see the following sample code and its console output for your reference. As you can see it prints all the names (named ranges) of your workbook including print area named range.

Java
Workbook workbook = new Workbook(dirPath + “test.xlsx”);

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

int count = nms.getCount();

for(int i=0; i<count; i++)
{
Name nm = nms.get(i);
System.out.println(nm);
}

Console Output
Aspose.Cells.Name [ _Example; ReferTo:=Variables!$B$1]
Aspose.Cells.Name [ _Look; ReferTo:=Variables!$B$4]
Aspose.Cells.Name [ _Series; ReferTo:=Variables!$B$3]
Aspose.Cells.Name [ _Shading; ReferTo:=Variables!$B$2]
Aspose.Cells.Name [ Changedtemp1; ReferTo:=‘Balloon Payment Loan’!$B$2:$F$8]
Aspose.Cells.Name [ DATA_01; ReferTo:=#REF!]
Aspose.Cells.Name [ DATA_02; ReferTo:=‘Balloon Payment Loan’!$F$5:$F$8]
Aspose.Cells.Name [ IntroPrintArea; ReferTo:=#REF!]
Aspose.Cells.Name [ Look1Area; ReferTo:=#REF!]
Aspose.Cells.Name [ Look2Area; ReferTo:=#REF!]
Aspose.Cells.Name [ Look3Area; ReferTo:=#REF!]
Aspose.Cells.Name [ Look4Area; ReferTo:=#REF!]
Aspose.Cells.Name [ Look5Area; ReferTo:=#REF!]
Aspose.Cells.Name [ PRINT_AREA; ReferTo:=‘Balloon Payment Loan’!$B$2:$F$15]
Aspose.Cells.Name [ TamplateChanged; ReferTo:=‘Balloon Payment Loan’!$H$6]
Aspose.Cells.Name [ TemplatePrintArea; ReferTo:=‘Balloon Payment Loan’!$B$2:$F$15]
Aspose.Cells.Name [ F151SUM; ReferTo:null]
3

Hello,


Thanks for your reply. But we have a few concerns.

What is the difference between getNamedRanges() API and getNames() API?

I used getNames() API, but it returns names which are not even present in the workbook (Please see the attached screenshot).

We rely on getNamedRanges() API to know which named-ranges are present in Excel. Since getNames() API is returning extra names, we can not use it.

Is there any API that returns exactly those names which are defined in the excel?


Thanks,
Neha
4

Hi,


Thanks for your posting and using Aspose.Cells.

Please use the Name.isVisible() property to know whether the Name is visible inside the MS-Excel or not.

Please try the following code and it should fit your needs. Let us know your feedback.

Java
Workbook workbook = new Workbook(dirPath + “test.xlsx”);

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

int count = nms.getCount();

for(int i=0; i<count; i++)
{
Name nm = nms.get(i);
if(nm.isVisible())
System.out.println(nm);
}