excel cell value precision

Question

I am using Apache POI to retrieve a cell double value:

1.The actual value of the cell stored in openxml format is "5.259761432023309", 2.The formatted display value of the same cell is "5.26%". 3.However the value showing on the MS Excel formula bar is "5.25976143202331%"

Using Apache POI , i am able to retrieve :

value 1, using cell.getNumericValue();

I am also able to retrieve value 2, using DataFormatter df = new DataFormatter();String asItLooksInExcel = df.formatCellValue(cell);

However, I am unable to retrieve value 3, which is the value showing on the formula bar, plz suggest ways to retrieve the same.

Answer 1

I cannot confirm your observations.

If I have the following Excel:

As you see 5.25976143202331% in A1. It is a German Excel so decimal separator is comma. But that does not matter.

There the XML is:

<sheetData>
 <row r="1" spans="1:1" x14ac:dyDescent="0.25">
  <c r="A1" s="1">
   <v>5.2597614320233098E-2</v>
  </c>
 </row>
</sheetData>

And the following code

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.format.CellNumberFormatter;

import java.io.*;

import java.math.BigDecimal;
import java.util.Locale;

class ExcelProcentValuePrecision {

 public static void main(String[] args) throws Exception{

  InputStream inp = new FileInputStream("ExcelProcentValuePrecision.xlsx");
  Workbook workbook = WorkbookFactory.create(inp);

  Cell cell = workbook.getSheetAt(0).getRow(0).getCell(0);

  String s = ((XSSFCell)cell).getCTCell().getV();
  System.out.println(s);

  BigDecimal bd = new BigDecimal(s);
  System.out.println(bd);

  double d = cell.getNumericCellValue();
  System.out.println(d);

  Locale.setDefault(Locale.US);

  DataFormatter dataformatter = new DataFormatter();
  s = dataformatter.formatCellValue(cell);
  System.out.println(s);

  CellNumberFormatter formatter = new CellNumberFormatter("#.#################%");
  s = formatter.format(cell.getNumericCellValue());
  System.out.println(s);

  workbook.close();

 }
}

leads to:

So 5.2597614320233098E-2 is the value directly out of the XML. 0.052597614320233098 is that value as an BigDecimal. 0.0525976143202331 is that value as floating point doubleaccording to IEEE floating point. 5.26% is that value formatted as displayed in Excel.

And 5.25976143202331% is that value as displayed in Excel's formula bar. Percent values are an exceptional case since there Excel shows % format also in formula bar but with fully count of significant digits (up to 15).

That exception with % values is because % formatting in Excel is not only formatting but also changing of the value. 1 = 100%. So if you put 100% in an Excelcell, the value 1 is stored. If you put 10% in an Excelcell, the value 0.1 is stored.

Answer 2

In case of Aspose.Cells API, please test the given sample code using the following sample Excel file. Please also see its image and console output.

All these things explain that Aspose.Cells API retrieves the cell A1 value accurately with true precision.

• Sample Excel File

Sample Code

//Load source workbook
Workbook wb = new Workbook(dirPath + "sampleExcelCellValuePrecision.xlsx");

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access cell A1
Cell a1 = ws.getCells().get("A1");

//The value is double which is shown as
System.out.println("Double Value: " + a1.getValue());

//This is the value shown by Microsoft Excel
System.out.println("String Value: " + a1.getStringValue());

Console Output

Double Value: 0.0525976143202331
String Value: 5.26%

Note: I am working as Developer Evangelist at Aspose

Answer 3

see my answer here: https://stackoverflow.com/a/58732907/12332761

This function should produce the same thing you see in the formula bar:

    private static BigDecimal stringedDouble(Cell cell) {
            BigDecimal result =  new BigDecimal(String.valueOf(cell.getNumericCellValue())).stripTrailingZeros();
            result = result.scale() < 0 ? result.setScale(0) : result;
            return result;
    }