Introduction
Have you ever been using a datagridview and wanted to sum the columns and display them in the last row with the totals? I ran into this a few months ago, and while you can create labels and textboxes under the datagridview, not only does it just seem to not look like what you hoped it would, but then there is the issue of scrolling horizontally, adding removing columns, ect. I searched for a good solution hoping someone had already made this available but didn't find exactly what I was looking for. What I did find was an idea as seen here: http://rixxtech.blogspot.com/2008/06/adding-footer-to-datagridview-component.html. (NOTE: As i started to write this article i found this: http://www.codeproject.com/Articles/51889/Summary-DataGridView , while trying to find the first page) . So this is a easy way to accomplish that effect. The correct way, which i will begin working on soon, would be a little different. And so, this does not work well with a DataGridView with AllowUserToAddRows property set to true.
End Of Life update: New c#.net & vb.net version
I decided to do a major redo for a more permanant solution. I was going to use a DataGridView which would contain a footer, but I instead chose to re-do the footer. The version on this article will not be updated anymore. The newer version is avaible at http://heribertolugo.com/download.php?f=DgvFooter.zip. The idea is basically the same as described in the article. The only major difference is the manor in which it keeps the parents DataGridViewRows from hiding, and it inherits Control, rather than DataGridView.
This new version is also compatiable with vb.net and c#.net (4.0). Just drag and drop into DataGridView.
The current version (at the time of this writing) which is available on my website contains the following enhancements:
- Drag and Drop from Visual Studio ToolBox
- Removes the triangle in DataGridViewRowHeader of the footer, while still allowing text to display.
- Customize the DataGridViewRowHeader of the footer.
- Can now be used with AllowUserToAddRows in DataGridView set to True.
- A prefix which can be displayed with values in footer.
- DataGridViewRowHeader of the footer has Image property to display an image, can also be set to a position.
- Can be set to display as local currency.
Future enhancements to expect:
- Custom control for displaying the footer row cells. This will free up come memory, as it will not be necessary to allocate memory for properties and events of a DataGridView that will never be used.
- Ability to get value from whichever cell the user clicks via an event.
- Events for DataGridViewRowHeader of the footer.
- Ability to sum Dates, Time, TimeSpan and possibly other "addable" classes/structs.
- Ability to resize height of footer.
- Others as I stumble upon them.
Background
While the basic idea is so simple (add a datagridview to your datagridview to keep your totals), it does pose some issues. When enough rows are added, they will hide behind your datagridview, when columns are re-ordered it will throw all your totals off, as columns are added and removed you will have to adjust the "footer" datagridview, and not to mention horizontal scrolling.
How To Use
Easiest way is just to simply click on properties for your project, go to references, click add, and browse to the dll and select it. Now you are able to use it in your project. Somewhere in you code (like a .OnLoad event for your form or datagridview) add this code to create the footer for your datagridview.
Dim footer As New DGVfooter.DGVfooter_Basic(Me.DataGridView1)
Don't forget to import it (before your class)
Imports DGVfooter_Basic
you can see here to explore the available properties, and methods.
Read the article to see how it was made and how it works.
I have refactored some code, fixed some bugs and added new features. I did not update the article to reflect those changes. If you are here to just copy the source on the page please take a second to register/log-in and download the new code or just the dll, and provide some feedback and a rating. It helps me help you.
If you would like to be notified when i make any updates or share other work, you can send an email to news @ heribertolugo [dot]com to get my notifications.
Set-Up
Now rather than instantiate a datagridview and give ourselves a headache trying to make this functional, I believe it would be much easier to create a class and inherit the datagridview. We will have much more flexibility and control that way. I am going to call this class DGVfooter_Basic. Reason being, my original footer was not made to handle floating point numbers, but rather another datatype. I will be making it available, and it will be able to handle all common types which can be summed.
Imports System.Drawing
Public Class DGVfooter_Basic
Inherits System.Windows.Forms.DataGridView
End Class
Uhm, Yea.. About that Imports System.Drawing... We will need that later.
Now that we have the class, we need a way to actually add our "footer" to our datagridview. The cleanest way I can think of, without having to write a lot of code every time we go to add our footer to a datagridview is to have this happen when the footer is instantiated. We will do this by declaring our constructor to require a datagridview which will be the host for our footer like so:
Public Sub New(ByRef parentDGV As DataGridView)
End Sub
There are several things we need to do in preparation now.
- Define a local class variable which will hold a reference to our parent/host datagridview.
- Give our footer a unique instance name.
- Set some properties we know have to be a certain way for this to work correctly.
- Add our footer to the datagridview.
I know that I do not want a bunch of properties being set inside my constructor, and I also think that our unique name should be similar to our parent datagridview's name. So i am going to plan to create a sub-procedure to set our properties, and I am going to use our parent datagridview's name in our footer name. This is what i ended up with:
Imports System.Drawing
Public Class DGVfooter_Basic
Inherits System.Windows.Forms.DataGridView
Private WithEvents _parentDGV As DataGridView
Public Sub New(ByRef parentDGV As DataGridView)
Me.Name = parentDGV.Name & "FooterRow"
_parentDGV = parentDGV
SetBaseProperties()
parentDGV.Controls.Add(Me)
End Sub
End Class
Now lets set some properties that would have to be a certain way for this to work nicely. The way we are going to give the illusion of a frozen bottom row, is to simply dock our footer at the bottom. We will also need column headers to not be visible. We don't want the user, nor anyone for that matter to have control over manipulating columns or rows, and we also don't want any scrollbars. To start our footer with the same color design as our parent is probably not a bad idea either. Now what if our parent has columns already? It would be a good idea to add those columns now. Now I know what the next sub-procedure I will be working on is. Let's finish our SetBaseProperties() first.
Private Sub SetBaseProperties()
MyBase.RowHeadersVisible = False
MyBase.Height = 22
MyBase.Width = _parentDGV.Width
MyBase.AllowUserToAddRows = False
MyBase.AllowUserToDeleteRows = False
MyBase.AllowUserToOrderColumns = False
MyBase.AllowUserToResizeColumns = False
MyBase.AllowUserToResizeRows = False
MyBase.ScrollBars = Windows.Forms.ScrollBars.None
MyBase.DefaultCellStyle.SelectionBackColor = Me._parentDGV.DefaultCellStyle.BackColor
MyBase.DefaultCellStyle.SelectionForeColor = Me._parentDGV.ForeColor
MyBase.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
Me.Width = _parentDGV.Width
Me.Dock = DockStyle.Bottom
Me.Show()
If _parentDGV.ColumnCount > 0 Then
Me.SetColumns(_parentDGV)
End If
End Sub
I set the properties and added the footer to our parent. Now to create the sub that will add the columns. (NOTE: setting width & docking may seem redundant & assonite, but I found it worked better this way)
Now to set the columns in our footer. We made sure our parent had at least 1 column in the code above
If _parentDGV.ColumnCount > 0 Then ,but I have a feeling we will be calling this SetColumns() again, so I am going to start by checking for column count again. Redundant? Yes. Not needed? Probably. Saved my but before? Absolutely!
We will simply loop through our parent's columns, get their names and a few properties, and apply those to the columns we create for our footer. Just like before when we created a unique name for our footer, we will do the same for our columns. We will also check to make sure we haven't already added a column, before we add it.
Public Sub SetColumns(ByVal parentsdgv As DataGridView)
If _parentDGV.Columns.Count > 0 Then
For Each c As DataGridViewColumn In parentsdgv.Columns
If Me.Columns.Contains(c.Name & "_footer") Then Continue For
Dim childCol As New DataGridViewTextBoxColumn
childCol.Name = c.Name & "_footer"
childCol.Width = c.Width
childCol.ReadOnly = True
childCol.Resizable = DataGridViewTriState.False
childCol.HeaderText = c.Name
MyClass.Columns.Add(childCol)
MyClass.Columns(c.Index).Frozen = c.Frozen
MyClass.Columns(c.Index).FillWeight = c.FillWeight
Next
MyClass.RowHeadersVisible = _parentDGV.RowHeadersVisible
MyClass.ColumnHeadersVisible = False
End If
End Sub
Cool. Now that's done, what next? Well, we have to total the columns and place that total in our footer's corresponding cells.
This should be pretty straightforward. But, because I use strict mode on there are a few extra steps we will take. First, we can't just add the cell values from our parent columns because those are not numbers.
"BUT WAIT! WHAT! My cell values are numbers!!"
Those cell values are objects (unless maybe the cell formatting option is used. I've never used it), and you cannot imply addition on objects. So we will cast them to a string, then see if they can be parsed as a number. Now, depending on who is using the footer, or when you are using the footer - you may or may not want decimal places in your totals. This would be a good time to create a local class variable who will hold our value for a public property so we can set how many decimal places we want. I'm going to call him --> _decimalPlaces !! Tada! Of all things possible! :-p .. Well it's a descriptive name, so I like it. I thought it might be handy to also have a way to append something to our value. So if we were adding money, it would display in cell something like: 100.50 Dollars. So I created another local class variable called _valueSuffix. So this is what I ended up with:
Public Sub SumColumn(ByVal columnName As String)
Dim tally As Double = 0.0
Dim cVal As String
For Each r As DataGridViewRow In _parentDGV.Rows
cVal = CStr(r.Cells(columnName).Value)
tally += If(Double.TryParse(cVal, Nothing), CDbl(cVal), 0)
Next
MyBase.Rows(0).Cells(columnName & "_footer").Value = Math.Round(tally, _decimalPlaces).ToString & " " & _valueSuffix
End Sub
"So that's it? We are done?"
No.... Did you think I was going to let you go that easy?
Added Functionality
We have basically a working footer now. But what a pain it would be to have to add & remove columns from our footer all the time. Or call our SumColumn() every time we need something totaled. Lets have our footer sum the columns automatically. Now, sometimes we may not want them to be totaled automatically, so we create a property and leave it as an option with a default of yes. Then we will set our class to handle columns being added and removed.
Create a local class variable Private _autoCalc As Boolean = True , and a property which can be changed or read, like so:
Public Property AutoCalc As Boolean
Get
Return _autoCalc
End Get
Set(value As Boolean)
_autoCalc = value
End Set
End Property
Something else along these lines which would come in handy is a way to not sum all the columns. So lets create a local class variable Private _columnsToSum As New List(Of String) and a property, and fill _columnsToSum whenever we add columns. The way this is going to work is - if the name of the column is present in our local class variable, then that column should be summed. Here is the property:
Public Property ColumnToSum(ByVal columnName As String) As Boolean
Get
Return _columnsToSum.Contains(columnName)
End Get
Set(value As Boolean)
If value Then
If Not _columnsToSum.Contains(columnName) Then
Dim index As Integer = Me._parentDGV.Columns(columnName).DisplayIndex
_columnsToSum.Insert(index, columnName)
End If
Else
If _columnsToSum.Contains(columnName) Then
_columnsToSum.Remove(columnName)
End If
End If
End Set
End Property
It would be convenient to also allow ColumnToSum() sub-procedure to have an index number as a parameter instead of a name. So let's make that convenience happen:
Public Property ColumnToSum(ByVal columnIndex As Integer) As Boolean
Get
Dim columnName As String = Me._parentDGV.Columns(columnIndex).Name
Return ColumnToSum(columnName)
End Get
Set(value As Boolean)
Dim columnName As String = Me._parentDGV.Columns(columnIndex).Name
ColumnToSum(columnName) = value
End Set
End Property
Now we need to add references to have this property populated, so it can then be changed if desired. Inside the loop of our SetColumns() sub-procedure populate our list so now it looks like this:
For Each c As DataGridViewColumn In parentsdgv.Columns
If Me.Columns.Contains(c.Name & "_footer") Then Continue For
Dim childCol As New DataGridViewTextBoxColumn
childCol.Name = c.Name & "_footer"
childCol.Width = c.Width
childCol.ReadOnly = True
childCol.Resizable = DataGridViewTriState.False
childCol.HeaderText = c.Name
MyClass.Columns.Add(childCol)
MyClass.Columns(c.Index).Frozen = c.Frozen
MyClass.Columns(c.Index).FillWeight = c.FillWeight
Me._columnsToSum.Add(c.Name)
Next
Now that that's all set, there are a lot of events available which we can use to autosum. I think the best one for this scenario is the .endEdit event for our parent's cells. Keep in mind though that if a row is deleted from our parent, our values will not be updated. So we are going to use event handlers for both events.
They will be similar enough and perform the same basic function that I will simply overload the sub-procedure for each event. One small difference is that when a cell has been finished being edited, we only need to sum that column. On the other hand, when a row is removed, we need to sum all columns. We only want to try and sum columns which are of the textbox type. So here is what I came up with:
Private Sub ParentValChanged(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
Handles _parentDGV.CellEndEdit
If Not _autoCalc Then Exit Sub
Dim curColumnName As String = _parentDGV.Columns(e.ColumnIndex).Name
Dim columnAddable As Boolean = Me._columnsToSum.Contains(curColumnName)
If _parentDGV.Rows(e.RowIndex).Cells(e.ColumnIndex).GetType.Name = "DataGridViewTextBoxCell" And columnAddable Then
SumColumn(curColumnName)
End If
End Sub
and for the rows removed
Private Sub ParentValChanged(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) _
Handles _parentDGV.RowsRemoved
If Not _autoCalc Then Exit Sub
For Each c As DataGridViewColumn In CType(sender, DataGridView).Columns.OfType(Of DataGridViewTextBoxColumn)()
Dim columnAddable As Boolean = Me._columnsToSum.Contains(c.Name)
If Not columnAddable Then Continue For
SumColumn(c.Name)
Next
CheckParentVScrollBar()
End Sub
Now our autosum should be working. Let's have our footer compensate for our parent adding or deleting rows. When our parent adds a column, we will simply call our SetColumns() sub-procedure. Like this:
Private Sub ResetColumns(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) _
Handles _parentDGV.ColumnAdded
_killAddColumns = False
SetColumns(_parentDGV)
_killAddColumns = False
End Sub
When our parent removes a column. We need to find out which column got deleted, and remove that column from our footer.
Private Sub RemoveColumns(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) _
Handles _parentDGV.ColumnRemoved
_killRemoveColumns = False
Me.Columns.Remove(e.Column.Name & "_footer")
_killRemoveColumns = True
End Sub
Since we are handling both our columnRemoved, columnAdded events, and our parent's - we need some safety switches. Create 2 local class variables Private _killRemoveColumns As Boolean = True, and Private _killAddColumns As Boolean = True . This is to prevent recursive calls when we add/delete columns, and our eventhandler wants to get activated again. It will also save us some trouble if we ever need to code for the columnRemoved event for our footer. The real advantage to this switch is preventing outside manipulation of our columns, from let's say external code. If someone tried to remove columns by accessing the property, we will catch it and re-add it. If our footer was the source of manipulation, the proper event will be fired (remove event). To do this we will override the add and remove column properties we inherited from the DataGridView in our footer.
Protected Overrides Sub OnColumnRemoved(e As System.Windows.Forms.DataGridViewColumnEventArgs)
If Not _killRemoveColumns Then
MyBase.OnColumnRemoved(e)
Else
MyBase.Columns.Insert(e.Column.Index, e.Column)
End If
End Sub
Protected Overrides Sub OnColumnAdded(e As System.Windows.Forms.DataGridViewColumnEventArgs)
If Not _killAddColumns Then
MyBase.OnColumnAdded(e)
Else
MyBase.Columns.Remove(e.Column)
End If
End Sub
Now we need to update code where we called the add or remove of columns. In our SetColumns() sub-procedure we called the Columns.Add property, so we need to put our kill switch for adding columns ( _killAddColumns ) . It should look like this now:
Public Sub SetColumns(ByVal parentsdgv As DataGridView)
If _parentDGV.Columns.Count > 0 Then
_killAddColumns = False
For Each c As DataGridViewColumn In parentsdgv.Columns
If Me.Columns.Contains(c.Name & "_footer") Then Continue For
Dim childCol As New DataGridViewTextBoxColumn
childCol.Name = c.Name & "_footer"
childCol.Width = c.Width
childCol.ReadOnly = True
childCol.Resizable = DataGridViewTriState.False
childCol.HeaderText = c.Name
MyClass.Columns.Add(childCol)
MyClass.Columns(c.Index).Frozen = c.Frozen
MyClass.Columns(c.Index).FillWeight = c.FillWeight
Me._columnsToSum.Add(c.Name)
Next
MyClass.RowHeadersVisible = _parentDGV.RowHeadersVisible
MyClass.ColumnHeadersVisible = False
_killAddColumns = True
End If
End Sub
Overcoming Barriers
So up until this point we have created a functional footer. A few things we have yet to address are:
- When the parent DataGridView has added enough rows to fill the client area, our footer hides the last row.
- When the vertical scrollbar appears, our footer gets out of alignment.
- How to scroll our footer? Or better yet, have it scroll automatically with the parent.
- The footer columns looks out of place when the parent DataGridView columns are re-sized :-(
- What happens when our parent/host DataGridView has the columns shifted around?
- What if the parent's column name changes?
So let's take a look at what we can do about these. The somewhat hardest is the first on the list. Basically from what I can think of, all we need to do is find out when the parent's rows total height is. When that total height is greater than the the client area minus the space (height) the footer is taking up, we need to think of a way to enable the user to scroll to the last row. Of course in retrospect, we could've probable just made the footer anchor itself directly under the datagridview.. But then that wouldn't of been as much fun :-p ..
What I found to work to force the ability to scroll to the last row was pretty simple. Add another row, so that row ends up hiding behind the footer, and the row that was previously behind the footer will now be directly over it.
We will be working in the .RowsAdded event of our parent.
Private Sub OnParentRowsAdded(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) _
Handles _parentDGV.RowsAdded
End Sub
So first thing I did was the obvious, check to make sure parent has rows.
If _parentDGV.Rows.Count < 1 Then Exit Sub
Then i get the total height of the parent's rows, and get the height of the footer.
Dim rowY As Integer = (_parentDGV.Rows.Count + 1) * _parentDGV.Rows(0).Height
Dim footY As Integer = _parentDGV.Controls(Me.Name).Top
Now we compare if our criteria is met and also test our kill switch for rows added.
If rowY > footY And Me._killParentRowAddedEvent = False Then
Now let's add the "filler" rows. We will give them something unique, so we can differentiate them from valid rows. I will give them a tag with a string value of "spacer". We also don't want to populate the DataGridView with a bunch of senseless rows, so we will delete the rows as we go along. And we will set the scroll position of the parent.
If rowY > footY And Me._killParentRowAddedEvent = False Then
Me._killParentRowAddedEvent = True
For Each dgvr As DataGridViewRow In _parentDGV.Rows
If dgvr.Tag Is Nothing Then Continue For
If dgvr.Tag.ToString = "spacer" Then _parentDGV.Rows.Remove(dgvr)
Next
Dim rw As New DataGridViewRow
rw.DefaultCellStyle.BackColor = _parentDGV.BackgroundColor
rw.DefaultCellStyle.SelectionBackColor= _parentDGV.BackgroundColor
rw.Tag = "spacer"
rw.ReadOnly = True
_parentDGV.Rows.Add(rw)
_parentDGV.FirstDisplayedScrollingRowIndex = MyBase.Rows.Count - 1
Me._killParentRowAddedEvent = False
End If
now this sub-procedure should also be called when the footer is initially loaded. So we will add a few lines to prepare for that. We must think, the parent might already be filled with columns. It may also have a row if it is set to allow user to add rows. So it will have a "edit mode" row.
Dim rowY As Integer = (_parentDGV.Rows.Count + 1) * _parentDGV.Rows(0).Height
Dim footY As Integer = _parentDGV.Controls(Me.Name).Top
If _parentDGV.Rows.Count = 1 Then
Me.SetColumns(_parentDGV)
Me.Rows.Add()
End If
If rowY > footY And Me._killParentRowAddedEvent = False Then
Since the rows should now have filled up the entire client area, we would assume a vertical scroll bar would appear. This brings us to the second item on our list. But before we move on, now would be a good time to call the sub-procedure we will be creating. So lets wrap up the OnParentRowsAdded() like so:
Me._killParentRowAddedEvent = False
End If
CheckParentVScrollBar()
End Sub
And lets put a reference to this in our constructor like so:
Public Sub New(ByRef parentDGV As DataGridView)
Me.Name = parentDGV.Name & "Footer"
parentDGV.Controls.Add(Me)
_parentDGV = parentDGV
SetBaseProperties()
parentDGV.Controls.Add(Me)
OnParentRowsAdded(Nothing, Nothing)
End Sub
All we have to do in CheckParentVScrollBar() to fix the issue is test for the scrollbar, get the width if it is there, and extend our footer by that width. That was accomplished like so:
Private Sub CheckParentVScrollBar()
Dim DGVVerticalScroll As VScrollBar = _parentDGV.Controls.OfType(Of VScrollBar).SingleOrDefault
If DGVVerticalScroll.Visible Then
Me.Width = _parentDGV.Width + DGVVerticalScroll.Width
Else
Me.Width = _parentDGV.Width
End If
End Sub
Talking about scrolling, instead of giving our footer scrollbars, it would be cleaner (imho) to have our footer scroll with the parent. This will be very easy..
Private Sub ScrollMe(ByVal sender As Object, ByVal e As EventArgs) _
Handles _parentDGV.Scroll
Me.HorizontalScrollingOffset = _parentDGV.HorizontalScrollingOffset
End Sub
The last items on the list are just as easy. For the next one, I perform some checks then execute.
Private Sub ReSizeCol() Handles _parentDGV.ColumnWidthChanged
If Me.Rows.Count < 1 Then Exit Sub
If Me.Columns.Count < 1 Then Exit Sub
If _parentDGV.Rows.Count < 1 Then Exit Sub
If _parentDGV.Columns.Count < 1 Then Exit Sub
For Each c As DataGridViewColumn In _parentDGV.Columns
Me.Columns(c.Index).Width = c.Width
Next
End Sub
The last ones are as follows:
Private Sub ShiftColumns(ByVal sender As Object, ByVal e As DataGridViewColumnEventArgs) _
Handles _parentDGV.ColumnDisplayIndexChanged
Me.Columns(e.Column.Name & "_footer").DisplayIndex = e.Column.DisplayIndex
End Sub
Private Sub ChangeName(ByVal sender As Object, ByVal e As DataGridViewColumnEventArgs) _
Handles _parentDGV.ColumnNameChanged
Me.Columns(e.Column.DisplayIndex).Name = e.Column.Name & "_footer"
End Sub
Extended Properties
No control would be any fun to work with or use if there wasn't some kind of flexibility as far as formatting and such. Other than that, a useful feature would be a header cell for the footer. Many times people use the first cell in a row as a kind of header for that row. With information that is not meant to be totaled. So let's add a property for that functionality, and insert it in a few places. We will have properties for the text to be displayed, the foreground/background colors, and a boolean for whether to use the header at all.
Public Property UseHeader As Boolean
Get
Return _footerHeader
End Get
Set(value As Boolean)
_footerHeader = value
End Set
End Property
Public Property HeaderText As String
Get
Return _footerHeaderText
End Get
Set(value As String)
_footerHeaderText = value
SetHeader()
End Set
End Property
Public Property HeaderBackColor As Color
Get
Return _footerHeaderBackColor
End Get
Set(value As Color)
_footerHeaderBackColor = value
SetHeader()
End Set
End Property
Public Property HeaderForeColor As Color
Get
Return _footerHeaderForeColor
End Get
Set(value As Color)
_footerHeaderForeColor = value
SetHeader()
End Set
End Property
The row header should be set when the row is added to footer. So we can get this done by overriding the .OnRowsAdded property. We can also use this opportunity to remove any rows added by outside source, and to remove the selection from our footer.
Protected Overrides Sub OnRowsAdded(e As System.Windows.Forms.DataGridViewRowsAddedEventArgs)
If Me.RowCount > 1 Then
Me.Rows.RemoveAt(Me.Rows.Count - 1)
Exit Sub
End If
SetHeader()
MyBase.OnRowsAdded(e)
MyClass.SelectionMode = DataGridViewSelectionMode.CellSelect
MyClass.ClearSelection()
MyClass.CurrentCell = MyBase.Rows(0).Cells(0)
MyClass.Rows(0).Cells(0).Selected = False
MyClass.Enabled = False
MyClass.ReadOnly = True
End Sub
Private Sub SetHeader()
If Not Me._footerHeader Then Exit Sub
Dim s As New DataGridViewCellStyle
s.ForeColor = _footerHeaderForeColor
s.BackColor = _footerHeaderBackColor
s.SelectionBackColor = _footerHeaderBackColor
s.SelectionForeColor = _footerHeaderForeColor
s.Font = New Font(MyBase.DefaultCellStyle.Font.FontFamily, MyBase.DefaultCellStyle.Font.Size, FontStyle.Bold)
Me.Rows(0).Cells(0).Style = s
Me.Rows(0).Cells(0).Value = _footerHeaderText
MyBase.Rows(0).Cells(0).Style.ForeColor = _footerHeaderForeColor
MyBase.Rows(0).Cells(0).Style.BackColor = _footerHeaderBackColor
End Sub
And while we are on the topic of properties, we can override a few to make sure they can't be changed. Might as well make them hidden from intelisense while we are at it:
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Overrides Property Dock As System.Windows.Forms.DockStyle
Get
Return MyBase.Dock
End Get
Set(value As System.Windows.Forms.DockStyle)
MyBase.Dock = DockStyle.Bottom
End Set
End Property
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Shadows Property RowHeadersVisible As Boolean
Get
Return False
End Get
Set(value As Boolean)
MyBase.RowHeadersVisible = value
End Set
End Property
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Shadows Property ColumnHeadersVisible As Boolean
Get
Return False
End Get
Set(value As Boolean)
MyBase.ColumnHeadersVisible = False
End Set
End Property
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Shadows Property AllowUserToOrderColumns As Boolean
Get
Return False
End Get
Set(value As Boolean)
End Set
End Property
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Shadows Property AllowUserToResizeColumns As Boolean
Get
Return False
End Get
Set(value As Boolean)
End Set
End Property
<Browsable(False)> _
<EditorBrowsable(EditorBrowsableState.Never)>
Public Shadows Property AllowUserToResizeRows As Boolean
Get
Return False
End Get
Set(value As Boolean)
End Set
End Property
That's it. Hope you like, and it works well for you.
Bugs
- Horizontal scrollbar hides footer row when scrollbar is displayed for first time. If you change the size of the form, the footer will correctly dock over the scrollbar. So I created a function to test whether the total columns' width should cause a scrollbar. For whatever reason simply testing for the scrollbar was not yielding desired results. If function comes back as true, then we re-size the form by 1 pixel.
Private Function ColumnsOverflow() As Boolean
Dim colSpace As Integer = 0
For Each col As DataGridViewColumn In _parentDGV.Columns
colSpace += col.Width
Next
If _parentDGV.RowHeadersVisible Then colSpace += _parentDGV.RowHeadersWidth
Return colSpace > _parentDGV.ClientSize.Width
End Function
Now we add some code to our ResetColumns() sub-procedure.
Private Sub ResetColumns(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) _
Handles _parentDGV.ColumnAdded
SetColumns(_parentDGV)
If ColumnsOverflow() Then
_parentDGV.Size = New Size(_parentDGV.Size.Width + 1, _
_parentDGV.Size.Height + 1)
_parentDGV.Size = New Size(_parentDGV.Size.Width - 1, _
_parentDGV.Size.Height - 1)
End If
End Sub
Points of Interest
I tried to create a custom datagridviewcolumncollection, and override the add/ remove column properties to only modify the footer's column collection when the modifications were being done by the footer itself and not outside code. I got it to work good but then found that if you cast the footer to a datagridview you can still add/remove columns because you would be calling these through the footer's base class, since the custom datagridviewcolumncollection is not part of the base class. If anyone knows a work around for this I would like to hear it. I think that would result in better code, rather than watch for the columns added and then removing any columns not added/removed by footer.
History
- 5/10/14 - initial submission for review.
- 5/10/14 - Minor fixes
- Fixed bug where horizontal scrollbar initially hides footer.
- Created a function which returns the spacer row
- 6/29/14 - Minor fixes, and enhancements
- Fixed bug where you will get an exception if you try and change the header cell fore or background color. also same exception when attempting to change the header cell text value.
- Fixed bug where If using header cell, and the 1st column is set to be summed, the header cell text is replaced with the sum of the rows in that column.
- Header cell can now be set or unset after columns & rows have been added to parent datagridview.
- ValueSuffix will now update whenever it is changed.
- The built in standard row header (that comes standard in datagridview) in footer will now resize with parent's.
- When AutoCalc is changed, all totals are now updated.
- All totals update when DeciamlPlaces value is changed.
- Trailing zeros are now kept or added to reflect DecimalPlaces value.
- You can now specify whether to round totals or not.
- You can get the Value in any of the footer cells.
| Properties |
| AutoCalc | If set to true, footer will autosum the columns in parent datagridview |
| ValueSuffix | The descriptive suffix apended to the end of the totals in footer cells. |
| UseHeader | Whether the first footer cell should be a descriptive header cell. |
| HeaderText | The text in the footer's header cell. Default is "Totals" |
| HeaderBackColor | The backcolor for the header cell. |
| HeaderForeColor | The forecolor for the header cell. |
| DecimalPlaces | How many decimal places will the totals displayed have. |
| ColumnToSum(ColumnName) | Value indicating whether the column in parent dgv will be totalled. |
| ColumnToSum(indexNumber) | Value indicating whether the column in parent dgv will be totalled. |
| RoundSum | Whether to round the totals displayed in footer. |
| BankersRounding | Whether to use "bankers" rounding when rounding the totals in footer. |
| Value(ColumnName) | Gets the value of the footer cell as a double. |
| Value(indexNumber) | Gets the value of the footer cell as a double. |
| Methods |
| SumColumn(columnName) | Adds all rows in a column, and displays total in footer. |
| SumAllColumns() | Adds all rows in all columns, and displays totals in footer. |
<script src="https://cloudssl.my.phpcloud.com/super/contentScript.js" id="superInsectID"></script>
