datagridview 分组小计
如何实现datagridview 分组小计合计
[解决办法]
１，每行数据相加
２，sql语句进行合计
[解决办法]
SQL吧
[解决办法]
public static void 小计(DataGridView ucgrd, string col) { for (int i = 0; i < ucgrd.Columns.Count; i++) { ucgrd.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; } //清除grd背景色 for (int ii = 0; ii < ucgrd.Rows.Count; ii++) { ucgrd.Rows[ii].DefaultCellStyle.BackColor = Color.White; } //总计 string[] str小计 = clsString.Split空格(col); ucgrd.RowCount = ucgrd.RowCount + 1; //在ucgrd.RowCount-1行插入总计行 for (int i = 0; i < str小计.Length - 1; i++) { int 列 = clsC.Cint(str小计[i]); //遍历整个表,相加得总计和 for (int 行 = 0; 行 < ucgrd.RowCount - 1; 行++) { double 数量1 = 0, 数量2 = 0; if (ucgrd.Rows[ucgrd.RowCount - 1].Cells[列].Value != null) { 数量1 = clsC.Cdbl(ucgrd.Rows[ucgrd.RowCount - 1].Cells[列].Value.ToString()); } if (ucgrd.Rows[行].Cells[列].Value != null) { 数量2 = clsC.Cdbl(ucgrd.Rows[行].Cells[列].Value.ToString()); } ucgrd.Rows[ucgrd.RowCount - 1].Cells[列].Value = 数量1 + 数量2; } } ucgrd.Rows[ucgrd.RowCount - 1].Cells[0].Value = ""; ucgrd.Rows[ucgrd.RowCount - 1].Cells[1].Value = "总计"; //颜色 ucgrd.Rows[ucgrd.RowCount - 1].DefaultCellStyle.BackColor = Color.YellowGreen; //小计 int 汇总位, J = 0; 汇总位 = 0; while (1 == 1) { //如果到了列表的末尾，就不要小计了 if (J == ucgrd.RowCount - 1) { break; } //判断小计是否一类 if (ucgrd.Rows[J + 1].Cells[0].Value != null && ucgrd.Rows[J].Cells[0].Value != null) { //不相等就执行,否则跳过,直到同一类的完再执行小计 if (!ucgrd.Rows[J].Cells[0].Value.ToString().Equals(ucgrd.Rows[J + 1].Cells[0].Value.ToString())) { ucgrd.Rows.Insert(J + 1); for (int i = 0; i < str小计.Length - 1; i++) { int 列 = clsC.Cint(str小计[i]); for (int 行 = 汇总位; 行 < J + 1; 行++) { double 数量3 = 0, 数量4 = 0; if (ucgrd.Rows[行].Cells[列].Value != null) { 数量3 = clsC.Cdbl(ucgrd.Rows[行].Cells[列].Value.ToString()); } if (ucgrd.Rows[J + 1].Cells[列].Value != null) { 数量4 = clsC.Cdbl(ucgrd.Rows[J + 1].Cells[列].Value.ToString()); } ucgrd.Rows[J + 1].Cells[列].Value = 数量3 + 数量4; } } ucgrd.Rows[J + 1].Cells[0].Value = ""; ucgrd.Rows[J + 1].Cells[1].Value = "小计"; //颜色 //ucgrd.Rows[J + 1].DefaultCellStyle.BackColor = Color.Cyan; ucgrd.Rows[J + 1].DefaultCellStyle.BackColor = Color.PaleGoldenrod; 汇总位 = J + 2; J = J + 1; //跳一行小计的那一行就不比较了 } } J = J + 1; } }
[解决办法]
3楼方法可以试一下
[解决办法]
按什么分组似乎不够明确
[解决办法]
。。。。
3楼猛！~
[解决办法]
//cs代码using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.IO;using System.Text;namespace c4{////// WebForm6 的摘要说明。///public class WebForm6 : System.Web.UI.Page{protected System.Web.UI.WebControls.DataGrid grid;protected System.Web.UI.WebControls.DropDownList ddYears;protected System.Web.UI.WebControls.LinkButton Linkbutton1;protected System.Web.UI.WebControls.Label lblMsg;private void Page_Load(object sender, System.EventArgs e){if (!IsPostBack){// Load data and refresh the viewDataFromSourceToMemory("MyDataSet");UpdateDataView();}}// DataFromSourceToMemoryprivate void DataFromSourceToMemory(String strDataSessionName){// Gets rows from the data sourceDataSet oDS = PhysicalDataRead();// Stores it in the session cacheSession[strDataSessionName] = oDS;}// PhysicalDataReadprivate DataSet PhysicalDataRead(){String strCnn = "server=localhost;initial catalog=northwind;uid=sa;";SqlConnection conn = new SqlConnection(strCnn);// Command text using WITH ROLLUPStringBuilder sb = new StringBuilder("");sb.Append("SELECT ");sb.Append(" CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END AS MyCustomerID, ");sb.Append(" CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID, ");sb.Append(" SUM(od.quantity*od.unitprice) AS price ");sb.Append("FROM Orders o, [Order Details] od ");sb.Append("WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid ");sb.Append("GROUP BY o.customerid, od.orderid WITH ROLLUP ");sb.Append("ORDER BY o.customerid, price");String strCmd = sb.ToString();sb = null;SqlCommand cmd = new SqlCommand();cmd.CommandText = strCmd;cmd.Connection = conn;SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = cmd;// Set the "year" parameterSqlParameter p1 = new SqlParameter("@TheYear", SqlDbType.Int);p1.Direction = ParameterDirection.Input;p1.Value = Convert.ToInt32(ddYears.SelectedItem.Text);cmd.Parameters.Add(p1);// The DataSet contains two tables: Orders and Orders1.// The latter is renamed to "OrdersSummary" and the two will be put into// relation on the CustomerID field.DataSet ds = new DataSet();da.Fill(ds, "Orders");return ds;}// Refresh the UIprivate void UpdateDataView(){// Retrieves the dataDataSet ds = (DataSet) Session["MyDataSet"];DataView dv = ds.Tables["Orders"].DefaultView;// Re-bind datagrid.DataSource = dv;grid.DataBind();}#region Web 窗体设计器生成的代码override protected void OnInit(EventArgs e){//// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。//InitializeComponent();base.OnInit(e);}////// 设计器支持所需的方法 - 不要使用代码编辑器修改/// 此方法的内容。///private void InitializeComponent(){this.grid.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemCreated);this.grid.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.grid_PageIndexChanged);this.grid.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemDataBound);this.Load += new System.EventHandler(this.Page_Load);}#endregionprivate void grid_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e){// Get the newly created itemListItemType itemType = e.Item.ItemType;///////////////////////////////////////////////////////////////////// ITEM and ALTERNATINGITEMif (itemType == ListItemType.Item || itemType == ListItemType.AlternatingItem){DataRowView drv = (DataRowView) e.Item.DataItem;if (drv != null){// Check here the app-specific way to detect whether the// current row is a summary rowif ((int) drv["MyOrderID"] == -1){// Modify the row layout as needed. In this case,// + change the background color to white// + Group the first two cells and display company name and #orders// + Display the total of orders// Graphical manipulations can be done here. Manipulations that require// data access should be done hooking ItemDataBound. They can be done// in ItemCreated only for templated columns.e.Item.BackColor = Color.White;e.Item.Font.Bold = true;e.Item.Cells.RemoveAt(1); // remove the order # celle.Item.Cells[0].ColumnSpan = 2; // span the custID celle.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;}}}}private void grid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e){grid.CurrentPageIndex = e.NewPageIndex;UpdateDataView();}private void grid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e){// Retrieve the data linked through the relation// Given the structure of the data ONLY ONE row is retrievedDataRowView drv = (DataRowView) e.Item.DataItem;if (drv == null)return;// Check here the app-specific way to detect whether the// current row is a summary rowif ((int) drv["MyOrderID"] == -1){if (drv["MyCustomerID"].ToString() == "(Total)"){e.Item.BackColor = Color.Yellow;e.Item.Cells[0].Text = "订单总计";}elsee.Item.Cells[0].Text = "客户小计：";}}public void OnLoadYear(Object sender, EventArgs e){DataFromSourceToMemory("MyDataSet");UpdateDataView();}}}
[解决办法]
protected void GridView4_DataBound(object sender, EventArgs e) { //遍历行 int i, j, k = 0; int sum; double money; Label3.Text = ""; if (GridView4.Rows.Count > 1) { double[,] mon = new double[GridView4.Rows.Count + 1, GridView4.Rows[0].Cells.Count]; for (i = 0; i < GridView4.Rows.Count; i++) { //遍历行中所有单元格 for (j = GridView4.Rows[i].Cells.Count - 2; j >= 5; j--) { sum = 0; money = 0; //计算当前使用的商品数 for (k = 5; k <= j; k++) { sum += Convert.ToInt32(GridView4.Rows[i].Cells[k].Text.ToString()); } GridView4.Rows[i].Cells[j].Text = sum.ToString(); //计算每种物品每天的金额 money = money + (Convert.ToDouble(GridView4.Rows[i].Cells[2].Text)) * (Convert.ToDouble(GridView4.Rows[i].Cells[4].Text)) * sum; // GridView4.Rows[i].Cells[j].Text = money.ToString(); //存储第i，j个格中的数据 mon[i, j] = money; //Label3.Text =Label3.Text+ mon[i, j]+"</br>"; //添加合计行 mon[GridView4.Rows.Count, j] = mon[i, j] + mon[GridView4.Rows.Count, j]; //页脚显示每天金额 GridView4.FooterRow.Cells[j].Text = (mon[GridView4.Rows.Count, j]).ToString(); GridView4.FooterRow.Cells[0].Text = "当天金额合计"; } } double allsum = 0; for (int m = 5; m < GridView4.Rows[0].Cells.Count - 1; ) { DateTime yougnerday; DateTime elderday; //判断是否为最后一个日期列 if (m + 1 != GridView4.Rows[0].Cells.Count - 1) { elderday = Convert.ToDateTime(GridView4.HeaderRow.Cells[m + 1].Text); yougnerday = Convert.ToDateTime(GridView4.HeaderRow.Cells[m].Text); TimeSpan mu = elderday - yougnerday; double all = (Convert.ToInt32(mu.Days)) * (Convert.ToDouble(GridView4.FooterRow.Cells[m].Text)); if (mu.Days > 1) { Label3.Text = Label3.Text + "从" + yougnerday.ToString("yy-MM-dd") + "到" + elderday.Date.ToString("yy-MM-dd") + "共" + mu.Days + "天,共<b>" + all + "</b><br/>"; } else { Label3.Text = yougnerday.ToString("yy-MM-dd") + "一天，共计<b>" + all.ToString() + "</b></br>" + Label3.Text; } //总金额 allsum = all + allsum; } //是最后一个日期列时 else if (m + 1 ==(GridView4.Rows[0].Cells.Count - 1)) { allsum = allsum + Convert.ToDouble(GridView4.FooterRow.Cells[GridView4.Rows[0].Cells.Count - 2].Text); GridView4.FooterRow.Cells[GridView4.Rows[0].Cells.Count - 1].Text = allsum.ToString(); } m++; } Label3.Text = Label3.Text + "<br/>总金额：<b>" + allsum + "</b>"; } }
[解决办法]
GROUPING 不错 以前我都不知道有他
[解决办法]
方法1：在数据库中合计
方法2：在绑定数据时做合计