Spread Windows Forms and Grouping

You can make data in a spreadsheet easier to read by organizing the data in groups. Spread Windows Forms supports grouping and allows you to add group footers. With grouping, you can allow the user to group rows of data according to the column headers that are dragged into the group bar. Special group headings are displayed above the grouped rows. By default, the spreadsheet does not allow the user to group the rows of a spreadsheet. You can turn on this feature and allow grouping of rows for an entire sheet. Besides allowing grouping, you also need to allow columns to move, since the user performs grouping by clicking and dragging a column header into the group bar, which is similar to moving a column. Also, the group bar must be visible and the column headers (at least one row) should be visible. Use the AllowGroup property of the sheet to turn on grouping. Use the Visible property of the GroupBarInfo class to display the group bar (the area at the top of the sheet into which the user can drag column headers. Set the AllowColumnMove property of the Spread to True to allow the user to click and drag column headers. Unless you are using the default value, set the ColumnHeaderVisible property of the sheet to True to ensure that the column headers are displayed. You can set the maximum number of levels of grouping that the end user can set with the GroupMaximumLevel property. This limits the number of column headers that can be dragged consecutively to the group bar. You can set up the display so that the items are shown initially all expanded or all collapsed when grouping is performed with the GroupingPolicy property. The GroupingPolicy property only applies to new groups. This example allows grouping. Select a column header and drag the header to the group area. BeforeBasicGroup No Groups BasicGroup Spread with Group C# 

//Add sample data  
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\\Program Files (x86)\\\GrapeCity\\\Spread Studio 8\\\Common\\\nwind.mdb";  
string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers";  
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);  
DataSet ds = new DataSet();  
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);  
da.Fill(ds);  
fpSpread1.ActiveSheet.DataAutoSizeColumns = true;  
fpSpread1.ActiveSheet.DataSource = ds;  

FpSpread1.AllowColumnMove = true;  
FpSpread1.ActiveSheet.GroupBarInfo.Visible = true;  
FpSpread1.ActiveSheet.AllowGroup = true;

VB 

Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb"  
Dim sqlStr As String = "Select CompanyName, ContactName, ContactTitle, Country from Customers"  
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)  
Dim ds As DataSet = New DataSet()  
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)  
da.Fill(ds)  
FpSpread1.ActiveSheet.DataAutoSizeColumns = True  
FpSpread1.ActiveSheet.DataSource = ds  

FpSpread1.AllowColumnMove = True  
FpSpread1.ActiveSheet.GroupBarInfo.Visible = True  
FpSpread1.ActiveSheet.AllowGroup = True

You can customize the appearance of the grouped rows. The following table describes the members used for customizing the appearance of grouped rows:

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

Grouped and Grouping events

Events in FpSpread class

GroupInfo class

Class that represents grouping information, can use to set appearance options for groups and group footers

GroupInfoCollection

Collection of grouping information

You can customize the appearance of the group bar at the top of the grouping display with properties in the GroupBarInfo class. The properties on the sheet (GroupBarInfo object) include:

GroupBarInfo Property

Description

BackColor

Sets the background color of the grouping bar

Font

Sets the font of the grouping bar

ForeColor

Sets the text color of the grouping bar

Height

Sets the height of the grouping bar

GroupVerticalIndent

Sets the vertical distance between group names (when more than one group name is used) in the grouping bar

Text

Sets the text of the grouping bar

Visible

Sets whether to display the grouping bar

You can use GroupInfo to set defaults for each level of grouping. The first GroupInfo added in GroupInfos applies to top-level groups (parent), the next applies to the first child group, and so on. You can define a set of properties in an array list called GroupInfo. Set the appearance of grouped rows by adding styles to the array list of appearance properties for grouping. A collection of GroupInfo objects is in the GroupInfoCollection. To set the appearance settings in a GroupInfo to a particular sheet, set the GroupInfos property on that sheet. Appearance settings for grouping in the GroupInfo class include:

  • Background color
  • Border
  • Font
  • Foreground (text) color
  • Horizontal alignment
  • Indent
  • Indent color
  • Vertical alignment

Only column and sheet appearance settings remain when grouping is turned on. Since rows and cells are moved when the grouping feature is turned on, any style or span settings are ignored. This example sets properties for the group bar at the top of the display. groupbar Group Bar Text C# 

