给GridView分组统计的Helper类,功能很强大
来源:互联网 发布:mac开机出现五国 编辑:程序博客网 时间:2024/05/21 15:42
资料链接
http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm
GridView: Creating groups and summaries with 2 lines of code
Site about multilingual website development launched: www.MultiLanguageWebSites.comGridView has a lot of improvements over the DataGrid but it still lacks some very important features. A recurring requirement not available in the GridView is to create groups and summaries. To create summaries we can easily code the RowDataBound event. Grouping is a more complex task, and involves more coding and debugging. But when we need to combine these two features the things really start to get messy. With this in mind I have implemented the GridViewHelper, as the name suggests, a helper class to aid the creation of groups and summaries.
[Online sample]
[Download source code]
Using the GridViewHelper
Below we will see some GridViewHelper samples. First we show the grid to which the groups and summaries will be created. The sample data comes from Northwind database, with a few modifications:
To create a summary for the ItemTotal column we need only the promised 2 lines of code:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
}
First we create the GridViewHelper setting the grid in which it will act in the constructor. Then we register the summary specifying the column name and the summary operation to be performed. The result is below:
In this sample a new line was added to display the summary. Another option is to use the footer row to display the summary instead of creating a new one. When a new row is added to the grid, only the required cells to display the summarized columns are created. Using the footer, all the cells are created. In case of group summaries, generation of all cells or only the needed cells is a group attribute.
Now we will create a group. The code is shown below:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.ApplyGroupSort();
}
The first parameter of RegisterGroup method defines the columns to which the group must be created. It's also possible to create a composite group, consisting of an array of columns. The second parameter specifies if the group is automatic. In this case a new row will be created automatically for the group header. The third parameter specifies if the group columns must be hidden. The ApplyGroupSort method sets the sort expression of the grid as being the group columns, in this case, ShipRegion. This is required to grouping works properly, except if the data comes ordered from database.
In the above sample the column ShipRegion have been hidden:
Let's make something more interesting, let's add a summary to the created group. We need just one more line to register the summary to the group:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipRegion");
helper.ApplyGroupSort();
}
This time, the RegisterSummary method takes another parameter. The parameter specifies the name of the group to which the summary must be created. Group name is automatically generated from the group column names. If the group has only one column, group name will be the name of that column. If the group has more than one column, the group name will be the ordered concatenation of the columns that composes the group, joined with a plus sign ("+"): "ShipRegion+ShipName".
We can see below the grid with grouping and a summary for the group:
It's possible to create more than one group in the grid, simulating a hierarchical grouping, as seen below:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.ApplyGroupSort();
}
Result:
Visualization is compromised when there is more than one group. GridViewHelper has events to allow easy implementation of visual or functional adjusts. The list of events follows below:
With a few more lines of code we can improve the visual aspect of the grid:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.GroupHeader += new GroupEvent(helper_GroupHeader);
helper.ApplyGroupSort();
}
private void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
{
if ( groupName == "ShipRegion" )
{
row.BackColor = Color.LightGray;
row.Cells[0].Text = " " + row.Cells[0].Text;
}
else if (groupName == "ShipName")
{
row.BackColor = Color.FromArgb(236, 236, 236);
row.Cells[0].Text = " " + row.Cells[0].Text;
}
}
The grid after the cosmetics:
More grouping options
There are two more interesting samples. The first presents a composite group. The second defines a suppress group, that has the same behavior of the sql GROUP BY clause. The repeating values are suppressed, and a summary operation is performed on the other columns.
Below we can see the code and the grid appearance for the composite group:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
string[] cols = new string[2];
cols[0] = "ShipRegion";
cols[1] = "ShipName";
helper.RegisterGroup(cols, true, true);
helper.ApplyGroupSort();
}
We can add a summary to the group. This time we will define an average operation and add a label to indicate the operation:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
string[] cols = new string[2];
cols[0] = "ShipRegion";
cols[1] = "ShipName";
helper.RegisterGroup(cols, true, true);
helper.RegisterSummary("ItemTotal", SummaryOperation.Avg, "ShipRegion+ShipName");
helper.GroupSummary += new GroupEvent(helper_GroupSummary);
helper.ApplyGroupSort();
}
private void helper_GroupSummary(string groupName, object[] values, GridViewRow row)
{
row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
row.Cells[0].Text = "Average";
}
The last sample will create a suppress group. It's important to mention that if a suppress group is defined, no other group may be created. In the same way, if there is already a group defined, we can't create a suppress group and an exception will be raised if we try it.
Below we can see the code and the grid appearance for the suppress group:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.SetSuppressGroup("ShipName");
helper.RegisterSummary("Quantity", SummaryOperation.Sum, "ShipName");
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
helper.ApplyGroupSort();
}
No value is displayed for the columns that don't have a summary operation defined. This makes sense because GridViewHelper doesn't know how to proceed to summarize the values found in the group rows to a unique value. This reminds the certain known message:
"Column 'column_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
It doesn't make sense to display the columns that don't have a summary operation, and to hide them we need to call a method:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.SetSuppressGroup(rdBtnLstGroup.SelectedValue);
helper.RegisterSummary("Quantity", SummaryOperation.Sum, "ShipName");
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
helper.SetInvisibleColumnsWithoutGroupSummary();
helper.ApplyGroupSort();
}
I know, it's a big big name! The resulting grid can be seen below:
Summary operations
GridViewHelper has 3 built-in summary operations: sum, average and row count. A very useful feature is the possibility of define custom summary operations. To achieve this we need to provide two methods to the GridViewHelper. A method will be called for each row found in the grid (or group) and the other will be called to retrieve the result of the summary operation. Below we have a sample of a custom summary operation. The semi-dummy operation will return the minimum value found:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterSummary("Quantity", SaveQuantity, GetMinQuantity);
}
private void SaveQuantity(string column, string group, object value)
{
mQuantities.Add(Convert.ToInt32(value));
}
private object GetMinQuantity(string column, string group)
{
int[] qArray = new int[mQuantities.Count];
mQuantities.CopyTo(qArray);
Array.Sort(qArray);
return qArray[0];
}
In the code above we can see the required methods signatures. Both receive the summarized group and column names. If the summary is not relative to a group, the group parameter will be null. The method that is called for each row found in the grid, receives also the value of the column in the current row.
The resulting grid can be seen below :
Limitations
In one sample we said that we can simulate a hierarchical grouping. Although the grid appears to present a hierarchical grouping, the actual implementation isn't hierarchical. There's no group or subgroup. There are only sequentially registered groups. This becomes a problem if we need to create a summary for an inner group. Below we can see what happens in this situation:
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
helper.GroupSummary += new GroupEvent(helper_Bug);
helper.ApplyGroupSort();
}
private void helper_Bug(string groupName, object[] values, GridViewRow row)
{
if (groupName == null) return;
row.BackColor = Color.Bisque;
row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
row.Cells[0].Text = "[ Summary for " + groupName + " " + values[0] + " ]";
}
As we can see, the summary is created after the header of the outer group. This occurs because the event sequence is:
Group1_Start
Group1_End
Group2_Start
Group2_End
To a hierarchical grouping, the event sequence should be:
Group1_Start
Group2_Start
Group2_End
Group1_End
Implementation
GridViewHelper was implemented as a standalone class instead of an inherited class. This makes possible to use the GridViewHelper with any GridView, and doesn't force the developer to inherit a specific GridView, what could affect the classes design. There's another four classes in the solution: GridViewSummary, GridViewGroup, GridViewSummaryList and GridViewGroupList. The "list" classes were created to allow access by a string indexer: helper.GeneralSummaries["ItemTotal"].Value.
When the GridViewHelper is created, a reference to the target GridView is saved, and the RowDataBound event is bound to the method that does the hard work:
{
this.mGrid = grd;
this.useFooter = useFooterForGeneralSummaries;
this.groupSortDir = groupSortDirection;
this.mGeneralSummaries = new GridViewSummaryList();
this.mGroups = new GridViewGroupList();
this.mGrid.RowDataBound += new GridViewRowEventHandler(RowDataBoundHandler);
}
Some methods used internally by the GridViewHelper were defined public because they provide some useful features that may be needed for some customizations. There are a few other options that wasn't shown in the samples but that can be easily verified with Visual Studio intellisense.
Known issues
Performance might be compromised with the excessive boxing and unboxing of value types. To solve this we could implement the built-in summary operations with generics, but this is not easy as we would like, as can be seen at Using Generics for Calculations. Another possibility: Operator Overloading with Generics. In real life this will not affect the application except if there are a million rows, or if there are thousand of users grouping and summarizing data concurrently.
The online sample keeps the GridView EnableViewState false. This is required because when EnableViewState is true, if the page is in a PostBack the GridView will be rebuilt from the ViewState, and won't trigger the RowDataBound event. We can securely disable the ViewState in ASP.Net 2.0 because the ControlState will still be saved.
Feel free to send comments and to implement more features to the GridViewHelper. Let me know and I will update the article.
Site about multilingual website development launched: www.MultiLanguageWebSites.com- 给GridView分组统计的Helper类,功能很强大
- GridView使用【GridViewHelper】分组统计
- Class Helper/record helper 给类“打补丁”
- 功能很强大的UI封装类
- 功能很强大的UI封装类
- 功能很强大的UI封装类
- 强大的GridView
- 基于ArcEngine实现分组统计面积的功能
- 利用bobo-browse 实现lucene的分组统计功能
- 利用bobo-browse 实现lucene的分组统计功能
- 实现类似于sharepoint列表的分组统计功能
- java 实现类似于oracle分组统计的功能
- 利用bobo-browse 实现lucene的分组统计功能
- Lucene聚类分组统计功能(grouping)
- solr 的分组统计
- 使用ClassHelper/record helper 给类“打补丁”
- 使用ClassHelper\record helper 给类“打补丁”
- wpf devexpress gridcontrol分组统计功能
- Oracle正则表达式函数
- LINUX stat 查看文件创建时间
- 天之虹:浅谈游戏数据的积累、挖掘与分析
- 关于shutdown、startup 参数解释,意义。
- 【转】 Spring对DAO的支持
- 给GridView分组统计的Helper类,功能很强大
- Ant的使用
- tftp和nfs服务的建立
- 网络游戏运营中的数据理解及流量转化
- NOTE for Oracle bitand() function
- Format 函数,%f,%d,%x,%p
- 游戏运营的十二大组成
- 非聚簇索引
- USACO 1.2.4 Palindromic Squares(palsquare)