Cell Data Grouping
- 6 minutes to read
The Spreadsheet Control provides the data grouping functionality as an additional option for cell data representation in spreadsheet documents. This feature works similarly to outlining data in Excel. By aggregating columns and/or rows into collapsible groups, you can organize large amounts of data and display both the headers and summary columns and/or rows for each logical group:
Each outlined group of table items, in turn, can contain one or more similar groups, allowing you to group cell data further into the subcategories framed by intermediate headers and summary rows and/or columns:
The maximum node level of the table item group hierarchy is limited by the dxSpreadSheetMaxOutlineLevel global constant value.
To expand/collapse all branches of the column or row group hierarchies to a certain node level, an end-user can click the numbered expand buttons. Clicking a numbered expand button expands all table item groups located below the respective node level and collapses all groups at the target level and above:
To expand/collapse column and row groups individually, an end-user can click expand buttons that correspond to specific groups:
To allow end-users to add, remove, and edit the row and column groups, you need to use additional controls, since the Spreadsheet control does not provide user interface elements for table item group management. For additional information on implementing cell data grouping functionality in your spreadsheet-based application, refer to the following sections of this document:
Table Item Groups Hierarchy;
Accessing Individual Column or Row Groups;
Groups Expanding and Collapsing;
Table Item Groups Hierarchy
All table item groups created within a Table View worksheet are organized into a tree-like hierarchical structure originating from the row and column root nodes. These non-visual nodes serve as containers for the primary (i.e., not nested) column and row groups which you can access by using the Table View worksheet’s Columns.Groups and Rows.Groups properties, respectively:
The primary column and row groups, created at the root node, can nest other table item groups. You can address these child (nested) groups by using the Items property of the respective parent (primary) group:
Accessing Individual Column or Row Groups
Use the Table View worksheet’s Columns.Groups.Items and Rows.Groups.Items properties for zero-based indexed access to the root column and row groups, respectively. To identify the total number of individual root column and row groups within the current worksheet, use the Columns.Groups.Count and Rows.Groups.Count properties, respectively. If a root group with the specified index does not exist, the Items property returns nil.
For zero-based indexed access to groups nested within the current group, use its Items property. To identify the total number of nested groups, use the Count property. As in the case of root column and row groups, the Items property returns nil if the addressed group does not exist.
The group’s own nesting level is returned by the Level property. A zero nesting level corresponds to the column and row groups created within the respective root nodes of a Table View worksheet.
Instead of addressing a particular table item group via the direct chain multiple of Items properties corresponding to individual node levels, you can use the Find function available for both the column and row group hierarchies. This function returns an instance of the first group with the specified index. By default, if the required table item group is not found at the first node level, the search continues at subsequent node levels either until the group is found or the last node level within the hierarchy is reached. If the search is unsuccessful, the Find function returns nil instead of an instance of the column or row group.
To improve the application performance, you can initiate the search starting from any node within the hierarchy. Use the group‘s own Find function to find the specified group among the groups nested within the current node (group). This function is identical to the TdxSpreadSheetTableItemGroups.Find function used to search groups starting from the first node level.
Groups Expanding and Collapsing
To expand or collapse individual column or row groups within the tree-like hierarchy, you can use either the Expanded property or the ToggleExpanded procedure of the respective group.
To expand or collapse the entire tree of column or row groups to the required node level programmatically, invoke the ExpandToLevel procedure via the Table View‘s Columns.Groups or Rows.Groups property, respectively. The ALevel parameter specifies the required node level that has a corresponding numbered outline button:
| ALevel Parameter Value | Outline Buttons in the Column or Row Grouping Area | The State of the Table Item Groups Tree |
|---|---|---|
| -1 or less |  |
This value (button) corresponds to the first node level of the table item group hierarchy. A tree of column or row groups is fully collapsed. Only the expand buttons of the collapsed parent groups are visible. |
| 0 |  |
A tree of column or row groups is expanded to the second node level. Only the parent groups are visible. All nested groups are collapsed. |
| 1 |  |
A tree of column or row groups is expanded to the third node level. Both the parent groups and the first level of nested groups are displayed. All groups located at higher levels are collapsed. |
| 2 |  |
A tree of column or row groups is expanded to the fourth node level. Both the parent groups and the next two levels of nested groups are displayed. All groups located at higher levels are collapsed. |
| 3 |  |
A tree of column or row groups is expanded to the fifth node level. Both the parent groups and the next three levels of nested groups are displayed. All groups located at higher levels are collapsed. |
| 4 |  |
A tree of column or row groups is expanded to the sixth node level. Both the parent groups and the next four levels of nested groups are displayed. All groups located at higher levels are collapsed. |
| 5 |  |
A tree of column or row groups is expanded to the seventh node level. Both the parent groups and the next five levels of nested groups are displayed. All groups located at higher levels are collapsed. |
| 6 or higher |  |
This value (button) corresponds to the highest node level of a tree of column or row groups. This ALevel parameter value matches the dxSpreadSheetMaxOutlineLevel global constant value. All table item groups are expanded within a tree. |
Note that you cannot expand the tree of table item groups to a level that exceeds the actual maximum node level within this hierarchy. For instance, if you set the ALevel parameter of the ExpandToLevel procedure to 3 while there is only one level of nested groups within a table item groups tree, all existing column or row groups are simply fully expanded instead.