CODEWITHSUNDEEP
javaexcelapache-poixlsx
Ondrej Tokar
Ondrej Tokar

Reputation: 5080

XLSX custom date format read as String

I have xlsx with this date in cell: 03-09-2014 when I read xlsx cell using apache POI, it writes this: 41883.0.

This is the way how I do that: 

while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(Cell.CELL_TYPE_STRING);
                System.out.print(cell.getStringCellValue() + ";");
            }

Since I have converted all cells to become type of String, I would expect no deformation of the date...

Any solution? apache poi DataFormatter on cell containing date This is the solution :)

Upvotes: 1

Views: 4440

Answers (1)

Gagravarr
Gagravarr

Reputation: 48346

Why? Why oh why oh why would you have written that code to start with? It's just wrong on so many levels, as covered by probably every third Apache POI question on Stackoverflow :( Oh, and it's explicitly advised against in the JavaDocs....

You have two options available to you. If you want to have full control of reading the values, then follow the instructions in the Apache POI documentation on reading cell values, and write code something like:

for (Row row : sheet1) {
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                System.out.println(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.println(cell.getDateCellValue());
                } else {
                    System.out.println(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                System.out.println(cell.getCellFormula());
                break;
            default:
                System.out.println();
        }
    }
}

Alternately, if you just want "give me the closest string you can to what this cell looks like in Excel", then you need to use the DataFormatter class, which provides methods which read the Excel format rules applied to a cell, then re-creates (as best it can) those in Java

Your code should be:

DataFormatter fmt = new DataFormatter();

String valueAsInExcel = fmt.formatCellValue(cell);

That will format the number based on the formatting rules applied in Excel, so should return it looking as you expect

Finally, Dates in excel are stored as floating point numbers since 1900 or 1904, which is why you see the number you do in the date cell.

Upvotes: 5

Related Questions