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:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private Date getDurationCellValue(Cell cell, String pattern) throws Exception {
    Date value = null;/*  w  ww . j  av  a2 s.  c o  m*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                return parseDuration(s, pattern);
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = getDataFormatter().formatCellValue(cell);
                        value = parseDate(s, pattern);
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getDateCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.NUMERIC) {
            if (parseDateFromVisibleString) {
                String s = getDataFormatter().formatCellValue(cell);
                value = parseDuration(s, pattern);
            } else {
                value = new Date(GenericDateUtil.parseDuration(cell.getNumericCellValue()));
            }
        } else if (cellType == CellType.STRING) {
            String s = getDataFormatter().formatCellValue(cell);
            value = parseDuration(s, pattern);
        }
    }
    return value;
}

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

License:Apache License

private boolean fetchCurrentCellValue(Cell cell) {
    if (cell != null) {
        currentCell = cell;/*from w  ww .  j  a v  a  2  s. c o 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 w  w  .jav  a2 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:de.topicmapslab.jexc.eXql.grammar.expression.ValueExpression.java

License:Apache License

/**
 * Returns the cell value represent by the given token
 * /*from  w ww .j  a v  a2s  .  co m*/
 * @param cell
 *            the cell to extract the values from cell
 * @param token
 *            the token specifies the value to extract
 * @return the cell value
 * @throws JeXcException
 *             thrown if cell value token is unknown
 */
public Object getCellValue(final Cell cell, final String token) throws JeXcException {
    if (VALUE.equalsIgnoreCase(token) || VALUE_STRING.equalsIgnoreCase(token)) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            Double d = cell.getNumericCellValue();
            Long l = d.longValue();
            /*
             * check if long value represents the same numeric value then
             * the double origin
             */
            if (d.doubleValue() == l.longValue()) {
                return String.valueOf(l);
            }
            return String.valueOf(d);
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_STRING:
        default:
            return cell.getStringCellValue();
        }
    } else if (VALUE_DATE.equalsIgnoreCase(token)) {
        return cell.getDateCellValue();
    } else if (VALUE_NUMERICAL.equalsIgnoreCase(token)) {
        return cell.getNumericCellValue();
    } else if (STYLE_FOREGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillForegroundColor();
    } else if (STYLE_BACKGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillBackgroundColor();
    } else if (BORDER_TOP.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderTop();
    } else if (BORDER_BOTTOM.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderBottom();
    } else if (BORDER_LEFT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderLeft();
    } else if (BORDER_RIGHT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderRight();
    } else if (ADDRESS.equalsIgnoreCase(token)) {
        StringBuilder builder = new StringBuilder();
        builder.append(cell.getSheet().getSheetName());
        builder.append(SLASH);
        builder.append(cell.getRow().getRowNum());
        builder.append(COLON);
        builder.append(cell.getColumnIndex());
        return builder.toString();
    } else if (HEIGHT.equalsIgnoreCase(token)) {
        CellRangeAddress address = XlsxCellUtils.getCellRange(cell);
        if (address != null) {
            return address.getLastRow() - address.getFirstRow() + 1;
        }
        return 1;
    } else if (ROW.equalsIgnoreCase(token)) {
        return cell.getRowIndex();
    } else if (COLUMN.equalsIgnoreCase(token)) {
        return cell.getColumnIndex();
    }
    throw new JeXcException("Unknown constant '" + token + "'!");
}

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()]);
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*from  www  .  j a v  a 2s. c om*/
 * @return
 * @throws IllegalStateException
 */
