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:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

private String getCellContentAsString(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 w  w.j  ava2  s.c  o m
            double d = cell.getNumericCellValue();
            // TODO find a flexible enough format for all numeric types
            return numberFormat.format(d);
            // return Double.toString(d);
        }
    case Cell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.toString(b);
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        byte bt = cell.getErrorCellValue();
        return Byte.toString(bt);
    default:
        return cell.getStringCellValue();

    }
}

---

From source file:com.tm.hiber.service.TMDataOperationServiceImpl.java

private String getCellData(Cell objCell) {
    mLogger.log(Level.INFO, "getCellData--Starts");
    String response;//from  www  . ja v a 2 s.  c  om
    switch (objCell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        Double d = objCell.getNumericCellValue();
        response = String.valueOf(d.intValue());
        break;
    case Cell.CELL_TYPE_STRING:
        response = objCell.getStringCellValue();
        break;
    default:
        response = objCell.getStringCellValue();
        break;
    }
    mLogger.log(Level.INFO, "getCellData--Ends");
    return response;
}

---

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Set's cell value for the newCell. It will be the same as shiftedCell
 * unless sequenceIncrement is not null, in that case the value changes
 * depending on sequenceIncrement and cell distance
 * //from  w  w  w. j  av a 2 s.  c o m
 * @param shiftedCell
 *            Source cell
 * @param newCell
 *            Resulting new cell
 * @param sequenceIncrement
 *            not null to increase the number in source cell
 */
private void shiftNumeric(Cell shiftedCell, Cell newCell, Double sequenceIncrement) {
    if (sequenceIncrement != null) {
        int dif;
        if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) {
            // shift column indexes
            dif = newCell.getColumnIndex() - shiftedCell.getColumnIndex();
        } else {
            dif = newCell.getRowIndex() - shiftedCell.getRowIndex();
        }
        newCell.setCellValue(shiftedCell.getNumericCellValue() + sequenceIncrement * dif);
    } else {
        newCell.setCellValue(shiftedCell.getNumericCellValue());
    }
}

