實現方法，第一，相同的列名稱合並的功能，很簡單，設置要合並的列的列屬性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);
既然知道了樹的節點中有需要的信息，那就遍歷樹，獲取電業局，變電工區，運維站，變電所。
數據表的電業局，變電工區，運維站，變電所已經獲取了，接下來就要匯總數據，添加“總計”行。
//求和之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);
}
};
這樣才算完成基本，代碼還要好好重構一下，大致解決思路和主要代碼都貼出來，時間倉促，以後好好潤色。
