Examples with CTCfRule org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule used on opensource projects

Example 1 with CTCfRule

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule in project poi by apache.

the class XSSFSheetConditionalFormatting method createConditionalFormattingRule.

/**
     * A factory method allowing to create a conditional formatting rule with a formula.<br>
     *
     * @param formula - formula for the valued, compared with the cell
     */
public XSSFConditionalFormattingRule createConditionalFormattingRule(String formula) {
    XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
    CTCfRule cfRule = rule.getCTCfRule();
    cfRule.addFormula(formula);
    cfRule.setType(STCfType.EXPRESSION);
    return rule;
}

Example 2 with CTCfRule

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule in project poi by apache.

the class XSSFRowShifter method updateConditionalFormatting.

public void updateConditionalFormatting(FormulaShifter shifter) {
    XSSFSheet xsheet = (XSSFSheet) sheet;
    XSSFWorkbook wb = xsheet.getWorkbook();
    int sheetIndex = wb.getSheetIndex(sheet);
    //don't care, structured references not allowed in conditional formatting
    final int rowIndex = -1;
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
    CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
    CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
    // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
    for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
        CTConditionalFormatting cf = conditionalFormattingArray[j];
        ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
        for (Object stRef : cf.getSqref()) {
            String[] regions = stRef.toString().split(" ");
            for (String region : regions) {
                cellRanges.add(CellRangeAddress.valueOf(region));
            }
        }
        boolean changed = false;
        List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
        for (CellRangeAddress craOld : cellRanges) {
            CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
            if (craNew == null) {
                changed = true;
                continue;
            }
            temp.add(craNew);
            if (craNew != craOld) {
                changed = true;
            }
        }
        if (changed) {
            int nRanges = temp.size();
            if (nRanges == 0) {
                ctWorksheet.removeConditionalFormatting(j);
                continue;
            }
            List<String> refs = new ArrayList<String>();
            for (CellRangeAddress a : temp) refs.add(a.formatAsString());
            cf.setSqref(refs);
        }
        for (CTCfRule cfRule : cf.getCfRuleArray()) {
            String[] formulaArray = cfRule.getFormulaArray();
            for (int i = 0; i < formulaArray.length; i++) {
                String formula = formulaArray[i];
                Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
                if (shifter.adjustFormula(ptgs, sheetIndex)) {
                    String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
                    cfRule.setFormulaArray(i, shiftedFmla);
                }
            }
        }
    }
}

Example 3 with CTCfRule

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule in project poi by apache.

the class XSSFSheetConditionalFormatting method createConditionalFormattingRule.

/**
     * A factory method allowing to create a conditional formatting rule
     * with a cell comparison operator<p/>
     * TODO - formulas containing cell references are currently not parsed properly
     *
     * @param comparisonOperation - a constant value from
     *		 <tt>{@link org.apache.poi.hssf.record.CFRuleBase.ComparisonOperator}</tt>: <p>
     * <ul>
     *		 <li>BETWEEN</li>
     *		 <li>NOT_BETWEEN</li>
     *		 <li>EQUAL</li>
     *		 <li>NOT_EQUAL</li>
     *		 <li>GT</li>
     *		 <li>LT</li>
     *		 <li>GE</li>
     *		 <li>LE</li>
     * </ul>
     * </p>
     * @param formula1 - formula for the valued, compared with the cell
     * @param formula2 - second formula (only used with
     * {@link org.apache.poi.ss.usermodel.ComparisonOperator#BETWEEN}) and
     * {@link org.apache.poi.ss.usermodel.ComparisonOperator#NOT_BETWEEN} operations)
     */
public XSSFConditionalFormattingRule createConditionalFormattingRule(byte comparisonOperation, String formula1, String formula2) {
    XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
    CTCfRule cfRule = rule.getCTCfRule();
    cfRule.addFormula(formula1);
    if (formula2 != null)
        cfRule.addFormula(formula2);
    cfRule.setType(STCfType.CELL_IS);
    STConditionalFormattingOperator.Enum operator;
    switch(comparisonOperation) {
        case ComparisonOperator.BETWEEN:
            operator = STConditionalFormattingOperator.BETWEEN;
            break;
        case ComparisonOperator.NOT_BETWEEN:
            operator = STConditionalFormattingOperator.NOT_BETWEEN;
            break;
        case ComparisonOperator.LT:
            operator = STConditionalFormattingOperator.LESS_THAN;
            break;
        case ComparisonOperator.LE:
            operator = STConditionalFormattingOperator.LESS_THAN_OR_EQUAL;
            break;
        case ComparisonOperator.GT:
            operator = STConditionalFormattingOperator.GREATER_THAN;
            break;
        case ComparisonOperator.GE:
            operator = STConditionalFormattingOperator.GREATER_THAN_OR_EQUAL;
            break;
        case ComparisonOperator.EQUAL:
            operator = STConditionalFormattingOperator.EQUAL;
            break;
        case ComparisonOperator.NOT_EQUAL:
            operator = STConditionalFormattingOperator.NOT_EQUAL;
            break;
        default:
            throw new IllegalArgumentException("Unknown comparison operator: " + comparisonOperation);
    }
    cfRule.setOperator(operator);
    return rule;
}