Excel Auto Filter Cell Color - POI Java Example

In this post, let us see how to Auto Filter Excel Cell by Color, using Apache POI with a Java Example. You may have a need to apply color to your cells and auto filter them, and save the work book with a filter on a specific color. At the time of writing, POI does not support this natively, so we will have to do some tweaks to get this done in POI. The input and color filtered output is shown below:

Apache POI - Auto Filter By Cell Color - Example

A step by step guide is provided below. I have tested this code in latest version of POI and it works. You should test it on your requirement before using it. Feel free to report a bug in this code, happy to help.

Get Auto Filter - Excel Workbook

The first step is to access the workbook, get access to the sheet and retrieve the CTAutoFilter object. It is through this object, you can write further Java code to define your filter conditions. The Java code that does this is shown below:

/* Step-1: Get the CTAutoFilter Object */
FileInputStream input_document = new FileInputStream(new File("auto_filter_output.xlsx"));    
XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
XSSFSheet my_sheet = my_xlsx_workbook.getSheetAt(0); 
CTAutoFilter sheetFilter=my_sheet.getCTWorksheet().getAutoFilter();             

Add Color Filter to Worksheet

Once you have the Auto Filter object, you can insert a filter column CTFilterColumn and add a color filter using the method addNewColorFilter. You have to set an ID for your color filter. Color filter is defined in the class CTColorFilter. The Java code is provided below:

/* Step -2: Create Filter Column Object and Define Column ID */
CTFilterColumn  myFilterColumn=sheetFilter.insertNewFilterColumn(0);            
myFilterColumn.setColId(1L);

/* Step-3: Create a Color Filter and set ID */  
CTColorFilter colorFilter=myFilterColumn.addNewColorFilter();
colorFilter.setDxfId(0L);       

Insert Auto Filter Color to Style Sheet

The filter colors in your auto filter conditions needs to be attached to the style sheet object in POI. This object is defined in the class CTStylesheet. You can get access to this object from Apache POI. Once you have this, you need to add the Auto Filter color information to this class. The Java code that does this is shown below:

/* Step-4: Update StyleSheet and Attach Filter Color Details */         
CTStylesheet wbStyle=my_xlsx_workbook.getStylesSource().getCTStylesheet();
CTDxfs dxSet=wbStyle.addNewDxfs();
dxSet.setCount(1L);
CTPatternFill myFill=dxSet.addNewDxf().addNewFill().addNewPatternFill();
myFill.setPatternType(STPatternType.SOLID);
CTColor fgColor=myFill.addNewFgColor();
CTColor bgColor=myFill.addNewBgColor();         
byte[] fg=javax.xml.bind.DatatypeConverter.parseHexBinary("FF008000");  // Foreground filter color = green      
byte[] bg=javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFFFFF"); // background filter color="white"
fgColor.setRgb(fg);
bgColor.setRgb(bg);

Filter Rows / Columns by Color 

You are almost done. You will now loop through your rows and columns and hide any rows where the filter color condition is not met. This is same as what Excel does when you filter a column based on color. Once you have done this, you can save the workbook to a file on disk. Viola, you have written an auto filter by color using POI and OOXML.

/* Step-5: Refresh Records to Match Filter Condition */
XSSFRow r1;             
/* Step-6: Loop through Rows and Apply Filter */
for(Row r : my_sheet) {
        for (Cell c : r) {
                //if foreground filter color is not green then hide the record
                if ( c.getColumnIndex()==1  && c.getCellStyle().getFillForegroundColor() !=17){
                        r1=(XSSFRow) c.getRow();
                        if (r1.getRowNum()!=0) { /* Ignore top row */
                        /* Hide Row that does not meet Filter Criteria */
                                r1.getCTRow().setHidden(true); }
                }                               
        }
}               
/* Step-7: Write changes to the workbook */
FileOutputStream out = new FileOutputStream(new File("auto_filter_2.xlsx"));
my_xlsx_workbook.write(out);
out.close();

You can combine all the steps above and get the full Java program. You would require ooxml-schemas-1.1.jar file which comes with Apache POI in your classpath. You will also require the following import declarations in your code:

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.*;

That completes a simple approach to filter cell in Excel by color in Apache POI. Go through the above code and post a comment if you are stuck.

Download Complete Java Program for Filter Cell by Color in Apache POI
View our full set of Apache POI Examples