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

List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DateUtil
  6. 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:de.escnet.ExcelTable.java

License:Open Source License

private String getCellValue(XSSFCell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";

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

    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellString();

    case Cell.CELL_TYPE_FORMULA:
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cellValue.getBooleanValue());

        case Cell.CELL_TYPE_NUMERIC:
            return getNumericValue(cellValue.getNumberValue());

        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();

        case Cell.CELL_TYPE_ERROR:
        case Cell.CELL_TYPE_FORMULA:
        default:/*from  w  w w . j a  va2s .  com*/
            throw new IllegalStateException("Illegal cell type: " + cell.getCellType());
        }

    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue().toString();
        }

        return getNumericValue(cell.getNumericCellValue());

    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    default:
        throw new IllegalStateException("Illegal cell type: " + cell.getCellType());
    }
}

From source file:de.fhg.fokus.odp.portal.uploaddata.service.Worker.java

/**
 * loop through all Cells and rows. Firstly, add correct keys to strings.
 * Secondly, parse corresponding value into correct json and add this
 * dataset to ckan via middleware.//from w  w w .  j  a v  a  2s .c o m
 * 
 * @param args
 * @throws Exception
 * 
 * @return a String of dataset indices, which were not uploaded.
 */
public String readXlsx() {
    final StringBuilder errormessage = new StringBuilder("");
    final StringBuilder resourceStringBuilder = new StringBuilder("[{");
    final StringBuilder extrasStringBuilder = new StringBuilder("{");

    HashMap<String, String> map = new HashMap<String, String>();
    ArrayList<String> strings = new ArrayList<String>();
    XSSFWorkbook workBook = null;
    try {
        workBook = new XSSFWorkbook(uploadFolder + "file.xlsx");
    } catch (IOException e1) {
        e1.printStackTrace();
    }
    int counter = 0;
    XSSFSheet sheet = workBook.getSheetAt(0);
    for (Row row : sheet) {
        for (Cell cell : row) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                String value = cell.getRichStringCellValue().getString();
                // first row, add value to strings
                if (counter == 0) {
                    if (!value.startsWith("resources:") && !value.startsWith("extras:")) {
                        map.put(value, null);
                    }

                    strings.add(value);
                    break;
                }
                // compute columnIndex for later use
                int columnIndex = cell.getColumnIndex();
                // compute parameter for later use in if-statements
                String parameter = strings.get(columnIndex);
                handleString(resourceStringBuilder, extrasStringBuilder, map, value, parameter);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // is a date;
                    parameter = strings.get(cell.getColumnIndex());
                    handleDate(map, parameter, cell, extrasStringBuilder);
                } else {
                    // is a number;
                    parameter = strings.get(cell.getColumnIndex());
                    handleNumber(map, parameter, cell, extrasStringBuilder);
                }
                break;
            default:
                break;
            }
        }
        // finish extras and resources
        finishParseResource(resourceStringBuilder);
        finishParseExtras(extrasStringBuilder);

        Validator.checkTagAndGroupsForEmptyValues(map);
        Validator.setlicenseAndNameToLowerCase(map);

        // add resources and extras to map
        map.put("resources", resourceStringBuilder.toString());
        map.put("extras", extrasStringBuilder.toString());

        createDataSet(errormessage, gw, map, counter);

        ++counter;
        // reset resourceStringBuilder and extrasStringBuilder
        resetStringBuilder(resourceStringBuilder, extrasStringBuilder);

        // reset map
        map.clear();
    }

    if (errormessage.toString().equalsIgnoreCase("")) {
        // no errors
        return errormessage.toString();
    } else {
        // return list of dataset indices
        return errormessage.substring(0, errormessage.length() - 1);
    }
}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

/**
 * Create sheets./*from  ww  w.  ja v a 2  s .co m*/
 * 
 * @param inputStream
 * @return Created sheets.
 * @throws IOException
 */
