Spread Windows Forms 13.0 Product Documentation
Spread Windows Forms 13.0 Product Documentation / Developer's Guide / Managing Data Binding / Customizing Data Binding / Working with Hierarchical Data Display
In This Topic
    Working with Hierarchical Data Display
    In This Topic

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

    The following sample code assumes you want to remove the first child sheet returned, in this example, alist(0):

    Code
    Copy Code
    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 a small example see the Tutorial: Binding to a Corporate Database (Older Visual Studio).

    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.NET 13\Docs\Windows Forms\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 OleDb.OleDbConnection()
    Dim cmd As New OleDb.OleDbCommand()
    Dim da As New OleDb.OleDbDataAdapter()
    Dim ds As New DataSet()
    Dim dt As DataTable
    
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
    Files (x86)\ComponentOne\Spread.NET 13\Docs\Windows Forms\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
    
    End Sub
    

    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 object or the Sheet object 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;
    }
    
    VB
    Copy Code
    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