Post
Ask Question

How to do Calculations in GridView

I've seen many developers in various forums struggling on implementing a simple calculation in GridView. Usually they wanted to calculate the total amount while typing the value into a TextBox control and display the grand total value at the footer. As you may already know, this can be easily done using a server-side approach.

Let's take a quick review on how to implement a server-side approach calculation. Consider that we have this GridView markup below.

The server-side approach

  1. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"  
  2.                              ShowFooter="true">  
  3.   <Columns>  
  4.      <asp:BoundField DataField="ItemDescription" HeaderText="Item"/>  
  5.      <asp:TemplateField HeaderText="Amount">  
  6.        <ItemTemplate>  
  7.          <asp:TextBox ID="TextBox1" runat="server"   
  8.             AutoPostBack="True"  
  9.                        ontextchanged="TextBox1_TextChanged">    
  10.          </asp:TextBox>  
  11.        </ItemTemplate>  
  12.      </asp:TemplateField>  
  13.   </Columns>  
  14. </asp:GridView>  
Now let's populate the grid with a data from the database using the ADO.NET way. Here's the code block below:
  1. private string GetConnectionString(){  
  2.         //Where MYDBConnection is the connetion string that was set up from the web config file  
  3.         return System.Configuration.ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString;  
  4. }  
  5. // Method for Binding the GridView Control  
  6. private void BindGridView(){  
  7.     using (SqlConnection connection = new SqlConnection(GetConnectionString())) {  
  8.                 string sql = "SELECT * FROM YourTable";  
  9.                 using (SqlCommand cmd = new SqlCommand(sql, connection)) {  
  10.                     connection.Open();  
  11.             using(var adapter = new SqlDataAdapter(cmd)){;  
  12.                     adapter.Fill(dt);  
  13.                     if (dt.Rows.Count > 0){  
  14.                         GridView1.DataSource = dt;  
  15.                         GridView1.DataBind();  
  16.                     }  
  17.            }  
  18.                 }  
  19.     }  
  20. }  
  21. //Bind GridView on initial postabck  
  22. protected void Page_Load(object sender, EventArgs e){  
  23.         if (!Page.IsPostBack)  
  24.             BindGridView();   
  25. }  
And here's the code block for calculating the total:
  1. //Calculate the Totals in the TextBox rows  
  2. protected void TextBox1_TextChanged(object sender, EventArgs e){  
  3.         double total = 0;  
  4.         foreach (GridViewRow gvr in GridView1.Rows)  
  5.         {  
  6.             TextBox tb = (TextBox)gvr.Cells[1].FindControl("TextBox1");  
  7.             double sum;  
  8.             if(double.TryParse(tb.Text.Trim(),out sum))  
  9.             {  
  10.                 total += sum;  
  11.             }  
  12.         }  
  13.         //Display  the Totals in the Footer row  
  14.         GridView1.FooterRow.Cells[1].Text = total.ToString();  
  15. }  
That is simple! Running the code will now provide you a grid with enabled total amount calculation. Now there are certain cases that you may be required to implement a client-side calculation for whatever reasons. In this article, we will take a look at how we will implement it.

The client-side approach with JavaScript

