Merging Cells in Excel Using POI

Merging Cells in Excel using POI can be done using the XSSFSheet.addMergedRegion(CellRangeAddress region) method.
CellRangeAddress is the range of the rows and columns that need to be selected. In our case to be merged. The constructor of CellRangeAddress takes 4 params – CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Lets see example on how we can merge cells in excel file

package com.kscodes.test;

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCellMergeExample {
	public static void main(String[] args) {

		// Create instance of the class and call the writeExcelFile()
		ExcelCellMergeExample mergeExample = new ExcelCellMergeExample();
		mergeExample.writeExcelFile("C:\\kscodes_temp\\SplitPaneText.xlsx");
	}

	public void writeExcelFile(String fileName) {
		XSSFWorkbook workbook = null;
		FileOutputStream fileOutputStream = null;
		try {

			fileOutputStream = new FileOutputStream(new File(fileName));

			// Create a Workbook
			workbook = new XSSFWorkbook();

			// Create an Empty Sheet
			XSSFSheet sheet = workbook.createSheet("Cell Merge Test");

			// Add a row and cell and some text in it.
			Row row = sheet.createRow((short) 3);
			Cell cell = row.createCell((short) 3);
			cell.setCellValue("This is a test of Cell Merging Using Apache POI in Java");

			// Create a cellRangeAddress to select a range to merge.
			CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 5, 3, 8);

			// Merge the selected cells.
			sheet.addMergedRegion(cellRangeAddress);

			workbook.write(fileOutputStream);

			System.out.println("Excel File created and written !!!!");

		} catch (Exception e) {
			System.out.println("An Exception occured while writing Excel");
		} finally {
			try {
				if (fileOutputStream != null) {
					fileOutputStream.close();
				}
				if (workbook != null) {
					workbook.close();
				}
			} catch (Exception e) {
			}
		}
	}

}

package com.kscodes.test;

import java.io.File;

import java.io.FileOutputStream;

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

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

import org.apache.poi.ss.util.CellRangeAddress;

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

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

public class ExcelCellMergeExample {

public static void main(String[] args) {

// Create instance of the class and call the writeExcelFile()

ExcelCellMergeExample mergeExample = new ExcelCellMergeExample();

mergeExample.writeExcelFile("C:\\kscodes_temp\\SplitPaneText.xlsx");

}

public void writeExcelFile(String fileName) {

XSSFWorkbook workbook = null;

FileOutputStream fileOutputStream = null;

try {

fileOutputStream = new FileOutputStream(new File(fileName));

// Create a Workbook

workbook = new XSSFWorkbook();

// Create an Empty Sheet

XSSFSheet sheet = workbook.createSheet("Cell Merge Test");

// Add a row and cell and some text in it.

Row row = sheet.createRow((short) 3);

Cell cell = row.createCell((short) 3);

cell.setCellValue("This is a test of Cell Merging Using Apache POI in Java");

// Create a cellRangeAddress to select a range to merge.

CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 5, 3, 8);

// Merge the selected cells.

sheet.addMergedRegion(cellRangeAddress);

workbook.write(fileOutputStream);

System.out.println("Excel File created and written !!!!");

} catch (Exception e) {

System.out.println("An Exception occured while writing Excel");

} finally {

try {

if (fileOutputStream != null) {

fileOutputStream.close();

}

if (workbook != null) {

workbook.close();

}

} catch (Exception e) {

}