Example usage for org.apache.poi.ss.usermodel Cell getDateCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getDateCellValue

Introduction

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

Prototype

Date getDateCellValue();

Source Link

Document

Get the value of the cell as a date.

Usage

From source file:step.datapool.excel.ExcelFunctions.java

License:Open Source License

/**
* Konvertiert unterschiedliche Formate in Strings.
* 
* @param cell Excel Zelle/*from   w  ww .  java2s  . c o  m*/
* @param evaluator FormulaEvaluator
* @return Wert der Zelle als String
*/
public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) {

    boolean isFormulaPatched = false;
    String initialFormula = null;

    int chkTyp = cell.getCellType();
    if (chkTyp == Cell.CELL_TYPE_FORMULA) {

        initialFormula = cell.getCellFormula();
        // Some formula have to be changed before they can be evaluated in POI
        String formula = FormulaPatch.patch(initialFormula);
        if (!formula.equals(initialFormula)) {
            isFormulaPatched = true;
            cell.setCellFormula(formula);
            evaluator.notifySetFormula(cell);
        }
    }

    try {
        int typ = evaluateFormulaCell(cell, evaluator);
        if (typ == -1)
            typ = cell.getCellType();
        switch (typ) {
        case Cell.CELL_TYPE_NUMERIC:
            /* Datum und Zeit (sind auch Zahlen) */
            if (DateUtil.isCellDateFormatted(cell)) {
                Date dat = cell.getDateCellValue();
                GregorianCalendar cal = new GregorianCalendar();
                cal.setTime(dat);
                /*
                 * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als
                 * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum
                 * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00).
                 * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck-
                 * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine
                 * Zeit.
                 */
                if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat
                    SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss");
                    return STD_TIM.format(dat);
                }

                SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy");
                return STD_DAT.format(dat); // Datumsformat
            } else {
                /* int, long, double Formate */
                double dbl = cell.getNumericCellValue();
                int tryInt = (int) dbl;
                long tryLong = (long) dbl;
                if (tryInt == dbl) {
                    return new Integer(tryInt).toString(); // int-Format
                } else if (tryLong == dbl) {
                    return new Long(tryLong).toString(); // long-Format
                }

                // return new Double(dbl).toString(); // double-Format
                String numberValueString = new Double(dbl).toString(); // double-Format

                // always use decimal format
                try {
                    // scale 14 to solve problem like value 0.22 --> 0.219999999999997
                    BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14,
                            RoundingMode.HALF_UP); // use constructor BigDecimal(String)!

                    String customValueString = getCustomDecimalFormat().format(roundedBigDecimal);
                    if (!customValueString.equals(numberValueString)) {
                        logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'",
                                numberValueString, customValueString);
                        numberValueString = customValueString; // bigdecimal-format

                    }
                } catch (Exception e) {
                    logger.error("An error occurred trying to convert the cell value number to decimal format "
                            + numberValueString, e);
                }

                return numberValueString;
            }

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());

        case Cell.CELL_TYPE_FORMULA:
            /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die
             * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat.
             */
            return cell.getCellFormula();

        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();

        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_ERROR:
            switch (cell.getErrorCellValue()) {
            case 1:
                return "#NULL!";
            case 2:
                return "#DIV/0!";
            case 3:
                return "#VALUE!";
            case 4:
                return "#REF!";
            case 5:
                return "#NAME?";
            case 6:
                return "#NUM!";
            case 7:
                return "#N/A";
            default:
                return "#ERR!";
            }

        default:
            return "ERROR: unknown Format";
        }
    } finally {
        if (isFormulaPatched) {
            cell.setCellFormula(initialFormula);
            evaluator.notifySetFormula(cell);
        }
    }

}

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

private Boolean isBlank(Cell cell) {
    switch (cell.getCellType()) {
    case STRING:/*from   www.  j ava 2s .  c  om*/
        if (cell.getRichStringCellValue().length() > 0) {
            return false;
        }
        break;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() != null
                : cell.getNumericCellValue() != 0) {
            return false;
        }
        break;
    case BOOLEAN:
        if (cell.getBooleanCellValue()) {
            return false;
        }
        break;
    case BLANK:
        return true;
    default:
        break;
    }
    return null;
}

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

