Free Support Forum - aspose.com

Getting #NUM! for a cell

For version 18.4 of aspose-cell liberary.

Getting NAN value for a cell that derives it values from another cell and so on.
For some values, values is fetched correctly, but sometimes it becomes NAN.

Please suggest.

Thanks

@thomas.pietrzykowski,

Thanks for providing us some details.

Could you paste your sample code (runnable) and provide template file (if any) to reproduce the issue on our end, we will check it soon.

By the way, could you call Workbook.CalculateFormula() before retrieving the cell’s value if it makes any difference.

Yes, we are already calling Workbook.CalculateFormula()

Its for a cell that uses the IRR excel formula.

@thomas.pietrzykowski,

As requested earlier, kindly paste your sample code (runnable) and provide your template file to show the issue on our end, we will check it soon.

Please find a sample.zip attached and a java code to read the cell values.

Scenario:

There are a set of value over which we are calculating IRR (Internal Rate of Return).

Aspose is showing the correct IRR thats computed into the cell B1 for the range of values defined over B3:B183

However, we get #NUM! when IRR is computed for the values (A3:A183).

Java Code:

public static void main(String[] args) throws Exception{

    Workbook book = new Workbook("C:/deep/sample.xlsx");

    book.calculateFormula();

    System.out.println(book.getWorksheets().get(0).getCells().get(0,0).getStringValue());


    System.out.println(book.getWorksheets().get(0).getCells().get(0,1).getStringValue());


}

Java Output:

#NUM!
-0.05%

sample.zip (15.1 KB)

@thomas.pietrzykowski,

Thanks for the template file and sample code.

After an initial test, I am able to observe the issue as you mentioned by using your sample code with your template file. I got ‘#NUM!’ value for a cell having IRR function:
e.g
Sample code:

import com.aspose.cells.Workbook; 

public class Main { 

    public static void main(String[] args) throws Exception{ 


        Workbook book = new Workbook("sample.xlsx"); 

        book.calculateFormula(); 

        System.out.println(book.getWorksheets().get(0).getCells().get(0,0).getStringValue());//#NUM! - Not Ok 



        System.out.println(book.getWorksheets().get(0).getCells().get(0,1).getStringValue());//-0.05% - Ok 


    } 
}  

I have logged a ticket with an id “CELLSJAVA-42667” for your issue. We will look into it soon.

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

Thanks for looking into the issue.

We are currently using cell library version 18.4

I hope the fix will be provided for the same version as we do not intend to update the library to another version for now.

@thomas.pietrzykowski,

Well, we provide fixes based on latest APIs set. For example the latest fix version is v18.6.2 currently. So if we fix your issue in the next fix. The fix version would be v18.6.3. The fixes are fully tested for QA and included all the functionality, features and enhancements full fledged. The fixes are up-to-date versions which can be used on production server without any problem.

But we face certain issues when we upgrade from cell library 18.4 to 18.5 or above and we encounter some Null Pointer Exception with our existing templates.

That might require some changes in templates to make them compatible with the new version, however for us its not feasible to change the templates.

@thomas.pietrzykowski,

It is not feasible for us to include fixes in older versions. The fixes are based on the latest APIs set only.

Could you provide details, samples, etc. to reproduce the issue, we will check it and fix it.

We are waiting for the fix urgently as this is a production issue. Can you please let us know by when will it be delivered ?

Issue with Aspose Library 18.5 or 18.6

As mentioned previously, we encounter a Null Pointer Exception for templates that are working absolutely fine with cell library version 18.4 but start giving error with cell library 18.5 and above.

Please find attached a sample excel file.

You can find that the java code runs perfectly fine with cell version 18.4 but gives exception with version 18.6

Java Code:

public static void main(String[] args) throws Exception{

    Workbook book = new Workbook("C:/deep/sample-2.xlsx");

    book.calculateFormula();


}

Java Output - Cell Library 18.4

Process finished with exit code 0

Java Output - Cell Library 18.6

Exception in thread “main” com.aspose.cells.CellsException: java.lang.NullPointerException: null(Based on cell Sheet1!C934)
at com.aspose.cells.zabz.a(Unknown Source)
at com.aspose.cells.zabz.b(Unknown Source)
at com.aspose.cells.zaca.a(Unknown Source)
at com.aspose.cells.zfo.a(Unknown Source)
at com.aspose.cells.zaca.b(Unknown Source)
at com.aspose.cells.zabz.c(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)
at Main.main(Main.java:10)

Process finished with exit code 1

sample.zip (36.0 KB)

@thomas.pietrzykowski,

I 'm afraid the issue “CELLSJAVA-42667” is not resolved yet. Hopefully it will be figured out in the next week or so.

Regarding your issue (“NullPointerException”) with Aspose.Cells v18.6, we will soon evaluate the issue with your file and code and get back to you.

@guptadeeptarun,

After evaluation using your sample code and template file, I am able to observe the issue as you mentioned by using your sample code with your template file (you attached in the previous post). I found an exception “com.aspose.cells.CellsException: java.lang.NullPointerException” when calculating formulas in the workbook. I also confirmed if we use v18.4, it works fine:
e.g
Sample code:

Workbook book = new Workbook("sample-2.xlsx");
    book.calculateFormula();

I have logged a ticket with an id “CELLSJAVA-42669” for your issue. We will look into it soon.

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

@guptadeeptarun,

Please try our latest version/fix: Aspose.Cells for Java v18.6.4

We have fixed your issues (“CELLSJAVA-42667” and “CELLSJAVA-42669”) in it.

Let us know your feedback.

The issues you have found earlier (filed as CELLSJAVA-42667,CELLSJAVA-42669) have been fixed in Aspose.Cells for Java 18.7. Please also see the document for your reference: https://docs.aspose.com/display/cellsjava/Installation