//Add sample data  
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\\Program Files (x86)\\\GrapeCity\\\Spread Studio 8\\\Common\\\nwind.mdb";  
string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers";  
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);  
DataSet ds = new DataSet();  
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);  
da.Fill(ds);  
fpSpread1.ActiveSheet.DataAutoSizeColumns = true;  
fpSpread1.ActiveSheet.DataSource = ds;  

fpSpread1.Sheets.Count = 2;  
fpSpread1.Sheets[0].GroupBarInfo.Visible = true;  
fpSpread1.Sheets[0].GroupBarInfo.Text = "GroupBar for Sheet1";  
fpSpread1.Sheets[0].GroupBarInfo.ForeColor = Color.Green;  
fpSpread1.Sheets[0].GroupBarInfo.Font = new Font("Tahama", 10.0f, FontStyle.Bold | FontStyle.Underline);  
fpSpread1.Sheets[0].GroupBarInfo.Height = 30;  
fpSpread1.Sheets[0].GroupBarInfo.GroupVerticalIndent = 2;  
fpSpread1.Sheets[1].GroupBarInfo.Visible = true;  
fpSpread1.Sheets[1].GroupBarInfo.Text = "GroupBar for Sheet2";  
fpSpread1.Sheets[1].GroupBarInfo.BackColor = Color.GreenYellow;  
fpSpread1.Sheets[1].GroupBarInfo.ForeColor = Color.Blue;  
fpSpread1.Sheets[1].GroupBarInfo.Font = new Font("Tahama", 10.0f, FontStyle.Italic);  
fpSpread1.AllowColumnMove = true;  
fpSpread1.Sheets[0].AllowGroup = true;  
fpSpread1.Sheets[1].AllowGroup = true;

VB 

Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb"  
Dim sqlStr As String = "Select CompanyName, ContactName, ContactTitle, Country from Customers"  
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)  
Dim ds As DataSet = New DataSet()  
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)  
da.Fill(ds)  
FpSpread1.ActiveSheet.DataAutoSizeColumns = True  
FpSpread1.ActiveSheet.DataSource = ds  

FpSpread1.Sheets.Count = 2  
FpSpread1.Sheets(0).GroupBarInfo.Visible = True  
FpSpread1.Sheets(0).GroupBarInfo.Text = "GroupBar for Sheet1"  
FpSpread1.Sheets(0).GroupBarInfo.ForeColor = Color.Green  
FpSpread1.Sheets(0).GroupBarInfo.Font = New Font("Tahama", 10.0F, FontStyle.Bold Or FontStyle.Underline)  
FpSpread1.Sheets(0).GroupBarInfo.Height = 30  
FpSpread1.Sheets(0).GroupBarInfo.GroupVerticalIndent = 2  
FpSpread1.Sheets(1).GroupBarInfo.Visible = True  
FpSpread1.Sheets(1).GroupBarInfo.Text = "GroupBar for Sheet2"  
FpSpread1.Sheets(1).GroupBarInfo.BackColor = Color.GreenYellow  
FpSpread1.Sheets(1).GroupBarInfo.ForeColor = Color.Blue  
FpSpread1.Sheets(1).GroupBarInfo.Font = New Font("Tahama", 10.0F, FontStyle.Italic)  
FpSpread1.AllowColumnMove = True  
FpSpread1.Sheets(0).AllowGroup = True  
FpSpread1.Sheets(1).AllowGroup = True

This example specifies the properties of the grouping bar and uses GroupInfo to set colors for parent and child groups. groupparentchild Group Bar C# 

fpSpread1.AllowColumnMove = true;  
fpSpread1.ActiveSheet.GroupBarInfo.Visible = true;  
fpSpread1.ActiveSheet.GroupBarInfo.BackColor = Color.Aquamarine;  
fpSpread1.ActiveSheet.GroupBarInfo.Height = 75;  
fpSpread1.ActiveSheet.GroupMaximumLevel = 3;  
fpSpread1.ActiveSheet.GroupBarInfo.GroupVerticalIndent = 20;  
fpSpread1.ActiveSheet.AllowGroup = true;  

FarPoint.Win.Spread.GroupInfo gi = new FarPoint.Win.Spread.GroupInfo();  
gi.BackColor = Color.Yellow;  
FarPoint.Win.Spread.GroupInfo gi2 = new FarPoint.Win.Spread.GroupInfo();  
gi2.BackColor = Color.Green;  
FarPoint.Win.Spread.GroupInfoCollection gic = new FarPoint.Win.Spread.GroupInfoCollection();  
gic.AddRange(new FarPoint.Win.Spread.GroupInfo[] { gi, gi2 });  
fpSpread1.ActiveSheet.GroupInfos.Add(gic[0]);  
fpSpread1.ActiveSheet.GroupInfos.Add(gic[1]);