@SuppressWarnings("unchecked")
private Column<?> appendValue(Column<?> column, Cell cell) {
    switch (cell.getCellType()) {
    case STRING:/* w  ww.ja  va 2 s .  c om*/
        column.appendCell(cell.getRichStringCellValue().getString());
        return null;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            // This will return inconsistent results across time zones, but that matches Excel's behavior
            LocalDateTime localDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            column.appendCell(localDate.toString());
            return null;
        } else {
            double num = cell.getNumericCellValue();
            if (column.type() == ColumnType.INTEGER) {
                Column<Integer> intColumn = (Column<Integer>) column;
                if ((int) num == num) {
                    intColumn.append((int) num);
                    return null;
                } else if ((long) num == num) {
                    Column<Long> altColumn = LongColumn.create(column.name(), column.size());
                    altColumn = intColumn.mapInto(s -> (long) s, altColumn);
                    altColumn.append((long) num);
                    return altColumn;
                } else {
                    Column<Double> altColumn = DoubleColumn.create(column.name(), column.size());
                    altColumn = intColumn.mapInto(s -> (double) s, altColumn);
                    altColumn.append(num);
                    return altColumn;
                }
            } else if (column.type() == ColumnType.LONG) {
                Column<Long> longColumn = (Column<Long>) column;
                if ((long) num == num) {
                    longColumn.append((long) num);
                    return null;
                } else {
                    Column<Double> altColumn = DoubleColumn.create(column.name(), column.size());
                    altColumn = longColumn.mapInto(s -> (double) s, altColumn);
                    altColumn.append(num);
                    return altColumn;
                }
            } else if (column.type() == ColumnType.DOUBLE) {
                Column<Double> doubleColumn = (Column<Double>) column;
                doubleColumn.append(num);
                return null;
            }
        }
        break;
    case BOOLEAN:
        if (column.type() == ColumnType.BOOLEAN) {
            Column<Boolean> booleanColumn = (Column<Boolean>) column;
            booleanColumn.append(cell.getBooleanCellValue());
            return null;
        }
    default:
        break;
    }
    return null;
}

From source file:techgarden.Controller.java

public Object[][] getData(String excelFilePath) throws IOException, InvalidFormatException {

    FileInputStream fis = new FileInputStream(new File(excelFilePath));
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
    org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0);
    int rownum = firstSheet.getLastRowNum();
    int colnum = firstSheet.getRow(0).getLastCellNum();
    Object[][] data = new Object[rownum][colnum];
    //String[][] stringData = new String[rownum][colnum];
    for (int i = 0; i < rownum; i++) {
        Row row = firstSheet.getRow(i);/*  w w w. j a v  a 2 s  .c om*/
        if (row != null) {
            for (int j = 0; j < colnum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    try {

                        if (cell.getColumnIndex() == 0) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            data[i][j] = cell.getStringCellValue();
                            // System.out.println(cell.getStringCellValue());
                        } else if (cell.getColumnIndex() == 1) {
                            data[i][j] = cell.getDateCellValue();
                            // System.out.println(cell.getDateCellValue());
                        } else {
                            data[i][j] = cell.getNumericCellValue();
                        }
                    } catch (IllegalStateException e) {
                        e.printStackTrace();
                        //
                    }
                }
            }
        }
    }
    workbook.close();
    fis.close();
    return data;
}

From source file:techGardenMap.Controller.java

public Double[][] getData(String excelFilePath) throws IOException, InvalidFormatException {
    FileInputStream fis = new FileInputStream(new File(excelFilePath));

    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);

    org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0);
    int rownum = firstSheet.getLastRowNum();
    int colnum = firstSheet.getRow(0).getLastCellNum();
    Double[][] data = new Double[rownum][colnum];
    //String[][] stringData = new String[rownum][colnum];
    for (int i = 0; i < rownum; i++) {
        Row row = firstSheet.getRow(i);/*from   w  ww.jav a 2s.  c  o  m*/
        if (row != null) {
            for (int j = 0; j < colnum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    try {
                        //cell.setCellType(Cell.CELL_TYPE_STRING);

                        data[i][j] = cell.getNumericCellValue();
                        System.out.println(cell.getDateCellValue());
                    } catch (IllegalStateException e) {
                        e.printStackTrace();
                        //
                    }
                }
            }
        }
    }
    workbook.close();
    fis.close();
    return data;
}

From source file:Test.LeerExcel.java

public static void main(String args[]) throws IOException {
    FileInputStream file = new FileInputStream(new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\"));
    // Crear el objeto que tendra el libro de Excel
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    /*/*from  ww  w .  jav a 2 s  . c o m*/
     * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice.
     * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator
     * que nos permite recorrer cada una de las filas que contiene.
     */
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    Row row;
    // Recorremos todas las filas para mostrar el contenido de cada celda
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        // Obtenemos el iterator que permite recorres todas las celdas de una fila
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell celda;
        while (cellIterator.hasNext()) {
            celda = cellIterator.next();
            // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero...
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(celda)) {
                    System.out.println(celda.getDateCellValue());
                } else {
                    System.out.println(celda.getNumericCellValue());
                }
                System.out.println(celda.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.println(celda.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println(celda.getBooleanCellValue());
                break;
            }
        }
    }
    // cerramos el libro excel
    workbook.close();
}

From source file:Test.LeerExcelXlsx.java

