POI reading excel strings as numeric

Question

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

            }

        }

Code seems correct... It seems that df.formatCellValue(cell)); is the culprit... please comment the print statement and then check again. — Sankumarsingh
– Sankumarsingh, Commented Dec 7, 2013 at 14:27
I think the problem is the cell.setCellType(Cell.CELL_TYPE_STRING); which shouldn't be there. Does the DataFormatter get it right when you zap the setCellType line? — Gagravarr
– Gagravarr, Commented Dec 7, 2013 at 15:02

Gagravarr · Accepted Answer · 2013-12-12 05:16:30Z

2

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

answered Dec 12, 2013 at 5:16

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anant Laxmikant Bobde · Accepted Answer · 2014-10-23 07:48:18Z

0

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.

answered Oct 23, 2014 at 7:48

Anant Laxmikant Bobde

5642 silver badges13 bronze badges

3 Comments

Gagravarr Over a year ago

Apache POI doesn't work with LibreOffice spreadsheets, use need to use the Apache ODF Toolkit to work with ODF spreadsheets as produced by OpenOffice and LibreOffice. Apache POI works with .xls and .xlsx Excel file formats only

Anant Laxmikant Bobde Over a year ago

Then how would we know if incoming xls or xlsx file is created in excel or libre office in poi?

Gagravarr Over a year ago

If the file is in .xls or .xlsx format, then it isn't in LibreOffice format! LibreOffice and OpenOffice use the .ods (Open Document Spreadsheet) format as their native spreadsheet format

Collectives™ on Stack Overflow

POI reading excel strings as numeric

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related