Example usage for org.apache.poi.ss.usermodel ConditionalFormattingRule createFontFormatting

List of usage examples for org.apache.poi.ss.usermodel ConditionalFormattingRule createFontFormatting

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. ConditionalFormattingRule
  6. createFontFormatting

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel ConditionalFormattingRule createFontFormatting.

Prototype

FontFormatting createFontFormatting();

Source Link

Document

Create a new font formatting structure if it does not exist, otherwise just return existing object.

Usage

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  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.
 *///from   w  w  w  .  j  a va2 s  .co m
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)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * 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.
 *///from  w  w  w  . j  a v a2  s  .c om
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)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight duplicate entries in a column.
 *//*from  w ww  .ja  v  a2s.  com*/
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)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  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.
 *//* w  ww  . j ava2s .  c  o  m*/
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");
}

From source file:domain.Excel.java

public void relleno(String rango, short color_relleno, short color_letra) {

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(color_relleno);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);//from   ww w.  j  a v  a 2 s . c  o m
        font.setFontColorIndex(color_letra);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf(rango) };

        sheetCF.addConditionalFormatting(regions, rule1, rule1);
    }

From source file:domain.Excel.java

public void reporteSesionPeso() {
        //        cargarLogo();

        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:E1"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A2:E2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A3:E4"));

        /*Name REPORT*/
        HSSFFont FontNameReport = wb.createFont();
        FontNameReport.setFontName("Calibri");
        FontNameReport.setFontHeightInPoints((short) 11);
        FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontNameReport.setColor(HSSFColor.DARK_RED.index);

        HSSFCellStyle styleNameReport = wb.createCellStyle();
        styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleNameReport.setFont(FontNameReport);

        Row row = sheet.createRow(0);/*from   w  ww .java 2 s .c  o  m*/
        Cell cell = row.createCell(0);
        cell.setCellValue("REPORTE DE SESIONES POR PESOS");
        cell.setCellStyle(styleNameReport);
        /**/

        /*DATE REPORT*/
        HSSFFont FontDateReport = wb.createFont();
        FontDateReport.setFontName("Calibri");
        FontDateReport.setFontHeightInPoints((short) 10);
        FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontDateReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleDateReport = wb.createCellStyle();
        styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleDateReport.setFont(FontDateReport);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date()));
        cell.setCellStyle(styleDateReport);

        /*Etiqueta parametro*/
        HSSFFont FontParametroReport = wb.createFont();
        FontParametroReport.setFontName("Calibri");
        FontParametroReport.setFontHeightInPoints((short) 9);
        FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        FontParametroReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleParamReport = wb.createCellStyle();
        styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleParamReport.setFont(FontParametroReport);

        row = sheet.createRow(2);
        cell = row.createCell(0);
        String etiqueta_parametro = "";

        switch (tipo) {
        case 1:
            etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini);
            break;
        case 2:
            etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al "
                    + formatoDate.format(fecha_fin);
            break;
        case 3:
            etiqueta_parametro = "Sesiones del animal " + animal.arete_visual;
            break;
        }

        cell.setCellValue(etiqueta_parametro);
        cell.setCellStyle(styleParamReport);

        /**/
        sheet.createRow(5).createCell(0).setCellValue("Arete Visual");
        sheet.getRow(5).createCell(1).setCellValue("Arete Electronico");
        sheet.getRow(5).createCell(2).setCellValue("Fecha");
        sheet.getRow(5).createCell(3).setCellValue("Peso");
        sheet.getRow(5).createCell(4).setCellValue("Corral");

        sheet.setColumnWidth(0, 16 * Unidad);
        sheet.setColumnWidth(1, 16 * Unidad);
        sheet.setColumnWidth(2, 16 * Unidad);
        sheet.setColumnWidth(3, 16 * Unidad);
        sheet.setColumnWidth(4, 16 * Unidad);

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        //Condition 2: Cell Value Is  less than      50   (Green Fill)
        ConditionalFormattingRule rule2 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50");
        PatternFormatting fill2 = rule2.createPatternFormatting();
        fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);
        font.setFontColorIndex(IndexedColors.WHITE.index);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:E6") };

        sheetCF.addConditionalFormatting(regions, rule1, rule2);

        Integer fila_inicial = 6;

        for (int i = 0; i < this.t_tabla.getRowCount(); i++) {

            sheet.createRow(fila_inicial + i).createCell(0).setCellValue(t_tabla.getValueAt(i, 1).toString());
            sheet.getRow(fila_inicial + i).getCell(0).setCellStyle(styleCenter);

            for (int j = 1; j < 5; j++) {
                sheet.getRow(fila_inicial + i).createCell(j).setCellValue(t_tabla.getValueAt(i, j + 1).toString());
                sheet.getRow(fila_inicial + i).getCell(j).setCellStyle(styleCenter);
            }

            sheet.getRow(fila_inicial + i).getCell(3)
                    .setCellValue(Double.parseDouble(t_tabla.getValueAt(i, 4).toString()));
            sheet.getRow(fila_inicial + i).getCell(3).setCellStyle(styleRight);

        }
        cargarLogo();
    }

