Spread for WPF Overview
Key Features
Getting Started
Quick Start
Designer
Features
- Workbook
- Worksheet
- Column
- Row
- Headers
- Cells
- Cell Types
- Data Binding
- Data Validation
- Conditional Formatting
- Drag and Drop
- Copy and Paste
- Group
- Zoom
- Undo/Redo
- Search Data
- Scroll
- Sort
- Filter
- Table
- Charts
- Formulas and Functions
- File Operations
- Print
Formula Reference
Assembly Reference

Data Binding

Spread for WPF provides data binding support that makes it easy to populate the data in worksheets with minimal code. By binding a worksheet to a data source, you can automatically display data from various sources, like data tables or data collections.

This topic contains how to bind worksheets to different data sources and additional new rows or unbound columns.

Worksheet Binding

Spread for WPF allows you to bind the worksheets to several types of data sources, as mentioned below:

DataTable
DataView
CollectionView

Spread for WPF also allows you to bind a table to a data source. For more information about table binding, you can refer to the Table Binding topic.

Bind Worksheet to a DataTable

To bind a worksheet to a data table, follow the steps below:

Create and populate the DataTable object using a method. (In this example, GetTable is used).

// Create a data table.
public DataTable GetTable()
{
     DataTable dt = new DataTable();
     dt.Columns.Add("Name");
     dt.Columns.Add("Age");
     DataRow dr = dt.NewRow();
     dr.ItemArray = new object[] { "Jack", 35 };
     dt.Rows.Add(dr);
     dr = dt.NewRow();
     dr.ItemArray = new object[] { "Jimmy", 42 };
     dt.Rows.Add(dr);
     dr = dt.NewRow();
     dr.ItemArray = new object[] { "Sam", 24 };
     dt.Rows.Add(dr);
     dr = dt.NewRow();
     dr.ItemArray = new object[] { "Venka", 55 };
     dt.Rows.Add(dr);
     return dt;
}

' Create a data table.
Public Function GetTable() As DataTable
    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("Name")
    dt.Columns.Add("Age")
    Dim dr As DataRow = dt.NewRow()
    dr.ItemArray = New Object() {"Jack", 35}
    dt.Rows.Add(dr)
    dr = dt.NewRow()
    dr.ItemArray = New Object() {"Jimmy", 42}
    dt.Rows.Add(dr)
    dr = dt.NewRow()
    dr.ItemArray = New Object() {"Sam", 24}
    dt.Rows.Add(dr)
    dr = dt.NewRow()
    dr.ItemArray = New Object() {"Venka", 55}
    dt.Rows.Add(dr)
    Return dt
End Function

Bind the worksheet to the DataTable object by using the DataSource property of the IDataManager interface.

// Bind the DataTable to the first sheet.
DataTable dataTable = GetTable();
spreadSheet1.Workbook.Worksheets[0].DataManager.DataSource = dataTable;
spreadSheet1.Workbook.Worksheets[0].Name = "DataTable";

' Bind the DataTable to the first sheet.
Dim dataTable As DataTable = GetTable()
spreadSheet1.Workbook.Worksheets(0).DataManager.DataSource = dataTable
spreadSheet1.Workbook.Worksheets(0).Name = "DataTable"

Bind Worksheet to a DataView

To bind a worksheet to the DataView, bind a data view created from the dataset using the DataSource property of the IDataManager interface.

string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Northwind.MDB";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand("Select * from Products", connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
// Bind the DataView to the second sheet.
DataView dataView = dataset.Tables[0].DefaultView;
spreadSheet1.Workbook.Worksheets[1].DataManager.DataSource = dataView;
spreadSheet1.Workbook.Worksheets[1].Name = "DataView";

Dim connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Northwind.MDB"
Dim connection As OleDbConnection = New OleDbConnection(connectionString)
Dim command As OleDbCommand = New OleDbCommand("Select * from Products", connection)
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(command)
Dim dataset As DataSet = New DataSet()
adapter.Fill(dataset)
' Bind the DataView to the second sheet.
Dim dataView As DataView = dataset.Tables(0).DefaultView
spreadSheet1.Workbook.Worksheets(1).DataManager.DataSource = dataView
spreadSheet1.Workbook.Worksheets(1).Name = "DataView"

Bind Worksheet to a CollectionView

To bind a worksheet to a CollectionView, follow the steps below.

Create a CollectionView and add data to it. (In this example, ProductCollection is used)

