Hello,
A NullPointerException is thrown when a workbook has the following conditions:
- A Workbook Name is defined.
- The Name refers to a formula that uses COUNTIF
- The reference parameter within the COUNTIF formula is to an external Workbook
When the above conditions exist and the Name.getRange() API is invoked, a NullPointerException occurs.
This behavior can be seen in the latest Aspose Cells for Java version 21.10, the attached NameWithExternalFormula.xlsx file and the following Java code:
final String xlFile = [PATH] + "NameWithExternalFormula.xlsx";
Workbook wb = new Workbook(xlFile);
Name name = wb.getWorksheets().getNames().get(0);
System.out.printf("Got Name '%s' which refers to: %s %n",
name.getFullText(), name.getRefersTo());
Range rng = name.getRange(); // NullPointerException thrown here!
System.out.println("Refers To Range: " +
(rng == null ? "none" : rng.getAddress()));
Running the above code should produce a NullPointerException with the following stack:
java.lang.NullPointerException
at com.aspose.cells.zagk.a(Unknown Source)
at com.aspose.cells.zbjk.c(Unknown Source)
at com.aspose.cells.zgo.a(Unknown Source)
at com.aspose.cells.zgo.b(Unknown Source)
at com.aspose.cells.zgo.a(Unknown Source)
at com.aspose.cells.zafg.a(Unknown Source)
at com.aspose.cells.zafg.a(Unknown Source)
at com.aspose.cells.zhm.a(Unknown Source)
at com.aspose.cells.zhe.a(Unknown Source)
at com.aspose.cells.zaew.l(Unknown Source)
at com.aspose.cells.zaew.d(Unknown Source)
at com.aspose.cells.zagu.a(Unknown Source)
at com.aspose.cells.zagu.F(Unknown Source)
at com.aspose.cells.zahb.a(Unknown Source)
at com.aspose.cells.zagu.ab(Unknown Source)
at com.aspose.cells.zagu.a(Unknown Source)
at com.aspose.cells.Name.getRange(Unknown Source)
at com.aspose.cells.Name.getRange(Unknown Source)
Key Observations:
- The defined name
ExternalRefFormularefers to the following formula=COUNTIF([TargetWorkbook.xlsx]Sheet1!$A$1:$B$1,"criteria") - The issue seems to be related to the combination of the
COUNTIFformula and the external reference toA1:B1.
The exception doesn’t happen with other Excel functions such asSUMIForCOUNT. - Even though the “TargetWorkbook.xlsx” file is not provided (and therefore the link is broken) - the exception occurs even when the Workbook does exist.
- This issue seems to be regressive. Running the same code under Aspose Cells for Java version 20.1 does not produce the exception.
Environment Details:
- Aspose Cells for Java 21.10
- Java version 1.8.0_211
- Windows 10 OS (but also reproducible under Linux).
File description in the NameWithExternalFormula.zip (7.3 KB) attachment:
- NameWithExternalFormula.xlsx: Workbook with the conditions described above and used by the code above.
Thank you!