VB 

FpSpread1.AllowColumnMove = True  
FpSpread1.ActiveSheet.GroupBarInfo.Visible = True  
FpSpread1.ActiveSheet.GroupBarInfo.BackColor = Color.Aquamarine  
FpSpread1.ActiveSheet.GroupBarInfo.Height = 75  
FpSpread1.ActiveSheet.GroupMaximumLevel = 3  
FpSpread1.ActiveSheet.GroupBarInfo.GroupVerticalIndent = 20  
FpSpread1.ActiveSheet.AllowGroup = True  

Dim gi As New FarPoint.Win.Spread.GroupInfo  
gi.BackColor = Color.Yellow  
Dim gi2 As New FarPoint.Win.Spread.GroupInfo  
gi2.BackColor = Color.Green  
Dim gic As New FarPoint.Win.Spread.GroupInfoCollection()  
gic.AddRange(New FarPoint.Win.Spread.GroupInfo() {gi, gi2})  
FpSpread1.ActiveSheet.GroupInfos.Add(gic(0))  
FpSpread1.ActiveSheet.GroupInfos.Add(gic(1))

This example uses the IsGroup method to determine whether the specified row is a group header (also called group node). C# 

//Add sample data  
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\\Program Files (x86)\\\GrapeCity\\\Spread Studio 8\\\Common\\\nwind.mdb";  
string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers";  
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);  
DataSet ds = new DataSet();  
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);  
da.Fill(ds);  
fpSpread1.ActiveSheet.DataAutoSizeColumns = true;  
fpSpread1.ActiveSheet.DataSource = ds;  
fpSpread1.AllowColumnMove = true;  
fpSpread1.ActiveSheet.GroupBarInfo.Visible = true;  
fpSpread1.ActiveSheet.AllowGroup = true;  

FarPoint.Win.Spread.GroupInfo gi = new FarPoint.Win.Spread.GroupInfo();  
gi.BackColor = Color.Yellow;  
FarPoint.Win.Spread.GroupInfo gi2 = new FarPoint.Win.Spread.GroupInfo();  
gi2.BackColor = Color.Green;  
FarPoint.Win.Spread.GroupInfoCollection gic = new FarPoint.Win.Spread.GroupInfoCollection();  
gic.AddRange(new FarPoint.Win.Spread.GroupInfo[] { gi, gi2 });  
fpSpread1.ActiveSheet.GroupInfos.Add(gic[0]);  
fpSpread1.ActiveSheet.GroupInfos.Add(gic[1]);  

private void fpSpread1_Grouped(object sender, EventArgs e)  
        {  
FarPoint.Win.Spread.Model.GroupDataModel gm;  
if ((fpSpread1.ActiveSheet.Models.Data) is FarPoint.Win.Spread.Model.GroupDataModel)  
{  
    gm = (FarPoint.Win.Spread.Model.GroupDataModel)fpSpread1.ActiveSheet.Models.Data;  
    if ((gm.TargetModel) is FarPoint.Win.Spread.Model.IRangeSupport)  
    {  
        FarPoint.Win.Spread.Model.IRangeSupport im = (FarPoint.Win.Spread.Model.IRangeSupport)gm.TargetModel;  
        bool groupvalue;  
        groupvalue = gm.IsGroup(0);          
        MessageBox.Show(groupvalue.ToString());  
    }  
}                
        }

VB 

Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb"  
Dim sqlStr As String = "Select CompanyName, ContactName, ContactTitle, Country from Customers"  
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)  
Dim ds As DataSet = New DataSet()  
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)  
da.Fill(ds)  
FpSpread1.ActiveSheet.DataAutoSizeColumns = True  
FpSpread1.ActiveSheet.DataSource = ds  
FpSpread1.AllowColumnMove = True  
FpSpread1.ActiveSheet.GroupBarInfo.Visible = True  
FpSpread1.ActiveSheet.AllowGroup = True  

Dim gi As New FarPoint.Win.Spread.GroupInfo  
gi.BackColor = Color.Yellow  
Dim gi2 As New FarPoint.Win.Spread.GroupInfo  
gi2.BackColor = Color.Green  

