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

Free Support Forum - aspose.com

Get string value and rounding problem

Aspose.Cells Product Family
#1

Hi,

getStringValue have a problem with rounding some specific numbers.
(version: aspose-cells-8.7.2)

Example :
//In excel file there is only one sheet with one value 40.15 and exel round this number to 40.2
<pre style=“font-family: “Courier New”; font-size: 9pt; background-color: rgb(255, 255, 255);”>Workbook loadWorkbook = new Workbook(excelPath);
String test = loadWorkbook.getWorksheets().get(0).getCells().get(0,0).getStringValue();
System.out.print(test);
// The result code above was <b style=“font-family: “Courier New”; font-size: 9pt;”>40.1
<pre style=“font-family: “Courier New”; font-size: 9pt; background-color: rgb(255, 255, 255);”>When I tried to process this same file with your .Net library from nuget the result was correct.<pre style=“font-family: “Courier New”; font-size: 9pt; background-color: rgb(255, 255, 255);”>
<pre style=“font-family: “Courier New”; font-size: 9pt; background-color: rgb(255, 255, 255);”>Regards
#2

Hi Bartlomiej,


Thank you for contacting Aspose support.

I believe you have some function applied to your cell which is causing the rounding of the said value in Excel. Please share your sample spreadsheet so we could test the scenario on our side while using the latest version of Aspose.Cells for Java 8.8.2.7 (attached) or you can test it yourself and let us know your feedback.

Please note, if the problem is resolved in latest major release of Aspose.Cells for .NET, it should be automatically resolved in its equivalent or higher versions of Aspose.Cells for Java API.
#3
Thanks for your immediate answer. Below there is spreadsheet used during tests.

https://drive.google.com/file/d/0Bz0Xwh1kSJ_lYVJISktEQllfWm8/view?usp=sharing

Please let me know did the problem is solved in latest version.
#4

Hi,


Thanks for the template file.

I have tested your scenario/ case using your sample code with your provided file with our latest version/fix: Aspose.Cells for Java v8.8.2.7 and it works fine and as expected. It gives me “40.2” value which is correct. So, kindly try our latest version/fix: Aspose.Cells for Java v8.8.2.7.

Thank you.
#5

Unfortunately problem’s still exist. I checked it with recommended by you version and with 40.15 It still round me it to 40.1.


Link with Print Screen :
#6

Hi,


Well, now that is strange. It works fine using the latest version/fix (v8.8.2.7) on my pc (OS: Windows 8, JDK version: 1.7.x, etc.). I simply compile and run the sample code (in a simple “.java” file) on console (command prompt). I print the version details and string value (of the cell) which is fine, see the screenshot here:
http://prntscr.com/birzma

Could you also print the version number that your application is using for confirmation. If you still find the issue, kindly provide us your environment details, we will check it soon.

Thank you.
#7

Hi,

I just checked this and your right, the problem is connected different jdk version. We’re using jdk 1.8 and the operation result is incorrect, but this same program running with jdk 1.7 is working.

Test with jdk 1.7

Test with jdk 1.8
#8

Hi,


Thanks for providing us further details and screenshots.

Well, since the issue is related to different JDK versions, so we might not fix it in Aspose.Cells APIs. But, still, if you want us to log an investigation ticket to evaluate the issue thoroughly, we can do that upon your confirmation.

Thank you.
#9

I’ll be very appreciate if you’ll investigate this issues more deeply. It’ll be also very important also for other customer using this library when it start to display incorrect data after upgrade.


Thanks a lot for help, and let me know when it’ll be fix.
Best Regard,
#10

Hi Bartlomiej,


Thank you for the confirmation. We have logged an investigative ticket CELLSJAVA-41882 to look further into this matter. Please spare us little time to properly analyze the scenario and revert back with updates in this regard.

Java

Workbook book = new Workbook(dir + “Book_Tests.xlsx”);
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getDisplayStringValue());
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getStringValue());
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getStringValue(CellValueFormatStrategy.DISPLAY_STYLE));
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getStringValue(CellValueFormatStrategy.CELL_STYLE));
System.out.println(book.getWorksheets().get(0).getCells().get(“A1”).getStringValue(CellValueFormatStrategy.NONE));

