Example usage for org.apache.poi.ss.usermodel ConditionalFormattingRule getFormula2

Introduction

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

Prototype

String getFormula2();

Source Link

Document

The formula used to evaluate the second operand of the comparison when comparison type is ConditionType#CELL_VALUE_IS and operator is either ComparisonOperator#BETWEEN or ComparisonOperator#NOT_BETWEEN

Usage

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

/**
 * Checks if the given cell value matches a
 * {@link ConditionalFormattingRule} of <code>VALUE_IS</code> type. Covers
 * all cell types and comparison operations.
 *
 * @param cell//w  w w.  j av a  2  s  .co m
 *            Target cell
 * @param rule
 *            Conditional formatting rule to match against.
 * @return True if the given cells value matches the given
 *         <code>VALUE_IS</code> rule, false otherwise
 */
protected boolean matchesValue(Cell cell, ConditionalFormattingRule rule) {

    boolean isFormulaType = cell.getCellType() == Cell.CELL_TYPE_FORMULA;
    boolean isFormulaStringType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING;
    boolean isFormulaBooleanType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_BOOLEAN;
    boolean isFormulaNumericType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC;

    if (isFormulaType) {
        try {
            // make sure we have the latest value for formula cells
            getFormulaEvaluator().evaluateFormulaCell(cell);
        } catch (NotImplementedException e) {
            LOGGER.log(Level.FINEST, e.getMessage(), e);
            return false;
        }
    }

    // other than numerical types
    if (cell.getCellType() == Cell.CELL_TYPE_STRING || isFormulaStringType) {

        // Excel stores conditional formatting strings surrounded with ", so
        // we must surround the cell value. String cell value from POI is
        // never null.
        String quotedStringValue = String.format("\"%s\"", cell.getStringCellValue());

        // Excel string comparison ignores case
        switch (rule.getComparisonOperation()) {
        case ComparisonOperator.EQUAL:
            return quotedStringValue.equalsIgnoreCase(rule.getFormula1());
        case ComparisonOperator.NOT_EQUAL:
            return !quotedStringValue.equalsIgnoreCase(rule.getFormula1());
        }
    }
    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN || isFormulaBooleanType) {
        // not sure if this is used, since no boolean option exists in
        // Excel..

        Boolean formulaVal = Boolean.parseBoolean(rule.getFormula1());

        switch (rule.getComparisonOperation()) {
        case ComparisonOperator.EQUAL:
            return cell.getBooleanCellValue() == formulaVal;
        case ComparisonOperator.NOT_EQUAL:
            return cell.getBooleanCellValue() != formulaVal;
        }
    }

    // numerical types
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || isFormulaNumericType) {

        double formula1Val = -1;
        try {
            formula1Val = Double.valueOf(rule.getFormula1());

        } catch (NumberFormatException w) {
            // non-numeric formatting rules cannot match
            return false;
        }

        switch (rule.getComparisonOperation()) {

        case ComparisonOperator.EQUAL:
            return cell.getNumericCellValue() == formula1Val;
        case ComparisonOperator.NOT_EQUAL:
            return cell.getNumericCellValue() != formula1Val;

        case ComparisonOperator.LT:
            return cell.getNumericCellValue() < formula1Val;
        case ComparisonOperator.LE:
            return cell.getNumericCellValue() <= formula1Val;
        case ComparisonOperator.GT:
            return cell.getNumericCellValue() > formula1Val;
        case ComparisonOperator.GE:
            return cell.getNumericCellValue() >= formula1Val;

        case ComparisonOperator.BETWEEN:
            boolean lt = cell.getNumericCellValue() >= formula1Val;
            boolean gt = cell.getNumericCellValue() <= Double.valueOf(rule.getFormula2());
            return lt && gt;

        case ComparisonOperator.NOT_BETWEEN:
            lt = cell.getNumericCellValue() <= formula1Val;
            gt = cell.getNumericCellValue() >= Double.valueOf(rule.getFormula2());
            return lt && gt;
        }
    }

    return false;
}

---

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

License:Apache License

private static String describeRuleComparisonOperator(ConditionalFormattingRule rule) {
    StringBuilder sb = new StringBuilder();
    sb.append(" comparison:");
    switch (rule.getComparisonOperation()) {
    case ComparisonOperator.LT:
        sb.append(rule.getFormula1());// w w  w. j  av  a2  s  .c  o  m
        sb.append(" < ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.LE:
        sb.append(rule.getFormula1());
        sb.append(" <= ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.GT:
        sb.append(rule.getFormula1());
        sb.append(" > ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.GE:
        sb.append(rule.getFormula1());
        sb.append(" >= ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.EQUAL:
        sb.append(rule.getFormula1());
        sb.append(" = ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.NOT_EQUAL:
        sb.append(rule.getFormula1());
        sb.append(" != ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.BETWEEN:
        sb.append(rule.getFormula1());
        sb.append(" between ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.NOT_BETWEEN:
        sb.append(rule.getFormula1());
        sb.append(" not between ");
        sb.append(rule.getFormula2());
        break;
    case ComparisonOperator.NO_COMPARISON:
        sb.append(" none ");
        break;
    }
    return sb.toString();
}

---