Calculation fails with NPE

Aspose.Cells Product Family
1

Hi,

Using Aspose Cells for Java, version 20.6.5.

Sample code:

System.out.println(CellsHelper.getVersion());
Workbook workbook = new Workbook("demo.xlsx");
workbook.calculateFormula();

Output:

20.6.5
Exception in thread "main" com.aspose.cells.CellsException: java.lang.NullPointerException: null("Based on cell "4!AB7)
	at com.aspose.cells.zaed.a(Unknown Source)
	at com.aspose.cells.zaed.a(Unknown Source)
	at com.aspose.cells.zaee.a(Unknown Source)
	at com.aspose.cells.zaee.b(Unknown Source)
	at com.aspose.cells.zaed.b(Unknown Source)
	at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
	at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
	at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
	   ...

The exception above is thrown after 10 minutes of calculation.

demo.zip (9.5 MB)

2

@TarasTielkes,

Thanks for the template file.

I tested your scenario/ case using your sample code segment with your template file and found the issue as you described. I found the exception after several minutes (10 - 15 minutes) of calculations. I have logged a ticket with an id “CELLSJAVA-43238” for your issue. We will look into it soon.

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

3

@TarasTielkes,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43238”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

@TarasTielkes,

Please try our latest version/fix: Aspose.Cells for Java v20.6.6 (attached)
aspose-cells-20.6.6-java.zip (7.1 MB)

Your issue should be fixed in it.

Let us know your feedback.

5

Hi @Amjad_Sahi,

The issue is indeed resolved in 20.6.6.

Thank you,
Taras

6

@TarasTielkes,

Good to know that your issue is resolved by the new fix/version. Feel free to contact us any time if you have further comments or questions, we will be happy to assist you soon.

7

The issues you have found earlier (filed as CELLSJAVA-43238) have been fixed in Aspose.Cells for Java 20.7. This message was posted using Bugs notification tool by Amjad_Sahi

8

@TarasTielkes,

We recommend you to kindly try using the attached fix.

We have made significant improvement for formula calculation and you should get your template file calculated faster with the new fix.

aspose-cells-20.7.2-java.zip (7.1 MB)

Let us know your feedback.

9

Hi @Amjad_Sahi,

We do indeed see the increased calculation performance for the sample file, which is very welcome.
Can you provide more details on what the previously existing bottleneck was, and for which types of formulas we can expect improved calculaction speed, starting from version 20.7.2?

Thanks in advance,
Taras

10

@TarasTielkes,
We are gathering details and will write back here soon to share our feedback.

11

@TarasTielkes,

There are lots of Search/Match functions in your template file with large dataset, such as SUMIF/COUNTIF/VLOOKUP… For those functions, they need to search different values in the large dataset and requires lots of time. We had made a great effort to optimize the data arrangement and search engine for such kind of formulas these days. With the improvement from 20.7.2, the performance for such kind of template file should be improved too.