Wednesday 28 March 2007

ASP.NET - Totals on a GridView

So you have lots of numeric data on a GridView and you would like to show totals in the footer? Here's how:

First of all, place the following at the top of the .vb file, under the Imports ...:

Dim MyTotal(100) as Decimal


Secondly, place the following within the GridView1.RowDataBound event:

On Error Resume Next
Dim i

If e.Row.RowType = DataControlRowType.DataRow Then
For i = 0 To e.Row.Cells.Count - 1
mytotal(i) += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, gv1.HeaderRow.Cells(i).Text))
Next
ElseIf e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(0).Text = "Totals:"

For i = 1 To e.Row.Cells.Count - 1
If mytotal(i) <> 0 Then
' for the Footer, display the running totals
e.Row.Cells(i).Text = mytotal(i).ToString("f")
e.Row.Cells(i).HorizontalAlign = HorizontalAlign.Right
End If
Next
e.Row.Font.Bold = True
End If


But my GridView is sortable and it won't work! In this case, use the following:

On Error Resume Next
Dim i
If e.Row.RowType = DataControlRowType.DataRow Then
For i = 0 To e.Row.Cells.Count - 1
Dim lnkBtnText As String = CType(GridView1.HeaderRow.Cells(i).Controls(0), LinkButton).Text
MyTotal(i) += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, lnkBtnText))
If MyTotal(i) = "" Then
MyTotal(i) += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, GridView1.HeaderRow.Cells(i).Text))
End If

Next
End If

If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(0).Text = "Totals"
For i = 1 To e.Row.Cells.Count - 1
If MyTotal(i) <> 0 Then
' for the Footer, display the running totals
e.Row.Cells(i).Text = MyTotal(i).ToString("f")
e.Row.Cells(i).HorizontalAlign = HorizontalAlign.Right
End If
Next
e.Row.Font.Bold = True
End If
End Sub