Group Data in the Spreadsheet Control

• May 19, 2022
• 5 minutes to read

The WPF Spreadsheet enables you to split a large amount of data into groups and display summary rows and columns for each group.

Users can create an outline of rows and columns, expand or collapse groups, and change group settings.

Group and Ungroup Data

To enable users to group data in a worksheet, add the Outline ribbon group to the Spreadsheet control. See the following topic for details on how to create a ribbon UI for the Spreadsheet control: Create a Simple Spreadsheet Application.

The table below lists the basic group operations in the UI and the corresponding API methods and properties.

Action	End-User Capabilities	API
Group rows and columns	Select rows or columns you want to group. Click the Group button on the ribbon, and then select Group in the drop-down menu. You can also use a key combination to group data quickly. Press SHIFT+ALT+RIGHT ARROW KEY to create an outline of the selected rows or columns.	ColumnCollection.Group RowCollection.Group How to: Outline Data Manually
Ungroup rows and columns	Select rows or columns you want to remove from the group. To remove the entire group, select all its rows or columns. Click the Ungroup button on the ribbon, and then select Ungroup in the drop-down menu. You can also use a key combination to ungroup data quickly. Press SHIFT+ALT+LEFT ARROW KEY to clear an outline for the selected rows or columns.	ColumnCollection.UnGroup RowCollection.UnGroup How to: Outline Data Manually
Outline data automatically	Specify summary rows/columns containing formulas that reference cells in detail rows/columns. Click the Group button on the ribbon, and then select Auto Outline in the drop-down menu.	Worksheet.AutoOutline ColumnCollection.AutoOutline RowCollection.AutoOutline How to: Outline Data Automatically
Remove an outline	Click the Ungroup button on the ribbon, and then select Clear Outline in the button’s drop-down menu.	Worksheet.ClearOutline ColumnCollection.ClearOutline RowCollection.ClearOutline RangeExtensions.ClearOutline
Outline data and calculate subtotals for related rows	Select the cell range you want to subtotal. Make sure it has column labels in the first row, and its data is sorted to show the same values in one group. Click the Subtotal button on the ribbon. In the invoked Subtotal dialog, configure subtotal options.	Worksheet.Subtotal RangeExtensions.Subtotal How to: Insert Subtotals in a Data Range
Clear an outline and remove the subtotals	Invoke the Subtotal dialog and click the Remove All button.	Worksheet.RemoveSubtotal RangeExtensions.RemoveSubtotal

Collapse or Expand Groups

Use the Outline Symbols

When you group data in a worksheet, the Spreadsheet control draws outline bars next to column and row groups. Each outline bar has a plus or minus symbol that indicates the group state. To collapse a group, click the minus sign or the outline bar itself. To expand the group, click the plus sign.

You can also use the Hide Detail and Show Detail buttons in the Outline ribbon group to hide or display a group’s detail rows and columns.

The following events occur when you expand/collapse row or column groups in the Spreadsheet control’s UI:

• BeforeGroupProcessing
• GroupStateChanging
• GroupStateChanged
• AfterGroupProcessing

Use the Outline Buttons

If you divide your data into different group levels, you can use the outline buttons to collapse or expand multiple groups at once. Each outline button has a number that indicates the group level. The largest number is 8, because you can create up to seven group levels. Click the outline button 1 to collapse all groups. Click the outline button with the largest number to display all detail data.

For instance, there are two levels of row groups in the image at the beginning of this article.

• Click outline button 1 to collapse all inner groups and display only the grand total row.

  

• Click number 2 to collapse all the groups except the first level and display subtotal rows for each inner group.

  

• Click outline button 3 to expand all the groups.

The following events occur when you click an outline button:

• BeforeGroupProcessing
• BeforeOutlineButtonClick
• AfterOutlineButtonClick
• AfterGroupProcessing

In Code

Use the following methods to expand or collapse row/column groups programmatically:

RowCollection.CollapseAllGroups, ColumnCollection.CollapseAllGroups

Collapse all row/column groups in a worksheet.

RowCollection.CollapseGroups, ColumnCollection.CollapseGroups

Collapse row/column groups starting with the specified group level.

RowCollection.ExpandAllGroups, ColumnCollection.ExpandAllGroups

Expand all row/column groups in a worksheet.

Hide Outline Symbols

Use the WorksheetView.ShowOutlineSymbols property to display or hide outline symbols and bars for a worksheet with grouped data.

C#

workbook.Worksheets[0].ActiveView.ShowOutlineSymbols = false;

VB.NET

workbook.Worksheets(0).ActiveView.ShowOutlineSymbols = False

Group Settings

You can specify a group’s direction using the Settings dialog. To invoke this dialog, click the Outline dialog box launcher on the Data tab.

Select the required check boxes depending on where your summary rows or columns are located. For instance, if you insert summary rows above detail rows in each group, clear the Summary rows below detail check box.

Summary rows below detail	Summary rows above detail

To specify group settings in code, use the Worksheet.OutlineOptions property.

End-User Restrictions

You can use the SpreadsheetBehaviorOptions.Group property to specify restrictions on operations with groups in the UI. The following options are available:

Restriction	Description
SpreadsheetGroupBehaviorOptions.Group	Specifies whether users can group data in a worksheet.
SpreadsheetGroupBehaviorOptions.Ungroup	Specifies whether users can ungroup data in a worksheet.
SpreadsheetGroupBehaviorOptions.Expand	Specifies whether users can expand groups.
SpreadsheetGroupBehaviorOptions.Collapse	Specifies whether users can collapse groups.
SpreadsheetGroupBehaviorOptions.ChangeSettings	Specifies whether users can change group settings.
SpreadsheetGroupBehaviorOptions.CollapseExpandOnProtectedSheet	Specifies whether users can collapse or expand groups located on a protected worksheet.
SpreadsheetGroupBehaviorOptions.CollapseExpandOnReadOnlyControl	Specifies whether users can collapse or expand groups when the SpreadsheetControl’s ReadOnly property is true.

You can set these properties to the Disabled or Hidden value to disable or hide the corresponding commands in the ribbon UI and deactivate the outline buttons and symbols.