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)

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.

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\Mescius\Spread.NET 12\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 12\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

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 12\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

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 object or the Sheet object 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#	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; }

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 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; }

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;
}