From source file:domain.Excel.java

private void reporteSesionMedicina() {
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:I1"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A2:I2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A3:I4"));

        /*Name REPORT*/
        HSSFFont FontNameReport = wb.createFont();
        FontNameReport.setFontName("Calibri");
        FontNameReport.setFontHeightInPoints((short) 11);
        FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontNameReport.setColor(HSSFColor.DARK_RED.index);

        HSSFCellStyle styleNameReport = wb.createCellStyle();
        styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleNameReport.setFont(FontNameReport);

        Row row = sheet.createRow(0);//from w ww .ja va  2s  .  com
        Cell cell = row.createCell(0);
        cell.setCellValue("REPORTE DE SESIONES POR MEDICAMENTOS");
        cell.setCellStyle(styleNameReport);
        /**/

        /*DATE REPORT*/
        HSSFFont FontDateReport = wb.createFont();
        FontDateReport.setFontName("Calibri");
        FontDateReport.setFontHeightInPoints((short) 10);
        FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontDateReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleDateReport = wb.createCellStyle();
        styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleDateReport.setFont(FontDateReport);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date()));
        cell.setCellStyle(styleDateReport);

        /*Etiqueta parametro*/
        HSSFFont FontParametroReport = wb.createFont();
        FontParametroReport.setFontName("Calibri");
        FontParametroReport.setFontHeightInPoints((short) 9);
        FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        FontParametroReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleParamReport = wb.createCellStyle();
        styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleParamReport.setFont(FontParametroReport);

        row = sheet.createRow(2);
        cell = row.createCell(0);
        String etiqueta_parametro = "";

        switch (tipo) {
        case 1:
            etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini);
            break;
        case 2:
            etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al "
                    + formatoDate.format(fecha_fin);
            break;
        case 3:
            etiqueta_parametro = "Sesiones del animal " + animal.arete_visual;
            break;
        }

        cell.setCellValue(etiqueta_parametro);
        cell.setCellStyle(styleParamReport);

        /**/
        sheet.createRow(5).createCell(0).setCellValue("Arete Visual");
        sheet.getRow(5).createCell(1).setCellValue("Arete Electronico");
        sheet.getRow(5).createCell(2).setCellValue("Codigo");
        sheet.getRow(5).createCell(3).setCellValue("Medicamento");
        sheet.getRow(5).createCell(4).setCellValue("Fecha");
        sheet.getRow(5).createCell(5).setCellValue("Corral");
        sheet.getRow(5).createCell(6).setCellValue("Dosis");
        sheet.getRow(5).createCell(7).setCellValue("Costo");
        sheet.getRow(5).createCell(8).setCellValue("Importe");

        sheet.setColumnWidth(0, 15 * Unidad);
        sheet.setColumnWidth(1, 20 * Unidad);
        sheet.setColumnWidth(2, 15 * Unidad);
        sheet.setColumnWidth(3, 20 * Unidad);
        sheet.setColumnWidth(4, 20 * Unidad);
        sheet.setColumnWidth(5, 20 * Unidad);
        sheet.setColumnWidth(6, 15 * Unidad);
        sheet.setColumnWidth(7, 15 * Unidad);
        sheet.setColumnWidth(8, 15 * Unidad);

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        //Condition 2: Cell Value Is  less than      50   (Green Fill)
        ConditionalFormattingRule rule2 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50");
        PatternFormatting fill2 = rule2.createPatternFormatting();
        fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);
        font.setFontColorIndex(IndexedColors.WHITE.index);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:I6") };

        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        Integer fila_inicial = 6;

        for (int i = 0; i < this.t_tabla.getRowCount(); i++) {
            agregarValor(fila_inicial + i, 0, t_tabla.getValueAt(i, 1).toString(), styleCenter);

            for (int j = 0; j < 9; j++) {
                agregarValor(fila_inicial + i, j, t_tabla.getValueAt(i, j + 1).toString(), styleCenter);
            }
        }
        cargarLogo();
    }