private Competitor parseCompetitorRow(Row row) throws IllegalStateException {
    Competitor competitor = new Competitor();
    // parse competitor data
    for (int i = 1; i < 6; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            switch (i) {
            // parse name
            case 1:
                String name = cell.getStringCellValue();
                if (name != null) {
                    name = StringUtil.ucwords(name);
                    if (name.lastIndexOf(' ') != -1) {
                        competitor.setFirstname(StringUtil.parseFirstname(name));
                        competitor.setSurname(StringUtil.parseSurname(name));
                        log.debug("Found competitor: {}", name);
                    } else {
                        log.error("[{}] Missing firstname and/or surname for row: {}",
                                row.getSheet().getSheetName(), row.getRowNum() + 1);
                    }
                }
                break;

            // parse iso country code
            case 2:
                String country = cell.getStringCellValue();
                if (country != null) {
                    String countryCode = null;
                    if (country.length() > 2) {
                        countryCode = getCountryUtil().getCountryCodeByName(country);
                    } else {
                        countryCode = getCountryUtil().getCountryByCode(country);
                    }
                    if (countryCode != null) {
                        competitor.setCountry(countryCode);
                        log.debug("Country: {} - {}", countryCode, country);
                    } else {
                        log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                                row.getRowNum() + 1);
                    }
                }
                break;

            // parse wca id
            case 3:
                String wcaId = cell.getStringCellValue();
                if (wcaId != null) {
                    wcaId = wcaId.trim();
                    if (wcaId.length() == 10) {
                        Matcher m = wcaIdPattern.matcher(wcaId);
                        if (m.find()) {
                            competitor.setWcaId(wcaId);
                            log.debug("WCA Id: {}", competitor.getWcaId());
                        } else {
                            log.warn("[{}] Invalid wcaId format: {}", row.getSheet().getSheetName(), wcaId);
                        }
                    } else {
                        log.warn("[{}] Entered WCA id has wrong length. Expected: 10, Was: {}. Row: {}",
                                new Object[] { row.getSheet().getSheetName(), wcaId.length(),
                                        row.getRowNum() + 1 });
                    }
                }
                break;

            // parse gender
            case 4:
                String gender = cell.getStringCellValue();
                if (gender != null) {
                    gender = gender.toLowerCase();
                    if ("f".equals(gender) || "m".equals(gender)) {
                        competitor.setGender(gender);
                        log.debug("Gender: {}", ("f".equals(gender) ? "Female" : "Male"));
                    } else {
                        log.warn("[{}] Invalid gender: {}", row.getSheet().getSheetName(), gender);
                    }
                } else {
                    log.warn("[{}] Missing gender information for row: {}", row.getSheet().getSheetName(),
                            row.getRowNum() + 1);
                }
                break;

            // parse birthday
            case 5:
                Date birthday = cell.getDateCellValue();
                if (birthday != null) {
                    try {
                        competitor.setBirthday(birthday);
                        log.debug("Birthday: {}", birthdayFormat.format(birthday));
                    } catch (Exception e) {
                        log.warn("[{}] Invalid birthday format: {}", row.getSheet().getSheetName(), birthday);
                    }
                } else {
                    log.warn("[{}] Missing birthday information for row: {}", row.getSheet().getSheetName(),
                            row.getRowNum() + 1);
                }
                break;
            }
        } else {
            switch (i) {
            case 1:
                log.error("[{}] Missing firstname and/or surname for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 2:
                log.error("[{}] Missing country information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 3:
                // WCA ID are optional
                break;
            case 4:
                log.warn("[{}] Missing gender information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            case 5:
                log.warn("[{}] Missing birthday information for row: {}", row.getSheet().getSheetName(),
                        row.getRowNum() + 1);
                break;
            }
        }
    }

    // parse registered events
    if (competitor.getFirstname() != null && competitor.getSurname() != null) {
        RegisteredEvents registeredEvents = new RegisteredEvents();
        for (int i = 0; i < getLastEventNum(); i++) {
            Cell cell = row.getCell(i + 7);
            boolean registered = false;
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                log.debug("Registered for: {}", getEventName(i));
                registered = true;
            } else {
                log.debug("Not registered for: {}", getEventName(i));
                registered = false;
            }
            try {
                Method method = registeredEvents.getClass().getMethod("setSignedUpFor" + getEventName(i),
                        boolean.class);
                method.invoke(registeredEvents, registered);
            } catch (Exception e) {
                log.error("[{}] " + e.getLocalizedMessage(), row.getSheet().getSheetName(), e);
            }
        }
        competitor.setRegisteredEvents(registeredEvents);
        return competitor;
    } else {
        return null;
    }
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row/*  w ww  .  j a v  a  2 s.  co m*/
 * @param i
 * @return
 * @throws IllegalStateException
 */
private int parseResultCell(Row row, int i) throws IllegalStateException {
    int result = 0;
    Cell cell = row.getCell(3 + i);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        String cellStr = null;

        switch (cell.getCellType()) {
        // result values
        case Cell.CELL_TYPE_NUMERIC:
            // seconds
            if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue() * 100).intValue();

                // minutes
            } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                try {
                    result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                } catch (ParseException e) {
                    log.error("[{}] " + e.getLocalizedMessage(), e);
                }

                // number
            } else if ("0".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue()).intValue();

                // unsupported
            } else {
                log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                        new Object[] { row.getSheet().getSheetName(), cell.getCellStyle().getDataFormatString(),
                                cell.getRowIndex(), cell.getColumnIndex() });
            }
            break;

        // Penalties
        case Cell.CELL_TYPE_STRING:
            cellStr = cell.getStringCellValue();
            if (cellStr != null) {
                if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                    result = Result.Penalty.DNF.getValue();
                } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                    result = Result.Penalty.DNS.getValue();
                }
            }
            break;

        // best / worst
        case Cell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            // calculated value
            case Cell.CELL_TYPE_NUMERIC:
                // seconds
                if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cellValue.getNumberValue() * 100).intValue();

                    // minutes
                } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                    try {
                        result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                    } catch (ParseException e) {
                        log.error("[{}] " + e.getLocalizedMessage(), e);
                    }

                    // number
                } else if ("0".equals(cell.getCellStyle().getDataFormatString())
                        || "GENERAL".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cell.getNumericCellValue()).intValue();

                    // unsupported
                } else {
                    log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                            new Object[] { row.getSheet().getSheetName(),
                                    cell.getCellStyle().getDataFormatString(), cell.getRowIndex(),
                                    cell.getColumnIndex() });
                }
                break;

            // Penalties
            case Cell.CELL_TYPE_STRING:
                cellStr = cellValue.getStringValue();
                if (cellStr != null) {
                    if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                        result = Result.Penalty.DNF.getValue();
                    } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                        result = Result.Penalty.DNS.getValue();
                    }
                }
                break;
            }
            break;
        }
    }
    return result;
}

