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

Introduction

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

Prototype

String getFormula1();

Source Link

Document

The formula used to evaluate the first operand for the conditional formatting rule.

Usage

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

/**
 * Checks if the formula in the given rule evaluates to <code>true</code>.
 * <p>/*from w w w  .  j a v a  2s  . c  om*/
 *
 * NOTE: Does not support HSSF files currently.
 *
 * @param rule
 *            Conditional formatting rule to get the formula from
 * @return True if the formula in the given rule is of boolean formula type
 *         and evaluates to <code>true</code>, false otherwise
 */
protected boolean matchesFormula(ConditionalFormattingRule rule, int deltaColumn, int deltaRow) {
    if (!(rule instanceof XSSFConditionalFormattingRule)) {
        // TODO Does not support HSSF files for now, since HSSF does not
        // read cell references in the file correctly.Since HSSF formulas
        // are read completely wrong, that boolean formula above is useless.
        return false;
    }
    String booleanFormula = rule.getFormula1();

    if (booleanFormula == null || booleanFormula.isEmpty()) {
        return false;
    }

    // Parse formula and use deltas to get relative cell references to work
    // (#18702)
    Ptg[] ptgs = FormulaParser.parse(booleanFormula, WorkbookEvaluatorUtil.getEvaluationWorkbook(spreadsheet),
            FormulaType.CELL, spreadsheet.getActiveSheetIndex());

    for (Ptg ptg : ptgs) {
        // base class for cell reference "things"
        if (ptg instanceof RefPtgBase) {
            RefPtgBase ref = (RefPtgBase) ptg;
            // re-calculate cell references
            if (ref.isColRelative()) {
                ref.setColumn(ref.getColumn() + deltaColumn);
            }
            if (ref.isRowRelative()) {
                ref.setRow(ref.getRow() + deltaRow);
            }
        }
    }

    ValueEval eval;
    try {
        eval = WorkbookEvaluatorUtil.evaluate(spreadsheet, ptgs);
    } catch (NotImplementedException e) {
        LOGGER.log(Level.FINEST, e.getMessage(), e);
        return false;
    }
    if (eval instanceof BoolEval) {
        return eval == null ? false : ((BoolEval) eval).getBooleanValue();
    } else {
        return false;
    }

}

---

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/*from w ww.ja  v  a2  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());
        sb.append(" < ");
        sb.append(rule.getFormula2());//w  w w.ja  v  a  2 s .  com
        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();
}

---

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

License:Apache License

private static String describeRule(ConditionalFormattingRule rule) {
    StringBuilder sb = new StringBuilder();
    sb.append("condition:");
    ConditionType ct = rule.getConditionType();
    if (ct.equals(ConditionType.CELL_VALUE_IS)) {
        sb.append(" cell value is: ");
        sb.append(describeRuleComparisonOperator(rule));
    } else if (ct.equals(ConditionType.FORMULA)) {
        sb.append(" formula: ");
        sb.append(rule.getFormula1());
    } else if (ct.equals(ConditionType.FILTER)) {
        sb.append(" filter: ");
        sb.append(describeRuleComparisonOperator(rule));
    } else if (ct.equals(ConditionType.ICON_SET)) {
        sb.append(" icon set: ");
        sb.append(rule.getMultiStateFormatting());
    } else if (ct.equals(ConditionType.COLOR_SCALE)) {
        sb.append(" color-scale: ");
        sb.append(rule.getColorScaleFormatting());
    } else if (ct.equals(ConditionType.DATA_BAR)) {
        sb.append(" data-bar: ");
        sb.append(rule.getDataBarFormatting());
    } else {/*from ww w.  j av a2  s.c  om*/
        sb.append(" type=" + rule.getConditionType());
    }
    sb.append(" formattings:");
    if (rule.getBorderFormatting() != null) {
        sb.append(" [has border formats]");
    }
    if (rule.getFontFormatting() != null) {
        sb.append(" [has font formattings]");
    }
    if (rule.getPatternFormatting() != null) {
        sb.append(" [has pattern formattings]");
    }
    return sb.toString();
}

---