WorksheetDataBinding Interface

Contains information about a cell range bound to an external data source or used to create a data source.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v18.2.Core.dll

Declaration

C#

public interface WorksheetDataBinding

VB.NET

Public Interface WorksheetDataBinding

Related API Members

The following members return WorksheetDataBinding objects:

• DataBindingErrorEventArgs.DataBinding
• WorksheetDataBindingCollection.BindToDataSource(Object, Int32, Int32, ExternalDataSourceOptions)
• WorksheetDataBindingCollection.BindToDataSource(Object, Int32, Int32)
• WorksheetDataBindingCollection.BindToDataSource(Object, Range, ExternalDataSourceOptions)
• WorksheetDataBindingCollection.BindToDataSource(Object, Range)
• WorksheetDataBindingCollection.BindToDataSource(Object)
• WorksheetDataBindingCollection.CreateDataSource(Range, RangeDataSourceOptions)
• WorksheetDataBindingCollection.CreateDataSource(Range)
• WorksheetDataBindingCollection.GetDataBinding(Cell)

Remarks

A WorksheetDataBinding establishes two-way data binding by default. However, if a worksheet range is bound to the read-only data source, the range itself is also read-only and its data are not updated. If a worksheet range is bound to the data source which allows change notifications, such as a data source with the IBindingList interface, the worksheet range has a number of rows synchronized to the number of data records. The worksheet automatically inserts or deletes rows when a data source inserts or deletes its records. When a row is inserted or deleted in the bound range, the data record is inserted or deleted in the data source.

The WorksheetDataBinding objects are contained in a WorksheetDataBindingCollection, accessible using the Worksheet.DataBindings property.

Classes and members relevant for the WorksheetDataBinding class are listed in the table below.

Class or Member	Remark
WorksheetDataBindingCollection	A collection of data bindings specified in a worksheet.
WorksheetDataBindingCollection.BindToDataSource	Creates a WorksheetDataBinding bound to the specified data source and adds it to the Worksheet.DataBindings collection.
ExternalDataSourceOptions	An object that contains various options for establishing data binding of a worksheet range to a data source. Create it and pass it as a parameter to the WorksheetDataBindingCollection.BindToDataSource method.
WorksheetDataBindingCollection.CreateDataSource	Creates a new data source from the specified cell range.
RangeDataSourceOptions	Contains various options for a data source created from a cell range. Create it and pass it as a parameter to the WorksheetDataBindingCollection.CreateDataSource method.
IBindingRangeValueConverter	An interface that defines how to display data from the bound data source in the worksheet and store it back to the data source. Use the DataSourceOptionsBase.CellValueConverter property to specify a custom converter.
WorksheetDataBindingCollection.Error	This event occurs when an error happens during data update in the binding range or in the bound data source.

A range used for data binding cannot have merged cells. If the method that creates the data binding encounters merged cells, it throws an exception.

If the DataSourceOptionsBase.SkipHiddenColumns or DataSourceOptionsBase.SkipHiddenRows option is turned on when establishing data binding, the bound range accessible using the WorksheetDataBinding.Range property may include cells which are not bound to data. This happens because they are located in the hidden columns or rows. When the hidden cells become visible, they are still unbound.

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-bind-a-worksheet-to-a-generic-list-or-a-bindinglist-data-source-t480285

Form1.cs

private void BindWeatherReportToRange(object weatherDatasource, Range bindingRange) {
    Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];

    // Check for range conflicts.
    var dataBindingConflicts = sheet.DataBindings.
        Where(d => (d.Range.RightColumnIndex >= bindingRange.LeftColumnIndex) || (d.Range.BottomRowIndex >= bindingRange.TopRowIndex)) ;
    if (dataBindingConflicts.Count() > 0) {
        MessageBox.Show("Cannot bind the range to data.\r\nThe worksheet contains other binding ranges which may conflict.", "Range Conflict");
        return;
    }

    // Specify the binding options.
    ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
    dsOptions.ImportHeaders = true;
    dsOptions.CellValueConverter = new MyWeatherConverter();
    dsOptions.SkipHiddenRows = true;

    // Bind the data source to the worksheet range.
    WorksheetDataBinding sheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions);

    // Adjust the column width.
    sheetDataBinding.Range.AutoFitColumns();
}

Form1.vb

Private Sub BindWeatherReportToRange(ByVal weatherDatasource As Object, ByVal bindingRange As Range)
    Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)

    ' Check for range conflicts.
    Dim dataBindingConflicts = sheet.DataBindings.Where(Function(d) (d.Range.RightColumnIndex >= bindingRange.LeftColumnIndex) OrElse (d.Range.BottomRowIndex >= bindingRange.TopRowIndex))
    If dataBindingConflicts.Count() > 0 Then
        MessageBox.Show("Cannot bind the range to data." & ControlChars.CrLf & "The worksheet contains other binding ranges which may conflict.", "Range Conflict")
        Return
    End If

    ' Specify the binding options.
    Dim dsOptions As New ExternalDataSourceOptions()
    dsOptions.ImportHeaders = True
    dsOptions.CellValueConverter = New MyWeatherConverter()
    dsOptions.SkipHiddenRows = True

    ' Bind the data source to the worksheet range.
    Dim sheetDataBinding As WorksheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions)

    ' Adjust the column width.
    sheetDataBinding.Range.AutoFitColumns()
End Sub