How To Get Column Other Total And Grand Total In Gridview[Answered] RSS

2 replies

Last post Jul 13, 2020 01:13 PM by asteranup

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

Gopi.MCA

Member

254 Points

592 Posts

How To Get Column Other Total And Grand Total In Gridview

Jul 13, 2020 06:40 AM|Gopi.MCA|LINK

Hello

This Is My Aspx page

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowCreated="OnRowCreated">
                <Columns>
                    <asp:BoundField DataField="Card_No" HeaderText="Card No" />
                    <asp:BoundField DataField="Day" HeaderText="Day" />
                    <asp:BoundField DataField="Dates" HeaderText="Dates" />
                    <asp:BoundField DataField="ItemName" HeaderText="ItemName" />
                    <asp:BoundField DataField="Qty" HeaderText="Qty" />
                    <asp:BoundField DataField="Value" HeaderText="Value" />
                </Columns>
            </asp:GridView>

This is my code behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Drawing;
using System.IO;

public partial class AAAAAAA : System.Web.UI.Page
{

    private void BindData()
    {
        
        string sql = "select [Card_No],[Day],replace(CONVERT(varchar(50),Dates,106),' ','-') as Dates,ItemName,Qty,Value from AAAAAA order by [Card_No]";
         
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEMIMSConnectionString"].ConnectionString);
            con.Open();
            SqlDataAdapter ad = new SqlDataAdapter(sql, con);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            int rcount = dt.Rows.Count;
            for (int i = 0; i < rcount; i++)
            {
                if (!checkdtRepeat(dt.Rows[i][0].ToString(), dt))
                {
                    DataRow newRow = dt.NewRow();
                    newRow.ItemArray = dt.Rows[i].ItemArray;
                    dt.Rows.Remove(dt.Rows[i]);
                    dt.Rows.InsertAt(newRow, dt.Rows.Count + 1);
                    i -= 1;
                    rcount -= 1;
                }
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {
            BindData();
        }
    }

    string currentNo = "";
    int subTotalQyt = 0;
    int subTotalValue = 0;
    int subTotalRowIndex = 0;
    protected void OnRowCreated(object sender, GridViewRowEventArgs e)
    {
        subTotalQyt = 0;
        subTotalValue = 0;
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
            string CardNo = dt.Rows[e.Row.RowIndex]["Card No"].ToString();
            if (CardNo != currentNo)
            {
                if (e.Row.RowIndex > 0)
                {
                    for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
                    {
                        //if (checkRepeat(GridView1.Rows[i].Cells[0].Text))
                        //{
                        subTotalQyt += Convert.ToInt32(GridView1.Rows[i].Cells[4].Text);
                        subTotalValue += Convert.ToInt32(GridView1.Rows[i].Cells[5].Text);
                        //}
                    }
                    if (checkRepeat(GridView1.Rows[subTotalRowIndex].Cells[0].Text))
                    {
                        this.AddTotalRow("Total", subTotalQyt.ToString(), subTotalValue.ToString());
                    }
                    subTotalRowIndex = e.Row.RowIndex;
                }
                currentNo = CardNo;
            }
        }
    }

    private void AddTotalRow(string labelText, string Qty, string value)
        {
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
            row.Cells.AddRange(new TableCell[6] { new TableCell (), new TableCell (),new TableCell (),
                                        new TableCell() { Text = "<strong>"+labelText+"</strong>"},
                                        new TableCell() { Text = "<strong>"+Qty+"</strong>"},new TableCell() { Text = "<strong>"+value+"</strong>"} });
            GridView1.Controls[0].Controls.Add(row);
        }

