16

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?

wrschneider
  • 17,913
  • 16
  • 96
  • 176

2 Answers2

28

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));
  }
}
Community
  • 1
  • 1
Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • DataFormatter is exactly what I was looking for. It works great for General and gives me the right # of decimal places for Accounting. Only thing I don't understand is, why is it giving me a "*" when there is no currency symbol in Excel? – wrschneider Mar 21 '13 at 21:07
  • It's worth checking what format string Excel applied to the cell (Excel sometimes displays a different thing to what it writes to the file, especially in non-English locales). – Gagravarr Mar 21 '13 at 21:33
  • I would have been really thankful if you had provided an example. – winklerrr Nov 05 '15 at 12:14
-2
// We create a variable of type Workbook and load the excel for reading the fields.    

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("LIST.xls"));

// Getting the working sheet

HSSFSheet sheet = workbook.getSheetAt(0);

// Getting the working row

HSSFRow row = sheet.getRow(0);

// Store the numeric value on Int var 

int d = (int) row.getCell(0).getNumericCellValue();

// Printing the result

System.out.println(d0);

// Close the current workbook 

workbook.close();
awwsmm
  • 1,353
  • 1
  • 18
  • 28
renelhs
  • 57
  • 4
  • This code will give incorrect answers for cells formatted as percentages, as currency symbols, and many other cases too! – Gagravarr Dec 08 '15 at 21:35
  • this code is only for read and Int field with Apache POI, using getNumericCellValue() method – renelhs Dec 09 '15 at 14:11
  • This example is for an Integer, but more generally is parser depending on the data type of the cell, for example, a type of String can use String.valueOf and if you use the method getNumericCellValue can use Double d = row.getCell(cellIndex).getNumericCellValue(); Integer i = d.intValue(); – renelhs Dec 12 '15 at 22:24