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

Free Support Forum - aspose.com

A Name with COUNTIF Function and External Reference Produces a NullPointerException

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:

  1. The defined name ExternalRefFormula refers to the following formula =COUNTIF([TargetWorkbook.xlsx]Sheet1!$A$1:$B$1,"criteria")
  2. The issue seems to be related to the combination of the COUNTIF formula and the external reference to A1:B1.
    The exception doesn’t happen with other Excel functions such as SUMIF or COUNT.
  3. Even though the “TargetWorkbook.xlsx” file is not provided (and therefore the link is broken) - the exception occurs even when the Workbook does exist.
  4. 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!

@oraspose,

Please notice, I am able to reproduce the issue as you mentioned by using your template file. I found “NullPointerException” is thrown when a workbook fulfills your mentioned conditions. I have logged a ticket with an id “CELLSJAVA-43969” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

@oraspose,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version within the next few days after we have performed QA and incorporating other enhancements and fixes.

The issues you have found earlier (filed as CELLSJAVA-43969) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi