We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

The Name.getRanges() API Produces Unexpected Results

In certain cases, the Name.getRanges() API indicates that a Name has an array of Ranges associated with it - when in fact it does not. The two scenarios where this can be observed is:

  1. When the Name’s “refers to” field has a formula which refers to another Name.
    For example:
  • Suppose the Name “SomeRate” refers to: =1.25
  • Suppose a second Name TestFormula" refers to: =2.68/SomeRate
  • In this case, invoking the following code returns “true” within the ‘if-condition’ and ultimately causes a NullPointerException in the subsequent line (because name.getRanges()[0] returns null):
    if (name.getRanges() != null && name.getRanges().length > 0) {
    string wsname = name.getRanges()[0].getWorksheet().getName();
  • When the Name’s “refers to” field refers to the same name (i.e. the Name is a “self-reference”). For example:
    • Suppose you create a worksheet-level in “Sheet1” named: Sheet1!_Rap1
    • In the “refers to” field, we add the formula: =Sheet1!_Rap1
    • Running the same code described above will also produce a NullPointerException.
    Admittedly, a “self-referenced” Name is uncommon, but it is valid (i.e. Excel permits it). More importantly, the “Name.getRanges()” API should not indicate that the Name has ranges under either scenario.

    The attached Workbook (“SelfRefNames.xlsx”) simulates the errors for both scenarios. You can examine the errors by running the following sample code:

    Workbook wb = new Workbook(“SelfRefNames.xlsx”);
    NameCollection names = wb.getWorksheets().getNames();
    System.out.println(“Names enumeration (” + names.getCount() + " names)");
    System.out.println("* * * * * * * * * *");
    System.out.println(" Name:\t\tVisible:\tScope:\tRefers To:\tRefers To Type:");
    String nameInfo = " %1$s\t%2$s\t%3$s\t%4$s\t%5$s\n";
    for (int i = 0, size = names.getCount(); i < size; i++) {
    Name name = names.get(i);
    // determine the name scope (global|local)
    String scope = null;
    if (name.getSheetIndex() == 0) {
    scope = “GLOBAL”;
    } else {
    scope = “LOCAL: " + name.getFullText().
    substring(0, name.getFullText().lastIndexOf(”!"));
    // determine the Refers To Type (reference|formula)
    String refToType = null;
    if (name.getRange() != null) {
    refToType = “Single Reference”;
    } else if (name.getRanges() != null && name.getRanges().length > 0) {
    if (name.getRanges()[0] != null) {
    refToType = “Multiple Cell Reference”;
    } else {
    // ERROR: name.getRanges().length is > 0 - BUT name.getRanges()[0] is null
    refToType = “ERROR! Invalid name.getRanges()”;
    } else {
    refToType = “Formula”;
    System.out.printf(nameInfo, name.getFullText(), name.isVisible(), scope,
    name.getRefersTo(), refToType);

    Our expectation is that the ‘referToType’ variable should never have the string with the ERROR (as highlighted above.



    Thanks for your posting and using Aspose.Cells.

    We were able to observe this issue by running your sample code with your source excel using the latest version: Aspose.Cells for Java v16.12.1. The code produces erroneous and unexpected results.

    We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

    This issue has been logged as

    • CELLSJAVA-42118 - Name.getRanges() API Produces Unexpected Results

    Here is the console output of your sample code when executed with your provided excel file.

    Console Output
    Names enumeration (8 names)

    Name: Visible: Scope: Refers To: Refers To Type:
    Sheet1!_HC1 true LOCAL: Sheet1 =Sheet1!_HC1 ERROR! Invalid name.getRanges()
    Sheet1!_Rap1 true LOCAL: Sheet1 =Sheet1!_Rap1 ERROR! Invalid name.getRanges()
    Sheet1!_ZM2 true LOCAL: Sheet1 =Sheet1!_ZM2 ERROR! Invalid name.getRanges()
    GlobalName true GLOBAL =Sheet1!$A$7 Single Reference
    Sheet1!LocalName true LOCAL: Sheet1 =Sheet1!$A$9:$A$11 Single Reference
    Sheet1!LocalRef true LOCAL: Sheet1 =Sheet1!$A$1 Single Reference
    SomeRate true GLOBAL =1.25 Formula

    Sheet1!TestFormula true LOCAL: Sheet1 =2.68/SomeRate ERROR! Invalid name.getRanges()


    Thanks for using Aspose.Cells.

    This is to inform you that we have fixed your issue CELLSJAVA-42118 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

    Please try our latest version/fix: Aspose.Cells for Java v16.12.3

    Your issue "CELLSJAVA-42118" should be fixed in it.

    Let us know your feedback.

    Thank you

    The issues you have found earlier (filed as CELLSJAVA-42118) have been fixed in this update.

    This message was posted using Notification2Forum from Downloads module by Aspose Notifier.