Apache POI Excel Cell Color in Java

March 15, 2023 / , , / By

This Apache POI tutorial shows you to fill Excel Cell Color and Pattern using Apache POI in Java. Here, we are going to discuss how to fill the background colors and foreground colors, and fill Patterns of the cells in Excel using the Apache POI.

How to Fill the Color of Cells in Excel using Java?

Apache POI provides three methods to fill the color of the cell in an Excel file. All three methods (setFillForegroundColor, setFillPattern, and setFillBackgroundColor) are available in CellStyle class. The following table describes the important methods for filling the cell colors:

Important MethodsDescription
setFillBackgroundColor()Set the background fill color. It takes the color index as a parameter. Generally, we should pick it from IndexedColors. eg:- style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
setFillForegroundColor()Set the background fill color. It also takes the color index as a parameter. eg:- style.setFillForegroundColor(IndexedColors.RED.getIndex());
setFillPattern()Set the fill pattern of the cell with the foreground color. set the default color (black) if the foreground color is not set. eg:- style.setFillPattern(FillPatternType.BIG_SPOTS);
createCell()Used to create a cell in a specific row in an Excel sheet. eg:- cell = row.createCell(1);
createCellStyle()Used to create a cell-style object. eg:- style = wb.createCellStyle();
setCellStyle()Used to set the style of the cell. eg:- cell.setCellStyle(style);

All available Pattern types in FillPatternType are listed below:

The FillPatternType is an enumeration value that indicates the style of fill pattern being used for a cell format. There are a total of 19 fill patterns available in Apache POI version 4.0.1

Pattern TypesDescription
NO_FILLNo background
SOLID_FOREGROUNDSolidly filled with the foreground color
FINE_DOTSSmall fine dots
ALT_BARSWide dots
SPARSE_DOTSSparse dots
THICK_HORZ_BANDSThick horizontal bands
THICK_VERT_BANDSThick vertical bands
THICK_BACKWARD_DIAGThick backward-facing diagonals
THICK_FORWARD_DIAGThick forward-facing diagonals
BIG_SPOTSLarge spots
BRICKSBrick-like layout
THIN_HORZ_BANDSThin horizontal bands
THIN_VERT_BANDSThin vertical bands
THIN_BACKWARD_DIAGThin backward diagonal
THIN_FORWARD_DIAGThin forward diagonal
SQUARESSquares
DIAMONDSDiamonds
LESS_DOTSLess Dots
LEAST_DOTSLeast Dots

Steps to Fill cell color in Excel with Apache POI:

  1. Create a Cell using createCell() method.
  2. Create a cell style using createCellStyle() method.
  3. Set the background color, foreground color, and fill pattern to style using setFillBackgroundColor(), setFillForegroundColor(), and setFillPattern() methods respectively.
  4. Finally, set the created cell style to the Excel cell using setCellStyle() method.

Apache POI Excel Cell Color Example

In this example, we have created a few cells in Excel with different-different background/foreground colors and fill patterns. Let’s see the complete java code below:

CellFillColorPatternExample.java

package com.javacodepoint.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This example shows Cell fill colors and Patterns
 */
public class CellFillColorPatternExample {

