Excel - Merge Cell Across Rows - Java POI Example Program

Merge Cells Across Rows - Apache POI - Introduction

In the last tutorial, we explained how to use POI to merge cell data across columns. We discussed how easy it is to merge cell data across columns. In this tutorial, we will provide some example programs for XLS and XLSX formats, that will explain how to merge data across rows. There is no big difference with row level merging, except that you need to specify the range correctly for this merge. Let us get started with some Java POI Examples.

XLS - Merge Data Across Rows - Java Example Program

The Java program that uses Apache POI to merge cell data across rows is given 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 MergeCellsAcrossRows {  
        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 Across Rows - Example");
                /* Columns remain 1 and 1 for us because we are not merging across columns */
                /* Rows change from 1 to 4 as we need a merge from 1 to 4 rows */
                my_sheet.addMergedRegion(new CellRangeAddress(
                        1, // mention first row here
                        4, //mention last row here, it is 4 as we are doing a row wise merging
                        1, //mention first column of merging
                        1  //mention last column to include in merge
                        ));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Across_Rows.xls"));
                my_workbook.write(out);
                out.close();
        }
}

This program merges data across rows as shown in the output below:

XLS - Merge Data Across Rows - Java POI Example Output

XLSX - Merge Data Across Rows - Java Example Program

The Java program that generates an XLSX files by merging data across rows is shown below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeCellsAcrossRowsXLSX {  
        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 Across Rows");
                my_sheet.addMergedRegion(new CellRangeAddress(
                        1, // mention first row here
                        5, //mention last row here, 5 to merge 5 rows
                        1, //mention first column of merging
                        1  //mention last column to include in merge
                        ));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Rows_Example.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

The output of the program is shown below:

XLSX - Merge Data Across Rows - Java POI Example Output

That completes a row level merge tutorial. Let us now get into the final part of this merge example, where we discuss how to merge across both rows and columns. Stay connected.