Grouping and Ungrouping Rows and Columns

Introduction

In a Microsoft Excel file, you can create an outline for the data to let you show and hide levels of detail with a single mouse click.

Click the Outline Symbols, 1,2,3, + and - to quickly display only the rows or columns that provide summaries or headings for sections in a worksheet, or you can use the symbols to see details under an individual summary or heading as shown below in the figure:

Grouping of rows & columns

Group Management of Rows & Columns

Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection that represents all cells in the worksheet.

The Cells collection provides several methods to manage rows or columns in a worksheet, few of these are discussed below in more detail.

Grouping Rows & Columns

It is possible to group rows or columns by calling the groupRows and groupColumns methods of the Cells collection. Both methods take the following parameters:

• First row/column index, the first row or column in the group.
• Last row/column index, the last row or column in the group.
• Is hidden, a Boolean parameter that specifies whether to hide rows/columns after grouping or not.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(GroupingRowsandColumns.class) + "RowsAndColumns/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Grouping first six rows (from 0 to 5) and making them hidden by
// passing true
cells.groupRows(0, 5, true);

// Grouping first three columns (from 0 to 2) and making them hidden by
// passing true
cells.groupColumns(0, 2, true);

// Setting SummaryRowBelow property to false
worksheet.getOutline().setSummaryRowBelow(true);

// Setting SummaryColumnRight property to false
worksheet.getOutline().setSummaryColumnRight(true);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "GroupingRowsandColumns_out.xlsx");

Group Settings

Microsoft Excel also allows to configure group settings for displaying:

• Summary rows below detail.
• Summary columns to right of detail.

Group settings

It is possible to configure these group settings using the Worksheet class' Outline property.

Summary Rows Below Detail

Developers can control displaying summary rows below detail by using the Outline class' SummaryRowBelow method.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SummaryRowBelow.class) + "RowsAndColumns/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Grouping first six rows (from 0 to 5) and making them hidden by passing true
cells.groupRows(0, 5, true);

// Grouping first three columns (from 0 to 2) and making them hidden by passing true
cells.groupColumns(0, 2, true);

// Setting SummaryRowBelow property to false
worksheet.getOutline().setSummaryRowBelow(false);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "SummaryRowBelow_out.xls");

Summary Columns to Right of Detail

It is possible to control whether summary columns are displayed to the right of the details with the Outline class' SummaryColumnRight method.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SummaryRowRight.class) + "RowsAndColumns/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "BookStyles.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Grouping first six rows (from 0 to 5) and making them hidden by passing true
cells.ungroupRows(0, 5);

// Grouping first three columns (from 0 to 2) and making them hidden by passing true
cells.ungroupColumns(0, 2);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "SummaryRowRight_out.xls");

Ungrouping Rows & Columns

Ungroup grouped rows or columns by calling the Cells collection’s UngroupRows and UngroupColumns methods. Both methods take the same parameters:

• First row or column index, the first row/column to be ungrouped.
• Last row or column index, the last row/column to be ungrouped.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(UngroupingRowsandColumns.class) + "rows_cloumns/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "BookStyles.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Grouping first six rows (from 0 to 5) and making them hidden by
// passing true
cells.ungroupRows(0, 5);

// Grouping first three columns (from 0 to 2) and making them hidden by
// passing true
cells.ungroupColumns(0, 2);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "UngroupingRowsandColumns_out.xls");

// Print message
System.out.println("Rows and Columns ungrouped successfully.");