Apache POI Merge Cells in Excel

In this article, you will learn how to Merge Cells in Excel using Apache POI. Here we will see an example in java for merging cells in an Excel file. The excel file generally comes in two formats XLS and XLSX. To create an XLS file we use HSSFWorkbook and for XLSX we use XSSFWorkbook.

How to Merge Cells in Excel using Java?

Steps for merging cells in Excel using Apache POI:

1. Create a Cell using createCell() method. eg- Cell cell = row.createCell(1);
2. Set a value to the cell using the setCellValue() method. eg- cell.setCellValue(“Merge cell example”);
3. Create an object of CellRangeAddress class using its constructor.
4. Finally, add the created CellRangeAddress to the Sheet using the addMergedRegion() method. eg- sheet.addMergedRegion(new CellRangeAddress(1,2,1,3));

Classes and methods for merging the cells using Apache POI

CellRangeAddress: It is a class available under the package org.apache.poi.ss.util.CellRangeAddress. It is used to create an object of a new cell range for given addresses. To create the object of CellRangeAddress, it has the below constructor defined.

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) – A constructor to create a new cell range. Indexes are zero-based.

Parameters:
firstRow – Index of the first row.
lastRow – Index of the last row (inclusive), must be equal to or larger than firstRow.
firstCol – Index of the first column.
lastCol – Index of the last column (inclusive), must be equal to or larger than firstCol.

addMergedRegion(): It is a method from the Sheet interface. The known implementation classes are XSSFSheet, HSSFSheet, XSSFDialogsheet, etc.

public int addMergedRegion(CellRangeAddress region) – A method to adds a merged region of cells on a sheet.

Parameters:
region – to merge
Returns:
index of this region
Throws:
java.lang.IllegalArgumentException – if region contains fewer than 2 cells
java.lang.IllegalStateException – if region intersects with a multi-cell array formula
java.lang.IllegalStateException – if region intersects with an existing region on this sheet

Apache POI Maven Dependency

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

Apache POI Merge Cells Example

In this example, we are merging two rows from 1 to 2 (0-based indexing) and three columns from 1 to 3 (0-based indexing). Let’s see the complete java program below:

MergeCellExample.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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Merge cell Example
 */
public class MergeCellExample {

	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("new sheet");

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

		// Create a cell and set some value
		Cell cell = row.createCell(1);
		cell.setCellValue("Merge cell example");

		// Merge the cell using CellRangeAddress
		sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)
				2, // last row (0-based)
				1, // first column (0-based)
				3 // last column (0-based)
		));

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

		// close the workbook
		wb.close();

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

	}

}

OUTPUT:

Conclusion

In this article, you have learned how to merge cells in excel using apache poi. By using CellRangeAddress class and addMergedRegion(), we can do merging of cells in Excel in java.

Related Articles:

• Apache POI – Overview
• Apache POI Excel Cell Alignment in Java
• Apache POI Excel Cell Border in Java
• Apache POI Excel Cell Color in Java
• Apache POI Multiple Styles to Excel Cell
• Apache POI RichTextString

You might like this:

• How to create password-protected Excel in java?
• 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