When grouping is turned on for a sheet, a separate target group data model is available to the sheet (or spreadsheet component) and this group data model is flat, completely without a hierarchy. This contains the group headers and other grouping-specific display data. Underneath that model is a target data model where the row data resides.
The following table describes the members used for grouping:
| Grouping API Member |
Description |
| IGroupSupport interface |
Interface that supports grouping |
| GroupDataModel class |
Class of grouping data in the underlying models |
| Group class |
Class in the underlying models that supports grouping |
| Grouping and Grouped events |
Events in FpSpread class |
| GroupInfo |
Class that represents grouping information |
| GroupInfoCollection |
Collection of grouping information
|
In the grouped sheet, you can reference the GroupDataModel object that represents the grouped data in the Data property of the SheetView.DocumentModels object referenced in the Models property of the SheetView class. You can use the IsGroup method of the GroupDataModel class to determine whether the specified row is a group header. You can also use the GetGroup method to reference a Group object that represents the group to which the specified row belongs.
Example
The following example references the GroupDataModel object on sheet and the each group.
| C# |
Copy Code
|
private void button1_Click(object sender, EventArgs e)
{
if (fpSpread1.ActiveSheet.Models.Data is FarPoint.Win.Spread.Model.GroupDataModel)
{
FarPoint.Win.Spread.Model.GroupDataModel gdm = (FarPoint.Win.Spread.Model.GroupDataModel)fpSpread1.ActiveSheet.Models.Data;
for (int i = 0; i < gdm.RowCount; i++)
{
// Determine whether it is group header row or not
if (gdm.IsGroup(i))
{
// Get group
var g = gdm.GetGroup(i);
// Get the reference column of the group and the number of rows included in the group
Console.WriteLine(string.Format("Column index：{0}, Row count：{1}",g.Column, g.Rows.Count));
}
}
}
}
|
| Visual Basic |
Copy Code
|
Private Sub button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If TypeOf FpSpread1.ActiveSheet.Models.Data Is FarPoint.Win.Spread.Model.GroupDataModel Then
Dim gdm As FarPoint.Win.Spread.Model.GroupDataModel = DirectCast(FpSpread1.ActiveSheet.Models.Data, FarPoint.Win.Spread.Model.GroupDataModel)
For i As Integer = 0 To gdm.RowCount - 1
'Determine whether it is group header row or not
If gdm.IsGroup(i) Then
' Get group
Dim g = gdm.GetGroup(i)
' Get the reference column of the group and the number of rows included in the group
Console.WriteLine(String.Format("Column index：{0}, Row count：{1}", g.Column, g.Rows.Count))
End If
Next
End If
End Sub
|
You can customize grouping by specifying your own comparer. For example, you can create a custom group that is by decade if the column has year information. As the Grouping event is raised, you can pass in your own IComparer (call it MyComparer, for example). You can determine what is displayed in the group header by setting the Text property for that group.
Example
The following example creates its own comparer and converts the date value to a number representing the year in decade for comparison.
| C# |
Copy Code
|
[Serializable()]
public class MyGroupComparer : System.Collections.IComparer
{
public int Compare(object x1, object y1)
{
int x=0, y=0;
if ((x1) is DateTime)
{
x = ((DateTime)(x1)).Year % 10;
x = ((DateTime)(x1)).Year - x;
}
if ((y1) is DateTime)
{
y = ((DateTime)(y1)).Year % 10;
y = ((DateTime)(y1)).Year - y;
}
if (x == y) return 0;
else if (x > y) return 1;
else return -1;
}
}
// Write the following code in the code behind of the form
private void Form1_Load(object sender, EventArgs e)
{
// Enable grouping
fpSpread1.AllowColumnMove = true;
fpSpread1.ActiveSheet.GroupBarInfo.Visible = true;
fpSpread1.ActiveSheet.AllowGroup = true;
// Set test data
fpSpread1.ActiveSheet.SetValue(0, 0, DateTime.Today);
fpSpread1.ActiveSheet.SetValue(1, 0, DateTime.Today.AddYears(1));
fpSpread1.ActiveSheet.SetValue(2, 0, DateTime.Today.AddYears(11));
}
private void fpSpread1_Grouping(object sender, FarPoint.Win.Spread.GroupingEventArgs e)
{
var colIndex = e.SortInfo.Last().Index;
// Generate custom group in first column
if (colIndex == 0)
{
e.GroupComparer = new MyGroupComparer();
}
}
|
| Visual Basic |
Copy Code
|
<Serializable>
Public Class MyGroupComparer
Implements System.Collections.IComparer
Public Function Compare(x1 As Object, y1 As Object) As Integer Implements IComparer.Compare
Dim x As Integer = 0, y As Integer = 0
If TypeOf (x1) Is DateTime Then
x = DirectCast(x1, DateTime).Year Mod 10
x = DirectCast(x1, DateTime).Year - x
End If
If TypeOf (y1) Is DateTime Then
y = DirectCast(y1, DateTime).Year Mod 10
y = DirectCast(y1, DateTime).Year - y
End If
If x = y Then
Return 0
ElseIf x > y Then
Return 1
Else
Return -1
End If
End Function
End Class
' Write the following code in the code behind of the form
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Enable grouping
FpSpread1.AllowColumnMove = True
FpSpread1.ActiveSheet.GroupBarInfo.Visible = True
FpSpread1.ActiveSheet.AllowGroup = True
' Set test data
FpSpread1.ActiveSheet.SetValue(0, 0, DateTime.Today)
FpSpread1.ActiveSheet.SetValue(1, 0, DateTime.Today.AddYears(1))
FpSpread1.ActiveSheet.SetValue(2, 0, DateTime.Today.AddYears(11))
End Sub
Private Sub FpSpread1_Grouping(sender As Object, e As GroupingEventArgs) Handles FpSpread1.Grouping
Dim colIndex = e.SortInfo.Last().Index
' Generate custom group in first column
If colIndex = 0 Then
e.GroupComparer = New MyGroupComparer()
End If
End Sub
|
