Conditional Formatting Icon Set POI Java Example

In this post, we deal with icon sets in conditional formatting, and how to add icon set using Apache POI and OOXML with suitable Java Examples.Icon sets allow you to present your data beautifully on the spreadsheet. These are available under conditional formatting section as shown below:

Conditional Formatting - Icon Set - Apache POI Example

Adding these using POI is a simple task and let us see a step by step example for this now.

Also, Check Color Scale - POI Example

Read Conditional Formatting Range

The first step is to read the workbook on which you want to apply icon set, and identify the range where you need this to be applied. This is shown below:

                /* Step-1: Read Workbook and Identify Icon Set Range */
                FileInputStream input_document = new FileInputStream(new File("input.xlsx"));    
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                XSSFSheet my_sheet = my_xlsx_workbook.getSheetAt(0);    
                List<String> my_range=new ArrayList<String>();
                my_range.add("A1:A10");
                /* Range is A1:A10 */   

Create Conditional Formatting Set

Now, you can create a CTConditionalFormatting object, and add a new conditional formatting section to the active worksheet. You then attach the range you created earlier to this set as shown below:

                /* Add Conditional Formatting Rule */
                CTConditionalFormatting iconSet= my_sheet.getCTWorksheet().addNewConditionalFormatting();               
                iconSet.setSqref(my_range); // Attach Range to conditional formatting set

Insert Rule and Set Type of Rule

In this step, you create a rule, and set type of rule to icon set. You also specify which icon set to be used.

                CTCfRule myCFRule=iconSet.addNewCfRule(); //create a rule
                myCFRule.setType(STCfType.ICON_SET); // type of rule = icon set
                myCFRule.setPriority(1); // rule priority = 1
                
                CTIconSet myIconSet=myCFRule.addNewIconSet();//attach icon set to rule
                myIconSet.setIconSet(STIconSetType.X_4_RATING); // which icon set to be used

Define Icon Set Rule Values

Since ours is a 0-100 icon set example, with 4 bars, we can add icon values and set the type to percent. The Java code logic that does this is shown below:

                /* Add icon Values, set Type to Percent, and specify percent ranges */
                CTCfvo iConValues1=myIconSet.addNewCfvo();
                CTCfvo iConValues2=myIconSet.addNewCfvo();
                CTCfvo iConValues3=myIconSet.addNewCfvo();
                CTCfvo iConValues4=myIconSet.addNewCfvo();
                iConValues1.setType(STCfvoType.PERCENT);
                iConValues2.setType(STCfvoType.PERCENT);
                iConValues3.setType(STCfvoType.PERCENT);
                iConValues4.setType(STCfvoType.PERCENT);
                iConValues1.setVal("0");
                iConValues2.setVal("25");
                iConValues3.setVal("50");
                iConValues4.setVal("75");

Once this is done, you are ready to write the workbook object to file. The input and output with icon set is provided below:

Icon Set Apache POI Example Output

A screenshot of the icon set rule is provided below:

Icon Set Rule Configured in Apache POI

Full Java Program - Icon Sets - Apache POI Example

The complete Java program to create icon sets in conditional formatting, with Apache POI is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
import java.util.*;
public class iconSets {
        public static void main(String[] args) throws Exception{
                
                /* Step-1: Read Workbook and Identify Icon Set Range */
                FileInputStream input_document = new FileInputStream(new File("input.xlsx"));    
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                XSSFSheet my_sheet = my_xlsx_workbook.getSheetAt(0);    
                List<String> my_range=new ArrayList<String>();
                my_range.add("A1:A10");
                /* Range is A1:A10 */   

                /* Add Conditional Formatting Rule */
                CTConditionalFormatting iconSet= my_sheet.getCTWorksheet().addNewConditionalFormatting();               
                iconSet.setSqref(my_range); // Attach Range to conditional formatting set
                
                CTCfRule myCFRule=iconSet.addNewCfRule(); //create a rule
                myCFRule.setType(STCfType.ICON_SET); // type of rule = icon set
                myCFRule.setPriority(1); // rule priority = 1
                
                CTIconSet myIconSet=myCFRule.addNewIconSet();//attach icon set to rule
                myIconSet.setIconSet(STIconSetType.X_4_RATING); // which icon set to be used
                
                /* Add icon Values, set Type to Percent, and specify percent ranges */
                CTCfvo iConValues1=myIconSet.addNewCfvo();
                CTCfvo iConValues2=myIconSet.addNewCfvo();
                CTCfvo iConValues3=myIconSet.addNewCfvo();
                CTCfvo iConValues4=myIconSet.addNewCfvo();
                iConValues1.setType(STCfvoType.PERCENT);
                iConValues2.setType(STCfvoType.PERCENT);
                iConValues3.setType(STCfvoType.PERCENT);
                iConValues4.setType(STCfvoType.PERCENT);
                iConValues1.setVal("0");
                iConValues2.setVal("25");
                iConValues3.setVal("50");
                iConValues4.setVal("75");
                
                /* Step-7: Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("auto_filter_2.xlsx"));
                my_xlsx_workbook.write(out);
                out.close();
        }
}