POI reading excel strings as numeric

问题:

I am using Apache POI for reading excel file. And while reading it I have noticed that it takes strings as float values.

If my cell contains 1 then it will fetch it as 1.0

I took some hints from previous questions here and modified the code but still the float representation remains as it is.

How would I read correctly the data for strings and dates?

DataFormatter df = new DataFormatter();

        for (Row row : sheet) {

            for(int cn=0; cn<row.getLastCellNum(); cn++) {
                   // If the cell is missing from the file, generate a blank one
                   // (Works by specifying a MissingCellPolicy)
                   Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                   // Print the cell for debugging
                   cell.setCellType(Cell.CELL_TYPE_STRING);

                   System.out.println("CELL: " + cn + " --> " + df.formatCellValue(cell));

                   if (row.getRowNum() == 0) {

                        sheetColumnNames.add(cell.getRichStringCellValue().getString());
                    }

            }

        }

回答1:

Promoting a comment to an answer

The problem is the call

cell.setCellType(Cell.CELL_TYPE_STRING);

What that is doing is asking POI to try to convert the cell from whatever it is currently (eg a number) into a string. The conversion applied to try to do this is a fairly simple one, which is why you're loosing the formatting

If you just want to get back a String that contains the Cell Value as shown in Excel, just call DataFormatter directly, and it'll do its best. Playing around with the Cell Type will only confuse things, and will risk loosing formatting

回答2:

Adding to above answer, poi will give you 1.0 as output even when you are using dataformatter class if you are trying to execute program against LIBRE OFFICE spreadsheet. As poi does not work in similar fashion with LIBRE SPREADSHEETS as it with excel.