Merge and Split Cells in Excel using Java

When manipulating data in Excel worksheets, we often find ourselves in a situation where we need to merge or split some cells located in the same row or column, to make a sense of our table or to make it more neatly. This tutorial will demonstrate how to merge or split cells in Excel by using Java codes.

It’s necessary to use a free API called Free Spire.XLS for Java to realize the functions above. Before running codes, you need to add a Jar file in the API to IDEA. Please download the package from the link, unzip it and find the Jar file in the “lib” folder, or refer to it by adding the following Maven configurations.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

Merge Cells in Excel Using Java

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class MergeCell {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Merge cells by range
        sheet.getRange().get("A1:E1").merge();

        //Save the resulting file
        workbook.saveToFile("output/MergeCells.xlsx", FileFormat.Version2013);
    }
}

Output

Split Cells in Excel Using Java

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SplitCell {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\MergeCells.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Split cells by range
        sheet.getRange().get("A1:E1").unMerge();

        //Save the resulting file
        workbook.saveToFile("output/SplitCells.xlsx", FileFormat.Version2013);
    }
}

Output