Group or Ungroup Rows and Columns in Excel in Java
Tags: javaexcel, grouprowsandcolumns, ungrouprowsandcolumns, addsubtotalstocellrange
Published: 2021-12-22 07:42:38

---
title: Group or Ungroup Rows and Columns in Excel in Java
published: true
description: This article shows how to group or ungroup rows and columns in Excel in Java, along with how to add subtotals to a cell range in Excel in Java.
tags: Java#Excel, group#rows#and#columns, ungroup#rows#and#columns, add#subtotals#to#cell#range
//cover_image: https://direct_url_to_image.jpg
---
In Microsoft Excel, rows and columns can be grouped or ungrouped to analyze and summarize data. In this article, we will demonstrate how to group or ungroup rows and columns in Excel in Java using [Spire.XLS for Java library](https://www.e-iceblue.com/Introduce/xls-for-java.html).

**Contents Summary:**
* Group Rows and Columns
* Add Subtotals to a Range
* Ungroup Rows and Columns

##Add Dependencies##
To begin with, you need to add needed dependencies for including Spire.XLS for Java library into your Java project. 
You can either download the library’s jar from the [official website](https://www.e-iceblue.com/Download/xls-for-java.html) or install it from Maven by adding the following code to your maven-based project’s pom.xml file.
```java
<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>4.12.2</version>    
    </dependency>    
</dependencies>
```
##Group Rows and Columns##
You can group rows and columns by using the **groupByRows** and **groupByColumns** methods of **XlsWorksheet** class. Both of the methods accept the following parameters:
* **int**: the first row/column index to be grouped.
* **int**: the last row/column index to be grouped.
* **boolean**: indicates whether to show/hide the grouped data.

Note: You need to make sure that each column of the data that you want to group has a label in the first row (e.g., Region), contains similar facts in each column, and the range that you want to group has no blank rows or columns.
```java
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 instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Report.xlsx");

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

        //Group specific rows
        sheet.groupByRows(2, 6, false);
       //Group specific columns
        sheet.groupByColumns(1, 3, false);

        //Save the result file
        workbook.saveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}
```
Output:
![Group rows and columns in Excel using Java](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d175h9t4skvlshgpf4gb.png)
 
##Add Subtotals to a Range##
You can insert subtotals and totals to quickly calculate rows of related data in a range by using the **subtotal** method of **XlsWorksheet** class. The method accepts the following parameters:
* **IXLSRange**: the specific cell range.
* **int**: the column index (zero-based) on which you wish to group data.
* **int[]**: the list of column indexes (zero-based) on which the subtotals should be calculated.
* **SubtotalTypes**: the function that you want to use to calculate the subtotals.        
* **boolean**: Indicates whether to replace existing subtotals.
* **boolean**: Indicates whether to insert page breaks between groups.
* **boolean**: Indicates whether to add summary rows below each group of detail rows.
```java
import com.spire.xls.*;

public class AddSubtotalsToRange {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Report.xlsx");

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

        //Specify the range that you wish to subtotal
        CellRange range = sheet.getCellRange("A2:C11");

        //Add subtotal to the range
        sheet.subtotal(range, 0, new int[] { 2 }, SubtotalTypes.Sum, true, false, true);

        //Save the result file
        workbook.saveToFile("AddSubtotal.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}
```
Output:
![Add subtotals to a cell range in Excel using Java](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/09gj714cyv13avjrfvch.png) 
##Ungroup Rows and Columns##
To ungroup rows and columns, you need to use the **ungroupByRows** and **ungroupByColumns** methods of **XlsWorksheet** class. Both of the methods accept the following parameters:
* **int**: the first row/column index to be ungrouped.
* **int**: the last row/column index to be ungrouped.
```java
import com.spire.xls.CellRange;
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 instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("GroupRowsAndColumns.xlsx");

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

        //Ungroup specific rows
        sheet.ungroupByRows(2, 6);
       //Ungroup specific columns
        sheet.ungroupByColumns(1, 3);

        //Save the result file
        workbook.saveToFile("UnGroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}
``` 
Output:
![Ungroup rows and columns in Excel using Java](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kl9l1bn87xzyo9orikjo.png)