[]
        
(Showing Draft Content)

Creating a Custom Group

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

Using a Group Data Model

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.

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));                       
            }
        }
    }
}
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

Creating a Custom Group

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.

[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();
    }
}
<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

See Also

Allowing the User to Group Rows

Using Grouping

Setting the Appearance of Grouped Rows

Customizing the Group Bar

Interoperability of Grouping with Other Features