Bug with how numbers larger than 15 digits are parsed

Aspose.Cells Product Family
1

We have Aspose Cell Java version 17.9.

We noticed a bug where if you have an XLSX file with a column with numerics that have longer than 15 digits the number coming back doesn’t match what is in Excel

sample.zip (9.5 KB)

See attached file.

If you click on the cell E:2 (row 2, column RecordID) you will see in Excel the # is shown as:

3200000000487860000

saving as CSV gives you this same number.

Notice that the zeros start after the 15th digit
Formatting thinks Number, no thousands, no decimals.

Return from com.aspose.cells.Cell.getStringValue(): “3200000000487859700”
getStringValueWithoutFormat(): “3.2000000004878602E18”

(Prior version of Aspose: “3200000000487860200”)

This is a problem for us as numbers for our customers are changing. Is there a known workaround for this or a fix possibly in a newer version?

2

@matthalls

Thanks for using Aspose APIs.

We have tested this issue with the following sample code using the most recent version which you can download from the following link.

The code works fine, please see the console output of the sample code for a reference.

Java

Workbook wb = new Workbook(dirPath + "sample-aws.xlsx");

Worksheet ws = wb.getWorksheets().get(0);

Cell e2 = ws.getCells().get("E2");

String str = e2.getStringValue();

System.out.println(str);

Console Output

3200000000487859700

3

Yes that is the wrong number we are seeing now. The correct number which you will see in Excel is: 3200000000487860000

see attached picture

excel.png (67.2 KB)

4

@matthalls

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and screenshot. We have logged the issue in our database for product team investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42488 - 15 digits numbers do not match what is in Excel
5

@matthalls

Please download and try the following fix and let us know your feedback. Now we make the parsed value same with old versions.

*Aspose.Cells for Java (Latest Version)

6

I’m still seeing the value of: 3200000000487859700

The prior # was 3200000000487860200

7

@matthalls

Please provide us your Excel file. When I input 3200000000487860200 in the Excel cell, it becomes 3200000000487860000, that’s why I need your Excel file so that I could test this issue correctly.

8

I included the XLS file in the original response above but here it is againsample.zip (9.5 KB)

9

@matthalls

We were able to observe the issue by converting your Excel to Pdf. Is this the issue you are facing? Please see the screenshot and confirm it.

Screenshot

10

Yes this is the same issue I am seeing.

11

@matthalls,

Thanks for your confirmation.

We evaluated your issue further. We found the issue was actually fixed for JDK1.6 and 1.7 but still it gives the incorrect result for JDK1.8. We will make further investigation for it.

Once we figure it out, we will let you know here.

12

@matthalls,

Please download and try the following fix and let us know your feedback.

*Aspose.Cells for Java (Latest Version)

13

Yes that matches the prior version of: 3200000000487860200

14

@matthalls

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

15

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

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

Please also check the following document/blog for your reference:

16

This issue still reproduces. Here is a test (I had to zip up UT_Aspose.xlsx to be able to attach it):

UT_Aspose.zip (9.6 KB)
import com.aspose.cells.Cells;
import com.aspose.cells.Workbook;
import org.junit.Test;

import java.io.InputStream;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

public class UT_Aspose {

@Test
public void testTextCellWithLongNumber() throws Exception {
InputStream in = getClass().getResourceAsStream( “UT_Aspose.xlsx” );
assertNotNull( in );
Workbook wb = new Workbook( in );
Cells cells = wb.getWorksheets().get(0).getCells();
assertEquals( “Very Long Number In a Text Column”, cells.get( 0,0 ).getStringValue() );
assertEquals( “3200000000487860000”, cells.get( 1,0 ).getStringValue() );
}
}

17

@sgolestane

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42581 - Aspose.Cells returns wrong value of cell A2
18

@sgolestane,

Please try our latest version/fix: Aspose.Cells for Java (Latest Version)

We have fixed your issue (logged earlier as “CELLSJAVA-42581”) now.

Let us know your feedback.

19

Confirming that the issue is fixed. Thanks!

20

@sgolestane

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.