CODEWITHSUNDEEP
javaexcelapache-poi
yoda
yoda

Reputation: 569

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());
                    }

            }

        }

Upvotes: 3

Views: 1236

Answers (2)

Anant Laxmikant Bobde
Anant Laxmikant Bobde

Reputation: 564

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.

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48326

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

Upvotes: 2

Related Questions