Examples with ConditionalFormattingRule org.apache.poi.ss.usermodel.ConditionalFormattingRule used on opensource projects

Example 6 with ConditionalFormattingRule

use of org.apache.poi.ss.usermodel.ConditionalFormattingRule in project poi by apache.

the class ConditionalFormats method evaluateRules.

/**
     * Print out a summary of the conditional formatting rules applied to cells on the given sheet.
     * Only cells with a matching rule are printed, and for those, all matching rules are sumarized.
     * @param wb
     * @param sheetName
     */
static void evaluateRules(Workbook wb, String sheetName) {
    final WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEval = new ConditionalFormattingEvaluator(wb, wbEvalProv);
    // if cell values have changed, clear cached format results
    cfEval.clearAllCachedValues();
    final Sheet sheet = wb.getSheet(sheetName);
    for (Row r : sheet) {
        for (Cell c : r) {
            final List<EvaluationConditionalFormatRule> rules = cfEval.getConditionalFormattingForCell(c);
            // check rules list for null, although current implementation will return an empty list, not null, then do what you want with results
            if (rules == null || rules.isEmpty())
                continue;
            final CellReference ref = ConditionalFormattingEvaluator.getRef(c);
            if (rules.isEmpty())
                continue;
            System.out.println("\n" + ref.formatAsString() + " has conditional formatting.");
            for (EvaluationConditionalFormatRule rule : rules) {
                ConditionalFormattingRule cf = rule.getRule();
                StringBuilder b = new StringBuilder();
                b.append("\tRule ").append(rule.getFormattingIndex()).append(": ");
                // check for color scale
                if (cf.getColorScaleFormatting() != null) {
                    b.append("\n\t\tcolor scale (caller must calculate bucket)");
                }
                // check for data bar
                if (cf.getDataBarFormatting() != null) {
                    b.append("\n\t\tdata bar (caller must calculate bucket)");
                }
                // check for icon set
                if (cf.getMultiStateFormatting() != null) {
                    b.append("\n\t\ticon set (caller must calculate icon bucket)");
                }
                // check for fill
                if (cf.getPatternFormatting() != null) {
                    final PatternFormatting fill = cf.getPatternFormatting();
                    b.append("\n\t\tfill pattern ").append(fill.getFillPattern()).append(" color index ").append(fill.getFillBackgroundColor());
                }
                // font stuff
                if (cf.getFontFormatting() != null) {
                    final FontFormatting ff = cf.getFontFormatting();
                    b.append("\n\t\tfont format ").append("color index ").append(ff.getFontColorIndex());
                    if (ff.isBold())
                        b.append(" bold");
                    if (ff.isItalic())
                        b.append(" italic");
                    if (ff.isStruckout())
                        b.append(" strikeout");
                    b.append(" underline index ").append(ff.getUnderlineType());
                }
                System.out.println(b);
            }
        }
    }
}

Example 7 with ConditionalFormattingRule

use of org.apache.poi.ss.usermodel.ConditionalFormattingRule in project poi by apache.

the class ConditionalFormats method dataBars.

/**
     * DataBars / Data-Bars allow you to have bars shown vary
     *  based on the values, from full to empty
     */
static void dataBars(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Data Bars");
    Row r = sheet.createRow(1);
    r.createCell(1).setCellValue("Green Positive");
    r.createCell(2).setCellValue("Blue Mix");
    r.createCell(3).setCellValue("Red Negative");
    r = sheet.createRow(2);
    r.createCell(1).setCellValue(0);
    r.createCell(2).setCellValue(0);
    r.createCell(3).setCellValue(0);
    r = sheet.createRow(3);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(-5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(4);
    r.createCell(1).setCellValue(10);
    r.createCell(2).setCellValue(10);
    r.createCell(3).setCellValue(-10);
    r = sheet.createRow(5);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(6);
    r.createCell(1).setCellValue(20);
    r.createCell(2).setCellValue(-10);
    r.createCell(3).setCellValue(-20);
    sheet.setColumnWidth(0, 3000);
    sheet.setColumnWidth(1, 5000);
    sheet.setColumnWidth(2, 5000);
    sheet.setColumnWidth(3, 5000);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF63BE7B");
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db1 = rule1.getDataBarFormatting();
    db1.getMinThreshold().setRangeType(RangeType.MIN);
    db1.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule1);
    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF5A8AC6");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db2 = rule2.getDataBarFormatting();
    db2.getMinThreshold().setRangeType(RangeType.MIN);
    db2.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule2);
    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FFF8696B");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db3 = rule3.getDataBarFormatting();
    db3.getMinThreshold().setRangeType(RangeType.MIN);
    db3.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule3);
}

Example 8 with ConditionalFormattingRule

use of org.apache.poi.ss.usermodel.ConditionalFormattingRule in project poi by apache.

the class ConditionalFormats method hideDupplicates.

/**
     * Use Excel conditional formatting to hide the duplicate values,
     * and make the list easier to read. In this example, when the table is sorted by Region,
     * the second (and subsequent) occurences of each region name will have white font colour.
     */
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(1).createCell(1).setCellValue("<== the second (and subsequent) " + "occurences of each region name will have white font colour.  " + "Condition: Formula Is   =A2=A1   (White Font)");
}

Example 9 with ConditionalFormattingRule

use of org.apache.poi.ss.usermodel.ConditionalFormattingRule in project poi by apache.

the class ConditionalFormats method shadeAlt.

/**
     * Use Excel conditional formatting to shade alternating rows on the worksheet
     */
static void shadeAlt(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

Example 10 with ConditionalFormattingRule

use of org.apache.poi.ss.usermodel.ConditionalFormattingRule 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;
}