Sheets can display relational data, such as from a relational database, in hierarchical views. The following figure shows an example of such a hierarchical view, which uses the database provided for the tutorials. The user can expand or collapse the levels of the hierarchy by clicking on the expand and collapse hierarchy icons (plus and minus signs).
To set up hierarchical data display, you first create a data set to hold the relational data, then define the relations between the data, and finally, set the Spread component to display the data as you want. You can customize the cell type, the colors, the headers, and other aspects of the appearance of the child view.
You can bind to a hierarchical collection.
If you set a skin to a sheet, you must apply that skin to the parent sheet and all the child sheets. For more information about skins, refer to Applying a Skin to a Sheet.
You can set the display of the hierarchy, which Spread treats as child views of the overall parent sheet. You can get information about child views using these properties of the SheetView class.
- ChildRelationCount
- GetChildDataModel
- GetChildRelation
- GetChildSheets
- GetChildView
- GetChildVisible
- ParentRelationName
You can catch when the end user is expanding or collapsing the child view. For more information, see the Expand event and the ChildViewCreated event. You can determine if the row is expandable using the GetRowExpandable and SetRowExpandable methods, and if the row is expanded using the IsRowExpanded method.
If you need to set properties on the child SpreadView, the best place to put code to do that is in the ChildWorkbookCreated event. That event fires when a child SpreadView has been created. The ChildViewCreated event fires after the child SheetView has been created, but the child SpreadView does not get created until afterward, and it does not get created unless the child sheet is visible in the component (so that the layout calculations are faster).
Rather than deleting child sheets from a parent sheet, when working with bound data, you would delete the relation in your data source to delete that child sheet from Spread.
This sample code assumes you want to remove the first child sheet returned - alist(0):
Dim alist As ArrayList = FpSpread1.Sheets(0).GetChildSheets()
Dim sv As FarPoint.Win.Spread.SheetView = alist(0)
Dim ds As DataSet = CType(FpSpread1.DataSource, DataSet)
ds.Relations.Remove(sv.ParentRelationName)
For more information about printing a hierarchical sheet, refer to Printing a Child View of a Hierarchical Display.
For information on creating a hierarchical display programmatically, refer to
For a small example see the Tutorial: Binding to a Corporate Database.
Return to the overall list of tasks in Customizing Data Binding.
Using a Shortcut
- Create your data set.
- Set up the data relations between the data coming from the data set, for example, between tables coming from a relational database.
- Set the DataSource property of the FpSpread or the SheetView object equal to the data set.
- Provide code in the ChildViewCreated event of the Spread component for displaying the parent and child views of the data.
Example
This example code binds the Spread component to a data set that contains multiple related tables from a database, and sets up the component to display hierarchical views. This code example uses the database provided for the tutorials (databind.mdb). If you performed the default installation, the database file is in \Program Files\GrapeCity\Spread.WinForms\Docs\TutorialFiles.
[Visual Basic]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Call subroutines to set up data and format the Spread component
InitData()
FormatSpread()
End Sub
Private Sub InitData()
Dim con As New OleDbConnection()
Dim cmd As New OleDbCommand()
Dim da As New OleDbDataAdapter()
Dim ds As New DataSet()
Dim dt As DataTable
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Program Files\GrapeCity\Spread.WinForms.v4\Docs\TutorialFiles\databind.mdb"
con.Open()
With cmd
.Connection = con
.CommandType = CommandType.TableDirect
.CommandText = "Categories"
End With
da.SelectCommand = cmd
da.Fill(ds, "Categories")
cmd.CommandText = "Products"
da.SelectCommand = cmd
da.Fill(ds, "Products")
cmd.CommandText = "Inventory Transactions"
da.SelectCommand = cmd
da.Fill(ds, "Inventory Transactions")
ds.Relations.Add("Root", ds.Tables("Categories").Columns("CategoryID"), ds.Tables("Products").Columns("CategoryID"))
ds.Relations.Add("Secondary", ds.Tables("Products").Columns("ProductID"), ds.Tables("Inventory Transactions").Columns("TransactionID"))
FpSpread1.DataSource = ds
End Sub
Private Sub FormatSpread()
With FpSpread1.Sheets(0)
.ColumnHeader.Rows(0).Height = 30
.Columns(0).Visible = False
End With
End Sub
Private Sub FpSpread1_ChildViewCreated(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.ChildViewCreatedEventArgs) Handles FpSpread1.ChildViewCreated
Dim dateType As New FarPoint.Win.Spread.CellType.DateTimeCellType()
If e.SheetView.ParentRelationName = "Root" Then
With e.SheetView
.DataAutoCellTypes = False
.DataAutoSizeColumns = False
.ColumnHeader.Rows(0).Height = 30
.Columns(0).Visible = False
.Columns(3).Visible = False
.Columns(4).Visible = False
.Columns(1).Width = 200
.Columns(2).Width = 185
.Columns(6).Width = 85
.Columns(7).Width = 80
.Columns(8).Width = 80
.Columns(5).CellType = New FarPoint.Win.Spread.CellType.CurrencyCellType()
.Columns(7).CellType = New FarPoint.Win.Spread.CellType.CheckBoxCellType()
End With
Else
With e.SheetView
.DataAutoCellTypes = False
.DataAutoSizeColumns = False
.ColumnHeader.Rows(0).Height = 30
.Columns(0).Visible = False
.Columns(2).Visible = False
.Columns(3).Visible = False
.Columns(4).Visible = False
.Columns(7).Visible = False
.Columns(8).Visible = False
.Columns(9).Visible = False
.Columns(1).Width = 100
.Columns(6).Width = 80
.Columns(5).CellType = New FarPoint.Win.Spread.CellType.CurrencyCellType()
dateType.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.ShortDate
.Columns(1).CellType = dateType
'Add a total column
.ColumnCount = .ColumnCount + 1
.ColumnHeader.Cells(0, .ColumnCount - 1).Value = "Total"
.Columns(.ColumnCount - 1).CellType = New FarPoint.Win.Spread.CellType.CurrencyCellType()
.Columns(.ColumnCount - 1).Formula = "F1*G1"
End With
End If
Using a Shortcut
- Create your data set.
- Set up the data relations between the data coming from the data set, for example, between tables coming from a relational database.
- Set the FpSpread or the Sheet DataSource property equal to the data set.
- Provide code in the FpSpread ChildViewCreated event for displaying the parent and child views of the data.
Example
This example code binds the Spread component to a hierarchical collection.
[C#]
public class Score
{
private string classname;
private string grade;
public string ClassName
{
get { return classname; }
set { classname = value; }
}
public string Grade
{
get { return grade; }
set { grade = value; }
}
}
public class Student
{
private string name;
private string id;
private ArrayList score = new ArrayList();
public string Name
{
get { return name; }
set { name = value; }
}
public string Id
{
get { return id; }
set { id = value; }
}
public ArrayList Score
{
get { return score; }
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
ArrayList list = new ArrayList();
Student s = new Student();
s.Name = "John Smith";
s.Id = "100001";
Score sc = new Score();
sc.ClassName = "math";
sc.Grade = "A";
s.Score.Add(sc);
sc = new Score();
sc.ClassName = "English";
sc.Grade = "A";
s.Score.Add(sc);
list.Add(s);
s = new Student();
s.Name = "David Black";
s.Id = "100002";
sc = new Score();
sc.ClassName = "math";
sc.Grade = "B";
s.Score.Add(sc);
sc = new Score();
sc.ClassName = "English";
sc.Grade = "A";
s.Score.Add(sc);
list.Add(s);
fpSpread1_Sheet1.DataSource = list;
}
[Visual Basic]
Public Class Score
Private classn as String
Private grade As String
Public Property ClassName() As String
Get
Return classn
End Get
Set(ByVal Value As String)
classn = Value
End Set
End Property
Public Property Grades() As String
Get
Return grade
End Get
Set(ByVal Value As String)
grade = Value
End Set
End Property
End Class
Public Class student
Private name As String
Private id As String
Private sco As ArrayList = New ArrayList()
Public Property names() As String
Get
Return name
End Get
Set(ByVal Value As String)
name = Value
End Set
End Property
Public Property ids() As String
Get
Return id
End Get
Set(ByVal Value As String)
id = Value
End Set
End Property
Public ReadOnly Property score() As ArrayList
Get
Return sco
End Get
End Property
End Class
'Form Load
Dim list As ArrayList = New ArrayList()
Dim s As student = New student()
s.names = "John Smith"
s.ids = "100001"
Dim sc As Score = New Score()
sc.ClassName = "math"
sc.Grades = "A"
s.Score.Add(sc)
sc = New Score()
sc.ClassName = "English"
sc.Grades = "A"
s.Score.Add(sc)
list.Add(s)
s = New student()
s.names = "David Black"
s.ids = "100002"
sc = New Score()
sc.ClassName = "math"
sc.Grades = "B"
s.Score.Add(sc)
sc = New Score()
sc.ClassName = "English"
sc.Grades = "A"
s.Score.Add(sc)
list.Add(s)
FpSpread1_Sheet1.DataSource = list