Data Grouping
- 5 minutes to read
The SpreadsheetControl enables you to split a large amount of data into groups and display summary rows and columns for each group.
End users can create an outline of rows and columns, expand or collapse groups, and change group settings
Group and Ungroup Data
To enable end users to group data in a worksheet, add the Outline ribbon group to the SpreadsheetControl. Refer to the Getting Started topic for details on how to provide a ribbon UI for the SpreadsheetControl.
The table below lists the basic group operations in the UI, and the corresponding API methods and properties.
Action | End-User Capabilities | API methods/properties |
---|---|---|
Group rows and columns |
| |
Ungroup rows and columns |
| |
Outline data automatically |
| |
Remove an outline | Click the Ungroup button on the ribbon, and select Clear Outline in the button’s drop-down menu. | |
Outline data and calculate subtotals for related rows |
| |
Clear an outline and remove the subtotals | Invoke the Subtotal dialog and click the Remove All button. |
Collapse or Expand Groups
Use the outline symbols
When you group data in a worksheet, the SpreadsheetControl 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 group to hide or display a group’s detail rows and columns.
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. 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 number of outline buttons changes depending on the levels of grouping in a worksheet. The largest number is 8, because you can create up to seven group levels.
In code
Use the following methods to expand or collapse row/column groups programmatically:
- RowCollection.CollapseAllGroups, ColumnCollection.CollapseAllGroups - collapse all the 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 the row/column groups in a worksheet.
Hide outline symbols
Use the WorksheetView.ShowOutlineSymbols property to show or hide outline symbols and bars for a worksheet with grouped data.
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 | Gets or sets whether end users can group data in a worksheet. |
SpreadsheetGroupBehaviorOptions.Ungroup | Gets or sets whether end users can ungroup data in a worksheet. |
SpreadsheetGroupBehaviorOptions.Expand | Gets or sets whether end users can expand groups. |
SpreadsheetGroupBehaviorOptions.Collapse | Gets or sets whether end users can collapse groups. |
SpreadsheetGroupBehaviorOptions.ChangeSettings | Gets or sets whether end users can change group settings. |
SpreadsheetGroupBehaviorOptions.CollapseExpandOnProtectedSheet | Gets or sets whether end users can collapse or expand groups located on a protected worksheet. |
SpreadsheetGroupBehaviorOptions.CollapseExpandOnReadOnlyControl | Gets or sets whether end users can collapse or expand groups when the SpreadsheetControl’s ReadOnly property is true. |
You can set these properties to the DocumentCapability.Disabled or DocumentCapability.Hidden value, to disable or hide the corresponding commands in the ribbon UI and deactivate the outline buttons and symbols.