public static Sheets createSheets(final InputStream inputStream) throws IOException {
    // sheets
    final Sheets sheets = new Sheets();
    // create workbook
    final Workbook workbook = new HSSFWorkbook(inputStream);
    final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum);
        // ingrid sheet
        final Sheet sheet = new Sheet();
        sheet.setSheetIndex(sheetNum);
        sheets.addSheet(sheet);
        final Values values = new Values();
        sheet.setValues(values);
        for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) {
            boolean hasValues = false;
            final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>();
            for (final Cell poiCell : poiRow) {

                Comparable<? extends Object> value = null;
                switch (poiCell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = new Boolean(poiCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(poiCell)) {
                        value = getFormattedDateString(poiCell);
                    } else {
                        value = new Double(poiCell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = poiCell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = calculateFormula(poiCell, eval);
                    break;
                default:
                    value = "";
                    break;
                }
                // trim strings
                if (value instanceof String) {
                    value = ((String) value).trim();
                }
                // only add if at least one value does exist in row
                if (!value.equals("")) {
                    hasValues = true;
                    // ingrid column
                    if (sheet.getColumn(poiCell.getColumnIndex()) == null) {
                        final Column column = new Column(poiCell.getColumnIndex());
                        sheet.addColumn(column);
                    }
                }

                // ingrid point and value
                final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex());
                valuesInCell.put(point, value);
            }
            // ingrid row
            // ! only add if at least one value does exist
            if (hasValues) {
                final Row row = new Row(poiRow.getRowNum());
                sheet.addRow(row);
                for (final Point point : valuesInCell.keySet()) {
                    //
                    if (sheet.getColumn(point.getX()) != null) {
                        values.addValue(point, valuesInCell.get(point));
                    }
                }
            }
        }
    }

    return sheets;
}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

