Introduction
Developing a Client-Server-Application based on Windows Forms I was searching for a DataGridView component able to display an numeric aggregation of the contents of a column. For example: Summing up the total amount of items in a customer order displayed in one column the Grid. Just like Excel could do. I didn’t found an appropriate solution and so I decided to develop my own component. It should work like the DataGridView with the capability to display a row at the end of the Grid.
For the repositioning and resizing of the SummaryRow I refactored some code which was taken over from the nice Filterable DataGrid from Robert Rhode: gridextensions.aspx. Many thanks to him for his good work.
Getting Started
To use the SummaryDataGridViewTest-Application you have to copy the Nwind.mdb database to the output directory.
Background
A good knowledge of the DataGridView and the Window-Forms library helps in customizing the code but it is not necessary. The use of the SummaryDataGridView is quite simple.
Using the Code
The SummaryDataGridView control can be used like any other control of Windows-Forms and supports design time configuration. It has a set of public properties available in the designer. The use is quite similar to the DataGridView as it is derived from it. To display data, set the DataSource property of the control. Each column that should be summed up must be added to the array of strings SummaryColumns. See Figure 1 and Figure 2 for public properties and how they affect the SummaryDataGridView.
Figure SEQ Figure \* ARABIC 1
Figure SEQ Figure \* ARABIC 2
The SummaryRow
Using one row of the DataGridView as SummaryRow is very tricky and brings some problems. I didn’t find a solution to lock the SummaryRow at the bottom of the grid which is necessary for scrolling For this reason I decided to use a simple control with Textboxes which is displayed under the DataGridView. All the TextBoxes resize the same way the DataGridView does. Furthermore it’s necessary to draw your own Horizontal scrollbar that will be displayed under our SummaryControlContainer instead of using the h-scrollbar of the DataGridView which would be displayed above our SummaryRow. Accordingly a good part of the code is dealing with positioning, resizing and reordering of our SummaryRow. Summing up the values of one row is the easiest part of the control. The following DataGridView events are handled to synchronize the SummaryRow with DataGridView:
ColumnAdded, ColumnRemoved, ColumnStateChanged, ColumnDisplayIndexChanged
For more information about the synchronization have a look at the methods: reCreateSumBoxes() and resizeSumBoxes() of the SummaryControlContainer class.
Glue between SummaryRow and DataGridView
One question is how you can attach the SummaryRow to the DataGridView. The easiest way would be to use a Control and then include the DataGridView and the SummaryRow in it. The inner grid could be made accessible via a public property. I decided to let the DataGridView create its own SummaryRow. To avoid problems with the designer this is only done runtime. After initialization the DataGridView calls the ChangeParent() method. This method removes the DataGridView from its parent, creates a panel at its place then includes the DataGridView and the SummaryRow in the panel. For the TableLayoutPanel we must determine the exact position of the DataGridView before removing it.
private void changeParent()
{
if (!DesignMode && Parent != null)
{
[..]
panel.Bounds = this.Bounds;
panel.BackColor = this.BackgroundColor;
panel.Dock = this.Dock;
[..]
summaryControl.Dock = DockStyle.Bottom;
this.Dock = DockStyle.Fill;
Special handling for TableLayoutPanels
if (this.Parent is TableLayoutPanel)
{
int rowSpan, colSpan;
TableLayoutPanel tlp = this.Parent as TableLayoutPanel;
TableLayoutPanelCellPosition cellPos =
tlp.GetCellPosition(this);
rowSpan = tlp.GetRowSpan(this);
colSpan = tlp.GetColumnSpan(this);
tlp.Controls.Remove(this);
tlp.Controls.Add(panel, cellPos.Column, cellPos.Row);
tlp.SetRowSpan(panel, rowSpan);
tlp.SetColumnSpan(panel, colSpan);
}
else
{
Control parent = this.Parent;
remove DataGridView from ParentControls
parent.Controls.Remove(this);
parent.Controls.Add(panel);
}
summaryControl.Controls.Add(hScrollBar);
hScrollBar.BringToFront();
panel.Controls.Add(this);
panel.Controls.Add(summaryControl);
adjustSumControlToGrid();
adjustScrollbarToSummaryControl();
resizeHScrollBar();
}
}
The ReadOnly TextBox
The main problem in using a standard Windows-Forms TextBox with its ReadOnly property set to true is, that the color of that TextBox changes to Caption (some type of grey) and could not be set to any other color. Usually we want to have our SummaryRow displayed with a white background. That’s the reason why I included my own TextBox. It’s a simple control because no EventHandling is necessary as in a usual TextBox. The TextBox has got an IsSummary property to indicate if it shoud be used for aggregation. Drawing the control is straight forward in the OnPaint event.
protected override void OnPaint(PaintEventArgs e)
{
Rectangle textBounds;
textBounds = new Rectangle(this.ClientRectangle.X + 2, this.ClientRectangle.Y + 2,
this.ClientRectangle.Width - 2, this.ClientRectangle.Height - 2);
using (Pen pen = new Pen(borderColor))
{
e.Graphics.FillRectangle(new SolidBrush(this.BackColor), this.ClientRectangle);
e.Graphics.DrawRectangle(pen, this.ClientRectangle.X, this.ClientRectangle.Y,
this.ClientRectangle.Width - subWidth, this.ClientRectangle.Height - 1);
e.Graphics.DrawString(Text, Font, Brushes.Black, textBounds, format);
}
}
The color of the SummaryRow could be set in the public property SumaryRowBackColor.
Summing it up!
The actual summation of the values is done in the method calcSummaries(). This method is called from the EventHandlers of the [RowsAdded] [RowsRemoved] and [CellValueChanged] Events of the DataGridView. It iterates through every Row of the DataGridView an summarizes the values of Columns defined in SummaryColumns.
History
- May, 10th, 2009:
- Release 1.0
