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

Example 1 with CTConditionalFormatting

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

the class XSSFSheetConditionalFormatting method addConditionalFormatting.

public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
    if (regions == null) {
        throw new IllegalArgumentException("regions must not be null");
    }
    for (CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);
    if (cfRules == null) {
        throw new IllegalArgumentException("cfRules must not be null");
    }
    if (cfRules.length == 0) {
        throw new IllegalArgumentException("cfRules must not be empty");
    }
    if (cfRules.length > 3) {
        throw new IllegalArgumentException("Number of rules must not exceed 3");
    }
    CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
    CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
    List<String> refs = new ArrayList<String>();
    for (CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
    cf.setSqref(refs);
    int priority = 1;
    for (CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()) {
        priority += c.sizeOfCfRuleArray();
    }
    for (ConditionalFormattingRule rule : cfRules) {
        XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) rule;
        xRule.getCTCfRule().setPriority(priority++);
        cf.addNewCfRule().set(xRule.getCTCfRule());
    }
    return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1;
}

Example 2 with CTConditionalFormatting

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

the class XSSFSheetConditionalFormatting method getConditionalFormattingAt.

/**
    * gets Conditional Formatting object at a particular index
    *
    * @param index
    *			of the Conditional Formatting object to fetch
    * @return Conditional Formatting object
    */
public XSSFConditionalFormatting getConditionalFormattingAt(int index) {
    checkIndex(index);
    CTConditionalFormatting cf = _sheet.getCTWorksheet().getConditionalFormattingArray(index);
    return new XSSFConditionalFormatting(_sheet, cf);
}

Example 3 with CTConditionalFormatting

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting 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);
                }
            }
        }
    }
}