Highlight Multiple Cells - Formula - Java POI Example Program

Highlight Multiple Cells Based on Formula - Conditional Formatting

In this tutorial, we will provide a Java program that teaches how to apply conditional formatting and highlight multiple cells based on a formula, using Apache POI library. This example can be considered as an intermediate level conditional formatting tutorial, as we slowly introduce you to some complex examples. To understand what we are talking about, refer to the excel cells below:

Highlight Multiple Cells Formula  - Conditional Formatting - Java Example Program

Here, we highlight an entire row if the cost per item is greater than or equal to 100. Let us discuss how to write a program for this in Java and get this output for XLS and XLSX files. We have covered the basics of conditional formatting already, so we will provide only the program here.

XLS - Highlight Cells Based on Formula - Java Example Program

A Java program that applies conditional formatting and highlights multiple cells based on formula, (XLS format) is given 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 FormulaBasedFormatting {  
        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("Formula_Formatting");
                /* Create Test Data */
                /* Add header rows */
                Row row0 = my_sheet.createRow(0);               
                row0.createCell(0).setCellValue("Total Cost");
                row0.createCell(1).setCellValue("Count");
                row0.createCell(2).setCellValue("Cost Per Item");
                /* Add test data*/
                Row row1 = my_sheet.createRow(1);       
                row1.createCell(0).setCellValue(new Double(1200));
                row1.createCell(1).setCellValue(new Double(12));
                row1.createCell(2).setCellValue(new Double(100));
                
                Row row2 = my_sheet.createRow(2);       
                row2.createCell(0).setCellValue(new Double(1350));
                row2.createCell(1).setCellValue(new Double(135));
                row2.createCell(2).setCellValue(new Double(10));
                
                Row row3 = my_sheet.createRow(3);       
                row3.createCell(0).setCellValue(new Double(770));
                row3.createCell(1).setCellValue(new Double(10));
                row3.createCell(2).setCellValue(new Double(77));
                
                Row row4 = my_sheet.createRow(4);       
                row4.createCell(0).setCellValue(new Double(8000));
                row4.createCell(1).setCellValue(new Double(50));
                row4.createCell(2).setCellValue(new Double(160));
                
                        
                /* Access conditional formatting facet layer */
                HSSFSheetConditionalFormatting my_cond_format_layer = my_sheet.getSheetConditionalFormatting();
                
                /* Create a Rule */
                HSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule("$C2>=100");
                
                /* We fill matches in yellow */
                HSSFPatternFormatting my_pattern = my_rule.createPatternFormatting();
                my_pattern.setFillBackgroundColor(IndexedColors.YELLOW.index);
                
                /* Create a Cell Range Address - we want all cells to be highlighted - so select multiple range */
                CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("A2:C5")};
                
                /* 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:\\highlight_multiple_cells.xls"));
                my_workbook.write(out);
                out.close();
                
        }
}

The important change you need to do is highlighted in yellow. The output of the program is provided below:

XLS -Highlight Multiple Cells - Output Example - Java POI Program

When I ran the program, I got all the four rows highlighted initially. I reloaded the same formula again in Excel, which then highlighted only two rows. I saw a note in API doc at the time of writing,

TODO - formulas containing cell references are currently not parsed properly

Hopefully, it will be fixed soon.

XLSX - Highlight Cells- Formula - Java Example Program

The changes you need to support XLSX files for getting the same output is very minimal. You need to use XSSF version of the class files, and you need to change formulas suitably. You can give a try for XLSX version yourself, and post us a comment if something is not working.  You can use the XSSF tutorial on conditional formatting as a reference.