Border and Color in excel file using Apache POI in Java

Introduction

In this example I will show you how to create border around a cell and put color on a particular cell like background color, foreground color using Apache POI in Java language.

The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).

Prerequisites

Java at least 8, maven 3.6.3, gradle 6.5.1 – 6.7.1, Apache POI 4.1.1 – 5.0.0

Project Setup

You need to create a maven or gradle project in your favorite IDE or tool. The name of the project is apache-poi-excel-color-border.

Here I will add apache poi API as a dependency for working with Microsoft word document or even you can work with open source word document.

If you are creating gradle based project then you can use below build.gradle script:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.1' to 5.0.0
    
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.11.2')
}

If you are creating maven based project then you can use below pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>apache-poi-excel-color-border</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>12</maven.compiler.source>
		<maven.compiler.target>12</maven.compiler.target>
	</properties>

	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.0.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>
		</plugins>
	</build>
</project>

Excel Color and Border

Now I am going to create three cells in a row in excel file to work with different types of border and colors – foreground & background. I am applying different border style at each side of the cell box.

You may also play with other many border types and colors in the cell of the excel file.

package com.roytuts.apache.poi.excel.color.border;

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

import org.apache.poi.ss.usermodel.BorderStyle;
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;

public class ExcelColorBorderApp {

	public static void main(String[] args) {
		final String fileName = "excel-color-border.xlsx";
		createExcel(fileName);
	}

	public static void createExcel(final String fileName) {
		// get the file extension
		String fileExt = PoiUtils.getFileExtension(fileName);
		Workbook workbook = null;

		// based on file extension create Workbook object
		if (".xls".equalsIgnoreCase(fileExt)) {
			workbook = PoiUtils.getHSSFWorkbook();
		} else if (".xlsx".equalsIgnoreCase(fileExt)) {
			workbook = PoiUtils.getXSSFWorkbook();
		}

		// create Sheet object
		// sheet name must not exceed 31 characters
		// the name must not contain 0x0000, 0x0003, colon(:), backslash(\),
		// asterisk(*), question mark(?), forward slash(/), opening square
		// bracket([), closing square bracket(])
		Sheet sheet = workbook.createSheet("my_sheet");
		sheet.setColumnWidth(0, 9000);
		sheet.setColumnWidth(1, 9000);
		sheet.setColumnWidth(2, 9000);

		// Create first row. Rows are 0 based.
		Row row = sheet.createRow((short) 0);

		// Create a cell
		CellStyle cellStyle1 = workbook.createCellStyle();
		cellStyle1.setBorderTop(BorderStyle.THIN);
		cellStyle1.setTopBorderColor(IndexedColors.BLUE_GREY.getIndex());
		cellStyle1.setBorderBottom(BorderStyle.THICK);
		cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle1.setBorderLeft(BorderStyle.DASHED);
		cellStyle1.setLeftBorderColor(IndexedColors.AQUA.getIndex());
		cellStyle1.setBorderRight(BorderStyle.DOTTED);
		cellStyle1.setRightBorderColor(IndexedColors.BROWN.getIndex());

		Cell cell = row.createCell(0);
		cell.setCellValue("This is surrounded with border");
		cell.setCellStyle(cellStyle1);

		// background color
		CellStyle cellStyle2 = workbook.createCellStyle();
		cellStyle2.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
		cellStyle2.setFillPattern(FillPatternType.BIG_SPOTS);
		cell = row.createCell(1);
		cell.setCellValue("Background Color");
		cell.setCellStyle(cellStyle2);

		// foreground color
		CellStyle cellStyle3 = workbook.createCellStyle();
		cellStyle3.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
		cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cell = row.createCell(2);
		cell.setCellValue("Foreground Color");
		cell.setCellStyle(cellStyle3);

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(fileName);
			workbook.write(fileOut);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fileOut.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

}

Testing the Application

Executing the above code will give you the following output in the generated excel file. The file gets created under project’s root directory.

That’s all about how to work with border and color using Java and Apache POI.

Source Code

Download