Group or Ungroup Rows and Columns in Excel in Java

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.

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 or install it from Maven by adding the following code to your maven-based 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>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.

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:

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.

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:

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.

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: