Read Excel Numeric cell values as they are in Java

问题:

When I am reading excel numeric cell values, i am getting the output with decimals. eg: 79 is reading as 79.0, 0.00 reading as 0.0. The code for my application I have written is:

int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_NUMERIC)
  System.out.println(cell.getNumericCellValue());

回答1:

This question comes up a lot on Stack Overflow, so you'd be well advised to look through many of the similar ones to see there answers!

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. If you want it to look like it does in Excel, you need to apply the formatting rules defined against the cell to the number. Thus, 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.

All you need to do is:

// Only need one of these
DataFormatter fmt = new DataFormatter();

// Once per cell
String valueAsSeenInExcel = fmt.formatCellValue(cell);

回答2:

I'm not 100% this would work but I believe what you're looking for is DecimalFormat.

回答3:

I assume you're using Apache POI.

You should consider playing with DataFormats. Here is some very minimal draft code.

Otherwise, if the data in your work book is consistent, you might want to round the double returned by getNumericCellValue to int.

System.out.println((int)Math.round(cell.getNumericCellValue()));

回答4:

Use the DataFormatter as the following, it will detect the format of the cell automatically and produce the string output

                    DataFormatter formatter = new DataFormatter();
                    String df2 = formatter.formatCellValue(cell);