Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Data Binding

This topic introduces the main concepts of data binding in the Spreadsheet Control. 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.

Expanded Bind a Cell Range to a Data Source

The Spreadsheet Control provides the WorksheetDataBindingCollection.BindToDataSource method overloads that let you bind a specific cell range in a worksheet to a data source. The data source can be a DataTable, BindingList<T> class instance, or an implementer of the IBindingList, ITypedList, or IEnumerable interface.

Important

The Spreadsheet Control does not support hierarchical data sources, i.e., data sources with master-detail relationships set up between lists of objects.

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 System.ComponentModel.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.

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.

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

Expanded 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 Range.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.

Expanded 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 Range.GetDataSource method described previously in this document.

Expanded Examples

How would you rate this topic?​​​​​​​