Excel Conditional Formatting - Java POI Example Program

POI Excel Conditional Formatting - Introduction

So, you have a requirement to apply conditional formatting to a cell, based on a rule. You have landed at the right place. Our aim on this example would be to explain in simple terms, how you can set conditional formatting to a range of cells, using Java and Apache POI. We will cover conditional formatting for both XLS and XLSX formats. There are some basic steps when it comes to defining rules:

• Define a conditonal formatting rule.
• Select formatting of cells that match the condition
• Apply rule to a range of cells.

We will discuss all of these with suitable examples. Once we complete this post, we will cover some advanced examples in subsequent posts.

Conditional Formatting - Input Sheet

Let us have an input sheet. Refer to the screenshot below

Conditional Formatting - Excel Java POI Example - Input

The requirement is to highlight cells that equals a value of 12. Here is a XLS example, that explains how to do this, step by step.

Create Sheet And Add Conditional Formatting Rows

The first step is to create a workbook, worksheet and add some data on which we can apply conditional formatting.  We have seen this quite a lot of times now. So, we directly provide the code snippet for this. Refer below:

                /* Create Workbook and Worksheet - Add Input Rows */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Conditional_Formatting");
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(new Double(10));
                row = my_sheet.createRow(1);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(12));
                row = my_sheet.createRow(2);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(13));
                row = my_sheet.createRow(3);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(12));

Get Conditional Formatting Facet of the Worksheet - SheetConditionalFormatting

To apply a rule, you need to the conditional formatting facet.Think of it as a layer for a worksheet on which you can write some rules. Here is how you can get access to the facet;

                /* Access conditional formatting facet layer */
                HSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();

Create Rule - ConditionalFormattingRule

Once you have the layer, you can write rules on it. This is done using the class ConditionalFormattingRule, defined in org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule. We need to first create a rule, which is done using createConditionalFormattingRule method.

                /* Create a Rule */
                HSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL, "12");

Here, we say we want to create a "equal" rule, and the condition should match value of 12.

Attach Formatting to Rule - HSSFFontFormatting

Ok, we defined a rule. What next? The next step is to define a formatting pattern, if the rule is satisfied.  This is done using org.apache.poi.hssf.usermodel.HSSFFontFormatting class. You can get a scope for this object, by invoking createFontFormatting method in HSSFConditionalFormatting class. Here is how we can define a pattern, if the rule is met;

                /* Define font formatting if rule is met */
                /* Here we say, if the rule is met, set font color to Green */
                HSSFFontFormatting my_rule_pattern = my_rule.createFontFormatting();
                my_rule_pattern.setFontColorIndex(IndexedColors.GREEN.getIndex());

Define Cell Range to Apply Rules

Wow, we got a rule and font color on match. Are we done? No. The rule has to be applied on a range of cells.So, you need to define a cell range ,on which your rule will operate. Here is how you can define a cell range for our input.[ we use org.apache.poi.ss.util.CellRangeAddress]

                /* Create a Cell Range Address */
                CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A1:A4")};

Link Cell Range and Rule

Great, we got a cell range. We got a rule / font format. The last step would be to link these two. The SheetConditionalFormatting class has a method that links the rule to a cell range - the method is addConditionalFormatting, and you pass the cell range and rule to this method. The code snippet for this is provided below:

                /* Attach rule to cell range */
                my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule);

Finally, you write the output to a file using workbook.write method, and hope that your conditional formatting is applied. Here is the full Java program, that explains conditional formatting to XLS workbook.

XLS - Conditional Formatting - Java POI Example

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class ConditionalFormatting {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet - Add Input Rows */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Conditional_Formatting");
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(new Double(10));
                row = my_sheet.createRow(1);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(12));
                row = my_sheet.createRow(2);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(13));
                row = my_sheet.createRow(3);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(12));
                
                /* Access conditional formatting facet layer */
                HSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();
                
                /* Create a Rule */
                HSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL, "12");
                
                /* Define font formatting if rule is met */
                /* Here we say, if the rule is met, set font color to Green */
                HSSFFontFormatting my_rule_pattern = my_rule.createFontFormatting();
                my_rule_pattern.setFontColorIndex(IndexedColors.GREEN.getIndex());
                
                /* Create a Cell Range Address */
                CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A1:A4")};
                
                /* Attach rule to cell range */
                my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule);
                
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_conditional_formatting.xls"));
                my_workbook.write(out);
                out.close();
                
        }
}

Program Output:

It works! You have created your first program in POI, that uses conditional formatting on excel spreadsheets. The output is shown below:

XLS - Conditional Formatting - Java POI Example- Output

 Time for some advanced conditional formatting examples. See you soon.