Data Grouping

• Aug 22, 2018
• 6 minutes to read

The SpreadsheetControl provides you with the capability to split a large amount of data into separate groups and display summary rows and columns for each group. Data grouping is useful when you wish to temporally hide unnecessary rows or columns to display only significant information.

End-users can create an outline of rows and columns, expand or collapse the specified groups, and change the group settings.

Group and Ungroup Data

To enable end-users to group data in a worksheet, add the Outline Ribbon page group to the SpreadsheetControl. For an example on how to provide a Ribbon UI for the SpreadsheetControl, see the Getting Started topic.

The table below lists the basic grouping operations available to end-users in the user interface, and the corresponding API methods and properties.

Action	End-User Capabilities	API methods/properties
Group rows and columns	Select the rows or columns you wish to group. On the Data tab, in the Outline group, click the Group button, and select the Group item in the button’s 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 the rows or columns you wish to remove from the group. To remove the entire group, select all its rows or columns. On the Data tab, in the Outline group, click the Ungroup button, and select the Ungroup item in the button’s 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 the summary rows or columns containing formulas that reference cells to be outlined. On the Data tab, in the Outline group, click the Group button, and select the Auto Outline item in the button’s drop-down menu.	Worksheet.AutoOutline ColumnCollection.AutoOutline RowCollection.AutoOutline How to: Outline Data Automatically
Remove an outline	On the Data tab, in the Outline group, click the Ungroup button, and select the Clear Outline item in the button’s drop-down menu.	Worksheet.ClearOutline ColumnCollection.ClearOutline RowCollection.ClearOutline RangeExtensions.ClearOutline
Outline data and calculate subtotals for the related rows	Select the range you wish to subtotal. Make sure that this range contains column labels and it is sorted by a column that will be used for grouping. On the Data tab, in the Outline group, click the Subtotal button. In the invoked Subtotal dialog, configure the subtotals and options needed. For more information, refer to Subtotal Dialog.	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 the Group

• Using the outline symbols

  Since the group is created, it is marked by the grouping bar displayed along the grouped rows to the left side of a worksheet and along the grouped columns at the top of a worksheet. Each grouping bar is accompanied by a plus or minus outline symbol (depending on the group state). To collapse the required group, click the minus sign, which automatically changes into a plus sign after the group is collapsed, or just click the grouping bar itself. To expand the collapsed group, click the plus sign.

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

  

• Using the outline buttons

  If your data is divided into different grouping levels , you can collapse or expand multiple groups at once using the outline buttons . The outline buttons for row grouping are displayed above the row headers, while the outline buttons for column grouping are shown to the left of the column headers. Each outline button has a number that indicates a grouping level. For example, in the image at the beginning of this article there are two levels of row grouping. Clicking the outline button 1 collapses all the inner groups and displays only the grand total for all detail rows. Clicking the number 2 collapses all the groups except the first level and displays the subtotal rows for each inner group in the second level. Clicking the last outline button 3 displays the entire worksheet by expanding all the specified groups.

  Thus, to hide all detail data, click the outline button 1. To display all detail data, click the outline button with the largest number.

  The number of outline buttons changes depending on the levels of grouping created in a worksheet. The largest number is 8, because you can create a maximum of seven levels of grouping.

Group Settings

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

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

In this case, the grouping bar changes direction, so that the minus outline symbol is displayed opposite the summary row at the top of the group.

Summary rows below detail	Summary rows above detail

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

End-User Restrictions

The SpreadsheetControl allows you to prevent end-users from creating and modifying the outlines of rows and columns in a worksheet. To implement end-user restrictions, use the SpreadsheetBehaviorOptions.Group property, which provides access to the restriction settings. The table below lists possible restrictions you can impose on working with groups of rows and columns in the SpreadsheetControl.

Restriction	Description
SpreadsheetGroupBehaviorOptions.Group	Gets or sets whether end-users are allowed to group data in a worksheet.
SpreadsheetGroupBehaviorOptions.Ungroup	Gets or sets whether or not end-users are allowed to ungroup data in a worksheet.
SpreadsheetGroupBehaviorOptions.Expand	Gets or sets whether or not end-users are allowed to expand groups.
SpreadsheetGroupBehaviorOptions.Collapse	Gets or sets whether or not end-users are allowed to collapse groups.
SpreadsheetGroupBehaviorOptions.ChangeSettings	Gets or sets whether or not end-users are allowed to change group settings.

Set the desired property to the DocumentCapability.Disabled or DocumentCapability.Hidden value, to disable or hide the corresponding command in the Ribbon UI and deactivate the outline buttons and symbols.