XLS - Merge Cell Data - Java POI Example Program

Merge Cells Using Apache POI - Introduction

In this tutorial, we will explain how to merge Excel data across multiple cells, in Java, using Apache POI library. We will set up a small test case for us on merging cell data, and see how to write a Java program for that to get the required result. We will write programs for the following type of merging:

• Merging data across columns
• Merging data across rows
• Merging across rows and columns

These are illustrated in the diagram below:

Different Types of Merging - Excel Java POI Example - Input

 Let us get started with the tutorial.

addMergedRegion - Example

The method addMergedRegion is used to merge data across columns / rows. We will see how to use this method to get the output we are looking for. This tutorial provides XLS workbook based examples. Refer to the related post section below for XLSX based examples.

XLS - Merge Across Columns - Java POI Example Program

An example Java program that merges data across columns is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeCellsAcrossColumns {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Merge Cells");
                
                Row row = my_sheet.createRow((short) 1);
                Cell cell = row.createCell((short) 1);
                cell.setCellValue("Merge Data with Apache POI");
                my_sheet.addMergedRegion(new CellRangeAddress(
                        1, // mention first row here
                        1, //mention last row here, it is 1 as we are doing a column wise merging
                        1, //mention first column of merging
                        5  //mention last column to include in merge
                        ));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Across_Columns.xls"));
                my_workbook.write(out);
                out.close();
                
                
        }
}

Here, we used addMergedRegion method and specified the starting row, ending row, starting column and end column values. The output of this program is on the expected lines, which is shown below (I have drawn a manual border to indicate merge. You can set this programatically too)

Merge Data Across Columns - Java POI Example - Output

XLSX - Merge Data Across Columns - Java Example Program

Writing an XLSX version for the same program is very simple. We invoke the same method in XSSFWorksheet class and get the output we want. Here is a Java program that produces an XLSX workbook by merging data across columns:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeCellsAcrossColumnsXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("XLSX Merge Cells");
                Row row = my_sheet.createRow((short) 1);
                Cell cell = row.createCell((short) 1);
                cell.setCellValue("XLSX - Merge Data with Apache POI");
                my_sheet.addMergedRegion(new CellRangeAddress(
                        1, // mention first row here
                        1, //mention last row here, it is 1 as we are doing a column wise merging
                        1, //mention first column of merging
                        5  //mention last column to include in merge
                        ));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Column_Example.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

No difference to the core method. The output is shown below:

XLSX - Merge Data Across Columns - Java POI Example - Output

In the next tutorial, we will examine how to merge data across rows. Meanwhile, if you have any questions around this, you can post it in the comments section.