Example usage for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted.

Prototype

public static boolean isCellDateFormatted(Cell cell) 

Source Link

Document

Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.

Usage

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyCell(Cell srcCell, Cell distCell) {
    distCell.setCellStyle(srcCell.getCellStyle());
    if (srcCell.getCellComment() != null) {
        distCell.setCellComment(srcCell.getCellComment());
    }// ww  w. j  a v  a 2 s .c  om
    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);

    if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(srcCell)) {
            distCell.setCellValue(srcCell.getDateCellValue());
        } else {
            distCell.setCellValue(srcCell.getNumericCellValue());
        }
    } else if (srcCellType == Cell.CELL_TYPE_STRING) {
        distCell.setCellValue(srcCell.getRichStringCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
        //nothing
    } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
        distCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
        distCell.setCellFormula(srcCell.getCellFormula());
    } else {
        //nothing
    }
}

---

From source file:com.dituiba.excel.ImportTableService.java

License:Apache License

/**
 * ExcelTableBean//  w  ww  .  ja  va  2s  .  com
 */
public void doImport() {
    int rowNum = sheet.getLastRowNum() + 1;
    int columnNum = 0;
    for (int i = 0; i < rowNum; i++) {
        if (sheet.getRow(i) != null) {
            int last = sheet.getRow(i).getLastCellNum();
            columnNum = last > columnNum ? last : columnNum;
        }
    }
    tableBean = new TableBean(rowNum, columnNum);
    Collection<CellBean> cellBeans = new ArrayList<CellBean>();
    for (int r = startRow; r < rowNum; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    String cellValue = null;
                    if (cellHandlerMap.containsKey(c)) {
                        cellValue = cellHandlerMap.get(c).readCell(cell) + "";
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        Integer type = forceCellType.get(c);
                        if (type != null) {
                            cell.setCellType(type);
                        }
                        if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                            cellValue = cell.getBooleanCellValue() + "";
                        } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
                            try {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            } catch (IllegalStateException e) {
                                cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
                            }
                        } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date date2 = cell.getDateCellValue();
                                SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
                                cellValue = dff.format(date2); //
                            } else {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            }
                        } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                            cellValue = cell.getStringCellValue();
                        }
                        if (cellValue != null && cellValue instanceof String) {
                            cellValue = cellValue.toString().trim();
                        }
                    }
                    CellBean cellBean = new CellBean(cellValue, r, c);
                    cellBean.setCell(cell);
                    cellBeans.add(cellBean);
                }
            }
        }
    }
    tableBean.setCellBeans(cellBeans);
}

---

From source file:com.dua3.meja.model.poi.PoiCell.java

License:Apache License

private boolean isCellDateFormatted() {
    /*//www.ja  v a  2  s .c  om
     * DateUtil.isCellDateFormatted() throws IllegalStateException
     * when cell is not numeric, so we have to work around this.
     * TODO create SCCSE and report bug against POI
     */
    int poiType = poiCell.getCellType();
    if (poiType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) {
        poiType = poiCell.getCachedFormulaResultType();
    }
    return (poiType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC)
            && DateUtil.isCellDateFormatted(poiCell);
}

---

From source file:com.Excel.Excel.java

private void leerArchivo(int indiceHoja) {
    abrirArchivo();// w w  w. java  2  s.  c  o m
    this.datos = new HashMap<>();
    HSSFSheet sheet = workbook.getSheetAt(indiceHoja);
    Iterator<Row> rowIterator = sheet.iterator();
    int fila = 0;
    Row row;

    while (rowIterator.hasNext()) {
        List<Object> datosFila = new ArrayList<>();
        Cell celda;
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Object dato = null;
            celda = cellIterator.next();
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celda)) {
                    System.out.print(celda.getDateCellValue());
                    dato = celda.getDateCellValue();
                } else {
                    System.out.print(celda.getNumericCellValue());
                    dato = celda.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(celda.getStringCellValue());
                dato = celda.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(celda.getBooleanCellValue());
                dato = celda.getBooleanCellValue();
                break;
            }
            datosFila.add(dato);
        }
        datos.put(fila++, datosFila);
        System.out.println("");
    }
    cerrarArchivo();
}

