Spread WPF 18
Features / Data Binding
In This Topic
    Data Binding
    In This Topic

    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:

    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:

    1. Create and populate the DataTable object using a method. (In this example, GetTable is used).
      Copy Code 
      // 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;
}
      Copy Code 
      ' 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
         
    2. Bind the worksheet to the DataTable object by using the DataSource property of the IDataManager interface.
      Copy Code 
      // Bind the DataTable to the first sheet.
DataTable dataTable = GetTable();
spreadSheet1.Workbook.Worksheets[0].DataManager.DataSource = dataTable;
spreadSheet1.Workbook.Worksheets[0].Name = "DataTable";
      Copy Code 
      ' 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.

    Copy Code 
    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";
    Copy Code 
    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.

    1. Create a CollectionView and add data to it. (In this example, ProductCollection is used)
      Copy Code 
      // 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 });
    }
}
      Copy Code 
      ' 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
    2. 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.
      Copy Code 
      // Bind CollectionView to the third sheet
spreadSheet1.Workbook.Worksheets[2].DataManager.DataSource = CollectionViewSource.GetDefaultView(new ProductCollection());
spreadSheet1.Workbook.Worksheets[2].Name = "CollectionView";
      Copy Code 
      ' 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.
      Copy Code 
      // 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";
      Copy Code 
      ' 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.

    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.

    Copy Code 
    <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>
    Copy Code 
    // A blank row appears at the end of the sheet for adding data.
spreadSheet1.Workbook.Worksheets[0].DataManager.DataAllowAddNew = true;
    Copy Code 
    ' 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:

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

    Copy Code 
    // 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);
    Copy Code 
    ' 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.

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

    Copy Code 
    // 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;
    Copy Code 
    ' 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