If you would like to hire my services, you can now do so by visiting the following link:
Website Design Darlington
Article Statistics
Code Bank Statistics
Summary: An example of how to create a re-usable function to add a total row to a GridView
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
Posted on 21/03/2007 03:05:14
1. Oddmar 13/04/2007 00:25:27
Couldnt you use DataTable.Compute("Sum(ColumnName)")
2. Mark Smith 13/04/2007 01:23:53
Thanks for your comment. Yes, that's a good suggestion and it is possible, but there are cases when this will fail. If the DataColumn type hasn't been specifically set then it will default to a String and the SUM method will fail. So, if you wanted to implement this method you would have to check the type first. Also if the type is set to a String but actually contains numbers then the Compute method won't count them unless you specifically cast them first. The method I chose actually checks the values so hopefully that won't be a problem.
3. Rick 15/04/2007 16:15:46
Mark, I have rows where people have entered integer values and instead of summing the data I want to count the number of rows in the gridview column where the value is not zero. How would you count a column like this with either integer or bit values?Rc
4. Mark Smith 16/04/2007 00:21:57
Rick, in the section where we sum the values by setting the value of the "intTemp" variable, rather than adding the value of the Cell you'll just need to increase the counter by one.
5. Dana 24/04/2007 11:34:00
I have a column that may contain null values. I have assigned a text value to these columns, and It is throwing off your code on my page. How would you skip text values when computing?
6. Mark Smith 25/04/2007 07:20:51
Dana, if you run the example, you should see that the columns which have text in them are simply not computed. This is done by using the Integer.TryParse method.
7. Daniel Perez 17/05/2007 12:43:44
Hi.I read your article, but I solve the situation easier. I did 2 steps.1. I have the function:public static int CantidadFilas(object afuente){try{int cant=0;if (afuente!=null){ if (afuente is DataSet) { cant= ((DataSet)afuente).Tables[0].Rows.Count; } if (afuente is DataTable) { cant= ((DataTable)afuente).Rows.Count; } if (afuente is DataView) { cant= ((DataView)afuente).Count; }}return cant;}catch{return 0;}}2. I call the function:int cant= CUtilitario.CantidadFilas(DataGrid1.DataSource);What do you think about?bye,Daniel.
8. Mark Smith 19/05/2007 06:50:44
Daniel,I think you have mis-understood the scope of the article. The point isn't to count how many rows are in the GridView, it's to add a total row to the bottom that sums up each column.Thanks,Mark
9. tushar 03/08/2007 09:00:31
nice artical.....
10. Supraj Venkat 14/08/2007 06:41:15
Very nice article, can you please advice what to do when you have paging enabled? It totals the value in the page viewed by the user, but I want it to display the total of all the rows? Thanks in Advance
Please keep your comments relevant to this page. Any inappropriate or purely promotional comments may be removed. Email addresses are never displayed but are required so you can confirm your comments.