From source file:net.ceos.project.poi.annotated.core.ConditionalFormattingHandler.java

License:Apache License

/**
 * Apply the conditional formatting according the values defined at the
 * respective annotation. Is only available at the declared object, in
 * another words, at the linked {@link Sheet} with the object.
 * //from w w  w .  java2 s.com
 * @param configCriteria
 *            the {@link XConfigCriteria}
 * @param conditionalFomat
 *            the {@link XlsConditionalFormat}
 * @throws ConfigurationException
 */
protected static void applyCondition(XConfigCriteria configCriteria, XlsConditionalFormat conditionalFomat)
        throws ConfigurationException {
    // Define a Conditional Formatting rule, which triggers formatting
    // according the developer definition and applies patternFormatting
    SheetConditionalFormatting sheet = configCriteria.getSheet().getSheetConditionalFormatting();

    /* apply all rules defined */
    int i = 0;
    ConditionalFormattingRule[] rules = new ConditionalFormattingRule[conditionalFomat.rules().length];
    XlsConditionalFormatRules[] rulesAnnotated = conditionalFomat.rules();
    for (XlsConditionalFormatRules rule : rulesAnnotated) {
        ConditionalFormattingRule setRule = sheet.createConditionalFormattingRule(rule.operator(),
                rule.formula1(), StringUtils.isNotBlank(rule.formula2()) ? rule.formula2() : null);

        CellStyle decorator = null;
        try {
            decorator = configCriteria.getCellStyle(conditionalFomat.decorator());
        } catch (ElementException e) {
            throw new ConfigurationException(ExceptionMessage.CONFIGURATION_DECORATOR_MISSING.getMessage(), e);
        }
        /* add FontFormatting */
        FontFormatting fontFormat = setRule.createFontFormatting();
        Font f = configCriteria.getWorkbook().getFontAt(decorator.getFontIndex());
        fontFormat.setFontStyle(f.getItalic(), f.getBold());
        fontFormat.setFontColorIndex(f.getColor());
        fontFormat.setUnderlineType(f.getUnderline());

        /* add BorderFormatting */
        BorderFormatting borderFormat = setRule.createBorderFormatting();
        borderFormat.setBorderBottom(decorator.getBorderBottom());
        borderFormat.setBorderTop(decorator.getBorderTop());
        borderFormat.setBorderLeft(decorator.getBorderLeft());
        borderFormat.setBorderRight(decorator.getBorderRight());
        borderFormat.setBottomBorderColor(decorator.getBottomBorderColor());
        borderFormat.setTopBorderColor(decorator.getTopBorderColor());
        borderFormat.setLeftBorderColor(decorator.getLeftBorderColor());
        borderFormat.setRightBorderColor(decorator.getRightBorderColor());

        /* add PatternFormatting */
        PatternFormatting patternFormat = setRule.createPatternFormatting();
        patternFormat.setFillBackgroundColor(decorator.getFillForegroundColor());

        /* join rule */
        rules[i++] = setRule;
    }

    /* Define a region */
    CellRangeAddress[] regions = { CellRangeAddress.valueOf(CellFormulaConverter
            .calculateRangeAddressFromTemplate(configCriteria, conditionalFomat.rangeAddress())) };

    /* Apply Conditional Formatting rule defined above to the regions */
    sheet.addConditionalFormatting(regions, rules);

}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * 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.
 *///from w  w w.j a  va 2s. c o  m
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)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * 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./*from   w  w  w .j a va2 s.com*/
 */
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)");
}