Apache POI - get number as integer

Question

Numbers all come back from Apache POI cell values as Double.

When I do getCell(...).toString(), a number that appeared as "123" in Excel will convert to "123.0".

How can I tell that the number should have displayed as an integer? Is there some magic I need to apply in Java to replicate what Excel does with "General" formatting?

Answer 1

Excel stores almost all numbers in the file format as floating point values, which is why POI will give you back a double for a numeric cell as that's what was really there

I believe, though it's not quite clear from your question, that what you want to do is get a String object in Java that contains the number as it would look in Excel? i.e. apply the formatting rules applied to the cell to the raw number, and give you back the formatted string?

If so, you want to do exactly the same thing as in my answer here. To quote:

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

Edit And for those of you who apparently find clicking through to the JavaDocs to be just that little bit too much work..., you need to use the DataFormatter.formatCellValue(Cell) method. If iterating, you'd do something along the lines of:

Workbook workbook = WorkbookFactory.create(new File("input.xlsx"));
DataFormatter formatter = new DataFormatter();
Sheet s = workbook.getSheetAt(0);
for (Row r : s) {
  for (Cell c : r) {
     System.out.println(formatter.formatCellValue(c));
  }
}