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:
- 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();
}
- 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.