Dim gic As New FarPoint.Win.Spread.GroupInfoCollection()  
gic.AddRange(New FarPoint.Win.Spread.GroupInfo() {gi, gi2})  
FpSpread1.ActiveSheet.GroupInfos.Add(gic(0))  
FpSpread1.ActiveSheet.GroupInfos.Add(gic(1))  

Private Sub FpSpread1_Grouped(sender As Object, e As EventArgs) Handles FpSpread1.Grouped  
Dim gm As FarPoint.Win.Spread.Model.GroupDataModel  
If TypeOf (FpSpread1.ActiveSheet.Models.Data) Is FarPoint.Win.Spread.Model.GroupDataModel Then  
    gm = FpSpread1.ActiveSheet.Models.Data  
    If TypeOf (gm.TargetModel) Is FarPoint.Win.Spread.Model.IRangeSupport Then  
        Dim im As FarPoint.Win.Spread.Model.IRangeSupport = gm.TargetModel  
        Dim groupvalue As Boolean  
        groupvalue = gm.IsGroup(0)  
        MsgBox(groupvalue)  
    End If  
End If  
End Sub

The group footer is an extra row that is displayed below the group after grouping by a column header. The GroupFooterVisible property must be set to true after the group has been created. The Grouped event can be used to put information in the group footer after a user has created the group. This example uses the IsGroupFooter method to check for a group footer and returns all the group footer rows. group Group Footer C# 

fpSpread1.AllowColumnMove = true;  
fpSpread1.ActiveSheet.GroupBarInfo.Visible = true;  
fpSpread1.ActiveSheet.AllowGroup = true;  
fpSpread1.ActiveSheet.RowCount = 20;  
fpSpread1.ActiveSheet.ColumnCount = 10;  

FarPoint.Win.Spread.GroupInfo gi = new FarPoint.Win.Spread.GroupInfo();  
gi.Font = new Font("MS Sans Serif", 12, FontStyle.Bold);  
gi.ForeColor = Color.Red;  
gi.FooterBackColor = Color.Blue;  
fpSpread1.ActiveSheet.GroupInfos.Add(gi);  
FarPoint.Win.Spread.GroupInfo gi1 = new FarPoint.Win.Spread.GroupInfo();  
gi1.Font = new Font("MS Sans Serif", 12, FontStyle.Bold);  
gi1.ForeColor = Color.Tomato;  
gi1.FooterBackColor = Color.Yellow;  
fpSpread1.ActiveSheet.GroupInfos.Add(gi1);  

for (int rowc = 0; rowc < fpSpread1.Sheets[0].RowCount; rowc++)  
{  
    for (int colc = 0; colc < fpSpread1.Sheets[0].ColumnCount; colc++)  
    {  
        fpSpread1.Sheets[0].Models.Data.SetValue(rowc, colc, colc + rowc);  
    }  
}        

private void fpSpread1_Grouped(object sender, EventArgs e)  
        {  
listBox1.Items.Clear();  
FarPoint.Win.Spread.Model.GroupDataModel gdm;  
gdm = (FarPoint.Win.Spread.Model.GroupDataModel)fpSpread1.ActiveSheet.Models.Data;  
gdm.GroupFooterVisible = true;            
FarPoint.Win.Spread.Model.Group g1 = (FarPoint.Win.Spread.Model.Group)gdm.Groups[1];  
fpSpread1.ActiveSheet.Models.Data = gdm;  

int irow;  
for (irow = 0; irow < gdm.RowCount; irow++)  
{  
    if (gdm.IsGroupFooter(irow) == true) {  
   listBox1.Items.Add(irow);  
        }  
}             
        }

VB 

FpSpread1.AllowColumnMove = True  
FpSpread1.ActiveSheet.GroupBarInfo.Visible = True  
FpSpread1.ActiveSheet.AllowGroup = True  
FpSpread1.ActiveSheet.RowCount = 20  
FpSpread1.ActiveSheet.ColumnCount = 10  

