Way to get correct double value

Aspose.Cells Product Family
,
1

Hi. Can you please help me to find correct way to get double value from cell? I have a book with double value “1142.237”. I need to read it to String, but API returns 1142.236999999. Is there correct way to get real value?

@Test
void doubleValueTest() throws Exception {
    Workbook workbook = new Workbook("E://Load.xlsx");

    Cell cell = workbook.getWorksheets().get(0).getCells().get("B2");
    
    assertEquals("1142.237", cell.getStringValueWithoutFormat());
    assertEquals(1142.237, cell.getValue());
}

Best regards. AlexeyLoad.zip (5.7 KB)

2

@makarovalv,

I checked your file and used your code to got result as “1142.2369999999999” which is expected and right result. Please note, in your template file (source .xml of the file), the saved value of this cell is “1142.2369999999999” value. So, there is no issue on Aspose.Cells’ end by any means. This is MS Excel’s behavior where the value is shown as “1142.237” when opening the file into it. I am afraid, Aspose.Cells just processes source file format and use/get the value from its original source data. So, you have to use your own (Java) code to just part value or split value accordingly to your specific needs. Please note, MS Excel can display numeric values as 15-digits only whereas the real value for the cell is 17-digits, so you need to format/round the value accordingly yourselves.

3

Hi. I understand, that it is Excel behavior - for some numbers it saves it as is “5538.7778”, but for sometimes it start to save it in 15-digit format. May be you can help to read value, that Excel shows in UI?

You have some CellsHelper.getSignificantDigits() that, probably used for this purposes

Best regards. Alexey

4

@makarovalv,

CellsHelper.getSignificantDigits() property is not for such kind of purpose, it is used for saving OOXML files such as XLSX. We are sorry but we cannot support such kind of requirements. Formatting and rounding double values with specific precision sometimes is a complicated task. Commonly you may simply check the value range and format it to 15-digits by some kind of pattern such as “#.#…#” etc. This way fits most of the common scenarios for users. However, as a product vendor, we have to take all situations into consideration, so the task becomes much more complicated and it is hard to provide a solution which can fit all requirements for all scenarios and cases.

Thanks for your understanding!

5

I found the solution.

private static final int EXCEL_MAX_DIGITS = 15;
/**
 * Fix floating-point rounding errors.
 * <p>
 * https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
 * https://support.microsoft.com/en-us/kb/214118
 * https://support.microsoft.com/en-us/kb/269370
 */
double fixFloatingPointPrecision(double value) {
    BigDecimal original = new BigDecimal(value);
    BigDecimal fixed = new BigDecimal(original.unscaledValue(), original.precision()).setScale(EXCEL_MAX_DIGITS, RoundingMode.HALF_UP);
    int newScale = EXCEL_MAX_DIGITS - (original.precision() - original.scale());
    return new BigDecimal(fixed.unscaledValue(), newScale).doubleValue();
}

I hope it helps somebody

6

@makarovalv,

Thanks for sharing the workaround. It might be helpful for users with similar requirements.