Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
  [Expand]Scheduler (legacy)
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[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, Charts Suite, 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 doesn't 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?​​​​​​​