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

Example 1 with SheetConditionalFormatting

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

the class ConditionalFormattingEvaluator method getRules.

/**
     * lazy load by sheet since reading can be expensive
     * 
     * @param sheet
     * @return unmodifiable list of rules
     */
protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet) {
    final String sheetName = sheet.getSheetName();
    List<EvaluationConditionalFormatRule> rules = formats.get(sheetName);
    if (rules == null) {
        if (formats.containsKey(sheetName)) {
            return Collections.emptyList();
        }
        final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
        final int count = scf.getNumConditionalFormattings();
        rules = new ArrayList<EvaluationConditionalFormatRule>(count);
        formats.put(sheetName, rules);
        for (int i = 0; i < count; i++) {
            ConditionalFormatting f = scf.getConditionalFormattingAt(i);
            //optimization, as this may be expensive for lots of ranges
            final CellRangeAddress[] regions = f.getFormattingRanges();
            for (int r = 0; r < f.getNumberOfRules(); r++) {
                ConditionalFormattingRule rule = f.getRule(r);
                rules.add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
            }
        }
        // need them in formatting and priority order so logic works right
        Collections.sort(rules);
    }
    return Collections.unmodifiableList(rules);
}

Example 2 with SheetConditionalFormatting

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

the class ConditionalFormats method expiry.

/**
     *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
     *  In this example, Due dates are entered in cells A2:A4.
     */
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));
    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");
    for (int rownum = 1; rownum <= 3; rownum++) {
        sheet.getRow(rownum).getCell(0).setCellStyle(style);
    }
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

Example 3 with SheetConditionalFormatting

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

the class ConditionalFormats method iconSets.

/**
     * Icon Sets / Multi-States allow you to have icons shown which vary
     *  based on the values, eg Red traffic light / Yellow traffic light /
     *  Green traffic light
     */
static void iconSets(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Icon Sets");
    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("Reds");
    r.createCell(1).setCellValue(0);
    r.createCell(2).setCellValue(0);
    r.createCell(3).setCellValue(0);
    r = sheet.createRow(2);
    r.createCell(0).setCellValue("Yellows");
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(5);
    r.createCell(3).setCellValue(5);
    r = sheet.createRow(3);
    r.createCell(0).setCellValue("Greens");
    r.createCell(1).setCellValue(10);
    r.createCell(2).setCellValue(10);
    r.createCell(3).setCellValue(10);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B1:B4") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS);
    IconMultiStateFormatting im1 = rule1.getMultiStateFormatting();
    im1.getThresholds()[0].setRangeType(RangeType.MIN);
    im1.getThresholds()[1].setRangeType(RangeType.PERCENT);
    im1.getThresholds()[1].setValue(33d);
    im1.getThresholds()[2].setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule1);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C1:C4") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_FLAGS);
    IconMultiStateFormatting im2 = rule1.getMultiStateFormatting();
    im2.getThresholds()[0].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[0].setValue(0d);
    im2.getThresholds()[1].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[1].setValue(33d);
    im2.getThresholds()[2].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[2].setValue(67d);
    sheetCF.addConditionalFormatting(regions, rule2);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D1:D4") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_SYMBOLS_CIRCLE);
    IconMultiStateFormatting im3 = rule1.getMultiStateFormatting();
    im3.setIconOnly(true);
    im3.getThresholds()[0].setRangeType(RangeType.MIN);
    im3.getThresholds()[1].setRangeType(RangeType.NUMBER);
    im3.getThresholds()[1].setValue(3d);
    im3.getThresholds()[2].setRangeType(RangeType.NUMBER);
    im3.getThresholds()[2].setValue(7d);
    sheetCF.addConditionalFormatting(regions, rule3);
}

Example 4 with SheetConditionalFormatting

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

the class ConditionalFormats method errors.

/**
     *  Use Excel conditional formatting to check for errors,
     *  and change the font colour to match the cell colour.
     *  In this example, if formula result is  #DIV/0! then it will have white font colour.
     */
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

Example 5 with SheetConditionalFormatting

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

the class ConditionalFormats method multiCell.

/**
     * Highlight multiple cells based on a formula
     */
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");
    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);
    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);
    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =$B2>75   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}