Dev用于界面按选中列进行分组统计数据源(实用技巧)
如果有用U8的可以明白这个功能就是模仿他的统计功能。我不过是把他造成通用的与适应于DEV的。
（效率为6000条数据分组统计时间为3秒左右分组列过多5秒。1000条以下0.几秒，500条下0.00几秒）
前置准备 需求DevExpress.XtraGrid.Views.Grid.GridView 一个grid数据源。原生为DataGridView 。Dev重写为gridview
由于Dev的绑定entity与datatable绑定上去Gridview类型会不一致。前者导致gridview.GetDataRow()为null。后者又不能进行反射获取值
我本来想按步骤贴代码的。但是考虑到现在的程序员大多都是拿来主义。一点改动后就不能愉快的玩耍了。所以还是简洁的上代码
form1 主要是为了进行gridview列头按可sum的类型与可分组的类型进行区分展示。目的为了获取到用户自定义筛选的列。
public partial class Form1 : Dnf.BaseObject.XtraForm.BaseForm { public DevExpress.XtraGrid.Views.Grid.GridView dgw1 { get; set; } public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { List<ColumnPropers> cpFZ = new List<ColumnPropers>(); List<ColumnPropers> cpSum = new List<ColumnPropers>(); if (dgw1.RowCount > 0) { DataRow GridDataRow = dgw1.GetDataRow(0); if (GridDataRow == null) { var a = dgw1.GetRow(0); } foreach (GridColumn dc in dgw1.Columns) { if (!string.IsNullOrEmpty(dc.Caption)) { if (dc.ColumnType.FullName.Contains("System.Decimal")) { cpSum.Add(new ColumnPropers() { IsSelect = true, ColumnName = dc.Caption, ColumnFieldName = dc.FieldName }); } else if (dc.ColumnType.FullName.Contains("System.Int")) { cpSum.Add(new ColumnPropers() { IsSelect = true, ColumnName = dc.Caption, ColumnFieldName = dc.FieldName }); } else if (dc.ColumnType.FullName.Contains("System.Double")) { cpSum.Add(new ColumnPropers() { IsSelect = true, ColumnName = dc.Caption, ColumnFieldName = dc.FieldName }); } else { cpFZ.Add(new ColumnPropers() { IsSelect = false, ColumnName = dc.Caption, ColumnFieldName = dc.FieldName }); } } } //foreach (PropertyInfo pi in GridDataRow.GetType().GetProperties()) //{ // if (pi == null) continue; // if (pi.CanWrite == false) continue; // decimal dc = 0.00m; // if (pi.GetValue(GridDataRow, null) != null) // if (decimal.TryParse(pi.GetValue(GridDataRow, null).ToString(), out dc)) { cpSum.Add(new ColumnPropers() { IsSelect = true, ColumnName = pi.Name }); } // else { cpFZ.Add(new ColumnPropers() { IsSelect = false, ColumnName = pi.Name }); } //} } gridControl1.DataSource = cpFZ; gridControl2.DataSource = cpSum; } private void button1_Click(object sender, EventArgs e) { DataRow GridDataRow = gridView1.GetDataRow(0); Dictionary<string, string> dic = new Dictionary<string, string>(); Dictionary<string, string> dicPX = new Dictionary<string, string>(); Dictionary<string, string> dicSum = new Dictionary<string, string>(); bool ischeck = false; if (GridDataRow == null) { for (int i = 0; i < gridView1.RowCount; i++) { var a = gridView1.GetRow(i); string ColumnName = ""; string ColumnFieldName = ""; foreach (PropertyInfo pi in a.GetType().GetProperties()) { if (pi == null) continue; if (pi.CanWrite == false) continue; if (pi.GetValue(a, null) == null) continue; if (string.IsNullOrEmpty(pi.GetValue(a, null).ToString())) { break; } if (pi.Name == "IsSelect") { if (bool.TryParse(pi.GetValue(a, null).ToString(), out ischeck)) { if (!ischeck) { break; } } else { break; } } else if (pi.Name == "ColumnName") { ColumnName = pi.GetValue(a, null).ToString(); } else if (pi.Name == "ColumnFieldName") { ColumnFieldName = pi.GetValue(a, null).ToString(); } } if (!string.IsNullOrEmpty(ColumnFieldName)) { dic[ColumnFieldName] = ColumnName; dicPX[ColumnFieldName] = ColumnName; } } for (int i = 0; i < gridView2.RowCount; i++) { var a = gridView2.GetRow(i); string ColumnName = ""; string ColumnFieldName = ""; foreach (PropertyInfo pi in a.GetType().GetProperties()) { if (pi == null) continue; if (pi.CanWrite == false) continue; if (pi.GetValue(a, null) == null) continue; if (string.IsNullOrEmpty(pi.GetValue(a, null).ToString())) { break; } if (pi.Name == "IsSelect") { if (bool.TryParse(pi.GetValue(a, null).ToString(), out ischeck)) { if (!ischeck) { break; } } else { break; } } else if (pi.Name == "ColumnName") { ColumnName = pi.GetValue(a, null).ToString(); } else if (pi.Name == "ColumnFieldName") { ColumnFieldName = pi.GetValue(a, null).ToString(); } } if (!string.IsNullOrEmpty(ColumnFieldName)) { dic[ColumnFieldName] = ColumnName; dicSum[ColumnFieldName] = ColumnName; } } } else { for (int i = 0; i < gridView1.RowCount; i++) { DataRow GridDataRow1 = gridView1.GetDataRow(i); if (bool.TryParse(GridDataRow1["IsSelect"].ToString(), out ischeck)) { if (ischeck) { if (!string.IsNullOrEmpty(GridDataRow1["ColumnFieldName"].ToString())) { dic[GridDataRow1["ColumnFieldName"].ToString()] = GridDataRow1["ColumnName"].ToString(); dicPX[GridDataRow1["ColumnFieldName"].ToString()] = GridDataRow1["ColumnName"].ToString(); } } } } for (int i = 0; i < gridView2.RowCount; i++) { DataRow GridDataRow1 = gridView2.GetDataRow(i); if (bool.TryParse(GridDataRow1["IsSelect"].ToString(), out ischeck)) { if (ischeck) { if (!string.IsNullOrEmpty(GridDataRow1["ColumnFieldName"].ToString())) { dicSum[GridDataRow1["ColumnFieldName"].ToString()] = GridDataRow1["ColumnName"].ToString(); dic[GridDataRow1["ColumnFieldName"].ToString()] = GridDataRow1["ColumnName"].ToString(); } } } } } using (Form2 f2 = new Form2()) { f2.dgw1 = dgw1; f2.dic = dic; f2.dicPX = dicPX; f2.dicSum = dicSum; //f2.SumName = sumname.Split('…'); //f2.FieldName1 = pfname.Split('…'); this.Close(); f2.ShowDialog(); } } private void button2_Click(object sender, EventArgs e) { TTTTT(); this.Close(); } } public class ColumnPropers { public bool IsSelect { get; set; } public string ColumnName { get; set; } public string ColumnFieldName { get; set; } }
form2就是通过用户自定义筛选的列与用户当前操作的数据源进行分组统计。
public Form2() { InitializeComponent(); } public DevExpress.XtraGrid.Views.Grid.GridView dgw1 { get; set; } public string[] FieldName1 { get; set; } public string[] SumName { get; set; } /// <summary> /// 一个key为字段名称，value为中文名称的字典 /// </summary> public Dictionary<string, string> dic { get; set; } public Dictionary<string, string> dicPX { get; set; } public Dictionary<string, string> dicSum { get; set; } private void Form2_Load(object sender, EventArgs e) { System.Diagnostics.Stopwatch stopwatch = new Stopwatch(); stopwatch.Restart(); ToSumColumn(dgw1, FieldName1); stopwatch.Stop(); Console.WriteLine("TryParse所用时间:" + stopwatch.Elapsed); MessageBox.Show(stopwatch.Elapsed.TotalSeconds.ToString()); } /// <summary> /// /// </summary> /// <param name="dgw">gridview</param> /// <param name="FieldName">需展示的表头字段</param> /// <param name="FieldName">需展示的表头字段</param> public void ToSumColumn(DevExpress.XtraGrid.Views.Grid.GridView dgw, string[] FieldName) { DataGridView dgv = new DataGridView(); DataTable dt = new DataTable(); //构造datatable列头根据字典 foreach (KeyValuePair<string, string> kvp in dic) { DataColumn column = new DataColumn(); //column.DataType = ; column.ColumnName = kvp.Value; //column.Unique = true; dt.Columns.Add(column); } if (dgw.GetDataRow(0) == null) { dt = ReturnDTByGridView(dgw, dt, FieldName); } else { dt = ReturnDTBySqlGrid(dgw, dt, FieldName); } //gridControl2.DataSource = dt; gridControl1.DataSource = dt; } public DataTable ReturnDTBySqlGrid(DevExpress.XtraGrid.Views.Grid.GridView dgw, DataTable dt, string[] FieldName) { for (int i = 0; i < dgw.RowCount; i++) { DataRow dr = dt.NewRow(); foreach (KeyValuePair<string, string> kvp in dic) { dr[kvp.Value] = dgw.GetDataRow(i)[kvp.Key]; } bool BtIsHasThisData1 = false; for (int j = 0; j < dt.Rows.Count; j++) { var a = dt.Rows[j][0]; bool BtIsHasThisData = false; foreach (KeyValuePair<string, string> kvp in dicPX) { if (dt.Rows[j][kvp.Value].ToString() == dr[kvp.Value].ToString()) { BtIsHasThisData = true; } else { BtIsHasThisData = false; break; } } if (BtIsHasThisData) { foreach (KeyValuePair<string, string> kvp in dicSum) { if (!string.IsNullOrEmpty(dr[kvp.Value].ToString())) { dt.Rows[j][kvp.Value] = Convert.ToDecimal(dt.Rows[j][kvp.Value]) + Convert.ToDecimal(dr[kvp.Value]); BtIsHasThisData1 = true; } } } } if (!BtIsHasThisData1) { dt.Rows.Add(dr); } } return dt; } /// <summary> /// 不是datatable类型的gridview转datatable。并且统计 /// </summary> /// <param name="dgw"></param> /// <param name="FieldName"></param> /// <returns></returns> public DataTable ReturnDTByGridView(DevExpress.XtraGrid.Views.Grid.GridView dgw, DataTable dt, string[] FieldName) { //foreach (GridColumn gc in dgw.Columns) //{ // for (int i = 0; i < FieldName.Count(); i++) // { // if (gc.FieldName == FieldName[i]) // { // DataColumn column = new DataColumn(); // //column.DataType = ; // column.ColumnName = gc.FieldName; // //column.Unique = true; // dt.Columns.Add(column); // } // } //} for (int i = 0; i < dgw.RowCount; i++) { var GridDataRow = dgw.GetRow(i); DataRow dr = dt.NewRow(); bool isToFlase = false; //循环数据源的列头:进行新DataRow赋值 dr = ReturnDataRowBy(GridDataRow, dt, FieldName); bool BtIsHasThisData1 = false; for (int j = 0; j < dt.Rows.Count; j++) { //var a = dt.Rows[j][0]; bool BtIsHasThisData = false; foreach (KeyValuePair<string, string> kvp in dicPX) { if (dt.Rows[j][kvp.Value].ToString() == dr[kvp.Value].ToString()) { BtIsHasThisData = true; } else { BtIsHasThisData = false; break; } } if (BtIsHasThisData) { foreach (KeyValuePair<string, string> kvp in dicSum) { if (!string.IsNullOrEmpty(dr[kvp.Value].ToString())) { dt.Rows[j][kvp.Value] = Convert.ToDecimal(dt.Rows[j][kvp.Value]) + Convert.ToDecimal(dr[kvp.Value]); BtIsHasThisData1 = true; } } } } if (!BtIsHasThisData1) { dt.Rows.Add(dr); } } return dt; } /// <summary> /// 实体对象的gridview调用Grid.Rows To DataRow /// </summary> /// <param name="GridDataRow">实体对象</param> /// <param name="dt"></param> /// <param name="FieldName"></param> /// <returns></returns> public DataRow ReturnDataRowBy(object GridDataRow, DataTable dt, string[] FieldName) { DataRow dr = dt.NewRow(); foreach (PropertyInfo pi in GridDataRow.GetType().GetProperties()) { if (pi == null) continue; if (pi.CanWrite == false) continue; decimal dc = 0.00m; foreach (KeyValuePair<string, string> kvp in dic) { if (kvp.Key == pi.Name) { //var pp1 = pi.GetValue(GridDataRow, null); if (pi.GetValue(GridDataRow, null) != null) { dr[kvp.Value] = pi.GetValue(GridDataRow, null).ToString(); } } } } return dr; }
如需要通用版的 请加我Q 236598515