// Class to generate a collection of Product objects.
public class ProductCollection : ObservableCollection<Product>
{
    public ProductCollection()
    {
        Add(new Product() { ID = "CUPTNS0001", Name = "Tanabata-Star Teacup & Saucer", Quantity = 200, UnitPrice = 90 });
        Add(new Product() { ID = "CUPTND0002", Name = "Tanabata-decorated teacup and saucer", Quantity = 400, UnitPrice = 90 });
        Add(new Product() { ID = "CUPTNF0003", Name = "Tanabata-Festival Tea Cup & Saucer", Quantity = 490, UnitPrice = 60 });
        Add(new Product() { ID = "CUPSCO0004", Name = "Coast Teacup & Saucer", Quantity = 10, UnitPrice = 120 });
        Add(new Product() { ID = "CUPSCA0005", Name = "Aoba Teacup & Saucer", Quantity = 9, UnitPrice = 2000 });
    }
}

' Class to generate a collection of Product objects.
Public Class ProductCollection
    Inherits ObservableCollection(Of Product)
    Public Sub New()
        Add(New Product() With {
            .ID = "CUPTNS0001",
            .Name = "Tanabata-Star Teacup & Saucer",
            .Quantity = 200,
            .UnitPrice = 90
        })
        Add(New Product() With {
            .ID = "CUPTND0002",
            .Name = "Tanabata-decorated teacup and saucer",
            .Quantity = 400,
            .UnitPrice = 90
        })
        Add(New Product() With {
            .ID = "CUPTNF0003",
            .Name = "Tanabata-Festival Tea Cup & Saucer",
            .Quantity = 490,
            .UnitPrice = 60
        })
        Add(New Product() With {
            .ID = "CUPSCO0004",
            .Name = "Coast Teacup & Saucer",
            .Quantity = 10,
            .UnitPrice = 120
        })
        Add(New Product() With {
            .ID = "CUPSCA0005",
            .Name = "Aoba Teacup & Saucer",
            .Quantity = 9,
            .UnitPrice = 2000
        })
    End Sub
End Class

Bind the CollectionView to a worksheet by setting the DataSource property of the IDataManager interface.
By default, when you bind a worksheet to a data source, a column is automatically generated for each field in the data source. You can disable auto-generation of columns and define all columns yourself by seeting the AutoGenerateColumns property to false.

If the AutoGenerateColumns property is set to True, use the following code to bind the CollectionView.

// Bind CollectionView to the third sheet
spreadSheet1.Workbook.Worksheets[2].DataManager.DataSource = CollectionViewSource.GetDefaultView(new ProductCollection());
spreadSheet1.Workbook.Worksheets[2].Name = "CollectionView";

' Bind CollectionView to the third sheet
spreadSheet1.Workbook.Worksheets(2).DataManager.DataSource = CollectionViewSource.GetDefaultView(New ProductCollection())
spreadSheet1.Workbook.Worksheets(2).Name = "CollectionView"

If the AutoGenerateColumns property is set to False, use the following code to bind the CollectionView.

// Set AutoGenerateColumns property for the sheet to false and bind it to the CollectionView.
spreadSheet1.Workbook.Worksheets[0].DataManager.AutoGenerateColumns = false;
spreadSheet1.Workbook.Worksheets[0].DataManager.SetModelDataColumn(0, "ID");
spreadSheet1.Workbook.Worksheets[0].DataManager.SetModelDataColumn(1, "Name");
spreadSheet1.Workbook.Worksheets[0].DataManager.DataSource = CollectionViewSource.GetDefaultView(new ProductCollection());
spreadSheet1.Workbook.Worksheets[0].Name = "AutoGenerateColumns False";

' Set AutoGenerateColumns property for the sheet to false and bind it to the CollectionView.
spreadSheet1.Workbook.Worksheets(0).DataManager.AutoGenerateColumns = False
spreadSheet1.Workbook.Worksheets(0).DataManager.SetModelDataColumn(0, "ID")
spreadSheet1.Workbook.Worksheets(0).DataManager.SetModelDataColumn(1, "Name")
spreadSheet1.Workbook.Worksheets(0).DataManager.DataSource = CollectionViewSource.GetDefaultView(New ProductCollection())
spreadSheet1.Workbook.Worksheets(0).Name = "AutoGenerateColumns False"

Add New Bound Rows

You can use the DataAllowAddNew property of the IDataManager interface to allow users to add a new row to a bound sheet at runtime. When this property is set to true, an empty row appears at the end of the worksheet for users to enter new data. When data is entered, it becomes a bound row and is updated in the data source and a new empty row appears automatically at the end.

!type=note
Note:
You cannot perform certain operations such as copy, cut, and paste into the unbound rows.
You cannot set a custom format to the unbound rows.
You cannot select both bound and unbound rows at the same time.

The following example code will add an unbound row to a bound worksheet at run time. You can add an unbound row using both the XAML view and the code view.

XAML

