Group or Ungroup Rows and Columns in Excel Using Java

Andrew Wilson
2 min readMar 3, 2023

While working with a large data set, MS Excel allows you to organize data in groups so that you can collapse or expand rows/columns with similar content to make the whole document more compact. This article will share how to group or ungroup rows and columns in Excel using a free Java library.

Install the Free Library

Method 1: Download the free library(Free Spire.XLS for Java) and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

<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.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>

Group Rows and Columns in Excel in Java

Free Spire.XLS for Java provides the Worksheet.groupByRows() Worksheet.groupByColumns() methods to group the specified rows and columns. The following is the complete sample code.

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

public class GroupRowsAndColumns {

public static void main(String[] args) {

//Create a Workbook object
Workbook workbook = new Workbook();

//Load a sample Excel file
workbook.loadFromFile("group.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

//Group rows
sheet.groupByRows(2, 6, false);

sheet.groupByRows(8, 10, false);

//Group columns
sheet.groupByColumns(4, 6, false);

//Save to another Excel file
workbook.saveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}

Ungroup Rows and Columns in Excel in Java

To ungroup the rows and columns, you can use the Worksheet.ungroupByRows() and Worksheet.ungroupByColumns() methods. The following is the complete sample code.

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

public class UngroupRowsAndColumns {

public static void main(String[] args) {

//Create a Workbook object
Workbook workbook = new Workbook();

//Load a sample Excel file
workbook.loadFromFile("GroupRowsAndColumns.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

//Ungroup rows
sheet.ungroupByRows(2, 6);
sheet.ungroupByRows(8, 10);

//Ungroup columns
sheet.ungroupByColumns(4, 6);

//Save to a different Excel file
workbook.saveToFile("UngroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}

--

--

Andrew Wilson

Explore C#, Java and Python solutions for processing Word/Excel/PowerPoint/PDF files.