Excel Multiple Conditional Formatting Rules - Java POI Example Program

Multiple rules - Introduction

You now know how to apply a single conditional formatting rule to either a XLS or XLSX spreadsheet. POI offers a rather simplistic way to handle multiple rules across a single data range. You can define more than one rule with the same approach we discussed and handle different formatting for the resulting cells. In this example, we will see how to use Apache POI in Java, and handle multiple conditional formatting rules. We have seen enough introduction about this earlier, so we will provide direct Java programs.

XLS - Multiple Conditional Formatting Rules - Java Example Program

A Java program to handle multiple conditional formatting rules is provided below:

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 MultipleRulesConditionalFormatting {  
        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(18));
                
                /* Access conditional formatting facet layer */
                HSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();
                
                /* Create a Rule - Less than or Equal to */
                HSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.LE, "10");
                
                /* Define font formatting if rule is met */             
                HSSFFontFormatting my_rule_pattern = my_rule.createFontFormatting();
                my_rule_pattern.setFontColorIndex(IndexedColors.GREEN.getIndex());
                
                /* Set background fill to Gold*/                
                HSSFPatternFormatting fill_pattern = my_rule.createPatternFormatting();
                fill_pattern.setFillBackgroundColor(IndexedColors.GOLD.index);
                
                /* Define second conditional formatting rules - multiple conditions- greater than or equal to  */
                HSSFConditionalFormattingRule my_rule_2 = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.GE, "13");
                
                /* Format borders for all matching cells using  HSSFBorderFormatting */
                HSSFBorderFormatting border_pattern=my_rule_2.createBorderFormatting();
                border_pattern.setBorderLeft(BorderFormatting.BORDER_DOUBLE);
                border_pattern.setBorderRight(BorderFormatting.BORDER_DOUBLE);
                border_pattern.setBorderTop(BorderFormatting.BORDER_DOUBLE);
                border_pattern.setBorderBottom(BorderFormatting.BORDER_DOUBLE);
                /* You can set border color too, by using relevant methods */
                
                /* Set fill color to Green */
                HSSFPatternFormatting fill_pattern_2 = my_rule_2.createPatternFormatting();
                fill_pattern_2.setFillBackgroundColor(IndexedColors.YELLOW.index);
                
                /* OK, we have defined mutliple rules. Time to attach two rules to same range. We create an array of rules now */
                ConditionalFormattingRule [] multiple_rules = {my_rule,my_rule_2};
                /* Create a Cell Range Address */
                CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A1:A4")};
                
                /* Attach array of rules to the same range */
                my_cond_format_layer.addConditionalFormatting(my_data_range,multiple_rules);
                
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\multiple_conditonal_formatting.xls"));
                my_workbook.write(out);
                out.close();
                
        }
}

Here,we have defined two rules and two different formatting options when the rules are met. You are also introduced to HSSFBorderFormatting class which helps you to format the border of the cells in case the conditions match. Finally, we create an array of ConditionalFormattingRule, and attach all the rules to the same data range using addConditionalFormatting method. Easy!. An example output for the program is provided below:

XLS - Multiple Conditional Formatting Rules - Java POI Example Program - Output

XLSX - Multiple Rules - Java Example Program

The Java program for XLSX files is on the same lines as of XLS. There is no difference except that you use XSSF versions of the class. Here is a Java program that explains how to set multiple rules across a same data range for XLSX files.

import java.io.*;
import org.apache.poi.xssf.usermodel.*; 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class MultipleRulesConditionalFormattingXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet - Add Input Rows */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("XLSX_Conditional_Formatting");
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(new Double(7));
                row = my_sheet.createRow(1);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(22));
                row = my_sheet.createRow(2);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(15));
                row = my_sheet.createRow(3);            
                cell = row.createCell(0);
                cell.setCellValue(new Double(33));
                
                /* Access conditional formatting facet layer */
                XSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();
                
                /* Create a Rule - greater than rule , to pick all cells between 14 and 16 */
                XSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "14", "16");
                
                /* Define font formatting if rule is met */
                /* Royal Blue color for all matching cells */
                XSSFFontFormatting my_rule_pattern = my_rule.createFontFormatting();
                my_rule_pattern.setFontColorIndex(IndexedColors.ROYAL_BLUE.getIndex());
                
                /* Set background fill 25% Grey */
                XSSFPatternFormatting fill_pattern = my_rule.createPatternFormatting();
                fill_pattern.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                 
                /* Rule two - equal to 33 Yellow fill for matches */
                XSSFConditionalFormattingRule my_rule_2= my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL, "33");
                XSSFPatternFormatting fill_pattern_2 = my_rule_2.createPatternFormatting();
                fill_pattern_2.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
                
                /* Create Array of rules */
                ConditionalFormattingRule [] multiple_rules = {my_rule,my_rule_2};
                 /* Create a Cell Range Address */
                CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A1:A4")};
                
                /* Attach multiple rules to cell range */
                my_cond_format_layer.addConditionalFormatting(my_data_range,multiple_rules);
                
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\multiple_rules.xlsx"));
                my_workbook.write(out);
                out.close();
                
        }
}

A sample output is provided below

XLSX - Multiple Conditional Formatting Rules - Java POI Example Program - Output