How to set style in Excel in Java using Apache POI

HOME

In the previous tutorial, I have explained about How to add Formulas in Excel in Java. In this tutorial, I will explain How to customize the style of cells in Excel in Java using Apache POI.

I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.

If you are using maven, then you need to add below dependency in pom.xml.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>
   
   
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

To know more about various interfaces and classes for managing Excel, please refer to this tutorial.

In the below example, I have an existing excel with the name of “EmployeeDetails.xlsx”.

Steps to Customize the style in Excel

Step 1 – Create a blank work and then a sheet with name Write_TestData.

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Write_TestData");

Step 2- XSSFWorkbook and XSSFSheet are imported from the below consecutive packages.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 3 – Write data to the sheet. To know more about this part, refer this link.

Step 4 – Set the auto Size for column 0.

sheet.autoSizeColumn(0);

Step 5 – To create a cell style, we only need a reference to the excel workbook:

	CellStyle style = workbook.createCellStyle();

Step 6 – CellStyle is imported from package mentioned below.

import org.apache.poi.ss.usermodel.CellStyle;

Step 7 – To create font style and add attributes to this font

XSSFFont font = workbook.createFont();

Step 8 – XSSFFont is imported from below package

import org.apache.poi.xssf.usermodel.XSSFFont;

Step 9 – To set the Background Colour of the cell.

style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

Step 10 – BackgroundColor and FillPattern are imported from the below consecutive packages.

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.FillPatternType;

Step 11 – Write data to an OutputStream. Use the below code to write output stream.

FileOutputStream out = new FileOutputStream(new File("Styled_EmployeeDetails.xlsx"));
workbook.write(out);

Below is the complete program to show various cell setyles for Row 0 (Header Row).

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelStyleExample {

	public static void main(String[] args) {

		try {
			// create blank workbook
			XSSFWorkbook workbook = new XSSFWorkbook();

			// Create a blank sheet
			XSSFSheet sheet = workbook.createSheet("Write_TestData");

			ArrayList<Object[]> data = new ArrayList<Object[]>();
			data.add(new String[] { "Name", "Id", "Salary" });
			data.add(new Object[] { "Jim Lawrence", "001A", 10000 });
			data.add(new Object[] { "Jack", "1001B", 40000 });
			data.add(new Object[] { "Tim", "2001C", 20000 });
			data.add(new Object[] { "Gina", "1004S", 30000 });

			// Iterate over data and write to sheet
			int rownum = 0;
			for (Object[] employeeDetails : data) {

				// Create Row
				XSSFRow row = sheet.createRow(rownum++);

				int cellnum = 0;
				for (Object obj : employeeDetails) {

					// Create cell
					XSSFCell cell = row.createCell(cellnum++);

					// Set value to cell
					if (obj instanceof String)
						cell.setCellValue((String) obj);
					else if (obj instanceof Double)
						cell.setCellValue((Double) obj);
					else if (obj instanceof Integer)
						cell.setCellValue((Integer) obj);

					// Set Column width to the maximum for Column 0
					sheet.autoSizeColumn(0);

					if (rownum == 1) {

						CellStyle style = null;

						// Creating a font
						XSSFFont font = workbook.createFont();

						font.setFontHeightInPoints((short) 10);
						font.setFontName("Verdana");
						font.setColor(IndexedColors.BLACK.getIndex());
						font.setBold(true);
						font.setItalic(false);

						// Creating cell Style
						style = workbook.createCellStyle();

						// Setting Foreground Colour
						style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
						style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

						// Setting Alignment of font
						style.setAlignment(HorizontalAlignment.CENTER);

						// Setting font to style
						style.setFont(font);

						// Setting cell style
						cell.setCellStyle(style);

					}
				}
			}

			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File("Styled_EmployeeDetails.xlsx"));
			workbook.write(out);

			System.out.println("Style of Excel is updated successfully");

			out.close();

			// Close workbook
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s