---

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Returns an array with Double values in column with columnIndex from row
 * r1 to row r2 in activeSheet until first non numeric cell or null value
 * Used by//from   ww  w.  ja v a2  s  . c  om
 * {@link CellSelectionShifter#getColumnSequenceIncrement(int, int, int)}
 * 
 * @param activeSheet
 *            Sheet where the cells are goint to be taken from
 * @param columnIndex
 *            Defines the origin of the cell values to be returned, 1-based
 * @param r1
 *            First row of the column to be returned, 1-based
 * @param r2
 *            Last row of the column to be returned, 1-based
 * @return Double array with values
 */
private Double[] getColumnNumericValues(Sheet activeSheet, int columnIndex, int r1, int r2) {
    Double[] result = new Double[r2 - r1 + 1];
    Cell cell;
    Row row;
    for (int i = r1; i <= r2; i++) {
        row = activeSheet.getRow(i - 1);
        if (row != null) {
            cell = row.getCell(columnIndex - 1);
            if (cell != null && cell.getCellType() == CELL_TYPE_NUMERIC) {
                result[i - r1] = cell.getNumericCellValue();
            } else {
                break;
            }
        } else {
            break;
        }
    }
    return result;
}

---

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Returns an array with Double values in row from column c1 to column c2
 * until first non Numeric cell or null value. Used by
 * {@link CellSelectionShifter#getRowSequenceIncrement(int, int, int)}
 * /*from   ww w .ja  va  2s  .  c om*/
 * @param row
 *            Row where the cells are going to be taken from
 * @param c1
 *            First column of the row to be returned, 1-based
 * @param c2
 *            Last column of the column to be returned, 1-based
 * @return Double array with values
 */
private Double[] getRowNumericValues(Row row, int c1, int c2) {
    Double[] result = new Double[c2 - c1 + 1];
    Cell shiftedCell;
    for (int i = c1; i <= c2; i++) {
        shiftedCell = row.getCell(i - 1);
        if (shiftedCell != null && shiftedCell.getCellType() == CELL_TYPE_NUMERIC) {
            result[i - c1] = shiftedCell.getNumericCellValue();
        } else {
            break;
        }
    }
    return result;
}

---

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

private String getCachedFormulaCellValue(Cell formulaCell) {
    String result = null;/*from  w  w w  . ja  va2 s.co  m*/
    switch (formulaCell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_STRING:
        result = formulaCell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(formulaCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        result = ErrorEval.getText(formulaCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        CellStyle style = formulaCell.getCellStyle();
        result = formatter.formatRawCellContents(formulaCell.getNumericCellValue(), style.getDataFormat(),
                style.getDataFormatString());
        break;
    }
    return result;
}

---

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

protected CellData createCellDataForCell(Cell cell) {
    CellData cellData = new CellData();
    cellData.row = cell.getRowIndex() + 1;
    cellData.col = cell.getColumnIndex() + 1;
    CellStyle cellStyle = cell.getCellStyle();
    cellData.cellStyle = "cs" + cellStyle.getIndex();
    cellData.locked = spreadsheet.isCellLocked(cell);
    try {//  w ww  .j  a v  a  2 s .  c  om
        if (!spreadsheet.isCellHidden(cell)) {
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cellData.formulaValue = formulaFormatter.reFormatFormulaValue(cell.getCellFormula(),
                        spreadsheet.getLocale());
                try {
                    String oldValue = getCachedFormulaCellValue(cell);
                    String newValue = formatter.formatCellValue(cell, getFormulaEvaluator());
                    if (!newValue.equals(oldValue)) {
                        changedFormulaCells.add(new CellReference(cell));
                    }
                } catch (RuntimeException rte) {
                    // Apache POI throws RuntimeExceptions for an invalid
                    // formula from POI model
                    String formulaValue = cell.getCellFormula();
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(formulaValue);
                    spreadsheet.markInvalidFormula(cell.getColumnIndex() + 1, cell.getRowIndex() + 1);
                }

            }
        }

        if (cell.getCellStyle().getDataFormatString().contains("%")) {
            cellData.isPercentage = true;
        }

        String formattedCellValue = formatter.formatCellValue(cell, getFormulaEvaluator());

        if (!spreadsheet.isCellHidden(cell)) {
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                formattedCellValue = formattedCellValue.replaceAll("^-(?=0(.0*)?$)", "");
            }
        }
        if (spreadsheet.isMarkedAsInvalidFormula(cellData.col, cellData.row)) {
            // The prefix '=' or '+' should not be included in formula value
            if (cell.getStringCellValue().charAt(0) == '+' || cell.getStringCellValue().charAt(0) == '=') {
                cellData.formulaValue = cell.getStringCellValue().substring(1);
            }
            formattedCellValue = "#VALUE!";
        }

        if (formattedCellValue != null && !formattedCellValue.isEmpty() || cellStyle.getIndex() != 0) {
            // if the cell is not wrapping text, and is of type numeric or
            // formula (but not date), calculate if formatted cell value
            // fits the column width and possibly use scientific notation.
            cellData.value = formattedCellValue;
            cellData.needsMeasure = false;
            if (!cellStyle.getWrapText()
                    && (!SpreadsheetUtil.cellContainsDate(cell) && cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                            || cell.getCellType() == Cell.CELL_TYPE_STRING
                            || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                    && !cell.getCellFormula().startsWith("HYPERLINK")))) {
                if (!doesValueFit(cell, formattedCellValue)) {
                    if (valueContainsOnlyNumbers(formattedCellValue) && isGenerallCell(cell)) {
                        cellData.value = cellValueFormatter.getScientificNotationStringForNumericCell(
                                cell.getNumericCellValue(), formattedCellValue,
                                cellStyleWidthRatioMap.get((int) cell.getCellStyle().getIndex()),
                                spreadsheet.getState(false).colW[cell.getColumnIndex()] - 10);
                    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        cellData.needsMeasure = true;
                    }
                }
            }

            if (cellStyle.getAlignment() == CellStyle.ALIGN_RIGHT) {
                cellData.cellStyle = cellData.cellStyle + " r";
            } else if (cellStyle.getAlignment() == CellStyle.ALIGN_GENERAL) {
                if (SpreadsheetUtil.cellContainsDate(cell) || cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                        || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                && !cell.getCellFormula().startsWith("HYPERLINK")
                                && !(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING))) {
                    cellData.cellStyle = cellData.cellStyle + " r";
                }
            }

        }

        // conditional formatting might be applied even if there isn't a
        // value (such as borders for the cell to the right)
        Set<Integer> cellFormattingIndexes = spreadsheet.getConditionalFormatter().getCellFormattingIndex(cell);
        if (cellFormattingIndexes != null) {

            for (Integer i : cellFormattingIndexes) {
                cellData.cellStyle = cellData.cellStyle + " cf" + i;
            }

            markedCells.add(SpreadsheetUtil.toKey(cell));
        }

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            cellData.originalValue = cellData.value;
        } else {
            cellData.originalValue = getOriginalCellValue(cell);
        }

        handleIsDisplayZeroPreference(cell, cellData);
    } catch (RuntimeException rte) {
        LOGGER.log(Level.FINEST, rte.getMessage(), rte);
        cellData.value = "#VALUE!";
    }

    return cellData;
}

---

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

public String getOriginalCellValue(Cell cell) {
    if (cell == null) {
        return "";
    }/*from   w ww  .j a v  a2  s .co  m*/

    int cellType = cell.getCellType();
    switch (cellType) {
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date dateCellValue = cell.getDateCellValue();
            if (dateCellValue != null) {
                return new SimpleDateFormat().format(dateCellValue);
            }
            return "";
        }
        return originalValueDecimalFormat.format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return String.valueOf(cell.getErrorCellValue());
    }
    return "";
}

---

From source file:com.vaadin.addon.spreadsheet.charts.converter.Utils.java

public static Double getNumericValue(CellReference ref, Spreadsheet spreadsheet) {
    try {/*  ww w .j  a v  a  2s  . com*/
        Sheet sheet = spreadsheet.getWorkbook().getSheet(ref.getSheetName());
        Cell cell = spreadsheet.getCell(ref, sheet);
        spreadsheet.getFormulaEvaluator().evaluateFormulaCell(cell);

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getNumericCellValue();
        }
    } catch (NullPointerException e) {
    } catch (IllegalStateException e) {
    } catch (NumberFormatException e) {

    } catch (FormulaParseException e) {
        logError();
    }
    return null;
}

---

From source file:com.vaadin.addon.spreadsheet.command.CellValueCommand.java

License:Open Source License

/**
 * Returns the current value of the given Cell
 * //from w w w.ja  v  a 2 s  .c  o m
 * @param cell
 *            Target cell
 * @return Current value of the cell or null if not available
 */
protected Object getCellValue(Cell cell) {
    if (cell == null) {
        return null;
    } else {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_ERROR:
            return cell.getErrorCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return "=" + cell.getCellFormula();
        case Cell.CELL_TYPE_NUMERIC:
            return cell.getNumericCellValue();
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        default:
            return null;
        }
    }
}

---

• «
• 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
• »