Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Tables / Binding a Table
In This Topic
    Binding a Table
    In This Topic

    Spread for Winforms allows you to bind a table to a data source using cell-level binding.

    Table binding can be done in two ways:

    A data-bound table shows the following behavior:

    Binding Tables Automatically

    You can bind a table to a data source automatically by using the ITable Interface and its members.

    The AutoGenerateColumns property, as the name suggests, automatically generates columns. The DataSource property is used to set the data source.

    Note: AutoGenerateColumns property must be assigned before the DataSource property. Otherwise, current table columns will be kept.

    Consider the following example where the finance department of a company maintains a database of its employees' work-related travel details such as flight ID, flight date, source and destination. The database can be loaded in a table automatically to display travel details.

    C#
    Copy Code 
    GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
            
// Create table data
DataSet ds = new DataSet();
DataTable flightDetails = new DataTable("FlightDetails");
flightDetails.Columns.Add("Passenger Name");
flightDetails.Columns.Add("Department");
flightDetails.Columns.Add("Ticket Type");
flightDetails.Columns.Add("Flight ID");
flightDetails.Columns.Add("Flight Date");
flightDetails.Columns.Add("Flight Src");
flightDetails.Columns.Add("Flight Dest");
flightDetails.Rows.Add("Mark", "Sales", "Economy",7855, new DateTime(2021, 10, 11).ToShortDateString(), "New York", "Tokyo");
flightDetails.Rows.Add("Sophie", "Services", "Economy", 7426, new DateTime(2021, 10, 12).ToShortDateString(), "London", "Venice");
flightDetails.Rows.Add("Oliver", "Finance", "Business", 7641, new DateTime(2021, 10, 15).ToShortDateString(), "New Delhi", "Moscow");
flightDetails.Rows.Add("James", "R&D", "Economy", 7293, new DateTime(2021, 10, 18).ToShortDateString(), "Beijing", "Dubai");
flightDetails.Rows.Add("Emma", "Marketing", "Business", 7117, new DateTime(2021, 10, 20).ToShortDateString(), "Paris", "Seoul");

ds.Tables.Add(flightDetails);

// Create table
TestActiveSheet.Cells["A1"].Value = "For Finance Only:";
GrapeCity.Spreadsheet.ITable table = TestActiveSheet.Range("A2:G7").CreateTable(true);

// Set auto generate columns
table.AutoGenerateColumns = true;

// Bind table to datasource
table.DataSource = ds;
    Visual Basic
    Copy Code 
    Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1.AsWorkbook().ActiveSheet

'Create table data
Dim ds As DataSet = New DataSet()
Dim flightDetails As DataTable = New DataTable("FlightDetails")
flightDetails.Columns.Add("Passenger Name")
flightDetails.Columns.Add("Department")
flightDetails.Columns.Add("Ticket Type")
flightDetails.Columns.Add("Flight ID")
flightDetails.Columns.Add("Flight Date")
flightDetails.Columns.Add("Flight Src")
flightDetails.Columns.Add("Flight Dest")
flightDetails.Rows.Add("Mark", "Sales", "Economy", 7855, New DateTime(2021, 10, 11).ToShortDateString(), "New York", "Tokyo")
flightDetails.Rows.Add("Sophie", "Services", "Economy", 7426, New DateTime(2021, 10, 12).ToShortDateString(), "London", "Venice")
flightDetails.Rows.Add("Oliver", "Finance", "Business", 7641, New DateTime(2021, 10, 15).ToShortDateString(), "New Delhi", "Moscow")
flightDetails.Rows.Add("James", "R&D", "Economy", 7293, New DateTime(2021, 10, 18).ToShortDateString(), "Beijing", "Dubai")
flightDetails.Rows.Add("Emma", "Marketing", "Business", 7117, New DateTime(2021, 10, 20).ToShortDateString(), "Paris", "Seoul")

ds.Tables.Add(flightDetails)

'Create table
TestActiveSheet.Cells("A1").Value = "For Finance Only:"
Dim table As GrapeCity.Spreadsheet.ITable = TestActiveSheet.Range("A2:G7").CreateTable(True)

'Set auto generate columns
table.AutoGenerateColumns = True

'Bind table to datasource
table.DataSource = ds

    Binding Tables Manually

    You can bind a table to a data source and manually set its columns by using the ITableColumn Interface. The AutoGenerateColumns property must be set to false when manually binding a table.

    The DataField property helps to assign columns to a data field in the data source. You can also set the columns to a specific cell type by using the CellType property.

    Following up on the example from the previous section, the company can also choose to manually create a table using the existing data source. This table keeps a record of unavailability of employees on their flying date to keep their respective managers informed.

    C#
    Copy Code 
    // Create table
// GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["A9"].Value = "Unavailable Employees:";
GrapeCity.Spreadsheet.ITable table2 = TestActiveSheet.Range("A10:D12").CreateTable(true);
            
// Disable auto generate columns
table2.AutoGenerateColumns = false;

// Set data fields in columns
table2.TableColumns[0].DataField = "Passenger Name";
table2.TableColumns[1].DataField = "Department";
table2.TableColumns[2].DataField = "Flight Dest";
table2.TableColumns[3].DataField = "Flight Date";

// Bind table to the existing datasource 'ds' defined in the previous section
table2.DataSource = ds;
    Visual Basic
    Copy Code 
    'Create table
'Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.Cells("A9").Value = "Unavailable Employees:"
Dim table2 As GrapeCity.Spreadsheet.ITable = TestActiveSheet.Range("A10:D12").CreateTable(True)

'Disable auto generate columns
table2.AutoGenerateColumns = False

'Set data fields in columns
table2.TableColumns(0).DataField = "Passenger Name"
table2.TableColumns(1).DataField = "Department"
table2.TableColumns(2).DataField = "Flight Dest"
table2.TableColumns(3).DataField = "Flight Date"

'Bind table to the existing datasource 'ds' defined in the previous section
table2.DataSource = ds