Apache POI read cell format time 00:00:00

Question

Apache POI read cell format time 00:00:00

Navigation

#1 by (1 votes)

0

Use Apache POI XSSFWorkbook, when reading a cell with format: 12:55 identifies it as numeric and returns Numero(0.010416666666666666) .

XSSFSheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();

SimpleDateFormat formateador = new SimpleDateFormat("dd/MM/yyyy");

Map<String, MedidaCabBean> cabecera;
ArrayList<MedidaDetalleBean> det = new ArrayList<>();

while (iterator.hasNext()) {
    Row currentRow = iterator.next();
    Iterator<Cell> cellIterator = currentRow.iterator();

    while (cellIterator.hasNext()) {
        Cell currentCell = cellIterator.next();

        if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
            System.out.print("texto("+currentCell.getStringCellValue() + ")--");
        } else if (currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            if (HSSFDateUtil.isCellDateFormatted(currentCell)) {
                System.out.print("Fecha("+formateador.format(currentCell.getDateCellValue())+") ");
            } else {
                System.out.print("Numero("+currentCell.getNumericCellValue() + ")--");
            }
        } 
    }
}

How can I identify if a cell is of the hour type?

java apache-poi

asked by nachfren 19.04.2018 в 15:44

source

1 answer

Mismatch with virtual hosts Problem when running a View in SQL Server

score 1 · Accepted Answer

I do not really like this answer, but I'll put it in anyway:

Apache POI recognizes some cell types , such as:

_NONE
BLANK
BOOLEAN
ERROR
FORMULA
NUMERIC
STRING

Unfortunately there is no Date type. Dates are stored in excel as numbers double and therefore their type is NUMERIC

Apache POI provides some methods to determine if a cell could be Date:

DateUtil.isCellDateFormatted(Cell cell) :

true if it looks like a date
DateUtil.isCellInternalDateFormatted(Cell cell)

true if you use an internal date format

To interpret a date from the number entered in the cell with double num = cell.getNumericCellValue() there are a few more methods:

DateUtil.getJavaDate(double date)

returns null if it is not a valid excel date
DateUtil.getJavaDate(double date, boolean use1904windowing)

Same as above, but considering that the file can use the date system based on 1904 ( by default in mac )

And other 2 versions that include the TimeZone in which to interpret the date and time.

And finally, the sources of poi (at least in version 3.7) implements the method HSSFCell.getDateCellValue() as:

public Date getDateCellValue() {

    if (_cellType == CellType.BLANK) {
        return null;
    }
    double value = getNumericCellValue();
    if (_book.getWorkbook().isUsing1904DateWindowing()) {
        return HSSFDateUtil.getJavaDate(value, true);
    }
    return HSSFDateUtil.getJavaDate(value, false);
}

Therefore, it would be advisable to try to interpret the cell as Date using this method if the cell type is NUMERIC .