Group or Ungroup Rows and Columns in Excel Using Java

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);
    }
}