ASP.NET: Add a total row to a GridView
From Wiki
Summary: An example of how to create a re-usable function to add a total row to a GridView
Need help with ASP.NET? Come and ask a question in our ASP.NET Forum
One common piece of functionality that I've often found myself doing is adding totals to a GridView. Generally, the concept involves using the RowDataBound event, keeping a total of each column and then adding this total to a new label in each column. The problem with this method is that it isn't re-usable since the next GridView you create probably won't have the same columns in it.
To combat this, I've created a function that accepts a GridView as a parameter, loops through it and creates the total row for you. It then returns the GridView so that you can use it on your page.
Let's start by creating a simple page which contains a GridView:
- <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default1.aspx.vb" Inherits="Default1" Debug="true" %>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml" >
- <head runat="server">
- <title>Untitled Page</title>
- </head>
- <body>
- <form id="form1" runat="server">
- <div>
- <asp:GridView ID="GridView1" runat="server">
- </asp:GridView>
- </div>
- </form>
- </body>
- </html>
Now, let's add the code behind for this page that will populate the GridView with some sample data:
- Imports System.Data
- Partial Class Default1
- Inherits System.Web.UI.Page
- Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
- ' Check for a postback
- If Not Page.IsPostBack Then
- ' Bind the Gridview with some sample data
- GridView1.DataSource = GetData()
- GridView1.DataBind()
- End If
- End Sub
- Private Function GetData() As DataTable
- ' Declarations
- Dim dt As New DataTable
- Dim dr As DataRow
- ' Add some columns
- dt.Columns.Add("Column1")
- dt.Columns.Add("Column2")
- dt.Columns.Add("Column3")
- dt.Columns.Add("Column4")
- dt.Columns.Add("Column5")
- dt.Columns.Add("Column6")
- dt.Columns.Add("Column7")
- ' Add some test data
- For i As Integer = 0 To 10
- dr = dt.NewRow
- dr("Column1") = i
- dr("Column2") = "Some Text " & (i * 5)
- dr("Column3") = (i * 7)
- dr("Column4") = "Some More Text " & (i * 10)
- dr("Column5") = (i * 8)
- dr("Column6") = (i * 9)
- dr("Column7") = (i * 10)
- dt.Rows.Add(dr)
- Next
- ' Return the DataTable
- Return dt
- End Function
- End Class
If you run this page, you'll see a simple GridView that has some sample data in it. Some of the columns are numeric, some aren't. There is also no total row.
Now, here's the function I use to loop through the GridView and create the total row. It accepts the GridView as the first parameter, a boolean value to state whether you want the first column to be totalled (the word "Totals:" will be added if not) and an optional CSS class for the total row. The resulting GridView with the total row will then be returned back to the page.
- Private Function AddTotalRowToGridView(ByVal Grid As GridView, ByVal ShowTotalTextInFirstColumn As Boolean, _
- Optional ByVal FooterCSSClass As String = "") As GridView
- ' Declarations
- Dim dtTotals As New System.Data.DataTable
- Dim dr As System.Data.DataRow = dtTotals.NewRow
- Dim intTemp As Integer = 0
- ' Create a column for each of the GridView's Cells
- For iColumn As Integer = 0 To (Grid.Rows(0).Cells.Count - 1)
- dtTotals.Columns.Add("Column" & iColumn)
- Next
- ' Loop through each of the GridView's Rows
- For iRow As Integer = 0 To (Grid.Rows.Count - 1)
- If iRow = 0 AndAlso ShowTotalTextInFirstColumn = True Then
- dr("Column0") = "Totals:"
- Else
- ' Make sure the row type is a DataRow
- If Grid.Rows(iRow).RowType = DataControlRowType.DataRow Then
- ' Loop through each Cell
- For iCurrentColumn As Integer = 0 To (Grid.Rows(0).Cells.Count - 1)
- ' Add the value to the total if it is an Integer
- If Integer.TryParse(Grid.Rows(iRow).Cells(iCurrentColumn).Text, 0) _
- AndAlso Not (iCurrentColumn = 0 And ShowTotalTextInFirstColumn = True) Then
- ' If the current value is null, add the value to the total
- If IsDBNull(dr("Column" & iCurrentColumn)) Then
- dr("Column" & iCurrentColumn) = CInt(Grid.Rows(iRow).Cells(iCurrentColumn).Text)
- Else
- ' If we already have a total, add this value to that total
- intTemp = CInt(dr("Column" & iCurrentColumn))
- intTemp += CInt(Grid.Rows(iRow).Cells(iCurrentColumn).Text)
- dr("Column" & iCurrentColumn) = intTemp
- ' Reset the temp variable
- intTemp = 0
- End If
- End If
- Next
- End If
- End If
- Next
- ' Add the totals row to our totals DataTable
- dtTotals.Rows.Add(dr)
- ' Turn on the footer in the GridView
- Grid.ShowFooter = True
- Grid.FooterRow.Visible = True
- ' Add the totals to the footer row
- For iFooterColumn As Integer = 0 To (Grid.FooterRow.Cells.Count - 1)
- Grid.FooterRow.Cells(iFooterColumn).Text = dtTotals.Rows(0).Item(iFooterColumn).ToString
- Next
- ' Add the CSS class
- If Not String.IsNullOrEmpty(FooterCSSClass) Then
- Grid.FooterRow.CssClass = FooterCSSClass
- End If
- 'Return the Grid
- Return Grid
- End Function
Add this function to your code-behind page and after you've called the DataBind method of the GridView (in the Page Load event), call this function like so:
- ' Add the total row
- GridView1 = AddTotalRowToGridView(GridView1, False, "gridView")
You'll now see a total row with a total for each column that has numeric values in it.
Possible(Enhancements)
- Add an optional parameter for the text to be displayed if the column is not numeric (e.g. "N/A")
- Inherit the GridView control and add this functionality to it
This Hack is part of the ASP.NET Hacks collection
