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

Introduction

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

Prototype

boolean getBooleanCellValue();

Source Link

Document

Get the value of the cell as a boolean.

Usage

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

License:EUPL

/**
 * Create sheets.//from   w  w  w  . j av  a 2 s.  c o  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  . ja v a 2  s .co  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 ww . j av a  2 s  . c om
 * @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)/* w  w  w.  ja v  a2  s .com*/
 * 
 * @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.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;// ww  w  .  j a va  2  s .co  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 Boolean getBooleanCellValue(Cell cell) throws Exception {
    Boolean value = null;/*from  w  w  w .  j a 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.SpreadsheetReferencedCellInput.java

License:Apache License

private boolean fetchCurrentCellValue(Cell cell) {
    if (cell != null) {
        currentCell = cell;/* w ww.j  ava  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;/*from  w w  w .  j av a 2s  .  com*/
    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.FunctionExpression.java

License:Apache License

/**
 * Interpretation method for value function
 * /*  w w  w .  j  a  va2 s  .c o  m*/
 * @param workBook
 *            the workbook
 * @param row
 *            the row
 * @return the result value of the cell addressed by internal value
 *         expressions
 * @throws JeXcException
 *             thrown if operation fails
 */
private Object interpretValueFunction(Workbook workBook, Row row) throws JeXcException {
    if (getExpressions().size() != 2) {
        throw new JeXcException("Invalid number of contained value expression, expects 2 but was "
                + getExpressions().size() + ".");
    }
    Object oRowIndex = getExpressions().get(0).interpret(workBook, row);
    Object oColumnIndex = getExpressions().get(1).interpret(workBook, row);
    BigInteger rowIndex = LiteralUtils.asInteger(oRowIndex.toString());
    BigInteger columnIndex = LiteralUtils.asInteger(oColumnIndex.toString());
    Sheet sheet = row.getSheet();
    Row r = sheet.getRow(rowIndex.intValue());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(columnIndex.intValue());
    if (c == null) {
        return null;
    }
    if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        Double d = c.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);
    } else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return c.getStringCellValue();
    } else if (c.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return c.getBooleanCellValue();
    }
    return c.getStringCellValue();
}

---

From source file:de.topicmapslab.jexc.eXql.grammar.expression.ValueExpression.java

License:Apache License

/**
 * Returns the cell value represent by the given token
 * // w w w .j  a  va 2s  .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 + "'!");
}

---

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