Excel Conditional Formatting in Java

Following example shows how to apply various kinds of Conditional Formatting rules to cell ranges of an Excel worksheet with Java.

Conditional Formatting resolution is currently not implemented in GemBox.Spreadsheet. Conditional Formatting is loaded from and saved to XLSX and XLS file format only.

Excel conditional formatting created with GemBox.Spreadsheet for Java
Screenshot of Excel conditional formatting created with GemBox.Spreadsheet for Java
import com.gembox.spreadsheet.*;
import com.gembox.spreadsheet.conditionalformatting.*;
import java.time.LocalDateTime;
import java.util.Random;

class Program {

    public static void main(String[] args) throws java.io.IOException {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.addWorksheet("Conditional Formatting");

        int rowCount = %RowCount%;

        // Specify sheet formatting.
        worksheet.getRow(0).getStyle().getFont().setWeight(ExcelFont.BOLD_WEIGHT);
        worksheet.getColumn(0).setWidth(3, LengthUnit.CENTIMETER);
        worksheet.getColumn(1).setWidth(3, LengthUnit.CENTIMETER);
        worksheet.getColumn(2).setWidth(3, LengthUnit.CENTIMETER);
        worksheet.getColumn(3).setWidth(3, LengthUnit.CENTIMETER);
        worksheet.getColumn(3).getStyle().setNumberFormat("[$$-409]#,##0.00");
        worksheet.getColumn(4).setWidth(3, LengthUnit.CENTIMETER);
        worksheet.getColumn(4).getStyle().setNumberFormat("yyyy-mm-dd");

        CellRange cells = worksheet.getCells();

        // Specify header row.
        cells.get(0, 0).setValue("Departments");
        cells.get(0, 1).setValue("Names");
        cells.get(0, 2).setValue("Years of Service");
        cells.get(0, 3).setValue("Salaries");
        cells.get(0, 4).setValue("Deadlines");

        // Insert random data to sheet.
        Random random = new Random();
        String[] departments = new String[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
        String[] names = new String[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
        for (int i = 0; i < rowCount; ++i)
        {
            cells.get(i + 1, 0).setValue(departments[random.nextInt(departments.length)]);
            cells.get(i + 1, 1).setValue(names[random.nextInt(names.length)] + ' ' + (i + 1));
            cells.get(i + 1, 2).setValue(random.nextInt(30) + 1);
            cells.get(i + 1, 3).setValue((random.nextInt(91) + 10) * 100);
            cells.get(i + 1, 4).setValue(LocalDateTime.now().plusDays(random.nextInt(3) - 1));
        }

        // Apply shading to alternate rows in a worksheet using 'Formula' based conditional formatting.
        worksheet.getConditionalFormatting().addFormula(worksheet.getCells().getName(), "MOD(ROW(),2)=0").
                getStyle().getFillPattern().setPatternBackgroundColor(SpreadsheetColor.fromName(ColorName.ACCENT_1_LIGHTER_40_PCT));
        worksheet.getConditionalFormatting().addFormula(worksheet.getCells().getName(), "MOD(ROW(),2)=1").
                getStyle().getFillPattern().setPatternBackgroundColor(SpreadsheetColor.fromName(ColorName.ACCENT_5_LIGHTER_80_PCT));

        // Apply '2-Color Scale' conditional formatting to 'Years of Service' column. Supported only in XLSX
        worksheet.getConditionalFormatting().add2ColorScale("C2:C" + (rowCount + 1));

        // Apply '3-Color Scale' conditional formatting to 'Salaries' column. Supported only in XLSX
        worksheet.getConditionalFormatting().add3ColorScale("D2:D" + (rowCount + 1));

        // Apply 'Data Bar' conditional formatting to 'Salaries' column. Supported only in XLSX
        worksheet.getConditionalFormatting().addDataBar("D2:D" + (rowCount + 1));

        // Apply 'Icon Set' conditional formatting to 'Years of Service' column. Supported only in XLSX
        worksheet.getConditionalFormatting().addIconSet("C2:C" + (rowCount + 1)).setIconStyle(SpreadsheetIconStyle.FOUR_TRAFFIC_LIGHTS);

        // Apply green font color to cells in a 'Years of Service' column which have values between 15 and 20.
        worksheet.getConditionalFormatting().addContainValue("C2:C" + (rowCount + 1), ContainValueOperator.BETWEEN, 15, 20)
                .getStyle().getFont().setColor(SpreadsheetColor.fromName(ColorName.GREEN));

        // Apply double red border to cells in a 'Names' column which contain text 'Doe'.
        worksheet.getConditionalFormatting().addContainText("B2:B" + (rowCount + 1), ContainTextOperator.CONTAINS, "Doe")
                .getStyle().getBorders().setBorders(MultipleBorders.outside(), SpreadsheetColor.fromName(ColorName.RED), LineStyle.DOUBLE);

        // Apply red shading to cells in a 'Deadlines' column which are equal to yesterday's date. Supported only in XLSX
        worksheet.getConditionalFormatting().addContainDate("E2:E" + (rowCount + 1), ContainDateOperator.YESTERDAY)
                .getStyle().getFillPattern().setPatternBackgroundColor(SpreadsheetColor.fromName(ColorName.RED));

        // Apply bold font weight to cells in a 'Salaries' column which have top 10 values. Supported only in XLSX
        worksheet.getConditionalFormatting().addTopOrBottomRanked("D2:D" + (rowCount + 1), false, 10)
                .getStyle().getFont().setWeight(ExcelFont.BOLD_WEIGHT);

        // Apply double underline to cells in a 'Years of Service' column which have below average value. Supported only in XLSX
        worksheet.getConditionalFormatting().addAboveOrBelowAverage("C2:C" + (rowCount + 1), true)
                .getStyle().getFont().setUnderlineStyle(UnderlineStyle.DOUBLE);

        // Apply italic font style to cells in a 'Departments' column which have duplicate values.
        worksheet.getConditionalFormatting().addUniqueOrDuplicate("A2:A" + (rowCount + 1), true)
                .getStyle().getFont().setItalic(true);

        workbook.save("Conditional Formatting.%OutputFileType%");
    }
}

See also


Next steps

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy