officefileapi-devexpress-dot-spreadsheet-c6700631.md
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.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
public interface WorksheetDataBinding
Public Interface WorksheetDataBinding
The following members return WorksheetDataBinding objects:
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.
private void BindWeatherReportToRange(object weatherDatasource, CellRange 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();
}
Private Sub BindWeatherReportToRange(ByVal weatherDatasource As Object, ByVal bindingRange As CellRange)
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
See Also