---

From source file:com.Excel.Excel2007.java

private void leerArchivo(int indiceHoja) {
    abrirArchivo();/*www  .  j  av a 2 s  . c o  m*/
    this.datos = new HashMap<>();
    XSSFSheet sheet = workbook.getSheetAt(indiceHoja);
    Iterator<Row> rowIterator = sheet.iterator();
    int fila = 0;
    Row row;

    while (rowIterator.hasNext()) {
        List<Object> datosFila = new ArrayList<>();
        Cell celda;
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Object dato = null;
            celda = cellIterator.next();
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celda)) {
                    System.out.print(celda.getDateCellValue());
                    dato = celda.getDateCellValue();
                } else {
                    System.out.print(celda.getNumericCellValue());
                    dato = celda.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(celda.getStringCellValue());
                dato = celda.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(celda.getBooleanCellValue());
                dato = celda.getBooleanCellValue();
                break;
            }
            datosFila.add(dato);
        }
        datos.put(fila++, datosFila);
        System.out.println("");
    }
    cerrarArchivo();
}

---

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * purpose of this method to create ValueList along with types and column
 * name. Simple design followed : Just have ColumnMetaData object which
 * contains everything. For a Cell we will have one columnMetaData object
 * and it will have values across the//from   ww  w.j a  v  a2 s  . com
 * 
 * @param row
 * @throws Exception
 */
private void readARow(Row row, int nbrColumnsInARow) throws ExilityException {
    Value[] columnValues = new Value[nbrColumnsInARow];
    Value aColumnValue = null;
    String rawValue = null;

    for (int c = 0; c < nbrColumnsInARow; c++) {
        Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);

        ColumnMetaData columnInfo = this.columnsData.get(new Integer(c));
        int xlsColumnDataType = columnInfo.getXlsDataType();
        DataValueType exilDataType = null;

        int cellType = cell.getCellType();
        if (xlsColumnDataType != XLSReader.UNKNOWN_TYPE) {
            cellType = xlsColumnDataType;
        }

        try {

            switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:

                if (DateUtil.isCellDateFormatted(cell)) {
                    rawValue = DateUtility.formatDate(cell.getDateCellValue());
                    /*
                     * returns yyyy-mm-dd hh:mm:ss.sss full date with time.
                     */

                    exilDataType = DataValueType.DATE;
                } else {
                    double decimalNumber = cell.getNumericCellValue();
                    rawValue = NumberToTextConverter.toText(decimalNumber);

                    boolean isDecimal = rawValue.contains(".");
                    if (isDecimal) {
                        exilDataType = DataValueType.DECIMAL;
                    } else {
                        exilDataType = DataValueType.INTEGRAL;
                    }
                }

                break;

            case Cell.CELL_TYPE_STRING:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_FORMULA:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_BLANK:

                rawValue = cell.getStringCellValue();
                exilDataType = DataValueType.NULL;
                columnInfo.setExilDataType(exilDataType);
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                rawValue = cell.getBooleanCellValue() ? BooleanValue.TRUE : BooleanValue.FALSE;
                exilDataType = DataValueType.BOOLEAN;
                break;
            default:
                String msg = columnInfo.getColumnName() + XLSReader.INVALID_COLUMN_TYPE + row.getRowNum();
                Spit.out(msg);

            }

        } catch (Exception e) {
            // Trying to set valueType value and expected valueType value
            // for column in row
            String[] params = { this.getXlsTypeAsText(cell.getCellType()), this.getXlsTypeAsText(cellType),
                    columnInfo.getColumnName(), "" + row.getRowNum() };

            String message = this.replaceMessageParams(XLSReader.DATATYPE_MISMATCH, params);
            throw new ExilityException(message);
        }

        if (xlsColumnDataType == XLSReader.UNKNOWN_TYPE && cellType != Cell.CELL_TYPE_BLANK) {
            columnInfo.setXlsDataType(cellType);
            columnInfo.setExilDataType(exilDataType);
        }

        exilDataType = columnInfo.getExilDataType();

        aColumnValue = Value.newValue(rawValue, exilDataType);

        columnValues[c] = aColumnValue;
        this.columnsData.put(new Integer(c), columnInfo);
    }

    this.rows.add(columnValues);
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get text value of cell irrespective of its content type
 * //  w  w  w  . j av  a2s .  c  o m
 * @param cell
 * @return
 */
