实现方法，第一，相同的列名称合并的功能，很简单，设置要合并的列的列属性AllowMerge=true，并不总的GridView的AllowMerge设为true即可。
第二，主要难点在怎么实现汇总的功能。数据库中的获取的数据如下图所示：
数据说明：
PERSONID, //人员ID
PERSONNAME, //人员姓名 READTICKETCOUNT, //审核步数 EXECUTETICKETCOUNT, //操作步数 LOOKUPTICKETCOUNT, //监护步数 DUTYTICKETCOUNT, //值班负责步数 TOTALTICKETCOUNT, //总计 WRITETICKETCOUNT //拟票步数
既然要显示电业局，变电工区，运维站，变电所，就要在数据表中添加对应的列，添加代码如下：
string[] columnNames = { DbFiledName.STATIONID.ToString() ,//电业局ID和名称 DbFiledName.STATION.ToString(), DbFiledName.YWZ.ToString(), //变电工区ID和名称 DbFiledName.YWZID.ToString(), DbFiledName.BDGQ.ToString(), //运维站ID和名称 DbFiledName.BDGQID.ToString(), DbFiledName.DYJ.ToString(), //变电所ID和名称 DbFiledName.DYJID.ToString() }; _commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable);
_commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable)函数如下所示：
public void AddColumnToDataTable(IEnumerable<string> columnNameList, List<string> primaryKey ,ref DataTable dtTable) { if (ReferenceEquals(columnNameList, null)) { return; } foreach (var columnName in columnNameList) { DataColumn dcColumn = new DataColumn(columnName, typeof(string)); dtTable.Columns.Add(dcColumn); } if (ReferenceEquals(primaryKey,null)||primaryKey.Count<=0) { return; } DataColumn[] columns=new DataColumn[primaryKey.Count]; for (int i = 0; i < primaryKey.Count; i++) { columns[i]=new DataColumn(primaryKey[i],typeof(string)); } dtTable.PrimaryKey = columns; //要设置主键，才能合并。 }
既然列已经添加就要向列中添加数据，怎么获取数据呢？ 在构造选择树的时候，已经为树中的节点传入了必要的数据。树的显示图和传入数据为：
TreeListNode parentNode = tree.AppendNode(new object[] {
row["OrganizationId"], row["OrganizationName"], row["ParentId"], row["DeptClass"], row["Buro"],
"Organization", CheckState.Unchecked }, rootNode);
既然知道了树的节点中有需要的信息，那就遍历树，获取电业局，变电工区，运维站，变电所。
foreach (var personId in personIdList) { DataRow dr = dtTable.Rows.Find(personId); //用户 TreeListNode childNode = this.comboxOrgANdUserTree1.treeListOrganization.FindNodeByFieldValue(DbFiledName.PARENTID.ToString(), personId); if (childNode != null) { TreeListNode parentTreeListNode = childNode.ParentNode; //父节点，变电所 if (!ReferenceEquals(parentTreeListNode, null)) //3 class { string stationId = parentTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString(); dr[DbFiledName.STATIONID.ToString()] = stationId; dr[DbFiledName.STATION.ToString()] = parentTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()]; TreeListNode parentTreeListNodeNext = parentTreeListNode.ParentNode;//运维站 if (parentTreeListNodeNext != null) //2 class { string ywzId = parentTreeListNodeNext[DbFiledName.ORGANIZATIONID.ToString()].ToString(); dr[DbFiledName.YWZID.ToString()] = ywzId; dr[DbFiledName.YWZ.ToString()] = parentTreeListNodeNext[DbFiledName.ORGANIZATIONNAME.ToString()]; TreeListNode grandFatherTreeListNode = parentTreeListNodeNext.ParentNode; //父节点的父节点，工区 if (grandFatherTreeListNode != null) //1 class { //DbFiledName.BDGQ.ToString() string tempbdgqid = grandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString(); dr[DbFiledName.BDGQ.ToString()] = grandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()]; dr[DbFiledName.BDGQID.ToString()] = tempbdgqid; TreeListNode greatGrandFatherTreeListNode = grandFatherTreeListNode.ParentNode;//电业局 //父节点的父节点的父节点 if (greatGrandFatherTreeListNode != null) //0 class { string dyjid = greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString(); dr[DbFiledName.DYJ.ToString()] = greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()]; dr[DbFiledName.DYJID.ToString()] = dyjid; } else { dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.BDGQ.ToString()]; dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.BDGQID.ToString()]; dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.YWZ.ToString()]; dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.YWZID.ToString()]; dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()]; } } else { dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()]; dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()]; dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()]; dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()]; } } else { dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()]; dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()]; dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()]; dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()]; //dr[DbFiledName.STATIONID.ToString()] = ""; //dr[DbFiledName.STATION.ToString()] = ""; } } } }View Code
数据表的电业局，变电工区，运维站，变电所已经获取了，接下来就要汇总数据，添加“总计”行。
//求和之0，变电站 stationIdList = dtTable.AsEnumerable().Select(x => x.Field<string>(DbFiledName.STATIONID.ToString())).Distinct().ToList(); for (int i = 0; i < stationIdList.Count; i++) { //DataRow dr = dtTable.NewRow(); string stationId = stationIdList[i]; var exprssionOne = dtTable.AsEnumerable() .Where(x => x.Field<string>(DbFiledName.STATIONID.ToString()) == stationId); var oneList = exprssionOne.ToList(); if (oneList == null || oneList.Count <= 0) { continue; } DataRow dr = dtTable.NewRow(); DataRow drTemp = oneList[0]; dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()]; dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()]; dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()]; dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()]; dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()]; dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计："; dr[DbFiledName.STATION.ToString()] = drTemp[DbFiledName.STATION.ToString()]; dr[DbFiledName.STATIONID.ToString()] = drTemp[DbFiledName.STATIONID.ToString()]; dr[DbFiledName.PERSONNAME.ToString()] = CountName;//CountName是“总计” long m; //PERSONID是主键，所以，获取最小的主键并减1,作为“总计”列的主键，从而避免重复。 long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1; dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString(); personIdList.Add((newPersonId).ToString()); GetSumColumn(ref dtTable, ref dr, DbFiledName.STATIONID.ToString(), stationId, 1); AddNewRow(ref dtTable, i, stationIdList, DbFiledName.STATIONID.ToString(), ref dr); }
GetSumColumn和AddNewRow函数代码：
//获取列的和 private void GetSumColumn(ref DataTable dtTable, ref DataRow newRow, string id, string tempId, int div) { try { Func<List<long>, int, string> getSumFunc = (list, dicTemp) => (list == null || list.Count <= 0) ? "0" : (list.Sum() / dicTemp).ToString(); Action<DataTable, DataRow, string> resultAction = (dttable, dr, columnname) => { var sumColumn = dttable.AsEnumerable() .Where(x => x.Field<string>(id) == tempId) .Select(x => x.Field<long>(columnname)) .ToList(); dr[columnname] = getSumFunc(sumColumn, div); }; resultAction(dtTable, newRow, DbFiledName.READTICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.EXECUTETICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.LOOKUPTICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.TOTALTICKETCOUNT.ToString()); resultAction(dtTable, newRow, DbFiledName.WRITETICKETCOUNT.ToString()); } catch (Exception ex) { _throwExceptionAction(ex.Message); } }
//添加新行 private void AddNewRow(ref DataTable dtTable, int i, IList<string> idList, string id, ref DataRow dr) { try { int j = i; if (j < idList.Count - 1) { var exprssionNext = dtTable.AsEnumerable() .Where(x => x.Field<string>(id) == idList[j + 1]); if (exprssionNext.ToList() == null || exprssionNext.ToList().Count <= 0) { return; } DataRow nextRow = exprssionNext.ToList()[0]; int index = dtTable.Rows.IndexOf(nextRow); dtTable.Rows.InsertAt(dr, index); } else { dtTable.Rows.Add(dr); } } catch (Exception ex) { _throwExceptionAction(ex.Message); } }
其他的求和和电业局求和差不多，不在贴代码了。现在所有的数据就已经有了，对数据的排列问题阻扰了我好久，最开始的时候，我是用LINQ的GroupBy来排序，结果不能显示我要的数据。我的实现是，先对变电工区汇总，把分组完的数据分开，在对各个分开的数据中的运维站汇总，依次类推。在汇总之前，先把表中获得数据转化成List<T>中。转换代码不再给出。最后得到了需要的数据，效果如最开始的图所示。分组代码如下：
//工区 List<StepCountClass> resultClassList = new List<StepCountClass>(); List<string> tempbdgqIdList = stepCountList.Select(x => x.BDGQID).Distinct().ToList(); for (int i = 0; i < tempbdgqIdList.Count; i++) { //相同变电区的组 var temp = stepCountList.Where(x => x.BDGQID == tempbdgqIdList[i] && x.BDGQ != CountName && !string.IsNullOrEmpty(x.BDGQ)).ToList(); //相同运维站的组 var tempywzIdList = temp.Select(x => x.YWZID).Distinct().ToList(); for (int j = 0; j < tempywzIdList.Count; j++) { var ywztemp = temp.Where(x => x.YWZID == tempywzIdList[j] && x.YWZ != CountName && !string.IsNullOrEmpty(x.YWZ)).ToList(); //相同变电所的组 var stationidList = ywztemp.Select(x => x.STATIONID).Distinct().ToList(); for (int k = 0; k < stationidList.Count; k++) { var stationtemp = ywztemp.Where(x => x.STATIONID == stationidList[k] && x.STATION != CountName && !string.IsNullOrEmpty(x.STATION)).ToList(); //相同用户 var personidList = stationtemp.Select(x => x.PERSONID).ToList(); for (int l = 0; l < personidList.Count; l++) { var persontemp = stationtemp.Where(x => x.PERSONID == personidList[l] && x.PERSONNAME != CountName && !string.IsNullOrEmpty(x.PERSONNAME)) .ToList(); resultClassList.AddRange(persontemp); if (l == personidList.Count - 1) { var personCount = stationtemp.Where(x => x.PERSONNAME == CountName).ToList(); resultClassList.AddRange(personCount); } } //resultClassList.AddRange(stationtemp); if (k == stationidList.Count - 1) { var stationCount = ywztemp.Where(x => x.STATION == CountName).ToList(); resultClassList.AddRange(stationCount); } } if (j == tempywzIdList.Count - 1) { var ywzCount = temp.Where(x => x.YWZ == CountName).ToList(); resultClassList.AddRange(ywzCount); } }
本来以为就做完了，心想万事大吉，可是漏掉了用户权限这一点。如果用户的权限是电业局的话，上面正好符合要求，但是如果用户的权限是，变电工区，那么就查询不到其他工区的数据，只能查到他权限内的数据。现在已运维站为例，给出解决方法。先给出树结构图和最后的汇总效果就明白了。
从图中可以看出仅仅显示了，用户所在运维站（金华操作站）中的树结构。那么右边区域，也要仅仅显示到运维站，电业局、变电工区这两列。如下图所示：
this.comboxOrgANdUserTree1是一个用户控件，RootDeptClass是根节点的级别，0代表电业局，1代表变电工区，2代表运维站，3代表变电所。
switch (this.comboxOrgANdUserTree1.RootDeptClass) { case "0": break; case "1": this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false; var dataRowList = _queryResultDt.AsEnumerable().Where(x => x.Field<string>("YWZ") == CountName).ToList(); foreach (var row in dataRowList) { _queryResultDt.Rows.Remove(row); } this.gridView1.Columns["DYJ"].Caption = @"变电工区"; this.gridView1.Columns["BDGQ"].Caption = @"运维站"; //_queryResultDt.Rows.RemoveAt(_queryResultDt.Rows.Count-1); break; case "2": this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false; this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false; deleteAction(DbFiledName.BDGQ.ToString()); deleteAction(DbFiledName.YWZ.ToString()); deleteAction(DbFiledName.STATION.ToString()); List<string> personIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.PERSONID.ToString())).ToList(); List<string> ywzIdList = new List<string>(); ywzIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.DYJID.ToString())).Distinct().ToList(); for (int i = 0; i < ywzIdList.Count; i++) { //DataRow dr = dtTable.NewRow(); string ywzIdTemp = ywzIdList[i]; var exprssionOne = _queryResultDt.AsEnumerable() .Where(x => x.Field<string>(DbFiledName.YWZID.ToString()) == ywzIdTemp); var oneList = exprssionOne.ToList(); if (oneList == null || oneList.Count <= 0) { continue; } DataRow dr = _queryResultDt.NewRow(); DataRow drTemp = oneList[0]; //DataRow drTemp = exprssionOne.ToList()[0]; dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()]; dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()]; dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()]; dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()]; dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()]; dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计："; dr[DbFiledName.STATION.ToString()] = CountName; long m; long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1; dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString(); dr[DbFiledName.PERSONNAME.ToString()] = ""; personIdList.Add((newPersonId).ToString()); //dr[dyjName] = drTemp[dyjName]; GetSumColumn(ref _queryResultDt, ref dr, DbFiledName.YWZID.ToString(), ywzIdTemp, 2); AddNewRow(ref _queryResultDt, i, ywzIdList, DbFiledName.YWZID.ToString(), ref dr); } //_queryResultDt.AsEnumerable().Where(x=>x.Field<string>("YWZ")||x.Field<string>("BDGQ")) this.gridView1.Columns["DYJ"].Caption = @"运维站"; break; case "3": this.gridView1.Columns.ColumnByFieldName("DYJ").Visible = false; this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false; this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false; deleteAction(DbFiledName.DYJ.ToString()); deleteAction(DbFiledName.BDGQ.ToString()); deleteAction(DbFiledName.YWZ.ToString()); deleteAction(DbFiledName.STATION.ToString()); break; default: break; }
this.comboxOrgANdUserTree1.RootDeptClass和 deleteAction的实现方法分别为：
//获取根节点等级 public string RootDeptClass { get { for (int i = 0; i < this.treeListOrganization.AllNodesCount; i++) { TreeListNode node = this.treeListOrganization.FindNodeByID(i); if (node == null) continue; //DeptClass= node["DEPTCLASS"] var deptClass = node["DEPTCLASS"].ToString(); if (string.IsNullOrEmpty(deptClass)) //所有用户的DEPTCLASS被设置成“” continue; long deptClassLong; //bool flag= long.TryParse(deptClass, out deptClassLong); if (long.TryParse(deptClass, out deptClassLong)) { DeptClass = deptClassLong < DeptClass ? deptClassLong : DeptClass; } } return DeptClass.ToString(); } }
Action<string> deleteAction = fieldName => { var dataRowList = _queryResultDt.AsEnumerable().Where(x => x.Field<string>(fieldName) == CountName).ToList(); foreach (var row in dataRowList) { _queryResultDt.Rows.Remove(row); } };
这样才算完成基本，代码还要好好重构一下，大致解决思路和主要代码都贴出来，时间仓促，以后好好润色。
相关文章