	// main method
	public static void main(String[] args) throws FileNotFoundException, IOException {

		/*
		 * Creating a workbook for XLSX file using XSSFWorkbook, Use HSSFWorkbook for
		 * XLS File
		 */
		Workbook wb = new XSSFWorkbook();

		// create a sheet
		Sheet sheet = wb.createSheet("Fill Cell Colors & Patterns");

		// Create a row and put some cells in it. Rows are 0 based.
		Row row = sheet.createRow(1);

		// SOLID_FOREGROUND pattern
		CellStyle style = wb.createCellStyle();
		style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setFillForegroundColor(IndexedColors.BLUE.getIndex());

		// create a cell and set the created style
		Cell cell = row.createCell(1);
		cell.setCellValue("SOLID_FOREGROUND");
		cell.setCellStyle(style);

		// DIAMONDS pattern
		row = sheet.createRow(2);
		style = wb.createCellStyle();
		style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
		style.setFillPattern(FillPatternType.DIAMONDS);
		style.setFillForegroundColor(IndexedColors.RED.getIndex());
		cell = row.createCell(1);
		cell.setCellValue("DIAMONDS");
		cell.setCellStyle(style);

		// BIG_SPOTS pattern
		row = sheet.createRow(3);
		style = wb.createCellStyle();
		style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
		style.setFillPattern(FillPatternType.BIG_SPOTS);
		style.setFillForegroundColor(IndexedColors.RED.getIndex());
		cell = row.createCell(1);
		cell.setCellValue("BIG_SPOTS");
		cell.setCellStyle(style);

		// SQUARES pattern
		row = sheet.createRow(4);
		style = wb.createCellStyle();
		style.setFillBackgroundColor(IndexedColors.PINK.getIndex());
		style.setFillPattern(FillPatternType.SQUARES);
		style.setFillForegroundColor(IndexedColors.RED.getIndex());
		cell = row.createCell(1);
		cell.setCellValue("SQUARES");
		cell.setCellStyle(style);

		// THICK_FORWARD_DIAG pattern
		row = sheet.createRow(5);
		style = wb.createCellStyle();
		style.setFillBackgroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
		style.setFillPattern(FillPatternType.THICK_FORWARD_DIAG);
		style.setFillForegroundColor(IndexedColors.RED.getIndex());
		cell = row.createCell(1);
		cell.setCellValue("THICK_FORWARD_DIAG");
		cell.setCellStyle(style);

		// auto adjust the column width
		sheet.autoSizeColumn(1);

		// Write the output to a file
		try (OutputStream fileOut = new FileOutputStream("fill-colors-and-patterns.xlsx")) {
			wb.write(fileOut);
		}
		// close the workbook
		wb.close();

		// Print the confirmation message
		System.out.println("An Excel file has been created successfully!");

	}

}

OUTPUT:

Apache poi excel cell fill color and pattern

NOTE:

If you want to fill the color to the cell then setting fill pattern type is mandatory where as setting foreground and background color are optional.

If you don’t set any foreground color then it takes default color black.

If you set fill pattern to FillPatternType.SOLID_FOREGROUND then it fills the cell solidly with foreground color. meaning it hides the background color with foreground color if specified.

Apache POI Excel Cell all Fill Patterns Example

In this example, we have created an excel file with all available fill patterns in the enumeration FillPatternType. Here we have created two columns, one with a Fill pattern and another one with a Fill pattern name.

CellFillPatternsExample.java

package com.javacodepoint.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This example shows All Cell fill Patterns
 */
public class CellFillPatternsExample {

	// main method
	public static void main(String[] args) throws FileNotFoundException, IOException {
		/*
		 * Creating a workbook for XLSX file using XSSFWorkbook, Use HSSFWorkbook for
		 * XLS File
		 */
		Workbook wb = new XSSFWorkbook();

		// create a sheet
		Sheet sheet = wb.createSheet("All Patterns");

		// all available pattern types
		FillPatternType patterns[] = FillPatternType.values();

		// iterate all available patterns
		for (int i = 0; i < patterns.length; i++) {

			// create row
			Row row = sheet.createRow(i);

			// create cell style to fill the background/foreground color, and pattern
			CellStyle style = wb.createCellStyle();
			style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
			style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
			style.setFillPattern(patterns[i]);

			// create cell and set the style
			Cell cell = row.createCell(1);
			cell.setCellStyle(style);

			// create another cell to display pattern name
			cell = row.createCell(2);
			cell.setCellValue(patterns[i].name());
		}

		// auto adjust the column width
		sheet.autoSizeColumn(2);

		// write the output to a file
		try (OutputStream fileOut = new FileOutputStream("fill-patterns.xlsx")) {
			wb.write(fileOut);
		}

		// close the workbook
		wb.close();

		// Print the confirmation message
		System.out.println("An Excel file has been created successfully!");
	}

}

OUTPUT:

Apache poi excel cell all fill patterns
All available Fill Pattern Types

Apache POI – Maven Dependencies

If you are going to create a maven project then you have to add the following maven dependency in the pom.xml file of your project:

<!-- Used for Excel 2003 or before (xls) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
   
<!-- Used for Excel 2007 or later (xlsx)  -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

If you want to know the complete environment setup for Apache POI in Eclipse IDE, follow another article here: Apache POI – Getting Started

Conclusion

In this tutorial, you have learned about setting the cell background color, foreground color, and fill patterns to a cell in Excel using the Apache POI library in Java.

FAQ

How to set the background color of a cell using Apache POI?

// setting the blue background color
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Cell cell = row.createCell(1);
cell.setCellStyle(style);

Related Articles:

You might like this:

Share with friends

Leave a Comment

Your email address will not be published. Required fields are marked *