_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.
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;
}
}
}
Node rows are almost identical to regular rows, except for the following:
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:
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.
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.
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:
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.
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.
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:
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:
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.
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:
You can also explore the outline structure using the following methods:
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.