Output

API Version: 8.8.2.8
JDK Version: 1.8.0_66
40.1
40.1
40.1
40.1
40.15

API Version: 8.8.2.8
JDK Version: 1.7.0_79
40.2
40.2
40.2
40.2
40.15
#11

Hi again,


This is to update you that we have looked further into this matter. I am afraid, we cannot fix this issue quickly because it is a bug of DecimalFormat.format() for JDK1.8 with RoundingMode.HALF_UP. You can confirm this bug with code provided at the bottom of this post. In order to get the correct result, we need to write the formatting logic all by ourselves, which makes it a complicated task.

Java

DecimalFormat df = new DecimalFormat("#.#");
System.out.println(df.format(40.15));
#12

Hello!


This is actually not a bug in Java 8 but rather a bug FIX. The details can be found here:http://www.oracle.com/technetwork/java/javase/8-compatibility-guide-2156366.html (RFE 7131459).
So, it looks like Java is finally behaving correctly. However, Aspose library behaves differently from Excel which rounds such numbers up. Since Aspose is Excel format reading library it is probably to be expected that it behaves in the same way Excel does (not to mention .NET version of Aspose library).
All in all it looks like a fix on Aspose side is required in order for your library to work as expected. Can you give us any hints about when (approximately) we can expect this fix to be present in your code?

Thank you and best regards,

Wojciech Drzazgowski
#13

Hi Wojciech,


Thank you for sharing the details.

We have posted your comments on the aforementioned ticket and have requested the concerned member of the product team to reconsider the scenario based on the provided details. We need to reanalyze the scenario before we can share an ETA for the fix, if applicable. As soon as we have any updates in this regard, we will post here for your kind reference.
#14

Hi Wojciech,


We have analyzed the links you have provided. The mentioned bug fix is for half-even rounding mode. However, for the rounding mode used by Excel, it should be half_up. Please try the following snippet. The output is 40.1 which should not be taken as correct.

Java

DecimalFormat df = new DecimalFormat("#.#");
df.setRoundingMode(RoundingMode.HALF_UP);
System.out.println(df.format(40.15));
#15

Hello


Since Wojciech is on leave and I’ve been working with him on the problem I would like to respond.
We might be wrong but we don’t believe there is any problem in your example. We think everything works as expected and accordingly with Java 8 specification (as we understand it) which says that in situations close to a “tie” there might be unexpected rounding situations but those are justified due to the actual numbers representation.

That means that the number you’ve put in your example - 40.15 has it’s most accurate representation 4.01499999999999985789145284798E1.
So it means that we’re trying to apply HALF_UP rounding to 40.149999… rather than to 40.15 and that correctly results in 40.1 since …4999 is not “half” and only starting with “half” we go “up”.

Again - we might be wrong on this one however, if you believe that this is actually a bug in Java 8 were you able to find a bug entry for it on oracle or openjdk bug tracking that you could share?

To our knowledge there was actually a bug in HALF_UP however, it has been fixed in 8.40 release: http://bugs.java.com/view_bug.do?bug_id=8039915

Also this report here: https://bugs.openjdk.java.net/browse/JDK-7131459 suggests that another report https://bugs.openjdk.java.net/browse/JDK-8084348 that was specifically about HALF_UP is its “duplicate” hence it would be expected that the HALF_UP thing was fixed as well as part of 7131459 which we believe was not only about HALF_EVEN but other rounding modes as well.

Regards,
Marcin
#16

Hi Marcin,


Thank you for sharing your thoughts on the said issue. I have logged your comments to the aforementioned ticket. Please allow us some time to review the scenario based on your comments and get back to you with updates in this regard.
#17
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix: Aspose.Cells for Java v16.12.7 and let us know your feedback.
#18

Hi, I checked it. And It looks fine.


Thanks,
#19

Hi,


Thanks for your feedback.

Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
#20

The issues you have found earlier (filed as CELLSJAVA-41882) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.