Conditional Formatting Color Scales Java POI Example

In this post, we explain how to apply color scales in Conditional Formatting, on your Excel workbooks, with Apache POI / Java Example. A screenshot of color scales in Excel is shown below:

Conditional Formatting - Color Scales - Java POI Example

When you apply color scales to your Excel Data, Excel formats your data with min value and max value considered, as shown below:

Color Scales - Java Example Output

At the time of writing, there is no native support in POI to implement color scales. So, we will use OOXML on top of POI to create color scales. The list of steps is provided below:

• Read Color Scale Data Range
• Create Conditional Formatting Rule
• Define Color Scale Values and Colours
• Write Output to Excel Workbook

It is easy to define this type of conditional formatting rule in Apache POI / Java – the step by step guide is provided below.

Read Color Scale Data Range

In this step, we read the worksheet and identify the data range, on which we have to apply Color Scales. As an example, let us take a workbook with 10 rows – range of A1: A10 (see above for sample). We feed this range to a List object. The Java Code segment is provided below:

                /* Read Workbook and Identify Color Scale 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");

Create Conditional Formatting Color Scale Rule

Next, we create an object of type CTConditionalFormatting using the addNewConditionalFormatting method in CTWorksheet class. You have to attach the data range created earlier to this object using the setSqref method. See below;

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

You will now add a rule of type colorScale. A rule can be added by using addNewCfRule method. Once created, the method setType is used to set the type to colorScale, and priority is set as 1.

                CTCfRule myCFRule=colorScale.addNewCfRule(); //create a rule
                myCFRule.setType(STCfType.COLOR_SCALE); // set type of rule to Colour Scale
                myCFRule.setPriority(1); // rule priority = 1

Define Color Scale Format and Colors 

Now, use addNewColorScale method in CTCfRule class, to create a new color Scale. Once this is done, you can set the parameters of your color Scale rule. These include;

• Minimum Value of the Color Sale range
• Mid Point value as
• Percentile
• Number
• Percent
• Formula
• Maximum Value
• Colours for Minimum, Mid Point and Maximum Value as RGB

You set all these properties via CTCfvo object, which you can add from CTColorScale object. And, you define your color values using CTColor object.

                /* Define Color Scale and set Values */
                CTColorScale colorScaleDefn=myCFRule.addNewColorScale ();
                /* Define MIN Values */
                CTCfvo minValue=colorScaleDefn.addNewCfvo();
                minValue.setType(STCfvoType.MIN);
                minValue.setVal("0");
                /* Define MID Values */
                CTCfvo midValue=colorScaleDefn.addNewCfvo();
                midValue.setType(STCfvoType.PERCENTILE);
                midValue.setVal("50");
                /* Define MAX Values*/
                CTCfvo maxValue=colorScaleDefn.addNewCfvo();
                maxValue.setType(STCfvoType.MAX);
                maxValue.setVal("0");
                /* Add color ranges - min / max / mid */
                CTColor lowColor= colorScaleDefn.addNewColor();
                CTColor midColor= colorScaleDefn.addNewColor();
                CTColor highColor= colorScaleDefn.addNewColor();
                lowColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFF8696B"));
                midColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFEB84"));
                highColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FF5A8AC6"));

Once this is done, you can write the output to a file and you have successfully implemented a basic Color Scale setup in Java with Apache POI.

Conditional Formatting – Color Scales – Java POI Example 

The complete example for inserting color scales with conditional formatting in Apache POI / Java 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 colorScale {
        public static void main(String[] args) throws Exception{
                
                /* Read Workbook and Identify Color Scale 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");
                /* Add Conditional Formatting Rule */
                CTConditionalFormatting colorScale= my_sheet.getCTWorksheet().addNewConditionalFormatting();               
                colorScale.setSqref(my_range); // Attach Range to conditional formatting set        
                CTCfRule myCFRule=colorScale.addNewCfRule(); //create a rule
                myCFRule.setType(STCfType.COLOR_SCALE); // set type of rule to Colour Scale
                myCFRule.setPriority(1); // rule priority = 1
                /* Define Color Scale and set Values */
                CTColorScale colorScaleDefn=myCFRule.addNewColorScale ();
                /* Define MIN Values */
                CTCfvo minValue=colorScaleDefn.addNewCfvo();
                minValue.setType(STCfvoType.MIN);
                minValue.setVal("0");
                /* Define MID Values */
                CTCfvo midValue=colorScaleDefn.addNewCfvo();
                midValue.setType(STCfvoType.PERCENTILE);
                midValue.setVal("50");
                /* Define MAX Values*/
                CTCfvo maxValue=colorScaleDefn.addNewCfvo();
                maxValue.setType(STCfvoType.MAX);
                maxValue.setVal("0");
                /* Add color ranges - min / max / mid */
                CTColor lowColor= colorScaleDefn.addNewColor();
                CTColor midColor= colorScaleDefn.addNewColor();
                CTColor highColor= colorScaleDefn.addNewColor();
                lowColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFF8696B"));
                midColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFEB84"));
                highColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FF5A8AC6"));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("colorScale.xlsx"));
                my_xlsx_workbook.write(out);
                out.close();
        }
}

The formatted output is same as the screen shot provided earlier, give a try and post a comment below

View all our Apache POI Tutorials.
See How to Implement Icon Sets in Apache POI