public static void main(String args[]) throws IOException {
    FileInputStream file = new FileInputStream(
            new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\Libro1.xlsx"));
    // Crear el objeto que tendra el libro de Excel
    XSSFWorkbook workbook = new XSSFWorkbook(file);

    /*//ww  w. j av  a2s . c o m
     * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice.
     * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator
     * que nos permite recorrer cada una de las filas que contiene.
     */
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();

    Row row;
    // Recorremos todas las filas para mostrar el contenido de cada celda
    while (rowIterator.hasNext()) {
        row = rowIterator.next();

        // Obtenemos el iterator que permite recorres todas las celdas de una fila
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell celda;

        while (cellIterator.hasNext()) {
            celda = cellIterator.next();

            // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero...
            switch (celda.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celda)) {
                    System.out.print(celda.getDateCellValue());
                } else {
                    System.out.print(celda.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(celda.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(celda.getBooleanCellValue());
                break;
            }
        }
        System.out.println("");
    }

    // cerramos el libro excel
    workbook.close();
}

From source file:ui.MainWindow.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    if (jComboBox1.getSelectedIndex() == 0) {
        return;/*  ww w. j a  va 2 s.c o m*/
    }
    jLabel4.setText("tiene su prxima clase el da");
    jLabel5.setVisible(false);
    jLabel6.setVisible(false);
    jLabel7.setVisible(false);

    Calendar today = Calendar.getInstance();
    Sheet sheet = workbook.getSheetAt(0);
    Row colegio = sheet.getRow(3 + jComboBox1.getSelectedIndex());
    int i = 2;
    Cell c = colegio.getCell(i);
    DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
    Calendar clase = Calendar.getInstance();
    while (HSSFDateUtil.isCellDateFormatted(c)) {
        clase.setTime(c.getDateCellValue());
        if (clase.after(today)) {
            jLabel3.setText(jComboBox1.getSelectedItem().toString());
            jLabel5.setText(df.format(clase.getTime()));
            jLabel7.setText(sheet.getRow(3).getCell(i).getStringCellValue());

            jLabel2.setVisible(true);
            jLabel3.setVisible(true);
            jLabel4.setVisible(true);
            jLabel5.setVisible(true);
            jLabel6.setVisible(true);
            jLabel7.setVisible(true);
            return;
        } else {
            i++;
            c = colegio.getCell(i);
        }
    }
    jLabel3.setText(jComboBox1.getSelectedItem().toString());
    jLabel4.setText("no tiene ms clases.");
    jLabel2.setVisible(true);
    jLabel3.setVisible(true);
    jLabel4.setVisible(true);
}

From source file:ui.MainWindow.java

public String[] getClass(Date weekStart, Date weekEnd, int colegioIndex) {
    String[] date = new String[] { "-", "-" };
    Sheet sheet = workbook.getSheetAt(0);
    Row colegio = sheet.getRow(colegioIndex);
    int i = 2;/*from   ww w  . jav a  2  s  .  c  o  m*/
    Cell c = colegio.getCell(i);
    DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
    Date clase;
    while (c != null && HSSFDateUtil.isCellDateFormatted(c)) {
        clase = c.getDateCellValue();
        if (clase.after(weekStart) && clase.before(weekEnd)) {
            date[0] = df.format(clase);
            date[1] = sheet.getRow(3).getCell(i).getStringCellValue();
            break;
        } else {
            i++;
            c = colegio.getCell(i);
        }
    }
    return date;
}

From source file:utilities.XLSTaskManager.java

License:Open Source License

private String getColumn(Row row, String name, HashMap<String, Integer> header, int lastCellNum, String def)
        throws Exception {

    Integer cellIndex;// w w w.j  a  v  a 2s .c  o m
    int idx;
    String value = null;
    double dValue = 0.0;
    Date dateValue = null;
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm");

    cellIndex = header.get(name);
    if (cellIndex != null) {
        idx = cellIndex;
        if (idx <= lastCellNum) {
            Cell c = row.getCell(idx);
            if (c != null) {
                if (c.getCellType() == CellType.NUMERIC || c.getCellType() == CellType.FORMULA) {
                    if (HSSFDateUtil.isCellDateFormatted(c)) {
                        dateValue = c.getDateCellValue();
                        value = dateFormat.format(dateValue);
                    } else {
                        dValue = c.getNumericCellValue();
                        value = String.valueOf(dValue);
                        if (value != null && value.endsWith(".0")) {
                            value = value.substring(0, value.lastIndexOf('.'));
                        }
                    }
                } else if (c.getCellType() == CellType.STRING) {
                    value = c.getStringCellValue();
                } else {
                    value = null;
                }

            }
        }
    }

    if (value == null) { // Set to default value if null
        value = def;
    }

    return value;
}