Spread Windows Forms 18 Product Documentation

Binding a Table

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:

Automatically generate columns for a table using ITable Interface.
Manually set data fields in a table using ITableColumn Interface.

A data-bound table shows the following behavior:

You can resize the table by columns but not by rows.
Setting a value changes the data source.
Adding or deleting rows changes the data source.
Adding or deleting columns does not change the data source.
Removing, moving, or resizing the table does not change the data source.
Formulas are not saved to the data source.
The value of existing cells will be cleared if they belong to the binding data range (table range).
If ITable.AutoGenerateColumns is false, binding adjusts the table row count automatically (but column count stays the same). Otherwise, all table columns are regenerated.

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.

!type=note
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.

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;

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.

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

'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

Binding Tables Automatically
Binding Tables Manually