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

3 comments:

  1. Please help me how to read merged cell data since I'm in need of that

    ReplyDelete
  2. my value not shown in merged column please help me

    ReplyDelete