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

Example 11 with ConditionalFormattingRule

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

the class ConditionalFormats method overlapping.

/**
     * Multiple conditional formatting rules can apply to
     *  one cell, some combining, some beating others.
     * Done in order of the rules added to the 
     *  SheetConditionalFormatting object
     */
static void overlapping(Sheet sheet) {
    for (int i = 0; i < 40; i++) {
        int rn = i + 1;
        Row r = sheet.createRow(i);
        r.createCell(0).setCellValue("This is row " + rn + " (" + i + ")");
        String str = "";
        if (rn % 2 == 0) {
            str = str + "even ";
        }
        if (rn % 3 == 0) {
            str = str + "x3 ";
        }
        if (rn % 5 == 0) {
            str = str + "x5 ";
        }
        if (rn % 10 == 0) {
            str = str + "x10 ";
        }
        if (str.length() == 0) {
            str = "nothing special...";
        }
        r.createCell(1).setCellValue("It is " + str);
    }
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.getRow(1).createCell(3).setCellValue("Even rows are blue");
    sheet.getRow(2).createCell(3).setCellValue("Multiples of 3 have a grey background");
    sheet.getRow(4).createCell(3).setCellValue("Multiples of 5 are bold");
    sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)");
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Row divides by 10, red (will beat #1)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),10)=0");
    FontFormatting font1 = rule1.createFontFormatting();
    font1.setFontColorIndex(IndexedColors.RED.index);
    // Condition 2: Row is even, blue
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0");
    FontFormatting font2 = rule2.createFontFormatting();
    font2.setFontColorIndex(IndexedColors.BLUE.index);
    // Condition 3: Row divides by 5, bold
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("MOD(ROW(),5)=0");
    FontFormatting font3 = rule3.createFontFormatting();
    font3.setFontStyle(false, true);
    // Condition 4: Row divides by 3, grey background
    ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule("MOD(ROW(),3)=0");
    PatternFormatting fill4 = rule4.createPatternFormatting();
    fill4.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
    fill4.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    // Apply
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:F41") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheetCF.addConditionalFormatting(regions, rule2);
    sheetCF.addConditionalFormatting(regions, rule3);
    sheetCF.addConditionalFormatting(regions, rule4);
}

Example 12 with ConditionalFormattingRule

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

the class ConditionalFormats method shadeBands.

/**
     * You can use Excel conditional formatting to shade bands of rows on the worksheet. 
     * In this example, 3 rows are shaded light grey, and 3 are left with no shading.
     * In the MOD function, the total number of rows in the set of banded rows (6) is entered.
     */
static void shadeBands(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),6)<2   (Light Grey Fill)");
}

Example 13 with ConditionalFormattingRule

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

the class ConditionalFormats method colourScales.

/**
     * Color Scales / Colour Scales / Colour Gradients allow you shade the
     *  background colour of the cell based on the values, eg from Red to
     *  Yellow to Green.
     */
static void colourScales(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Colour Scales");
    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("Red-Yellow-Green");
    for (int i = 1; i <= 7; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(2);
    r.createCell(0).setCellValue("Red-White-Blue");
    for (int i = 1; i <= 9; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(3);
    r.createCell(0).setCellValue("Blue-Green");
    for (int i = 1; i <= 16; i++) {
        r.createCell(i).setCellValue((i - 1));
    }
    sheet.setColumnWidth(0, 5000);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs1 = rule1.getColorScaleFormatting();
    cs1.getThresholds()[0].setRangeType(RangeType.MIN);
    cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs1.getThresholds()[1].setValue(50d);
    cs1.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs1.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs1.getColors()[1]).setARGBHex("FFFFEB84");
    ((ExtendedColor) cs1.getColors()[2]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule1);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs2 = rule2.getColorScaleFormatting();
    cs2.getThresholds()[0].setRangeType(RangeType.MIN);
    cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs2.getThresholds()[1].setValue(50d);
    cs2.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs2.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs2.getColors()[1]).setARGBHex("FFFCFCFF");
    ((ExtendedColor) cs2.getColors()[2]).setARGBHex("FF5A8AC6");
    sheetCF.addConditionalFormatting(regions, rule2);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs3 = rule3.getColorScaleFormatting();
    cs3.setNumControlPoints(2);
    cs3.getThresholds()[0].setRangeType(RangeType.MIN);
    cs3.getThresholds()[1].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs3.getColors()[0]).setARGBHex("FF5A8AC6");
    ((ExtendedColor) cs3.getColors()[1]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule3);
}

Example 14 with ConditionalFormattingRule

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

the class ConditionalFormats method sameCell.

/**
     * Highlight cells based on their values
     */
static void sameCell(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Cell Value Is   greater than  70   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    // Condition 2: Cell Value Is  less than      50   (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };
    sheetCF.addConditionalFormatting(regions, rule1, rule2);
    sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
    sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
}

Example 15 with ConditionalFormattingRule

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

the class ConditionalFormats method inList.

/**
     * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
     */
static void inList(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Codes");
    sheet.createRow(1).createCell(0).setCellValue("AA");
    sheet.createRow(2).createCell(0).setCellValue("BB");
    sheet.createRow(3).createCell(0).setCellValue("GG");
    sheet.createRow(4).createCell(0).setCellValue("AA");
    sheet.createRow(5).createCell(0).setCellValue("FF");
    sheet.createRow(6).createCell(0).setCellValue("XX");
    sheet.createRow(7).createCell(0).setCellValue("CC");
    sheet.getRow(0).createCell(2).setCellValue("Valid");
    sheet.getRow(1).createCell(2).setCellValue("AA");
    sheet.getRow(2).createCell(2).setCellValue("BB");
    sheet.getRow(3).createCell(2).setCellValue("CC");
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(3).setCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}