_This post updates one of our most popular blog posts, Creating Outlines and Trees with the C1FlexGrid Control, originally published in 2010. Here we address the same topic, integrating all the updates we've made in the last 5 years._
One of the unique and popular features of the FlexGrid for WinForms control is the ability to add hierarchical grouping to regular unstructured data. To achieve this, the C1FlexGrid introduces the concept of Node rows. Node rows do not contain regular data. Instead, they act as headers under which similar data is grouped, exactly like nodes in a regular TreeView control. Like nodes in a TreeView control, node rows can be collapsed and expanded, hiding or showing the data they contain. Also like nodes in a TreeView control, node rows have a Level property that defines the node hierarchy. Lower level nodes contain higher level nodes. For example, suppose you had a grid showing product, country, city, and sales amounts.
This typical grid would normally look like this:
All the information is there, but it's hard to see the total sales for each country or customer. You could use the C1FlexGrid's outlining features to group the data by country (level 0), then by city within each country (level 1), then by product within each city (level 2).
Here is the same grid after adding the outline:
This grid shows the same information as the previous one (it is bound to the same data source), but it adds a tree where each node contains a summary of the data below it. Nodes can be collapsed to show only the summary, or expanded to show the detail. Note that each node row can show summaries for more than one column (in this case, total units sold and total amount). In this article, we will walk you through the process of turning a regular grid into a richer outline grid.
Download Visual Studio Sample (C#) >>
Loading the Data
For loading data into the grid, we'l use Database First development with Entity Framework. You can read more about it in this MSDN article in the Data Developer Center.
Note that in our example we'll use EntityFramework 6.0, Northwind database and SQL Server 2014 Express LocalDB.
Since we're using SQL Server 2014 Express LocalDB, our connection sting to northwnd.mdf file will be look like this:
Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\C1FlexGridOutlinesAndTreesBound\\C1FlexGridOutlinesAndTreesBound\\northwnd.mdf;Integrated Security=True
Your connecting string may vary from using different versions of SQL Server. For example, if you are using SQL Server 2012 Express LocalDB, connection string will be:
Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\C1FlexGridOutlinesAndTreesBound\\C1FlexGridOutlinesAndTreesBound\\northwnd.mdf;Integrated Security=True
If you're using some new versions of SQL Server and cannot connect Northwind database to it, you can try to find the solution here: Solving Install Northwind Database on SQL Server Problem
After generation of the model is completed, we'll get something like shown on this diagram:
Now we can fill our grid with data this way:
public Form1()
{
InitializeComponent();
// get data
_ctx = new northwndEntities();
DbSet orders = _ctx.Orders;
DbSet ordersDetails = \_ctx.Order\_Details;
IQueryable ordersQuery = from order in orders
join orderDetail in ordersDetails on order.OrderID equals orderDetail.OrderID
orderby order.ShipCountry, order.ShipCity, orderDetail.Product.ProductName, orderDetail.UnitPrice, orderDetail.Quantity
select new OrderInfo
{
Country = order.ShipCountry,
City = order.ShipCity,
Product = orderDetail.Product.ProductName,
Price = orderDetail.UnitPrice,
Quantity = orderDetail.Quantity,
};
_ordersData = ordersQuery.ToList();
// bind grid to data
\_flex.DataSource = \_ordersData;
// format Price column
_flex.Cols["Price"].Format = "n2";
// format Cost column
_flex.Cols["Cost"].Format = "n2";
}
The code uses LINQ to Entities query to fill list of records for our grid, then assigns the list to the grid’s DataSource property. In addition, above code uses OrderInfo class, which we'll use to keep our data. Its code is quite simple and reflects the data we actually want to see in the grid:
class OrderInfo
{
decimal _price = 0;
short _quantity = 0;
public string Country { get; set; }
public string City { get; set; }
public string Product { get; set; }
public decimal Price
{
get
{
if (_price > 0)
return _price;
return 0;
}
set
{
if (_price != value)
_price = value;
}
}
public short Quantity
{
get
{
if (_quantity > 0)
return _quantity;
return 0;
}
set
{
if (_quantity != value)
_quantity = value;
}
}
public decimal Cost
{
get
{
return Price * Quantity;
}
}
}
Creating Node Rows
Node rows are almost identical to regular rows, except for the following:
- Node rows are not data bound. When the grid is bound to a data source, each regular row corresponds to an item in the data source. Node rows do not. Instead, they exist to group regular rows that contain similar data.
- Node rows can be collapsed or expanded. When a node row is collapsed, all its data and child nodes are hidden. If the outline tree is visible, users can collapse and expand nodes using the mouse or the keyboard. If the outline tree is not visible, then nodes can only be expanded or collapsed using code.
To determine whether a row is a node or not, you can use the IsNode property:
var row = _flex.Rows[rowIndex];
if (row.IsNode)
{
// row is a node
var node = row.Node;
DoSomethingWithTheNode(node);
}
else
{
// this row is not a node
}
Node rows can be created in three ways:
- Use the Rows.InsertNode method. This inserts a new node row at a specified index. Once the node row has been created, you can use it like any other row (set the data for each column, apply styles, etc.). This is the low-level way of inserting totals and building outlines. It gives the most control and flexibility and is demonstrated below.
- Use the Subtotal method. This method scans the entire grid and automatically inserts node rows with optional subtotals at locations where the grid data changes. This is the high-level way of inserting totals and building outlines. It requires very little code, but makes some assumptions about how the data is structured on the grid and what the outline should look like.
- If the grid is unbound, then you can turn regular rows into node rows by setting the IsNode property to true. Note that this only works when the grid is unbound. Trying to turn a regular data bound row into a node will cause the grid to throw an exception.
The code below shows how you could implement a GroupBy method that inserts node rows grouping identical values on a given column.
// group on a given column inserting nodes of a given level
void GroupBy(string columnName, int level)
{
object current = null;
for (int r = \_flex.Rows.Fixed; r < \_flex.Rows.Count; r )
{
if (!_flex.Rows[r].IsNode)
{
var value = _flex[r, columnName];
if (!object.Equals(value, current))
{
// value changed: insert node
_flex.Rows.InsertNode(r, level);
// show group name in first scrollable column
\_flex[r, \_flex.Cols.Fixed] = value;
// update current value
current = value;
}
}
}
}
The code scans all the columns, skipping existing node rows (so it can be called to add several levels of nodes), and keeps track of the current value for the column being grouped on. When the current value changes, a node row is inserted showing the new in the first scrollable column.
Back to our example: you could use this method to create a two-level outline by calling:
void \_btnGroupCountryCity\_Click(object sender, EventArgs e)
{
GroupBy("Country", 0);
GroupBy("City", 1);
}
Very simple, but there are some caveats.
- The method assumes that the data is sorted according to the outline structure. In this example, if the data were sorted by Product instead of by Country, the outline would have several level-0 nodes for each country, which probably is not what you want.
- Also, the GroupBy method may insert many rows, which would cause the grid to flicker. To avoid this, you would normally use BeginUpdate method before making the updates and EndUpdate when done.
To handle these issues, the code that creates the outline should be re-written as follows:
void \_btnGroupCountryCity\_Click(object sender, EventArgs e)
{
// suspend redrawing while updating
using (new DeferRefresh(_flex))
{
// restore original sort (by Country, City, etc.)
ResetBinding();
// group by Country, City
GroupBy("Country", 0);
GroupBy("City", 1);
}
}
The DeferRefresh class is a simple utility that uses BeginUpdate method when it is created and EndUpdate when it is disposed. This ensures that all updates to the grid are done even when exceptions happen during the updates. Here is the implementation of the DeferRefresh class:
/// Utility class used to encapsulate grid lengthy operations in BeginUpdate/EndUpdate block.
/// This avoids flicker and ensures that all updates are done in case
/// an exception is thrown during the operation.
class DeferRefresh : IDisposable
{
C1FlexGrid _grid;
public DeferRefresh(C1FlexGrid grid)
{
_grid = grid;
_grid.BeginUpdate();
}
public void Dispose()
{
_grid.EndUpdate();
}
}
The BindGrid method ensures that the grid is sorted in the order required by our outline structure. In our example, the sort order is by Country, City, and Product. The code looks like this:
void ResetBinding()
{
// re-bind grid
_flex.DataSource = null;
\_flex.DataSource = \_ordersData;
// format Price column
_flex.Cols["Price"].Format = "n2";
// format Cost column
_flex.Cols["Cost"].Format = "n2";
// resize columns to fit their content
_flex.AutoSizeCols();
}
If you run this code now, you'll notice that the node rows are created as expected, but the outline tree is not visible, so you can't expand and collapse the nodes. The outline tree is described in the next section.
Outline Tree
The outline tree is very similar to the one you see in a regular TreeView control. It shows an indented structure with collapse/expand icons next to each node row so the user can expand and collapse the outline to see the desired level of detail.
The outline tree can be displayed in any column, defined by the Tree.Column property. By default, this property is set to -1, which causes the tree not to be displayed at all. To show the outline tree in the example given above, use this code:
void \_btnTreeCountryCity\_Click(object sender, EventArgs e)
{
using (new DeferRefresh(_flex))
{
// group by country and city as before
\_btnGroupCountryCity\_Click(this, EventArgs.Empty);
// show outline tree
_flex.Tree.Column = 0;
// autosize to accommodate tree
\_flex.AutoSizeCol(\_flex.Tree.Column);
// collapse detail nodes
_flex.Tree.Show(1);
}
}
The code calls the previous method to build the outline, and then sets the Tree.Column property to zero in order to show the outline tree in the first column. It also calls the AutoSizeCol method to ensure that the column is wide enough to accommodate the outline tree. Finally, it calls the Tree.Show method to display all level-0 nodes (cities in this case) and hide all the detail.
The Tree property returns a reference to a GridTree object that exposes several methods and properties used to customize the outline tree. The main ones are listed below:
- Column: Gets or sets the index of the column that contains the outline tree. Setting this property to -1 causes the outline tree to be hidden from the users.
- Indent: Gets or sets the indent, in pixels, between adjacent node levels. Higher indent levels cause the tree to become wider.
- Style: Gets or sets the type of outline tree to display. Use this property to determine whether the tree should include a button bar at the top to allow users to collapse/expand the entire tree, whether lines and/or symbols should be displayed, and whether lines should be displayed connecting the tree to data rows as well as node rows.
- LineColor: Gets or sets the color of the tree's connecting lines.
- LineStyle: Gets or sets the style of the tree's connecting lines.
For example, by changing the code above to include these two lines:
// show outline tree
_flex.Tree.Column = 0;
_flex.Tree.Style = TreeStyleFlags.CompleteLeaf;
_flex.Tree.LineColor = Color.Blue;
_flex.Tree.Indent = 30;
The outline tree would change as follows:
Notice the buttons labeled "1", "2", and "*" on the top left cell. Clicking these buttons would cause the entire tree to collapse or expand to the corresponding level. Also notice the much wider indentation and the lines connecting the tree to regular rows ("Boston Crab Meat", "Gnocchi di nonna Alice", etc.) as well as to node rows.
Adding Subtotals
So far we've covered the creation of node rows and outline trees. To make the outlines really useful, however, the node rows should include summary information for the data they contain. If you create an outline tree using the Subtotal method, then the subtotals are added automatically. This will be described in a later section. If you created the outline tree using the Rows.InsertNode method as described above, use the Aggregate method to calculate the subtotals for each group of rows and insert the result directly into the node rows.
The Subtotal method listed below shows how to do this:
// add subtotals to each node at a given level
void AddSubtotals(int level, string colName)
{
// get column we are going to total on
int colIndex = _flex.Cols.IndexOf(colName);
// scan rows looking for nodes at the right level
for (int r = \_flex.Rows.Fixed; r < \_flex.Rows.Count; r )
{
if (_flex.Rows[r].IsNode)
{
var node = _flex.Rows[r].Node;
if (node.Level == level)
{
// found a node, calculate the sum of extended price
var range = node.GetCellRange();
var sum = _flex.Aggregate(AggregateEnum.Sum,
range.r1, colIndex, range.r2, colIndex,
AggregateFlags.ExcludeNodes);
// show the sum on the grid
// (will use the column format automatically)
_flex[r, colIndex] = sum;
}
}
}
}
The AddSubtotals method scans the grid rows looking for node rows. When a node row of the desired level is found, the method uses the Node.GetCellRange method to retrieve the node's child rows. Then it uses the Aggregate method to calculate the sum of the values on the target column over the entire range. The call to Aggregate includes the ExcludeNodes flag to avoid double-counting existing nodes. Once the subtotal has been calculated, it is assigned to the node row's cell with the usual _flex[row, col] indexer.
Note that this does not affect the data source in any way, since node rows are not bound to the data. In addition, this method can be used to add multiple totals to each node row. In this example, we'll add totals for the Quantity and Cost columns. In addition to sums, you could add other aggregates such as average, maximum, minimum, etc.
We can use this method to create a complete outline, with node rows, outline tree, and subtotals:
void \_btnTreeCountryCity\_Click(object sender, EventArgs e)
{
using (new DeferRefresh(_flex))
{
// restore original sort (by Country, City, SalesPerson)
ResetBinding();
// group by Country, City
GroupBy("Country", 0); // group by country (level 0)
GroupBy("City", 1); // group by city (level 1)
// add totals per Country, City
AddSubtotals(0, "Cost"); // cost per country (level 0)
AddSubtotals(0, "Quantity"); // quantity per country (level 0)
AddSubtotals(1, "Cost"); // cost per city (level 1)
AddSubtotals(1, "Quantity"); // quantity per city (level 1)
// show outline tree
_flex.Tree.Column = 0;
\_flex.AutoSizeCol(\_flex.Tree.Column);
_flex.Tree.Show(1);
}
}
If you run the project now, you'll see a tree with node rows that show the total quantity and amount sold for each country and city. This is very nice, but there's a little problem: If you expand any of the node rows, you'll see a lot of duplicate values. All rows under a given city node have the same country and city:
This is correct, but it's a waste of screen real estate.
Eliminating these duplicate values is easy: all you have to do is set the Width of the columns that are being grouped on to zero. When you do that, however, remember to set the grid's AllowMerging property to Nodes, so the text assigned to the node rows will spill into the visible columns. (Another option would be to assign the node text to the first visible column, but merging is usually a better solution because it allows you to use longer text for the node rows).
Here's the revised code and the final result:
void \_btnTreeCountryCity\_Click(object sender, EventArgs e)
{
using (new DeferRefresh(_flex))
{
// restore original sort (by Country, City, SalesPerson)
ResetBinding();
// group by Country, City
GroupBy("Country", 0); // group by country (level 0)
GroupBy("City", 1); // group by city (level 1)
// hide columns that we grouped on
// (they only have duplicate values which already appear on the tree nodes)
// (but don't make them invisible, that would also hide the node text)
_flex.Cols["Country"].Width = 0;
_flex.Cols["City"].Width = 0;
// allow node content to spill onto next cell
_flex.AllowMerging = AllowMergingEnum.Nodes;
// add totals per Country, City
AddTotals(0, "Cost"); // cost per country (level 0)
AddTotals(0, "Quantity"); // quantity per country (level 0)
AddTotals(1, "Cost"); // cost per city (level 1)
AddTotals(1, "Quantity"); // quantity per city (level 1)
// show outline tree
_flex.Tree.Column = 0;
\_flex.AutoSizeCol(\_flex.Tree.Column);
_flex.Tree.Show(1);
}
}
The Country and City columns are now invisible, but their values still appear in the node rows. Collapsing the tree shows totals for each country and city.
Using the Subtotal Method
We mentioned earlier that you could also create trees using the C1FlexGrid's Subtotal method. The Subtotal method performs the same tasks as the GroupBy and AddSubtotals methods described above, except it does both things in a single step and is therefore a little more efficient. The code below shows how you can use the Subtotal method to accomplish the same thing we did before, only a little faster and without using any helper methods:
void \_btnTreeCountryCity\_Click(object sender, EventArgs e)
{
using (new DeferRefresh(_flex))
{
// restore original sort (by Country, City, SalesPerson)
ResetBinding();
// group and total by country and city
_flex.Subtotal(AggregateEnum.Sum, 0, "Country", "Cost");
_flex.Subtotal(AggregateEnum.Sum, 0, "Country", "Quantity");
_flex.Subtotal(AggregateEnum.Sum, 1, "City", "Cost");
_flex.Subtotal(AggregateEnum.Sum, 1, "City", "Quantity");
// hide columns that we grouped on
// (they only have duplicate values which already appear on the tree nodes)
// (but don't make them invisible, that would also hide the node text)
_flex.Cols["Country"].Width = 0;
_flex.Cols["City"].Width = 0;
_flex.AllowMerging = AllowMergingEnum.Nodes;
// show outline tree
_flex.Tree.Column = 0;
\_flex.AutoSizeCol(\_flex.Tree.Column);
_flex.Tree.Show(1);
}
}
The Subtotal method is very convenient and flexible. It has a number of overloads that allow you to specify which columns should be grouped on and totaled on by index or by name, whether to include a caption in the node rows that it inserts, how to perform the grouping, and so on. The summary below describes the overloads available:
- Subtotal(AggregateEnum aggType)
This version of the method takes only one aggregate type as a parameter. It is useful only for removing existing subtotals before inserting new ones. In this case, theaggType parameter is set to AggregateEnum.Clear.
- Subtotal(AggregateEnum aggType, int groupBy, int totalOn) Subtotal(AggregateEnum aggType, string groupBy, string totalOn)
These are the most commonly used overloads. The parameters are the type of aggregate to insert and the columns to group by and total on. The columns may be referenced by index or by name. The latter is the one we used in the example above.
- Subtotal(AggregateEnum aggType, int groupBy, int totalOn, string caption) Subtotal(AggregateEnum aggType, string groupBy, string totalOn, string caption)
These overloads add an extra caption parameter. The caption parameter determines the text that is added to the new node rows to identify the value being grouped on. By default, the value being grouped on is shown, so if you are grouping by country, the node rows would show "Argentina", "Belgium", and so on. If you set the caption parameter to a string such as "Country: {0}", then the node rows would show "Country: Argentina" instead.
- Subtotal(AggregateEnum aggType, int groupFrom, int groupTo, int totalOn, string caption) Subtotal(AggregateEnum aggType, string groupFrom, string groupTo, string totalOn, string caption)
These overloads separate the groupBy parameter into two: groupFrom and groupTo. By default, the Subtotal method inserts a node row whenever the value of thegroupBy or any previous column changes. For example, if you a row has the same value in the "City" column as the previous row, but a different value in the "Country" column, then the Subtotal method assumes the rows should be in different groups and inserts a new node row even though the value of the groupBy column is the same. These aggregates let you override that behavior and specify the range of columns that should be considered when identifying a group.
Outline Maintenance
So far we've discussed how to create outlines with trees and totals using the high-level Subtotal method as well as lower-level Rows.InsertNode and Aggregate methods. At this point, it's important to remember that the outline tree is created based on the data, but is not bound to it in any way, and is not automatically maintained when there are changes to the grid or to the data. If the user modifies a value in the "ExtendedPrice" column, for example, the subtotals will not automatically update. If the user sorts the grid, the data will be refreshed and the subtotals will disappear.
There are two common ways to maintain the outlines:
- Prevent the user from making any changes that would invalidate the outline. This is the easiest option. You would set the grid's AllowEditing, AllowDragging, and AllowSorting properties to false and prevent any changes that would affect the outline.
- Update the outline when there are changes to the data or to the grid. You would attach handlers to the grid's AfterDataRefresh, AfterSort, and AfterEdit events and re-generate the outline appropriately.
Option 2 is usually more interesting since it provides a quick and simple tool for dynamic data analysis. This approach is illustrated by the Analyze sample provided with the C1FlexGrid. The sample creates an initial outline and allows users to reorder the columns. When the column order changes, the sample automatically re-sorts the data and re-creates the outline. The user can easily create simple reports showing sales by country, by product, by salesperson, and so on.
Using the Node class
The Node class provides a number of methods and properties that can be used to create and manage outline trees. Many of these methods and properties are based on the standard TreeView object model, so they should be familiar to most developers. To obtain a Node object, you can either: Use the return value of the Rows.InsertNode method:
var node = _flex.Rows.InsertNode(index, level);
Or you can retrieve the node for an existing row using the row's Node property:
var node = _flex.Rows[index].IsNode
? _flex.Rows[index].Node
: null;
Either way, once you have a Node object you can manipulate it using the following properties and methods:
- Level: Gets or sets the node level in the outline tree.
- Data: Gets or sets the value in the cell defined by Node.Row and the Tree.Column.
- Image: Gets or sets the image in the cell defined by Node.Row and the Tree.Column.
- Checked: Gets or sets the check state of the cell defined by Node.Row and the Tree.Column.
- Collapsed/Expanded: Gets or sets the node's collapsed/expanded state.
You can also explore the outline structure using the following methods:
- GetCellRange(): Gets a CellRange object that described the range of rows that belong to this node.
- Children: Gets the number of child nodes under this node.
- Nodes: Gets a node array containing this node's child nodes.
- GetNode: Gets the node that has a given relationship to this node (parent, first child, next sibling, and so on).
The discussion above focused on bound scenarios, where the grid is attached to a data source that provides the data. You can also create trees and outlines in unbound scenarios. Things are actually somewhat simpler in this case, since you can turn any row into a node row by setting its IsNode property to true. If the grid is unbound, it owns all the data that is displayed, and you do things that are not possible when a data source owns the data. For example, you can move nodes around the tree using the Node.Move method as shown by the TreeNode sample provided with the C1FlexGrid. Using nodes in an unbound grid is very similar to using nodes in a regular TreeView control.