java - Apache POI DataFormatter Returns Scientific Notation

Question

I have an xlsx file I'm reading with apache poi 3.17 in java 6. In one instance I have a cell with the value, 123456789011. This is read into java as a NUMERIC CellTypeEnum Cell. When I use DataFormatter to get the value of the cell like this:

DataFormatter formatter = new DataFormatter(Locale.US); String strVal = formatter.formatCellValue(cell);

The String value comes out as "1.234567+11". I need the real value in the cell which is "123456789011". How can I get that?

I already tried using BigDecimal in Java to convert the String, but that returns "123456700000" because that's the best it can do with the given information; meaning I need to get the correct value from the actual cell object. I also tried using cell.getNumericCellValue() but that returns a double, which has a limit too small to handle the original value, so it is retrieved as "1.234567E11" which has the same issue as the other method of retrieval.

Is there a way to get the value as a String from the original Cell as it is entered in the xlsx? I have no power over the original xlsx.

Thank you.

Answer 1

use toPlainString() method of BigDecimal. It takes in the scientific notation and converts it to its corresponding numerical String value.

I tried doing this: (I have the number 123456789011 at cell A1 in my sheet, with cell type as NUMERIC)

Row row = sheet.getRow(0);
Object o = getCellValue(row.getCell(0));
System.out.println(new BigDecimal(o.toString()).toPlainString());

getCellValue method is my generic method that reads the cell:

public Object getCellValue(Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();

            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            }
        }
        return null;
    }

Hope this helps!