To get started let's setup the form. For simplicity let's just setup the form like this:
  1. <asp:gridview ID="GridView1"  runat="server"  ShowFooter="true" AutoGenerateColumns="false">  
  2.         <Columns>  
  3.         <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />  
  4.         <asp:BoundField DataField="Description" HeaderText="Item Description" />  
  5.         <asp:TemplateField HeaderText="Item Price">  
  6.             <ItemTemplate>  
  7.                 <asp:Label ID="LBLPrice" runat="server" Text='<%# Eval("Price","{0:C}") %>'></asp:Label>  
  8.             </ItemTemplate>  
  9.             <FooterTemplate>  
  10.                 <b>Total Qty:</b>  
  11.             </FooterTemplate>  
  12.         </asp:TemplateField>  
  13.         <asp:TemplateField HeaderText="Quantity">  
  14.             <ItemTemplate>  
  15.                 <asp:TextBox ID="TXTQty" runat="server" onkeyup="CalculateTotals();"></asp:TextBox>  
  16.             </ItemTemplate>  
  17.             <FooterTemplate>  
  18.                 <asp:Label ID="LBLQtyTotal" runat="server" Font-Bold="true" ForeColor="Blue" Text="0" ></asp:Label>       
  19.                 <b>Total Amount:</b>  
  20.             </FooterTemplate>  
  21.         </asp:TemplateField>  
  22.         <asp:TemplateField HeaderText="Sub-Total">  
  23.             <ItemTemplate>  
  24.                 <asp:Label ID="LBLSubTotal" runat="server" ForeColor="Green" Text="0.00"></asp:Label>  
  25.             </ItemTemplate>  
  26.             <FooterTemplate>  
  27.                 <asp:Label ID="LBLTotal" runat="server" ForeColor="Green" Font-Bold="true" Text="0.00"></asp:Label>  
  28.             </FooterTemplate>  
  29.         </asp:TemplateField>  
  30.         </Columns>  
  31.     </asp:gridview>  
As you can see, there's really nothing fancy about the markup above. It just contains a standard GridView with BoundFields and TemplateFields on it. Just for the purpose of the demo, I just use a dummy data for populating the GridView. Here's the code block:
  1. public partial class GridCalculation : System.Web.UI.Page  
  2.     {  
  3.         private void BindDummyDataToGrid() {  
  4.   
  5.             DataTable dt = new DataTable();  
  6.             DataRow dr = null;  
  7.   
  8.             dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));  
  9.             dt.Columns.Add(new DataColumn("Description", typeof(string)));  
  10.             dt.Columns.Add(new DataColumn("Price", typeof(string)));  
  11.   
  12.             dr = dt.NewRow();  
  13.             dr["RowNumber"] = 1;  
  14.             dr["Description"] = "Nike";  
  15.             dr["Price"] = "1000";  
  16.             dt.Rows.Add(dr);  
  17.   
  18.             dr = dt.NewRow();  
  19.             dr["RowNumber"] = 2;  
  20.             dr["Description"] = "Converse";  
  21.             dr["Price"] = "800";  
  22.             dt.Rows.Add(dr);  
  23.   
  24.             dr = dt.NewRow();  
  25.             dr["RowNumber"] = 3;  
  26.             dr["Description"] = "Adidas";  
  27.             dr["Price"] = "500";  
  28.             dt.Rows.Add(dr);  
  29.   
  30.             dr = dt.NewRow();  
  31.             dr["RowNumber"] = 4;  
  32.             dr["Description"] = "Reebok";  
  33.             dr["Price"] = "750";  
  34.             dt.Rows.Add(dr);  
  35.             dr = dt.NewRow();  
  36.             dr["RowNumber"] = 5;  
  37.             dr["Description"] = "Vans";  
  38.             dr["Price"] = "1100";  
  39.             dt.Rows.Add(dr);  
  40.             dr = dt.NewRow();  
  41.             dr["RowNumber"] = 6;  
  42.             dr["Description"] = "Fila";  
  43.             dr["Price"] = "200";  
  44.             dt.Rows.Add(dr);  
  45.            //Bind the GridView  
  46.             GridView1.DataSource = dt;  
  47.             GridView1.DataBind();  
  48.         }  
  49.         protected void Page_Load(object sender, EventArgs e) {  
  50.             if (!IsPostBack) {  
  51.                 BindDummyDataToGrid();  
  52.             }  
  53.         }  
  54.     }  
When you run your page it should display something as in the following:


Keep in mind the client-side implementation is way different since you need to deal with the DOM elements to extract the control and you'll need to understand JavaScript syntax that is a bit complex compared to C# with server-side implementation. Now let's go into the “meat” of this article and that is the implementation of the client-side calculation. The main functionality includes the following: 
  • Number validation.
  • Formatting values into readable money format with separators.
  • Calculation for sub-totals and total amount.

