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

Introduction

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

Prototype

@Deprecated
@Removal(version = "4.2")
CellType getCachedFormulaResultTypeEnum();

Source Link

Document

Only valid for formula cells

Usage

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected void readColumnNames() {
    // read the schema from the first row or the named row.
    _headerRange = null;/*from  www.  j  a  v  a  2s . c o m*/
    Row headerRow = null;
    if (StringUtilities.isNotNullOrEmpty(_columnNameAddress)) {
        _headerRange = new ExcelRange(_columnNameAddress);
        CellReference cr = _headerRange.getStartCell();
        headerRow = _sheet.getRow(cr.getRow());
    } else {
        Iterator<Row> iterator = _sheet.iterator();
        headerRow = iterator.next();
    }

    _columnCount = 0;
    int endColumnIndex = (_headerRange == null) ? -1 : _headerRange.getEndColumn();
    String value = "";
    _columnLabels = new ArrayList<String>();
    _columnAddress = new ArrayList<String>();
    List<String> usedLabels = new ArrayList<String>();
    for (Cell cell : headerRow) {
        String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
        if ((endColumnIndex != -1) && (cell.getColumnIndex() > endColumnIndex)) {
            break;
        }

        CellType ct = cell.getCellTypeEnum();
        if (ct == CellType.FORMULA)
            ct = cell.getCachedFormulaResultTypeEnum();
        switch (ct) {
        case STRING:
            value = cell.getStringCellValue();
            if (usedLabels.contains(value.toLowerCase()))
                value = String.format("%s_%s%d", value, columnLetter, cell.getRowIndex() + 1);
            else
                usedLabels.add(value.toLowerCase());
            _columnLabels.add(value);
            _columnAddress.add(columnLetter);
            break;
        case BOOLEAN:
        case NUMERIC:
        case FORMULA:
        case BLANK:
            _columnLabels.add(String.format("Column_%s", columnLetter));
            _columnAddress.add(columnLetter);
            break;
        default:
            break;
        }
    }

    _columnCount = _columnLabels.size();
    _dataSchema = new String[_columnCount][2];
    _dataTypes = new DataType[_columnCount];
    for (int i = 0; i < _columnCount; i++) {
        _dataSchema[i][0] = _columnLabels.get(i);
    }
}

---

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected void readRowSchema(Row dataRow, int endColumnIndex) {
    String cellAddress = "";
    String schemaColumnType = null;
    String currentCellDataType = null;
    try {//from   w w  w.  j av a2  s .  c o  m
        for (Cell cell : dataRow) {
            cellAddress = cell.getAddress().toString();
            String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
            int columnIndex = _columnAddress.indexOf(columnLetter);
            if (columnIndex == -1) {
                continue;
            }

            currentCellDataType = "String";
            CellType ct = cell.getCellTypeEnum();
            if (ct == CellType.FORMULA)
                ct = cell.getCachedFormulaResultTypeEnum();
            switch (ct) {
            case STRING:
                currentCellDataType = "String";
                break;
            case BOOLEAN:
                currentCellDataType = "Boolean";
                break;
            case NUMERIC:
                currentCellDataType = "Double";
                break;
            case FORMULA:
                currentCellDataType = "Object";
                break;
            case BLANK:
                currentCellDataType = "String";
                break;
            default:
                break;
            }

            // Object, String, Numeric, Boolean
            schemaColumnType = _dataSchema[columnIndex][1];
            if (schemaColumnType == null) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Object".equals(schemaColumnType)) {
                // no change
            } else if ("String".equals(schemaColumnType) && "Object".equals(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Double".equals(schemaColumnType) && "Object|String".contains(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Boolean".equals(schemaColumnType)
                    && "Object|String|Double".contains(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            }
            _dataTypes[columnIndex] = DataUtilities.dataTypeToEnum(_dataSchema[columnIndex][1]);
            columnIndex++;
        }
    } catch (Exception ex) {
        throw new PieException(String.format("Error while reading Excel cell %s for its data type (%s). %s",
                cellAddress, currentCellDataType, ex.getMessage()), ex);
    }
}

---

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected Object[] readExcelData(Row excelDataRow) {
    Object[] data = new Object[_columnCount];
    String cellAddress = "";
    int dataIndex = 0;
    try {/*from   w w w . jav  a  2s  . c  o  m*/
        for (Cell cell : excelDataRow) {
            cellAddress = cell.getAddress().toString();
            String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
            int columnIndex = _columnAddress.indexOf(columnLetter);
            if (columnIndex == -1) {
                continue;
            }

            CellType ct = cell.getCellTypeEnum();
            if (ct == CellType.FORMULA)
                ct = cell.getCachedFormulaResultTypeEnum();
            switch (ct) {
            case STRING:
                data[dataIndex] = cell.getStringCellValue();
                break;
            case BOOLEAN:
                data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue())
                        : cell.getBooleanCellValue();
                break;
            case NUMERIC:
                data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue())
                        : cell.getNumericCellValue();
                break;
            default:
                data[dataIndex] = _allTypesStrings ? "" : null;
                break;
            }
            dataIndex++;
        }
        if (_addFilename)
            data[data.length - 1] = _filenameOnly;
    } catch (Exception ex) {
        throw new PieException(
                String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()),
                ex);
    }

    return data;
}

