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
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
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
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.

4 comments:

  1. where is the next tutorial about merging across rows??

    ReplyDelete
  2. @Anonymous,

    use the search features on the site or related post ("More") button. If you still cannot find it, check here

    http://thinktibits.blogspot.com/2012/12/Excel-Merge-Cell-Across-Rows-Java-POI-Example-Program.html

    ReplyDelete
  3. HI suppost i want to add border to this area how will you do that
    i am not able to give border for cells merged across rows...it just wraps border for first cell

    ReplyDelete
  4. Hi , I want to concatenate the multiple excel columns data with Comma (,) and keep it in a single column using java code or sql query ...
    could any help me how can i do that

    ReplyDelete