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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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>
Member
294 Points
679 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
This is my code behind
This is the output its coming for now
I want Other Total and Final Grand Total Like This below
Thanking You
Contributor
2900 Points
852 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:
Code behind:
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
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-
The running code is here.
Anup Das Gupta
Visit My Blog
You can also connect with me in LinkedIn