From source file:edu.vt.cs.irwin.etdscraper.retriever.excel.ExcelEtdSource.java

License:Apache License

private String getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue().toString();
        } else {/*from  w ww  .  j a  va  2 s. co  m*/
            return ((Double) cell.getNumericCellValue()).toString();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return ((Boolean) cell.getBooleanCellValue()).toString();
    case Cell.CELL_TYPE_BLANK:
        return "";
    }
    throw new UnsupportedOperationException("Don't know how to work with type: " + cell.getCellType());
}

From source file:egovframework.rte.fdl.excel.util.EgovExcelUtil.java

License:Apache License

/**
 * ? ? String   .//from   www  .  j  a va  2s  .c  o m
 * 
 * @param cell <code>Cell</code>
 * @return  
 */
public static String getValue(Cell cell) {

    String result = "";

    if (null == cell || cell.equals(null)) {
        return "";
    }

    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        LOGGER.debug("### Cell.CELL_TYPE_BOOLEAN : {}", Cell.CELL_TYPE_BOOLEAN);
        result = String.valueOf(cell.getBooleanCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
        LOGGER.debug("### Cell.CELL_TYPE_ERROR : {}", Cell.CELL_TYPE_ERROR);
        // byte errorValue =
        // cell.getErrorCellValue();

    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        LOGGER.debug("### Cell.CELL_TYPE_FORMULA : {}", Cell.CELL_TYPE_FORMULA);

        String stringValue = null;
        String longValue = null;

        try {
            stringValue = cell.getRichStringCellValue().getString();
            longValue = doubleToString(cell.getNumericCellValue());
        } catch (Exception e) {
            LOGGER.debug("{}", e);
        }

        if (stringValue != null) {
            result = stringValue;
        } else if (longValue != null) {
            result = longValue;
        } else {
            result = cell.getCellFormula();
        }

    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        LOGGER.debug("### Cell.CELL_TYPE_NUMERIC : {}", Cell.CELL_TYPE_NUMERIC);

        result = DateUtil.isCellDateFormatted(cell)
                ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
                : doubleToString(cell.getNumericCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        LOGGER.debug("### Cell.CELL_TYPE_STRING : {}", Cell.CELL_TYPE_STRING);
        result = cell.getRichStringCellValue().getString();

    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        LOGGER.debug("### Cell.CELL_TYPE_BLANK : {}", Cell.CELL_TYPE_BLANK);
    }

    return result;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Reads cell value and formats it according to element type defined in XML Schema. If the cell contains formula,
 * then calculated value is returned./*  ww  w.j  a  v  a 2  s .co  m*/
 *
 * @param cell       Spreadsheet Cell object.
 * @param schemaType XML Schema data type for given cell.
 * @return string value of the cell.
 */
protected String cellValueToString(Cell cell, String schemaType) {
    String value = "";

    if (cell != null) {
        switch (evaluator.evaluateInCell(cell).getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell) && !isYearValue(cell.getNumericCellValue())) {
                Date dateValue = cell.getDateCellValue();
                value = Utils.getFormat(dateValue, DEFAULT_DATE_FORMAT);
            } else if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue()) && schemaType != null
                    && schemaType.equals("xs:date") && !isYearValue(cell.getNumericCellValue())) {
                Date dateValue = cell.getDateCellValue();
                value = Utils.getFormat(dateValue, DEFAULT_DATE_FORMAT);
            } else {
                value = formatter.formatCellValue(cell);
            }
            break;
        case HSSFCell.CELL_TYPE_STRING:
            RichTextString richText = cell.getRichStringCellValue();
            value = richText.toString();
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            break;
        default:
            break;
        }
    }
    return StringUtils.strip(value.trim(), String.valueOf(NON_BREAKING_SPACE)).trim();
}