private String getTextValue(Cell cell) {
    if (cell == null) {
        return EMPTY_STRING;
    }

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_BLANK) {
        return EMPTY_STRING;
    }

    if (cellType == Cell.CELL_TYPE_FORMULA) {
        cellType = cell.getCachedFormulaResultType();
    }

    if (cellType == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue().trim();
    }

    /*
     * dates are internally stored as decimal..
     */
    if (cellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtility.formatDate(cell.getDateCellValue());
        }

        return NumberFormat.getInstance().format(cell.getNumericCellValue());
    }

    if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        if (cell.getBooleanCellValue()) {
            return "1";
        }
        return "0";
    }
    return EMPTY_STRING;
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * Guess the Exility DataValueType of a cell. Text, Decimal, Date abd
 * boolean are the types that we infer. Boolean is straightforward. If it is
 * numeric, we check whether it could be date. In case of formula, we go by
 * the type of cached result./*from   www .  j  a  va  2s  .c  om*/
 * 
 * @param cell
 * @return Best guess of cell type
 */
private DataValueType getExilityType(Cell cell) {
    if (cell == null) {
        return DataValueType.TEXT;
    }

    int cellType = cell.getCellType();

    if (cellType == Cell.CELL_TYPE_FORMULA) {
        cellType = cell.getCachedFormulaResultType();
    }

    // dates are internally stored as decimal..
    if (cellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(cell)) {
            return DataValueType.DATE;
        }

        return DataValueType.DECIMAL;
    }

    if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        return DataValueType.BOOLEAN;
    }

    return DataValueType.TEXT;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetReader.java

License:Apache License

private <T> T getBean(Class<T> clazz, Row row, List<String> fieldList) {
    Field[] fields = new Field[fieldList.size()];
    for (int i = 0; i < fields.length; i++) {
        Field field = null;//from   w  ww . ja  va2 s . c om
        try {
            field = clazz.getDeclaredField(fieldList.get(i));
            // field.getAnnotation();
            fields[i] = field;
            field.setAccessible(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    T t = null;
    try {
        t = clazz.newInstance();
    } catch (Exception e) {
        e.printStackTrace();
    }

    for (int i = 0; i < fields.length; i++) {
        Cell cell = null;
        cell = row.getCell(i);
        if (cell != null) {
            int cellType = cell.getCellType();
            Object value = null;
            switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                    Class fieldClass = fields[i].getType();
                    if (fieldClass == Integer.class || fieldClass == int.class) {
                        value = ((Double) value).intValue();
                    } else if (fieldClass == Short.class || fieldClass == short.class) {
                        value = ((Double) value).shortValue();
                    } else if (fieldClass == Byte.class || fieldClass == byte.class) {
                        value = ((Double) value).byteValue();
                    } else if (fieldClass == Long.class || fieldClass == long.class) {
                        value = ((Double) value).longValue();
                    } else if (fieldClass == Float.class || fieldClass == float.class) {
                        value = ((Double) value).floatValue();
                    } else if (fieldClass == Double.class || fieldClass == double.class) {
                        value = (Double) value;
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                break;
            }

            try {
                fields[i].set(t, value);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    return t;
}

---

From source file:com.github.camaral.sheeco.exceptions.SpreadsheetViolation.java

License:Apache License

private static Object getCellValue(final Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }/*from www  .j  av  a  2s  .  c om*/
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default:
        throw new UnsupportedOperationException("CellType " + cell.getCellType() + " is invalid");
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• »