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. 
No Groups 
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. 
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. 
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 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. 
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.