<Grid>
     <gss:GcSpreadSheet x:Name="spreadSheet1">
         <gss:GcSpreadSheet.Sheets>
             <!-- Set 'DataAllowAddNew' property as 'true' to allow adding new row to the sheet -->
             <gss:SheetInfo DataAllowAddNew ="True"/>
         </gss:GcSpreadSheet.Sheets >
     </gss:GcSpreadSheet>
</Grid>

// A blank row appears at the end of the sheet for adding data.
spreadSheet1.Workbook.Worksheets[0].DataManager.DataAllowAddNew = true;

' A blank row appears at the end of the sheet for adding data.
spreadSheet1.Workbook.Worksheets(0).DataManager.DataAllowAddNew = True

Add Unbound Rows

After binding a worksheet to a data source, you may want to add an unbound row to include additional data. This new unbound row can also be added back to the data source. Furthermore, you can create a new row at runtime and bind it to the data source. To add an unbound row to a bound worksheet, use the AddUnboundRows method from the IDataManager interface. Additionally, to include that unbound row in the data source, use the AddRowToDataSource method of the IDataManager interface.

Follow the steps below to add an unbound row and bind it to a data source:

Bind your worksheet to a data source.
Add the unbound row using the AddUnboundRows method.
Add data to this unbound row and set its properties.
Add the unbound row to the data source by using the AddRowToDataSource method.

The below code adds an unbound row to a bound worksheet and adds the unbound row to the data source.

// Add unbound rows to the bound worksheet.
spreadSheet1.Workbook.Worksheets[0].DataManager.AddUnboundRows(5, 1);
// Add data to the unbound row.
spreadSheet1.Workbook.Worksheets[0].Cells[5, 1].Text = "Total Units";
spreadSheet1.Workbook.Worksheets[0].Cells[5, 1].Font.Bold = true;
spreadSheet1.Workbook.Worksheets[0].Cells[5, 2].Formula = "SUM(C1:C4)";
spreadSheet1.Workbook.Worksheets[0].Cells[5, 2].Font.Color = Color.FromKnownColor(GrapeCity.Core.KnownColor.Blue);
// Add the unbound row to the data source.
spreadSheet.Workbook.Worksheets[0].DataManager.AddRowToDataSource(5, true);

' Add unbound rows to the bound worksheet.
spreadSheet1.Workbook.Worksheets(0).DataManager.AddUnboundRows(5, 1)
' Add data to the unbound row.
spreadSheet1.Workbook.Worksheets(0).Cells(5, 1).Text = "Total Units"
spreadSheet1.Workbook.Worksheets(0).Cells(5, 1).Font.Bold = True
spreadSheet1.Workbook.Worksheets(0).Cells(5, 2).Formula = "SUM(C1:C4)"
spreadSheet1.Workbook.Worksheets(0).Cells(5, 2).Font.Color = Color.FromKnownColor(GrapeCity.Core.KnownColor.Blue)
' Add the unbound row to the data source.
spreadSheet.Workbook.Worksheets(0).DataManager.AddRowToDataSource(5, True)

Add Unbound Columns

When your worksheet is bound to a data source, you can also add unbound columns to the data source and insert them at any position. There are two ways to add/insert an unbound column to a worksheet.

Option 1: Auto-generate columns with data binding to generate bound columns and add or insert unbound columns.
Option 2: Disables auto-generation of columns via data binding and sets bound and unbound columns respectively.

The following sample code automatically generates columns using data binding and then adds an unbound column "Total Amount" to the worksheet having the formula "Quantity * UnitPrice".

// Add an unbound column to the worksheet.
spreadSheet1.Workbook.Worksheets[0].InsertColumns(4,1);
spreadSheet1.Workbook.Worksheets[0].ColumnHeader.Cells[0, 4].Text = "Total Amount";
spreadSheet1.Workbook.Worksheets[0].Columns[4].Formula = "C:C*D:D";
spreadSheet1.Workbook.Worksheets[0].Columns[4].NumberFormat = "$#.00";
spreadSheet1.Workbook.Worksheets[0].Columns[4].ColumnWidth = 120;

' Add an unbound column to the worksheet.
spreadSheet1.Workbook.Worksheets(0).InsertColumns(4, 1)
spreadSheet1.Workbook.Worksheets(0).ColumnHeader.Cells(0, 4).Text = "Total Amount"
spreadSheet1.Workbook.Worksheets(0).Columns(4).Formula = "C:C*D:D"
spreadSheet1.Workbook.Worksheets(0).Columns(4).NumberFormat = "$#.00"
spreadSheet1.Workbook.Worksheets(0).Columns(4).ColumnWidth = 120

Worksheet Binding
Bind Worksheet to a DataTable
Bind Worksheet to a DataView
Bind Worksheet to a CollectionView
Add New Bound Rows
Add Unbound Rows
Add Unbound Columns