---

From source file:com.streamsets.pipeline.lib.parser.excel.Cells.java

License:Apache License

static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
    CellType cellType = cell.getCellTypeEnum();
    // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
    boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
    if (isFormula) {
        cellType = cell.getCachedFormulaResultTypeEnum();
    }/*from   w  w  w .  j  a va 2 s. com*/

    switch (cellType) {
    case STRING:
        return Field.create(cell.getStringCellValue());
    case NUMERIC:
        Double rawValue = cell.getNumericCellValue(); // resolves formulas automatically and gets value without cell formatting
        String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString()
                : dataFormatter.formatCellValue(cell);
        boolean numericallyEquivalent = false;
        try {
            numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
        } catch (NumberFormatException e) {
        }

        if (DateUtil.isCellDateFormatted(cell)) {
            // It's a date, not a number
            java.util.Date dt = cell.getDateCellValue();
            // if raw number is < 1 then it's a time component only, otherwise date.
            return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
        }

        // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
        return Field
                .create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
        return Field.create(cell.getBooleanCellValue());
    case BLANK:
        return Field.create("");
    default:
        throw new ExcelUnsupportedCellTypeException(cell, cellType);
    }
}

---

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

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;//  w ww.jav a 2  s . 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 Double getDoubleCellValue(Cell cell) throws Exception {
    Double value = null;//  w ww.j  av  a  2  s.com
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                if (s != null && s.trim().isEmpty() == false) {
                    Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                    value = n.doubleValue();
                }
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        if (s != null && s.trim().isEmpty() == false) {
                            Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                            value = n.doubleValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getNumericCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            if (s != null && s.trim().isEmpty() == false) {
                Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                value = n.doubleValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            value = cell.getNumericCellValue();
        }
    }
    return value;
}

---

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

License:Apache License

private Boolean getBooleanCellValue(Cell cell) throws Exception {
    Boolean value = null;/* w w  w .j a va2  s.  c o m*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                value = toBool(s);
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        value = toBool(s);
                    } else if (cellType == CellType.NUMERIC) {
                        double s = cell.getNumericCellValue();
                        value = toBool(s);
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    }
                }
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            value = toBool(s);
        } else if (cellType == CellType.NUMERIC) {
            double s = cell.getNumericCellValue();
            value = toBool(s);
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue();
        }
    }
    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;// ww  w  .  j a  v a  2  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.SpreadsheetInput.java

License:Apache License

private Date getDurationCellValue(Cell cell, String pattern) throws Exception {
    Date value = null;// ww  w.  j a  v  a 2  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:org.sysmodb.CellInfo.java

License:BSD License

private void readCellValueAndType(CellType cellType, Cell cell) {
    switch (cellType) {
    case BLANK://from   w ww.ja v a  2  s .c o  m
        value = "";
        type = "blank";
        break;
    case BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        type = "boolean";
        break;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            type = "datetime";
            Date dateCellValue = cell.getDateCellValue();
            value = dateFormatter.format(dateCellValue);
        } else {
            double numericValue = cell.getNumericCellValue();
            int intValue = (int) numericValue;
            if (intValue == numericValue) {
                value = String.valueOf(intValue);
            } else {
                value = String.valueOf(numericValue);
            }
            type = "numeric";
        }
        break;
    case STRING:
        value = cell.getStringCellValue();
        type = "string";
        break;
    case FORMULA:
        try {
            formula = cell.getCellFormula();
        } catch (FormulaParseException e) {

        }
        CellType resultCellType = cell.getCachedFormulaResultTypeEnum();
        readCellValueAndType(resultCellType, cell);
        break;
    default:
        value = "";
        type = "none";
        break;
    }
}

---

• «
• 1
• 2
• »