Hi,
The data provided in a cell is 119989000. But when we use aspose library to read cell value using cells.get(, ).getValue(), the value extracted is 119989000.00000001. Kindly suggest on any alternate approach to extract the entered data only.
NOTE: The cell had been formatted in excel to 2 decimal places.
Regards,
Deepa
Hi Deepa,
The issue you’re encountering with the value being read as 119989000.00000001 instead of 119989000 is due to the way floating-point numbers are represented in programming, which can lead to small inaccuracies.
To resolve this, you should use the getStringValue() method instead of getValue(). This method retrieves the formatted string representation of the cell’s value, ensuring it matches what you see in Excel, including the correct decimal formatting.
Here’s a quick example of how to implement this:
// Assuming 'cells' is your Cells object and you have the row and column indices
String cellValue = cells.get(row, col).getStringValue();
System.out.println("Cell Value: " + cellValue);
By using getStringValue(), you will obtain the exact value as formatted in the Excel cell, eliminating the floating-point inaccuracies.
Please try this solution and let me know if it resolves your issue!
We need the entered data without the format applied. .getStringValue() retrieves formatted data with commas like 119,989,000.00. We require only the entered data without the format. We are extracting BigDecimal value of this cell for further processing.
Ex: BigDecimal.valueOf(((Number)cells.get(,).getValue()).doubleValue()).toPlainString();
We simply set the value 119989000 to one cell in ms excel to create one template file, and then we read the cell value with our component. Unfortrunately, we cannot get the value 119989000.00000001. Would you please provide us your template file and code so we can look into it for you? If the value in your template file is 119989000.00000001, generally you will get this value when using Cell.getValue(). However, in ms excel the maximum digits for one numer is 15, so you can only find 119989000 is displayed in ms excel.
I tested your scenario/case using a sample Excel file (attached) that contains value “119989000” in the worksheet A1 cell. I used the following sample code and it works fine and as expected.
e.g.,
Sample code:
Workbook workbook = new Workbook("d:\\files\\Bk_test1.xlsx");
// Get the sheet
Worksheet sheet = workbook.getWorksheets().get(0);
System.out.println(sheet.getCells().get("A1").getValue());
Bk_test1.zip (6.0 KB)
console output:
119989000
Please try using latest version: Aspose.Cells for Java v25.7 (which I am using). If you still find the issue, kindly share your sample Excel file, we will check it soon.
Hi,
Sharing the sample excel file. Unzip the folder. We are trying to access row -420, col -6 of the sheet . Kindly suggest.
SampleExcel.zip (28.7 KB)
Thanks for the sample Excel file.
I tested your scenario/case using your template XLSX file with Aspose.Cells for Java v25.7 (please try it) and following sample code, it works fine and as expected.
Workbook workbook = new Workbook("d:\\files\\SampleExcel.xlsx");
// Get the sheet
Worksheet sheet = workbook.getWorksheets().get(1);
System.out.println(sheet.getCells().get("G420").getValue());
console output:
119989000
If you still find the issue, kindly share your sample code that you are using, we will check your issue soon.
Hi,
When the data is copied into the excel we are encountering the issue. Attaching the updated sample excel. Kindly suggest.
SampleExcel.zip (28.9 KB)
We are extracting the cell value as below.
Ex: BigDecimal.valueOf(((Number)cells.get(419,6).getValue()).doubleValue()).toPlainString();
In your template file, the value of the specific cell is saved as “119989000.00000001” and it is just valid significant digits for Java so the value read by our component for the cell is that value too.
Such kind of value cannot be found in ms excel(as we said, the max digits is 15 in ms excel), but it may take effect in other scenarios, such as when calculating formulas. So we cannot trim it automatically either. For your requirement, we think you may truncate the original value with your own logic after getting the original value from cell(by Cell.getValue()/Cell.getDoubleValue()).
Thanks for the new file.
Aspose.Cells retrieves the value “119989000.00000001” from the G420 cell, which is expected and valid. This behavior is not an issue with the Aspose.Cells for Java API. The API simply parses the source XML data documented in the XLSX file and provides the original (source) value stored in the spreadsheet. To clarify, please refer to the attached screenshot, which demonstrates the actual value recorded for the G420 cell when the XLSX file is opened using a zip tool. This should help you better understand why this is the expected value.
sc_shot1.png (220.3 KB)