Apache POI Excel Cell Alignment in Java

This Apache POI tutorial shows you about setting Excel cell Alignment in Java. The Apache POI is an open-source Java API library for manipulating the various Microsoft files format such as Excel, PowerPoint, and Word in Java.

How to set Excel cell alignment using Apache POI?

Steps to set Excel cell alignment in Java:

1. Create a Cell using createCell() method.
2. Set some cell content in it using setCellValue() method.
3. Create a cell style using createCellStyle() method.
4. Set the Horizontal and Vertical alignment properties using setAlignment() and setVerticalAlignment() method respectively.
5. Finally, apply the created cell style to the Excel cell using setCellStyle() method.

Classes and Methods for Align Excel Cell – Apache POI

HorizontalAlignment: The enumeration value indicating the horizontal alignment of a cell, i.e., whether it is aligned general, left, right, horizontally centered, filled (replicated), justified, centered across multiple cells, or distributed.

VerticalAlignment: This enumeration value indicates the type of vertical alignment for a cell, i.e., whether it is aligned top, bottom, vertically centered, justified, or distributed.

createCell(): This is a method to create new cells within the row and return it.

setCellValue(): Set the cell value of supported datatypes.

createCellStyle(): Create a new Cell style and add it to the workbook’s style table.

setAlignment(): Set the type of horizontal alignment for the cell.

setVerticalAlignment(): Set the type of vertical alignment for the cell.

setCellStyle(): Set the style for the cell. The style should be a CellStyle created/retrieved from the Workbook.

All available alignment properties with their description in HorizontalAlignment:

All available alignment properties with their description in VerticalAlignment:

Cell alignment in Excel file using Apache POI Example

In this example, we are going to create an XLSX file “xssf-align-example.xlsx” with different Horizontal alignment as well as Vertical alignment properties. Here we are defining createCellWithAlignment() method to create and set the cell alignment. Let’s see the java code below:

CellAlignmentsExcelExample.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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * An example of cell alignments in Excel file
 */
public class CellAlignmentsExcelExample {

	/*
	 * 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();
		Sheet sheet = wb.createSheet("Cell Align Example");

		// Creating a new row at index 2
		Row row = sheet.createRow(2);

		// Setting row height to see the alignment properly
		row.setHeightInPoints(30);

		// Creating multiple column with different cell align in the same row
		createCellWithAlignment(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
		createCellWithAlignment(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
		createCellWithAlignment(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
		createCellWithAlignment(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
		createCellWithAlignment(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
		createCellWithAlignment(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
		createCellWithAlignment(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);

		// Write the output to a file
		try (OutputStream fileOut = new FileOutputStream("xssf-align-example.xlsx")) {
			wb.write(fileOut);
		}

		// Close the workbook
		wb.close();

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

	/**
	 * Method to create a cell and align it in a certain way.
	 */
	private static void createCellWithAlignment(Workbook wb, Row row, int columnIndex, HorizontalAlignment hAlign,
			VerticalAlignment vAlign) {

		// Create a cell at specified column index
		Cell cell = row.createCell(columnIndex);

		// Setting the cell value
		cell.setCellValue("Align It");

		// Create a cell style and setting its property
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(hAlign);
		cellStyle.setVerticalAlignment(vAlign);

		// Finally setting the created style to cell
		cell.setCellStyle(cellStyle);
	}
}

OUTPUT:

Apache POI – Environment setup in Eclipse

There are two ways for installing Apache POI in Eclipse, based on your project type:

1. Maven Project
2. Stand-alone Java Project (Non-Maven)

Note: In this article, we have used the Apache POI-4.0.1 version of the library to test this example.

Maven Project

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>

Stand-alone Java Project (Non-Maven)

If you are going to create a standalone java project then you have to add the following jar files to your java build path:

1. poi-4.0.1.jar
2. poi-ooxml-4.0.1.jar
3. poi-ooxml-schemas-4.0.1.jar
4. xmlbeans-3.0.2.jar
5. curvesapi-1.05.jar
6. commons-codec-1.11.jar
7. commons-collections4-4.2.jar
8. commons-compress-1.18.jar
9. commons-math3-3.6.1.jar

You can easily download all the above jars in one place: Download Apache POI Jars.

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 Excel cell alignment property using the Apache POI library in Java.

FAQ

Related Articles:

• Apache POI – Read and Write Excel files in java
• How to create password-protected Excel in java?
• How to Read password-protected Excel in java?
• How to write data to an existing Excel file in java?

You might like this:

• How to create a Stopwatch in JavaScript?
• File upload validations in javascript
• Preview an image before uploading using Javascript
• Preview an image before uploading using jQuery
• File Upload in Java Servlet Example