Here is the JavaScript code (this should be placed inside the <head> section of your WebForm page):

  1. <script type="text/javascript">  
  2.         
  3.         function CalculateTotals() {  
  4.             var gv = document.getElementById("<%= GridView1.ClientID %>");  
  5.             var tb = gv.getElementsByTagName("input");  
  6.             var lb = gv.getElementsByTagName("span");  
  7.   
  8.             var sub = 0;  
  9.             var total = 0;  
  10.             var indexQ = 1;  
  11.             var indexP = 0;  
  12.             var price = 0;  
  13.   
  14.             for (var i = 0; i < tb.length; i++) {  
  15.                 if (tb[i].type == "text") {  
  16.                     ValidateNumber(tb[i]);  
  17.   
  18.                     price = lb[indexP].innerHTML.replace("$", "").replace(",", "");  
  19.                     sub = parseFloat(price) * parseFloat(tb[i].value);  
  20.                     if (isNaN(sub)) {  
  21.                         lb[i + indexQ].innerHTML = "0.00";  
  22.                         sub = 0;  
  23.                     }  
  24.                     else {  
  25.                         lb[i + indexQ].innerHTML = FormatToMoney(sub, "$", ",", "."); ;  
  26.                     }  
  27.                      
  28.                     indexQ++;  
  29.                     indexP = indexP + 2;  
  30.   
  31.                     total += parseFloat(sub);  
  32.                 }  
  33.             }  
  34.   
  35.             lb[lb.length - 1].innerHTML = FormatToMoney(total, "$", ",", ".");  
  36.         }  
  37.   
  38.         function ValidateNumber(o) {  
  39.             if (o.value.length > 0) {  
  40.                 o.value = o.value.replace(/[^\d]+/g, ''); //Allow only whole numbers  
  41.             }  
  42.         }  
  43.         function isThousands(position) {  
  44.             if (Math.floor(position / 3) * 3 == position) return true;  
  45.             return false;  
  46.         };  
  47.   
  48.         function FormatToMoney(theNumber, theCurrency, theThousands, theDecimal) {  
  49.             var theDecimalDigits = Math.round((theNumber * 100) - (Math.floor(theNumber) * 100));  
  50.             theDecimalDigits = "" + (theDecimalDigits + "0").substring(0, 2);  
  51.             theNumber = "" + Math.floor(theNumber);  
  52.             var theOutput = theCurrency;  
  53.             for (x = 0; x < theNumber.length; x++) {  
  54.                 theOutput += theNumber.substring(x, x + 1);  
  55.                 if (isThousands(theNumber.length - x - 1) && (theNumber.length - x - 1 != 0)) {  
  56.                     theOutput += theThousands;  
  57.                 };  
  58.             };  
  59.             theOutput += theDecimal + theDecimalDigits;  
  60.             return theOutput;  
  61.         }   
  62.     </script>  
Let's try to evaluate each JavaScript function above. The FormatToMoney() is a function that would format numeric values to money by passing the numeric value, the currency, thousands and decimal separators. The isThousand() function evaluates the value and returns Boolean. This function is used within the FormatToMoney() function to determine if the value is on thousand. The ValidateNumber() is a function that validates if the value supplied is a valid number. Finally, the CalculateTotals() is the main function that extracts each elements from the GridView, calculates the values and sets the calculated values back to the GridView element that in this case the sub-total and total amount.

Now call the JavaScript CalculateTotals() function on “onkeyup” or “onkeypress” event like this:
  1. <ItemTemplate>  
  2.      <asp:TextBox ID="TXTQty" runat="server" onkeyup="CalculateTotals();"></asp:TextBox>  
  3. </ItemTemplate>  
Running the page will provide you the following output.

On initial load:


After entering values into the TextBox:


That's it! I hope someone finds this article useful.

 


Similar Articles