17

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.

user3813942
  • 311
  • 1
  • 3
  • 13
  • 1
    Why don't you use Cell.getNumericCellValue()? It should return the actual value as double without any formatting applied. – centic Sep 26 '17 at 16:37
  • That's covered in the question centic – user3813942 Sep 26 '17 at 16:43
  • Try to see here https://stackoverflow.com/questions/16098046/how-to-print-double-value-without-scientific-notation-using-java – matrix Sep 26 '17 at 17:00
  • I saw that earlier matrix, still different fundamental issue from what I'm saying – user3813942 Sep 26 '17 at 19:50
  • 1
    What matrix is trying to tell you is that the double from getNumericCellValue() actually does hold the full value and the scientific notation is only what you get when you do a print via System.out.println or the like. – centic Sep 27 '17 at 06:31

1 Answers1

34

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!

Adithya
  • 456
  • 4
  • 6
  • This solved it, thank you, I would never have figured that out on my own. – user3813942 Sep 26 '17 at 19:49
  • 1
    Here it works for a maximum of 15-digit numbers but not more than that. Is there anything that will work for more than 15 digit number? I really appreciate any help you can provide. – Deepak Samria Jul 27 '23 at 11:13