path: root/poi-examples/src/main/java/org/apache/poi/examples/ss/ConditionalFormats.java

/*
 *  ====================================================================
 *    Licensed to the Apache Software Foundation (ASF) under one or more
 *    contributor license agreements.  See the NOTICE file distributed with
 *    this work for additional information regarding copyright ownership.
 *    The ASF licenses this file to You under the Apache License, Version 2.0
 *    (the "License"); you may not use this file except in compliance with
 *    the License.  You may obtain a copy of the License at
 *
 *        http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 * ====================================================================
 */

package org.apache.poi.examples.ss;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
import org.apache.poi.ss.formula.EvaluationConditionalFormatRule;
import org.apache.poi.ss.formula.WorkbookEvaluatorProvider;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ColorScaleFormatting;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
import org.apache.poi.ss.usermodel.DataBarFormatting;
import org.apache.poi.ss.usermodel.ExtendedColor;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel Conditional Formatting -- Examples
 *
 * <p>
 *   Partly based on the code snippets from
 *   http://www.contextures.com/xlcondformat03.html
 * </p>
 */
@SuppressWarnings({"java:S106","java:S4823","java:S1192"})
public final class ConditionalFormats {

    private ConditionalFormats() {}

    /**
     * generates a sample workbook with conditional formatting,
     * and prints out a summary of applied formats for one sheet
     * @param args pass "-xls" to generate an HSSF workbook, default is XSSF
     */
    public static void main(String[] args) throws IOException {
        final boolean isHSSF = args.length > 0 && args[0].equals("-xls");
        try (Workbook wb = isHSSF ? new HSSFWorkbook() : new XSSFWorkbook()) {

            sameCell(wb.createSheet("Same Cell"));
            multiCell(wb.createSheet("MultiCell"));
            overlapping(wb.createSheet("Overlapping"));
            errors(wb.createSheet("Errors"));
            hideDupplicates(wb.createSheet("Hide Dups"));
            formatDuplicates(wb.createSheet("Duplicates"));
            inList(wb.createSheet("In List"));
            expiry(wb.createSheet("Expiry"));
            shadeAlt(wb.createSheet("Shade Alt"));
            shadeBands(wb.createSheet("Shade Bands"));
            iconSets(wb.createSheet("Icon Sets"));
            colourScales(wb.createSheet("Colour Scales"));
            dataBars(wb.createSheet("Data Bars"));

            // print overlapping rule results
            evaluateRules(wb, "Overlapping");

            // Write the output to a file
            String file = "cf-poi.xls";
            if (wb instanceof XSSFWorkbook) {
                file += "x";
            }
            try (OutputStream out = Files.newOutputStream(Paths.get(file))) {
                wb.write(out);
            }
            System.out.println("Generated: " + file);
        }
    }

    /**
     * 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)");
    }

    /**
     * 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)");
    }

    /**
     * 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.isEmpty()) {
                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);
    }

    /**
     *  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)");
    }

    /**
     * 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) occurrences 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) " +
                "occurrences of each region name will have white font colour.  " +
                "Condition: Formula Is   =A2=A1   (White Font)");
    }

    /**
     * Use Excel conditional formatting to highlight duplicate entries in a column.
     */
    static void formatDuplicates(Sheet sheet) {
        sheet.createRow(0).createCell(0).setCellValue("Code");
        sheet.createRow(1).createCell(0).setCellValue(4);
        sheet.createRow(2).createCell(0).setCellValue(3);
        sheet.createRow(3).createCell(0).setCellValue(6);
        sheet.createRow(4).createCell(0).setCellValue(3);
        sheet.createRow(5).createCell(0).setCellValue(5);
        sheet.createRow(6).createCell(0).setCellValue(8);
        sheet.createRow(7).createCell(0).setCellValue(0);
        sheet.createRow(8).createCell(0).setCellValue(2);
        sheet.createRow(9).createCell(0).setCellValue(8);
        sheet.createRow(10).createCell(0).setCellValue(6);

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        // Condition 1: Formula Is   =A2=A1   (White Font)
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);
        font.setFontColorIndex(IndexedColors.BLUE.index);

        CellRangeAddress[] regions = {
                CellRangeAddress.valueOf("A2:A11")
        };

        sheetCF.addConditionalFormatting(regions, rule1);

        sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +
                "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
    }

    /**
     * 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");
    }

    /**
     *  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");
    }

    /**
     * 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)");
    }

    /**
     * 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)");
    }

    /**
     * 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);
    }

    /**
     * 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.0);
        }
        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.0);
        }
        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);
    }

    /**
     * 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);
    }

    /**
     * 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.
     */
    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);
                }
            }
        }
    }
}