Spreadsheet Data Binding
- 5 minutes to read
This topic introduces the main concepts of data binding in the Spreadsheet Document API. You can bind a specific cell range in a worksheet to a data source or use a cell range as a data source for any DevExpress or third-party data-aware control (Data Grid, Chart Control, etc.). Data binding is two-way by default.
Worksheet data bindings are WorksheetDataBinding objects stored in the WorksheetDataBindingCollection collection, which can be accessed using the Worksheet.DataBindings property.
Bind a Cell Range to a Data Source
The Spreadsheet Document API provides the WorksheetDataBindingCollection.BindToDataSource method overloads that let you bind a specific cell range in a worksheet to a data source. The following data source types are available:
- DataTable
- BindingList<T>
- The IBindingList, ITypedList, or IEnumerable interface implementation
Important
The Spreadsheet does not support hierarchical data sources, i.e., data sources with master-detail relationships set up between lists of objects.
A bound object should have the default constructor. Otherwise, the bound data source is considered as read-only. If a worksheet range is bound to a read-only data source, the range itself is also read-only and its data cannot be updated. If a worksheet range is bound to a data source that allows changing notifications, such as a data source with the IBindingList interface, the worksheet range has a number of rows synchronized with the number of data records. The worksheet automatically inserts or deletes rows when the 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.
Refer to the following example for code snippets:
Specify Data Binding Options
An ExternalDataSourceOptions class instance is passed to the WorksheetDataBindingCollection.BindToDataSource method and contains various options that allow you to control how data is retrieved from an external data source.
| Option | Description |
|---|---|
| DataSourceOptionsBase.CellValueConverter | Allows you to specify a converter that converts data to cell values and back. |
| ExternalDataSourceOptions.ImportHeaders | Retrieves data field names and displays them in the first row of the specified worksheet range. |
| DataSourceOptionsBase.SkipHiddenColumns | Allows you to skip hidden columns while importing data from the data source. |
| DataSourceOptionsBase.SkipHiddenRows | Allows you to skip hidden rows while importing data from the data source. |
Bind a Worksheet Table to a Data Source
To create a worksheet Table bound to the data source, use the WorksheetDataBindingCollection.BindTableToDataSource method. It returns the newly created worksheet table bound to the specified data source. The method also creates a WorksheetTableDataBinding object and adds it to the Worksheet.DataBindings collection.
The WorksheetDataBindingCollection.BindTableToDataSource method extends the WorksheetDataBindingCollection.BindToDataSource method, described earlier in this document, to worksheet tables. It uses the same parameters and returns an object that is the WorksheetDataBinding class descendant.
When the data binding is deleted, the table remains in the worksheet filled with data. When the table is deleted, the data binding is also deleted.
Use a Cell Range as a Data Source
To use a cell range as a data source for a data-aware control, create a data source object from the required cell range using the WorksheetDataBindingCollection.CreateDataSource method or the equivalent CellRange.GetDataSource method. The created data source object implements the IBindingList, ITypedList, ICancelAddNew and IDisposable interfaces and can be used to send/receive data to/from any data-aware control that supports the mentioned interfaces.
If RangeDataSourceOptions.UseFirstRowAsHeader is false and RangeDataSourceOptions.DataSourceColumnTypeDetector is not specified, field names are generated automatically (each field in the created data source has the default name “Column 0”, “Column 1”, etc.) and the field type is automatically determined based on the value and number format of the first existing cell of the corresponding column in the data source range.
A RangeDataSourceOptions class inherits from DataSourceOptionsBase and provides different options that allow you to control how data is extracted from the worksheet.
| Option | Description |
|---|---|
| DataSourceOptionsBase.CellValueConverter | Allows you to specify a converter that converts cell values to custom objects and vice versa. |
| DataSourceOptionsBase.SkipHiddenColumns | Allows you to specify whether to include hidden columns into the resulting data source. |
| DataSourceOptionsBase.SkipHiddenRows | Allows you to specify whether to include hidden rows into the resulting data source. |
| RangeDataSourceOptions.PreserveFormulas | Allows you to restrict editing formulas in the data source range. |
| RangeDataSourceOptions.EditingOptions | Allows you to specify the editing options for the resulting data source. |
| RangeDataSourceOptions.UseFirstRowAsHeader | Specifies whether to use the text contained in the first row cells of the data source range as column headers. |
| RangeDataSourceOptions.DataSourceColumnTypeDetector | Allows you to explicitly specify the name and type of each column in the resulting data source. |
| RangeDataSourceOptions.CellValueComparer | Allows you to specify the comparer used to sort cell values in the data source columns. |
Use a Worksheet Table as a Data Source
To use a worksheet Table as a data source for a data-aware control, create a data source object from the required cell range using the Table.GetDataSource method.
The method and its parameters are similar to the CellRange.GetDataSource method described previously in this document.
Examples
The following examples illustrate the data binding functionality of the Spreadsheet Document API.