Dim gi As New FarPoint.Win.Spread.GroupInfo()  
gi.Font = New Font("MS Sans Serif", 12, FontStyle.Bold)  
gi.ForeColor = Color.Red  
gi.FooterBackColor = Color.Blue  
FpSpread1.ActiveSheet.GroupInfos.Add(gi)  
Dim gi1 As New FarPoint.Win.Spread.GroupInfo()  
gi1.Font = New Font("MS Sans Serif", 12, FontStyle.Bold)  
gi1.ForeColor = Color.Tomato  
gi1.FooterBackColor = Color.Yellow  
FpSpread1.ActiveSheet.GroupInfos.Add(gi1)  

For rowc As Integer = 0 To FpSpread1.Sheets(0).RowCount - 1  
    For colc As Integer = 0 To FpSpread1.Sheets(0).ColumnCount - 1  
        FpSpread1.Sheets(0).Models.Data.SetValue(rowc, colc, colc + rowc)  
    Next  
Next  

Private Sub FpSpread1_Grouped(sender As Object, e As EventArgs) Handles FpSpread1.Grouped  
ListBox1.Items.Clear()  
Dim gdm As FarPoint.Win.Spread.Model.GroupDataModel  
Dim g1 As FarPoint.Win.Spread.Model.Group  
gdm = FpSpread1.Sheets(0).Models.Data  
gdm.GroupFooterVisible = True  
g1 = gdm.Groups(1)  
FpSpread1.ActiveSheet.Models.Data = gdm  

Dim irow As Integer  
For irow = 0 To gdm.RowCount - 1  
    If gdm.IsGroupFooter(irow) = True Then  
        ListBox1.Items.Add(irow)  
    End If  
Next  
End Sub

This example sums the values in the second group and puts them in the group footer. groupfootersum Group Footer with Sum C# 

fpSpread1.AllowColumnMove = true;             
fpSpread1.Sheets[0].ColumnCount = 15;  
fpSpread1.Sheets[0].GroupBarInfo.Visible = true;  
fpSpread1.Sheets[0].AllowGroup = true;  
fpSpread1.Sheets[0].GroupFooterVisible = true;  
//Value  
for (int rowc = 0; rowc < fpSpread1.Sheets[0].RowCount; rowc++)  
{  
    for (int colc = 0; colc < fpSpread1.Sheets[0].ColumnCount; colc++)  
    {  
        fpSpread1.Sheets[0].Models.Data.SetValue(rowc, colc, colc + rowc * fpSpread1.Sheets[0].ColumnCount);  
    }  
}  

private void fpSpread1_Grouped(object sender, EventArgs e)  
        {  
            FarPoint.Win.Spread.Model.GroupDataModel gdm;  
            gdm = (FarPoint.Win.Spread.Model.GroupDataModel)fpSpread1.ActiveSheet.Models.Data;  
            gdm.GroupFooterVisible = true;  
            FarPoint.Win.Spread.Model.Group g1 = (FarPoint.Win.Spread.Model.Group)gdm.Groups[1];  
            ((FarPoint.Win.Spread.Model.IAggregationSupport)g1.GroupFooter.DataModel).SetCellAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum);  
            fpSpread1.ActiveSheet.Models.Data = gdm;  
        }

VB 

FpSpread1.Sheets(0).RowCount = 8  
FpSpread1.Sheets(0).ColumnCount = 15  
FpSpread1.Sheets(0).GroupBarInfo.Visible = True  
FpSpread1.Sheets(0).AllowGroup = True  
FpSpread1.Sheets(0).GroupFooterVisible = True  
FpSpread1.AllowColumnMove = True  
'Value  
Dim rowc As Integer  
Dim colc As Integer  
For rowc = 0 To FpSpread1.Sheets(0).RowCount  
    For colc = 0 To FpSpread1.Sheets(0).ColumnCount  
        FpSpread1.Sheets(0).Models.Data.SetValue(rowc, colc, colc + rowc * FpSpread1.Sheets(0).ColumnCount)  
    Next colc  
Next rowc  

Private Sub FpSpread1_Grouped(sender As Object, e As EventArgs) Handles FpSpread1.Grouped  
Dim gdm As FarPoint.Win.Spread.Model.GroupDataModel  
Dim g1 As FarPoint.Win.Spread.Model.Group  
gdm = FpSpread1.Sheets(0).Models.Data  
gdm.GroupFooterVisible = True  
g1 = gdm.Groups(1)  
CType(g1.GroupFooter.DataModel, FarPoint.Win.Spread.Model.IAggregationSupport).SetCellAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum)  
FpSpread1.ActiveSheet.Models.Data = gdm  
End Sub

MESCIUS inc.

comments powered by Disqus