Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Expand]Controls and Libraries
 [Collapse]Common Features
  [Collapse]Data Binding Common Concepts
    How to: Bind GridControl to Database and Implement Master-Detail Mode at Design Time
    How to: Bind a Control to a Database at Runtime
    How to: Bind a Control to Data Created at Runtime
    How to: Bind a Control to Data in an XML File
    Traditional Data Binding Methods
    Data Source Configuration Wizard
    Data Binding Mechanism in ADO.NET
    Binding Controls to XML Data
    Binding Controls to Data Created at Runtime
    Add Unbound Data to a Data-Aware Control (XtraGrid, XtraPivotGrid)
    Binding to Excel Data Sources
    Binding to LINQ to SQL Classes
    Unbound Sources
    Binding to Entity Framework Core
    Binding to OData
    Binding to SQL Data
    Data Annotation Attributes
    How to: Add Virtual Rows Using the UnboundSource Component
    Obtaining Fields Available in Data Source
  [Expand]Data Source Wizard
  [Expand]Expressions
  [Expand]Behaviors
  [Expand]Application Appearance
  [Expand]Filtering UI Context
  [Expand]High DPI Support
  [Expand]Scaffolding Wizard
  [Expand]Formatting Values
   HTML Text Formatting
  [Expand]Menus
  [Expand]Tooltip Management
  [Expand]Saving and Restoring Layouts
   Clipboard - Copy Data and Formatting
   Version Compatibility: Default Property Values
  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

Binding to Excel Data Sources

This document illustrates how to bind to data from Microsoft Excel workbooks (XLS, XLSX or XLSM files) or CSV files using the Data Source Configuration Wizard, the ExcelDataSource component and in code.

Expanded Data Source Configuration Wizard

  1. Invoke the smart tag for the required data-aware DevExpress control and select the 'Data Source Wizard' option.

  2. On the wizard main page, select the 'Excel Data Source' option. Click 'New Data Source' to create a new data source or select the required data source from the list, then click 'Next' to proceed.

  3. Select the required data source and set advanced options using the 'Excel Data Source Editor' dialog. The following gallery illustrates this process.

    • First, select required data source from a local storage. In case the selected workbook is password protected, you will need to enter this password to proceed.
    • On the next page, check the import settings that will be used to load the data from the selected file. This page differs depending on your data source file format. For Excel workbooks (.XLS, .XLSX and .XLSM files), this page contains the following options.
      • Use values of the first row as field names - specifies whether or not values of the first row should be treated as field names. If you disable this option, field names will be generated automatically.
      • Skip empty rows - uncheck this setting to allow importing rows even if they are empty.
      • Skip hidden rows, Skip hidden columns - these options specify whether or not hidden columns and rows should be ignored.

      If your data is stored in a .CSV file, this page will look differently and contain a few more import options.

      • Trim blanks - specifies whether or not to remove all leading and trailing white-space characters from each value in the CSV document.
      • Encoding - specifies the character encoding of the CSV document. This and the two following settings can be detected automatically if related options are checked.
      • Newline type - specifies the character that identifies a new line in a CSV document.
      • Value separator - sets a character that separates values.
      • Culture - specifies the culture information used to parse imported data.
      • Text qualifier - sets the character that encloses document values.
    • For Excel workbooks, you can select the worksheet containing the required data, the table or the defined name referring to the specified cell range.
    • On the final wizard page, select columns that will be included to a data source. You can also rename columns and change their types on this page.
  4. Click finish and run the application to see the result.

Expanded The ExcelDataSource Component

Using the ExcelDataSource component, you can provide data from Excel data sources for any data-aware control that does not support the Data Source Configuration Wizard.

  1. Locate the ExcelDataSource component on the toolbox under the 'Data&Analytics' group and drop it onto your form.

  2. When you add the ExcelDataSource component to an application form, the 'Excel Data Source Editor' dialog automatically pops up. Follow the same steps as those for binding using the Data Source Configuration Wizard: select the data source file, provide import settings and choose required columns to be included to a data source. To invoke this dialog manually, use the component's smart-tag as shown below.

  3. Assign the customized ExcelDataSource component as the data source for the target control. For instance, for the Data Grid control, use the GridControl.DataSource property.

  4. The ExcelDataSource component does not retrieve records automatically. Thus, to populate your data control, call the Fill method as the code below shows.

Expanded Binding in Code

  1. Create a new instance of the ExcelDataSource component.
  2. Use the ExcelDataSource.FileName or ExcelDataSource.Stream properties to supply the component with a data source from a local storage or stream.
  3. Depending on the data source type (Excel workbook or CSV file), create either a ExcelSourceOptions or CsvSourceOptions object. Using this object's properties, you can specify a cell range to be loaded. Assign the object created to the ExcelDataSource.SourceOptions property.
    Important

    You must provide the ExcelWorksheetSettings, ExcelTableSettings or ExcelDefinedNameSettings objects for each ExcelSourceOptions class instance.

  4. Set additional import options if required.
  5. Assign your ExcelDataSource to the data-aware control and call the component's Fill method to populate it with records.
  6. The complete code is listed below.

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