Excel Cell Fill Color - Java POI Example Program

setFillBackgroundColor - Introduction


In this post, we will explain how to fill the background of a cell in Excel with a particular color, in Java using Apache POI, with example programs.Background color for a cell is set using setFillBackgroundColor method, and we will see how to use this method and pass different colors to it. We will provide two set of examples as usual, one for XLS worksheets and one for XLSX flavour. Let us get started.

XLS - Set Fill Color for Cell - Example Program


The method setFillBackgroundColor, takes a color which is of type short. These colors are defined in the POI library of type HSSFColor. You can choose the color you want first and pass it to the setFillBackgroundColor method.  You should set the foreground color of the cell before setting the background color. Setting foreground color is done by setFillForegroundColor method. Optionally you can also specify a fill pattern by using setFillPattern method. An example program is provided below:
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
public class ChangeCellFillColor {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Font");
                /* Get access to HSSFCellStyle */
                HSSFCellStyle my_style = my_workbook.createCellStyle();
                /* We will now specify a background cell color */
                my_style.setFillPattern(HSSFCellStyle.FINE_DOTS );
                my_style.setFillForegroundColor(new HSSFColor.BLUE().getIndex());
                my_style.setFillBackgroundColor(new HSSFColor.RED().getIndex());
                
                /* Create a row in the sheet */
                Row row = my_sheet.createRow(0);
                /* Create a cell */
                Cell cell = row.createCell(0);
                cell.setCellValue("This text will be in bold red color");
                /* Attach the style to the cell */
                cell.setCellStyle(my_style);
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_fill_color.xls"));
                my_workbook.write(out);
                out.close();
                
                
        }
}

You can pass different values to setFillPattern. We will have a separate tutorial on setFillPattern later. Foreground color should be set first  before background color.

XLSX Example - Cell Fill Color - Java Program


Here is a XLSX example for the cell fill color.

import java.io.*;
import org.apache.poi.xssf.usermodel.*; 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;
public class ChangeCellFillColorXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet XLSX Format */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Cell Font");
                /* Get access to XSSFCellStyle */
                XSSFCellStyle my_style = my_workbook.createCellStyle();
                
                /* We will now specify a background cell color */
                 my_style.setFillPattern(XSSFCellStyle.FINE_DOTS );
                 my_style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
                 my_style.setFillBackgroundColor(IndexedColors.RED.getIndex());
                
                /* Create a row in the sheet */
                Row row = my_sheet.createRow(0);
                /* Create a cell */
                Cell cell = row.createCell(0);
                cell.setCellValue("Cell Fill Color Test");
                /* Attach the style to the cell */
                cell.setCellStyle(my_style);
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_fill_color.xlsx"));
                my_workbook.write(out);
                out.close();
                
        }
}

Here we use IndexedColors class to pass a color to background / foreground methods. This however, may not be a desired practice. The methods accept a XSSFColor object as input, so you may want to specify your color through XSSFColor. XSSFColor class has a constructor that accepts a color object of type java.awt.Color, plethora of possibilities... Here is a code snippet on how to do this in Java.
                /* We will now specify a background cell color */
                 my_style.setFillPattern(XSSFCellStyle.FINE_DOTS );
                 XSSFColor my_foreground=new XSSFColor(Color.ORANGE);
                 XSSFColor my_background=new XSSFColor(Color.RED);
                 my_style.setFillForegroundColor(my_foreground);
                 my_style.setFillBackgroundColor(my_background);


where, Color, is of type java.awt.Color.

You can run these programs yourself to see the output in action. Make sure you have all the POI JAR files in your classpath. See you in a different tutorial next time.

2 comments:

  1. Hi,My name is jim Thanks for the example. I tried the same , but got an error when the cell which i chose to color is blank,
    Exception in thread "main" java.lang.NullPointerException
    at POI.Comparewith_POI.main(Comparewith_POI.java:153). I get the above message , Please could you help me resolving this issue

    ReplyDelete