Excel - Merge Cells - Rows and Columns - Java POI Example Program

Merge Cell Data Across Rows and Columns - Introduction

We have so far seen how to merge data across columns and how to merge data across rows, in Excel, using Java Apache POI library. In this tutorial, we will discuss how to place your cell data by merging it across rows and columns in POI, with examples. We will cover both XLS and XLSX version in the final part of this merge discussion series.

XLS - Merge Rows and Columns - Java POI Example

The Java program that uses Apache POI to merge data across rows and columns is provided below: (We also specify a simpler way to provide CellRangeAddress, a more meaningful approach)

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeRowsAndColumns {  
        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 and Columns - Example");
                //We want the Cell Data to be distributed across B2 to D5 range
                // We use static method valueOf in CellRangeAddress, to specify range
                my_sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D5"));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Rows_Columns.xls"));
                my_workbook.write(out);
                out.close();
        }
}

The output of this program is shown below, clearly indicating the merge regions

XLS - Merge Rows and Columns - Java POI Example Output

XLSX - Rows / Columns Merge - Java Example

We can write a XLSX example on the same lines to get the merge done across rows and columns. The Java Program example 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 MergeRowsAndColumnsXLSX {  
        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 Rows Columns");
                //We want the Cell Data to be distributed across B2 to D5 range
                // We use static method valueOf in CellRangeAddress, to specify range
                my_sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D5"));
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Rows_Columns.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

XLSX - Merge Rows and Columns - Java POI Example Output

That completes our merge series tutorials on Apache POI. More on POI usage with XLS workbooks to come. Stay connected to the blog.