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

Introduction

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

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

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

License:EUPL

/**
 * Create sheets.//from   ww w  .  jav  a2  s.c  om
 * 
 * @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 ww.ja  v a 2s . 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.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

/**
 * get the attribute from the cell. Do basic type conversions.
 * /*  w  w w  .ja v  a2 s. c o  m*/
 * @param cell the cell to read
 * @param convertNumberToDate if true, AND if the cell type is numeric, convert the cell value to Date; use the 
 *        numeric value otherwise.
 * 
 * @return the value from the cell, or null if cell is empty.
 */
@SuppressWarnings({ "PMD.MissingBreakInSwitch", "boxing" })
public static Object getCellValue(Cell cell, boolean convertNumberToDate) {

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (convertNumberToDate) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    case Cell.CELL_TYPE_ERROR:
        LOGGER.error("Error in cell {0}: contains Error", ExcelUtils.getFullCellName(cell));
        return null;
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default: // impossible.
        LOGGER.error("Error in cell {0}: contains unknown cell type.", ExcelUtils.getFullCellName(cell));
        return null;
    }
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns cell content or cell content being referenced in a formula as String. Also reads
 * numeric cells, but these must not be referenced (a poi formulaCell). A cell being NULL or empty
 * returns "". For formulaCells that are not string Formulas, an empty String is returned, too.
 * Directly using poi's getRichStringCellValue() on a HSSFCell being NULL would throw an
 * exception. Cell in row is specified by the headline map and the key. Warning: Does not support
 * Dates (see currentRowCoreKeyToCell)//from w  w  w  . ja  v a 2s  .c o m
 * 
 * @param cell
 *          a cell
 * @return a String from cell or an referenced cell, leading and ending white space removed
 */
public static String contentAsString(Cell cell, ProcessingLog processingLog) {
    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {

    case Cell.CELL_TYPE_STRING: // for richStringsCells and formulaCells
        return cell.getRichStringCellValue().getString().trim();

    case Cell.CELL_TYPE_FORMULA: // for formulaCells / Hyperlinks(id)
        int type = cell.getCachedFormulaResultType();
        String result = "";
        switch (type) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            result = String.valueOf(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            result = String.valueOf(cell.getRichStringCellValue());
            break;
        default:
            break;
        }
        if (result.isEmpty()) {
            processingLog.warn(
                    "Cell [{0}]  Cannot get a String from a numeric cell being referenced by a formula: {1}",
                    getCellRef(cell), cell.getCellFormula());
        }
        return result;

    case Cell.CELL_TYPE_NUMERIC:
        return getNumericCellContentAsString(cell, processingLog);

    default:
        return cell.toString().trim();
    }

}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns cell content as Double. A cell being NULL or empty returns NULL. Directly using poi's
 * getNumericCellValue() on a HSSFCell being NULL would throw an exception. An empty cell would be
 * 0. Cell in row is specified by the headline map and the key.
 * /*from w w w.ja v  a2s  . c  om*/
 * @param row
 *          a poi-HSSFRow
 * @param map
 *          a map to get the index by cell in headline
 * @param key
 *          a key for the map
 * @return a Double
 * @throws NumberFormatException
 *           if the number has a wrong format
 * @throws IllegalStateException
 *           if the content of the given <code>cell</code> is not a number
 */
@SuppressWarnings("boxing")
private static Double contentAsDouble(Cell cell) {
    if (cell == null) {
        throw new IllegalArgumentException("Cell must not be null");
    }

    Double result = null;
    if (!ExcelImportUtilities.isEmpty(cell)) {
        result = cell.getNumericCellValue();
    }
    return result;
}

---

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

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;/*from  w  w  w. j  a  v  a  2s. c  om*/
    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;/*from  w w w. j ava  2  s  .co m*/
    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;//from  w w w.  ja  v  a2  s .c  om
    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 getDurationCellValue(Cell cell, String pattern) throws Exception {
    Date value = null;//ww w . j  a  va 2s. c  om
    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  w w.j a v a  2 s  .  c  om*/
        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;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »