Introduction

Here I am going to show you how to merge cells in excel sheet using Apache POI in Java programming language. In other words I will merge two or more columns from single row or multiple rows in excel file using Java with the help of Apache POI library.

For merging cells you need to specify the range of rows and columns in the excel sheet. So, from which row to which row and from which column to which column you want to merge, you need to specify. Row and columns are 0 based index.

Remember when you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

Prerequisites

Java 1.8+, Maven 3.8.2, Apache POI 5.2.2

Project Setup

You can create maven based project in your favorite IDE or tool. The following pom.xml file can be used for your project.

<?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>java-apache-poi-merge-excell-cell</artifactId>
	<version>0.0.1-SNAPSHOT</version>

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

	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.2</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>

Merge Cells in Excel

Now I am going to write Java program to merge cells in the excel sheet. I am going to show you two examples. The one that will create a new excel file and merge cells in the new excel sheet. The other one will read an existing excel file and merge the cells over multiple rows and create a new excel file.

package com.roytuts.java.apache.poi.merge.excell.cell;

import java.io.File;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCellMerger {

	public static void main(String[] args) throws IOException {
		mergeCells();
		mergeCells("info.xlsx");
	}

	public static void mergeCells() throws IOException {
		Workbook wb = new XSSFWorkbook();

		Sheet sheet = wb.createSheet("sheet merge cells");

		Row row = sheet.createRow(1);

		Cell cell = row.createCell(1);

		cell.setCellValue("This is a test of merging cells");

		sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)
				1, // last row (0-based)
				1, // first column (0-based)
				2 // last column (0-based)
		));

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

		wb.close();
	}

	public static void mergeCells(String filename) throws IOException {
		Workbook wb = WorkbookFactory.create(new File(filename));

		Sheet sheet = wb.getSheetAt(0);

		sheet.addMergedRegion(new CellRangeAddress(2, // third row (0-based)
				3, // fourth row (0-based)
				3, // fourth column (0-based)
				4 // fifth column (0-based)
		));

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

		wb.close();
	}

}

Testing Merge Cells

The existing excel file info.xlsx is kept under the root folder of the project directory. Two output excel files will be created under the root folder of the project directory.

The first example (mergeCells()) will produce the following output in the excel sheet:

The content of the info.xlsx file is given below:

The merged cell is created out of few rows and cells after merging them and following output is produced:

Source Code

Download