    protected bool checkRepeat(string celltext)
    {
        int flag = 0;
        foreach (GridViewRow row in GridView1.Rows)
        {
            string No = row.Cells[0].Text;
            if (No == celltext)
            {
                flag += 1;
            }
        }
        if (flag > 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    protected bool checkdtRepeat(string celltext, DataTable dt)
    {
        int flag = 0;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string No = dt.Rows[i][0].ToString();
            if (No == celltext)
            {
                flag += 1;
            }
        }
        if (flag > 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

This is the output its coming for now

I want Other Total and Final Grand Total Like This below

Thanking You

Sean Fang

Participant

1500 Points

459 Posts

Re: How To Get Column Other Total And Grand Total In Gridview

Jul 13, 2020 10:32 AM|Sean Fang|LINK

Hi Gopi.MCA,

I would not change your original codes too much. Instead, I add some methods to your codes so that it would be much easier to integrate my codes.

However, you might still need to refactor your codes after integration.

For the total rows implementation, you could refer to below codes. The additions are marked in yellow background:

ASPX:

<form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowCreated="OnRowCreated" 
                OnRowDataBound="GridView1_RowDataBound" ShowFooter="true">
                <Columns>
                    <asp:BoundField DataField="Card_No" HeaderText="Card No" />
                    <asp:BoundField DataField="Day" HeaderText="Day" />
                    <asp:BoundField DataField="Dates" HeaderText="Dates" />
                    <asp:BoundField DataField="ItemName" HeaderText="ItemName" />
                    <asp:BoundField DataField="Qty" HeaderText="Qty" />
                    <asp:BoundField DataField="Value" HeaderText="Value" />
                </Columns>
            </asp:GridView>
        </div>
    </form>

Code behind:

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindData();
            }
        }

        private void BindData()
        {

            string sql = "select [Card_No],[Day],replace(CONVERT(varchar(50),Dates,106),' ','-') as Dates,ItemName,Qty,Value from AAAAAA order by [Card_No] ";

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            con.Open();
            SqlDataAdapter ad = new SqlDataAdapter(sql, con);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            int rcount = dt.Rows.Count;

            
            for (int i = 0; i < rcount; i++)
            {
                if (!checkdtRepeat(dt.Rows[i][0].ToString(), dt))
                {
                    DataRow newRow = dt.NewRow();
                    newRow.ItemArray = dt.Rows[i].ItemArray;
                    dt.Rows.Remove(dt.Rows[i]);
                    dt.Rows.InsertAt(newRow, dt.Rows.Count + 1);
                    i -= 1;
                    rcount -= 1;
                }
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();

            AddGrandTotal();



        }

        string currentNo = "";
        int subTotalQyt = 0;
        int subTotalValue = 0;
        int subTotalRowIndex = 0;

        
        protected void OnRowCreated(object sender, GridViewRowEventArgs e)
        {
            subTotalQyt = 0;
            subTotalValue = 0;
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
                string CardNo = dt.Rows[e.Row.RowIndex]["Card_No"].ToString();
                if (CardNo != currentNo)
                {
                    if (e.Row.RowIndex > 0)
                    {
                        for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
                        {
                            //if (checkRepeat(GridView1.Rows[i].Cells[0].Text))
                            //{
                            subTotalQyt += Convert.ToInt32(GridView1.Rows[i].Cells[4].Text);
                            subTotalValue += Convert.ToInt32(GridView1.Rows[i].Cells[5].Text);
                            //}
                        }
                        if (checkRepeat(GridView1.Rows[subTotalRowIndex].Cells[0].Text))
                        {
                            this.AddTotalRow("Total", subTotalQyt.ToString(), subTotalValue.ToString());
                        }
                        subTotalRowIndex = e.Row.RowIndex;
                    }
                    currentNo = CardNo;

                }

                
            } 
        }

        private void AddTotalRow(string labelText, string Qty, string value)
        {
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
            row.Cells.AddRange(new TableCell[6] { new TableCell (), new TableCell (),new TableCell (),
                                        new TableCell() { Text = "<strong>"+labelText+"</strong>"},
                                        new TableCell() { Text = "<strong>"+Qty+"</strong>"},new TableCell() { Text = "<strong>"+value+"</strong>"} });
            GridView1.Controls[0].Controls.Add(row);
        }

        protected bool checkRepeat(string celltext)
        {
            int flag = 0;
            foreach (GridViewRow row in GridView1.Rows)
            {
                string No = row.Cells[0].Text;
                if (No == celltext)
                {
                    flag += 1;
                }
            }
            if (flag > 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        protected bool checkdtRepeat(string celltext, DataTable dt)
        {
            int flag = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string No = dt.Rows[i][0].ToString();
                if (No == celltext)
                {
                    flag += 1;
                }
            }
            if (flag > 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //The other Total is always added after the last row, so call the DataBound event handler 
        protected void AddOthersTotal()
        {
            DataTable dt = (DataTable)GridView1.DataSource;

            int rcount = dt.Rows.Count;
            int othersTotalQyt = 0;
            int othersTotalValue = 0;
            int othersCount = 0;
            for (int i = 0; i < rcount; i++)
            {
                if (!checkdtRepeat(dt.Rows[i][0].ToString(), dt))
                {
                    othersCount++;
                    othersTotalQyt += Convert.ToInt32(dt.Rows[i]["Qty"]);
                    othersTotalValue += Convert.ToInt32(dt.Rows[i]["Value"]);
                }
            }

            if (othersCount != 0)
            {
                AddTotalRow("Others Total", othersTotalQyt.ToString(), othersTotalValue.ToString());
            }
        }

        //Add others total at the last row
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
                // last row
                if (e.Row.RowIndex == dt.Rows.Count - 1)
                {
                    AddOthersTotal();
                }
            }
        }

        private void AddGrandTotal()
        {
            DataTable dt = (DataTable)GridView1.DataSource;

            int grandTotalQyt = 0;
            int grandTotalValue = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //Calculate grand total before make any change
                grandTotalQyt += Convert.ToInt32(dt.Rows[i]["Qty"]);
                grandTotalValue += Convert.ToInt32(dt.Rows[i]["Value"]);
            }

            //Add Grand Total
            GridView1.FooterRow.Cells[3].Text = "<strong style='color: blue'>Grand Total</strong>";
            GridView1.FooterRow.Cells[4].Text = "<strong style = 'color: blue' >"+ grandTotalQyt.ToString() + "</strong>";
            GridView1.FooterRow.Cells[5].Text = "<strong style = 'color: blue' >" + grandTotalValue.ToString() + "</strong>";


        }

Demo:

BTW, I would not do the font-color stuff in codes directly (here is just for displaying the result). Therefore, just ignore the "<style>" in the codes if you mind.

Hope this can help you.

Best regards,

Sean

asteranup

All-Star

20953 Points

4984 Posts

Re: How To Get Column Other Total And Grand Total In Gridview

Jul 13, 2020 01:13 PM|asteranup|LINK

Hi Gopi,

I know you have almost completed the implementation on the same. However, I see that there is HTML dependency on C# code. If you want, you can completely achieve the same with JavaScript. Here is a codebase with jQuery and underscore-

    public class Data
    {
        public string Card_No { get; set; }
        public string Day { get; set; }
        public DateTime Dates { get; set; }
        public string ItemName { get; set; }
        public int Qty { get; set; }
        public int Value { get; set; }
    }
    public partial class _2168936_How_To_Get_Column_Other_Total_And_Grand_Total_In_Gridview : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var data = new List<Data>()
            {
                new Data(){ Card_No="A1", Day="D1", Dates=DateTime.Now, ItemName="Item 1", Qty=2, Value=29 },
                new Data(){ Card_No="A1", Day="D2", Dates=DateTime.Now, ItemName="Item 2", Qty=6, Value=9 },
                new Data(){ Card_No="M1", Day="D1", Dates=DateTime.Now, ItemName="Item 4", Qty=2, Value=39 },
                new Data(){ Card_No="M1", Day="D2", Dates=DateTime.Now, ItemName="Item 4", Qty=4, Value=19 },
                new Data(){ Card_No="M1", Day="D3", Dates=DateTime.Now, ItemName="Item 3", Qty=3, Value=59 },
                new Data(){ Card_No="B1", Day="D1", Dates=DateTime.Now, ItemName="Item", Qty=2, Value=29 },
                new Data(){ Card_No="C1", Day="D1", Dates=DateTime.Now, ItemName="Item 5", Qty=2, Value=39 },
                new Data(){ Card_No="K1", Day="D1", Dates=DateTime.Now, ItemName="Item 3", Qty=2, Value=19 },
            };

            GridView1.DataSource = data;
            GridView1.DataBind();
        }
    }

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.10.2/underscore-min.js" integrity="sha512-HKvDCFVKg8ZPGjecy6on7UECEpE76Y86h3GaE4JMCz+deFWdjcW/tWnh0hCfaBvURvlOa9f5CNVzt7EFkulYbw==" crossorigin="anonymous"></script>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Card_No" HeaderText="Card No" />
                <asp:BoundField DataField="Day" HeaderText="Day" />
                <asp:BoundField DataField="Dates" HeaderText="Date" />
                <asp:BoundField DataField="ItemName" HeaderText="ItemName" />
                <asp:BoundField DataField="Qty" HeaderText="Qty" />
                <asp:BoundField DataField="Value" HeaderText="Value" />
            </Columns>
        </asp:GridView>

        <script>
            function insertRow(table, quantity, value, index, totalString) {
                $('<tr>')
                    .append($('<td colspan=4 align="right">').html(totalString))
                    .append($('<td>').html(quantity))
                    .append($('<td>').html(value))
                    .insertAfter(table.find("tr:nth-child(" + (index) + ")"));
            }

            var group = [];
            var table = $("#GridView1")
            table.find("tr:nth-child(n+2)").each(function (i, item) {
                var card = $(item).find("td:first").text();
                var q = $(item).find("td:nth-child(5)").text();
                var v = $(item).find("td:nth-child(6)").text();

                var index = _.findIndex(group, function (obj) {
                    return obj.card == card
                });
                if (index < 0) {
                    var obj = {};
                    obj.card = card;
                    obj.count = 0;
                    obj.quantity = 0;
                    obj.value = 0;
                    index = group.length;
                    group[index] = obj;
                }
                group[index].lastIndex = i;
                group[index].count++;
                group[index].quantity += parseInt(q, 10);
                group[index].value += parseInt(v, 10);
            });

            var count1 = _.filter(group, function (item) { return item.count == 1 })
            insertRow(table,
                _.reduce(count1, function (sum, item) { return sum + item.quantity; }, 0),
                _.reduce(count1, function (sum, item) { return sum + item.value; }, 0),
                table.find("tr").length,
                'Other total');

            var group1 = _.filter(group, function (item) { return item.count != 1 });
            for (var i = group1.length - 1; i > -1; i--) {
                insertRow(table, group1[i].quantity, group1[i].value, group1[i].lastIndex + 2, 'Total');
            }
            insertRow(table,
                _.reduce(group, function (sum, item) { return sum + item.quantity; }, 0),
                _.reduce(group, function (sum, item) { return sum + item.value; }, 0),
                table.find("tr").length,
                'Grand Total');
        </script>
    </form>
</body>
</html>

The running code is here.

Thanks,
Anup Das Gupta
Visit My Blog
You can also connect with me in LinkedIn

‹ Previous Thread|Next Thread ›