private static Comparable<? extends Object> calculateFormula(final Cell poiCell, final FormulaEvaluator eval) {
    Comparable<? extends Object> ret = null;
    final int type = eval.evaluateFormulaCell(poiCell);
    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(poiCell)) {
            ret = getFormattedDateString(poiCell);
        } else {//  w w  w  .  j a va2 s  .c o m
            ret = poiCell.getNumericCellValue();
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        ret = poiCell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    case Cell.CELL_TYPE_STRING:
    default:
        ret = poiCell.getStringCellValue();
    }
    return ret;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;//  www.ja  v  a 2s. c  o  m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        if (returnURLInsteadOfName) {
                            Hyperlink link = cell.getHyperlink();
                            if (link != null) {
                                if (concatenateLabelUrl) {
                                    String url = link.getAddress();
                                    if (url == null) {
                                        url = "";
                                    }
                                    String label = link.getLabel();
                                    if (label == null) {
                                        label = "";
                                    }
                                    value = label + "|" + url;
                                } else {
                                    value = link.getAddress();
                                }
                            } else {
                                value = cell.getStringCellValue();
                            }
                        } else {
                            value = cell.getStringCellValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            if (defaultDateFormat != null) {
                                Date d = cell.getDateCellValue();
                                if (d != null) {
                                    value = defaultDateFormat.format(d);
                                }
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        } else {
                            if (overrideExcelNumberFormat) {
                                value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        }
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue() ? "true" : "false";
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                value = getDataFormatter().formatCellValue(cell);
            } else {
                if (overrideExcelNumberFormat) {
                    value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                } else {
                    value = getDataFormatter().formatCellValue(cell);
                }
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private Date getDateCellValue(Cell cell, String pattern) throws Exception {
    Date value = null;//from w w  w  .  j a  va2  s  .co m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                return parseDate(s, pattern);
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        value = parseDate(s, pattern);
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getDateCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell) && parseDateFromVisibleString == false) {
                value = cell.getDateCellValue();
            } else {
                String s = getDataFormatter().formatCellValue(cell);
                value = parseDate(s, pattern);
            }
        } else if (cellType == CellType.STRING) {
            String s = getDataFormatter().formatCellValue(cell);
            value = parseDate(s, pattern);
        }
    }
    if (returnZeroDateAsNull && GenericDateUtil.isZeroDate(value)) {
        value = null;
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetNamedCellInput.java

License:Apache License

public Object getCellValue() {
    if (currentNamedCell != null) { // cell.getCellTypeEnum() == CellType.BLANK
        if (currentNamedCell.getCellTypeEnum() == CellType.BLANK) {
            valueClass = null;// w w w  . ja  va  2  s. c om
            return null;
        } else if (currentNamedCell.getCellTypeEnum() == CellType.BOOLEAN) {
            valueClass = "java.lang.Boolean";
            return currentNamedCell.getBooleanCellValue();
        } else if (currentNamedCell.getCellTypeEnum() == CellType.ERROR) {
            valueClass = null;
            return null;
        } else if (currentNamedCell.getCellTypeEnum() == CellType.FORMULA) {
            valueClass = "java.lang.String";
            return getDataFormatter().formatCellValue(currentNamedCell, getFormulaEvaluator());
        } else if (currentNamedCell.getCellTypeEnum() == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(currentNamedCell)) {
                valueClass = "java.util.Date";
                return currentNamedCell.getDateCellValue();
            } else {
                valueClass = "java.lang.Double";
                return currentNamedCell.getNumericCellValue();
            }
        } else if (currentNamedCell.getCellTypeEnum() == CellType.STRING) {
            valueClass = "java.lang.String";
            return currentNamedCell.getStringCellValue();
        } else {
            valueClass = null;
            return null;
        }
    } else {
        valueClass = null;
        return null;
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private boolean fetchCurrentCellValue(Cell cell) {
    if (cell != null) {
        currentCell = cell;/*  w w  w .j  av a2 s  .co  m*/
        currentCellValueString = getStringCellValue(cell);
        Comment comment = cell.getCellComment();
        if (comment != null) {
            currentCellComment = comment.getString().getString();
            currentCellCommentAuthor = comment.getAuthor();
        }
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BLANK) {
            currentCellValueClassName = "Object";
        } else if (cellType == CellType.STRING) {
            currentCellValueClassName = "String";
            currentCellValueObject = currentCellValueString;
        } else if (cellType == CellType.BOOLEAN) {
            currentCellValueClassName = "Boolean";
            currentCellValueBool = cell.getBooleanCellValue();
            currentCellValueObject = currentCellValueBool;
        } else if (cellType == CellType.ERROR) {
            currentCellValueClassName = "Byte";
            currentCellValueObject = cell.getErrorCellValue();
        } else if (cellType == CellType.FORMULA) {
            currentCellValueClassName = "String";
            currentCellFormula = cell.getCellFormula();
            currentCellValueString = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            currentCellValueObject = currentCellValueString;
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                currentCellValueClassName = "java.util.Date";
                currentCellValueDate = cell.getDateCellValue();
                currentCellValueObject = currentCellValueDate;
            } else {
                currentCellValueClassName = "Double";
                currentCellValueNumber = cell.getNumericCellValue();
                currentCellValueObject = currentCellValueNumber;
            }
        }
        currentCellBgColor = getBgColor(cell);
        currentCellFgColor = getFgColor(cell);
        return currentCellValueObject != null;
    } else {
        return false;
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private String getStringCellValue(Cell cell) {
    String value = null;/*w  ww.  j ava  2  s  .c o m*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                value = defaultDateFormat.format(d);
            } else {
                value = numberFormat.format(cell.getNumericCellValue());
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}

From source file:demons.studentsmanagesystem.excel.poi.PoiSheet.java

License:Apache License

/**
 * {@inheritDoc}/*ww  w.j  a v a  2 s . c  o m*/
 */
@Override
public String[] getRow(final int rowNumber) {
    final Row row = this.delegate.getRow(rowNumber);
    if (row == null) {
        return null;
    }
    final List<String> cells = new LinkedList<String>();

    for (int i = 0; i < getNumberOfColumns(); i++) {
        Cell cell = row.getCell(i);
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                cells.add(String.valueOf(date.getTime()));
            } else {
                cells.add(String.valueOf(cell.getNumericCellValue()));
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cells.add(String.valueOf(cell.getBooleanCellValue()));
            break;
        case Cell.CELL_TYPE_STRING:
        case Cell.CELL_TYPE_BLANK:
            cells.add(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            cells.add(getFormulaEvaluator().evaluate(cell).formatAsString());
            break;
        default:
            throw new IllegalArgumentException("Cannot handle cells of type " + cell.getCellType());
        }
    }
    return cells.toArray(new String[cells.size()]);
}