Spread Windows Forms 6.0 Product Documentation
Working with Hierarchical Data Display
Support Options
Spread Windows Forms 6.0 Product Documentation > Developer's Guide > Managing Data Binding > Customizing Data Binding > Working with Hierarchical Data Display

Glossary Item Box

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).

Spread control with data displayed in hierarchy

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.

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

  1. Create your data set.
  2. Set up the data relations between the data coming from the data set, for example, between tables coming from a relational database.
  3. Set the DataSource property of the FpSpread or the SheetView object equal to the data set.
  4. 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.

VB Copy Code 
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

  1. Create your data set.
  2. Set up the data relations between the data coming from the data set, for example, between tables coming from a relational database.
  3. Set the FpSpread or the Sheet DataSource property equal to the data set.
  4. 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# Copy Code 
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

© 2002-2012 ComponentOne, a division of GrapeCity. All Rights Reserved.