Merging cells in Excel refers to combining two or more adjacent cells into one large cell that spans multiple rows or columns. This is useful for creating titles or labels that need to be centered over a range of cell. In this article, you will learn how to programmatically merge or unmerge cells in an Excel document using Spire.XLS for Java.
Install Spire.XLS for Java
First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>14.4.1</version>
</dependency>
</dependencies>
Merge Cells in Excel in Java
The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel document using Workbook.loadFromFile() method.
- Get a specified worksheet using Workbook.getWorksheets().get() method.
- Get a specified range using Worksheet.getRange().get() method.
- Merge cells in the specified range using XlsRange.merge() method.
- Set the horizontal alignment of merged cells to Center using XlsRange.getCellStyle().setHorizontalAlignment() method.
- Set the vertical alignment of merged cells to Center using XlsRange.getCellStyle().setVerticalAlignment() method.
- Save the result document using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class MergeCells {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Merge cells by range
sheet.getRange().get("A2:A4").merge();
sheet.getRange().get("A5:A7").merge();
//Set the horizontal alignment of merged cells to Center
sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
sheet.getRange().get("A5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//Set the vertical alignment of merged cells to Center
sheet.getRange().get("A2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
sheet.getRange().get("A5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//Save the result document
workbook.saveToFile("MergeCells.xlsx", FileFormat.Version2013);
}
}
Unmerge Cells in Excel in Java
The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel document using Workbook.loadFromFile() method.
- Get a specified worksheet using Workbook.getWorksheets().get() method.
- Get a specified range using Worksheet.getRange().get() method.
- Unmerge cells in the specified range using XlsRange.unMerge() method.
- Save the result document using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class UnmergeCells {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("MergeCells.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Unmerge cells by range
sheet.getRange().get("A2:A4").unMerge();
//Save the result document
workbook.saveToFile("UnMergeCells.xlsx", FileFormat.Version2013);
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
