Problem due to Yield formula while converting excel to PDF

Aspose.Cells Product Family
1

Hi,

I am using “YIELD” & “PRICE” formula in my excel cells and i am populating the excel in run time using aspose-cells-7.4.2.jar and trying to convert the excel into PDF after recalculating the formula. Am facing number exception if the calculated yield is more than 100 while saving it as pdf.

Please help me in resolving at the earliest

With Regards,
Nagaraj.A

2

Hi Nagaraj,

Thank you for using Aspose products, and welcome to Aspose.Cells support forum.

In order to evaluate your presented issue, we require your template file as well as your code snippet to reproduce the problem on our end. Before you provide your sample for further evaluation, please give a try to latest version of Aspose.Cells for Java 7.6.0.5 on your end, and see if this makes any difference.

3

Hi Babar,
I’ve attached the sample file and the code snippet that i’ve used. I’ve also tried using the latest jar that you’ve mentioned, am getting the same error in that.

I’ve tried setting the value in different ways but getting error when i use the calculate formula method. Please help to resolve the same

Regards,
Nagaraj A

4

Hi Nagaraj,

Thanks for your posting and using Aspose.Cells for Java.

We were able to reproduce this exception using the following code with your source file. We have logged this issue in our database for investigation. We will look into it and resolve this issue. Once, the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40659.

Java


System.out.println(“Initiatiing…”);

Long starMS = new Date().getTime();

File file = new File(“F:\Shak-Data-RW\Downloads\PrintPDF\Sample.xls”);

File outFile = new File(“F:\Shak-Data-RW\Downloads\PrintPDF\Opt1x.pdf”);

FileInputStream fis = new FileInputStream(file);

Workbook wb = new Workbook(fis);

FileOutputStream fos = new FileOutputStream(outFile);

wb.getWorksheets().get(0).getCells().get(0, 1).setValue(110.2);

// wb.getWorksheets().get(0).getCells().get(0, 1).setValue(Double.parseDouble(“110.2”));

//Cell cl = wb.getWorksheets().get(0).getCells().get(0, 1);

// cl.setFormula("=VALUE(110.2)");

wb.calculateFormula();

wb.save(fos,FileFormatType.PDF);

fos.flush();

fos.close();

System.out.println(“Printed…”);


Exception:
Exception in thread "main" com.aspose.cells.CellsException: com.aspose.cells.nm cannot be cast to java.lang.Double
Error in calculating cell D12 in Worksheet Sample
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at ClsAsposeTest.f1(ClsAsposeTest.java:73)
at ClsAsposeTest.main(ClsAsposeTest.java:29)
5

Hi Shakeel,
Thanks for considering the issue for fix. We are currently using the version aspose-cells-7.4.2, hope the patch will also contain the features available in this version.

Regards,
Nagaraj A

6

Hi Nagaraj,

Thanks for your posting and using Aspose.Cells for Java.

Yes, the newer fix will also include the features of older version. The newer fix does not break the features of older version. Once, we will have a fix for your issue, we will let you know asap.

7

Hi Shakeel,
It will be really helpful if you can let us know when the fix can be expected. This particular YIELD fix is required for our critical business logic.

Regards,
Nagaraj

8

Hi Nagaraj,

Thanks for your posting and using Aspose.Cells.

We have logged your comments and requested the development team to provide some ETA or fix for this issue. Please spare us some time. Once, we will have some fix or update for you, we will let you know asap.

9

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for Java v7.6.1.1 and let us know your feedback.

10

Hi Shakeel,
Thanks for the fix, we are able to generated the PDF with Yield formula but noticed that it is failing if the calculated yield value is negative. Please help to check once from your end if the observation is correct and also help us if it can be corrected. I’ve attached the sample once again with updated test value for reference.


Exception in thread “main” com.aspose.cells.CellsException: com.aspose.cells.nm cannot be cast to java.lang.Double
Error in calculating cell D14 in Worksheet Sample
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at PrintPDF2.main(PrintPDF2.java:26)


Regards,
Nagaraj

11

Hi Nagaraj,

Thanks for your feedback and using Aspose.Cells.

You are right. We were able to observe this issue. It is failing if the calculated yield value is negative. We have reopened this issue and logged your comment. We will look into it and resolve this issue. Once, it is resolved or we have some other update for you, we will let you know asap.

12

Hi Shakkel,
In addition to that we also observed that there is change in the calculated yield value in excel and generated PDF in certain cases. I’ve attached the updated sample showing the different yield value.

in Excel cell H11, calculated yield value is 33.3105% whereas in the pdf it is 33.3126

Regards,
Nagaraj A

13

Hi Nagaraj,

Thanks for your feedback and using Aspose.Cells.

The calculated value of Yield function is wrong in the output PDF generated from the source file after workbook.calculateFormula(). We have logged this issue in our database. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

The calculated value in cell H11 should be 33.3105%
but in the output pdf it is 33.3126%

This issue has been logged as CELLSJAVA-40665.

We have attached the source file and the output pdf for a reference. Below is a sample code we used for testing.

Java


String path = “F:/Shak-Data-RW/Downloads/PrintPDF/Sample2.xls”;


Workbook workbook=new Workbook(path);


workbook.calculateFormula();


workbook.save(path + “.out.pdf”, SaveFormat.PDF);


14

Hi Shakeel,
Thanks for the quick update.
For the negative yield issue which i’ve mentioned in previous post you’ve update that it has been reopened, please let us know if that fix will also be included in this issue.

Regards,
Nagaraj A

15

Hi Nagaraj,

Thanks for your posting and using Aspose.Cells.

One of your issue has been reopened and other has been logged separately. However, it is linked with the older issue as a related issue. We will try to resolve both issues in next fix. Once, there is some news for you, we will let you know asap.

16

Hi Shakeel,
Can you let us know on the update for the issue. As i’ve mentioned in one of my post above, this function is needed for our critical business logic so we are waiting for the fix.

Regards,
Nagaraj A

17

Hi Nagaraj,

Thanks for using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comments in our database against this issue. Please spare us some time. Once, there is some update or fix for you, we will let you know asap.

18

Hi Shakeel,
Kindly let us know if you’ve any updates or ETA available from the dev team on the issue fixes.

Regards,
Nagaraj A

19

Hi Nagaraj,

Thanks for your posting and using Aspose.Cells.

Currently, there is no update for you. However, the work on this issue is in progress. Once, there is some update or fix for you, we will let you know asap. We have also logged your comments in our database against these issues.

20

Hi,


Please try our latest version/fix: Aspose.Cells for Java v7.6.1.2 , your issue “CELLSJAVA-40659” is fixed in it.

Let us know your feedback.

Thank you.