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:
Top comments (0)