Binding to Excel Data Sources

• Feb 20, 2024
• 4 minutes to read

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, ExcelDataSource component, and in code.

Note

The ExcelDataSource works in read-only mode. It does not evaluate cell formulas, and you cannot read values from cells with formulas.

Data Source Configuration Wizard

Important

The ExcelDataSource wizard is not available for .NET projects. Please set up the ExcelDataSource in code.

How to Bind to Excel Data in Code

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 Data Source Wizard. The following gallery illustrates this process.

   

   1. Select the data source file

   

   2. Specify import settings

   

   3. Select the required worksheet, table or cell range.

   

   4. Select columns to be included to a data source

4. Click finish and run the application to see the result.

   

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 ‘Data Source Wizard’ dialog automatically pops up. Follow these steps to set up the connection. To invoke this wizard 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 or FillAsync method.

   C#

   private void Form1_Load(object sender, EventArgs e) {
       myExcelSource.Fill();
       //myExcelSource.FillAsync();
   }
   

   VB.NET

   Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
       myExcelSource.Fill()
       'myExcelSource.FillAsync()
   End Sub
   

Binding in Code

1. Create a new instance of the ExcelDataSource component.

   C#

   DevExpress.DataAccess.Excel.ExcelDataSource myExcelSource = new DevExpress.DataAccess.Excel.ExcelDataSource();
   

   VB.NET

   Dim myExcelSource As New DevExpress.DataAccess.Excel.ExcelDataSource()
   

2. Use the ExcelDataSource.FileName or ExcelDataSource.Stream properties to supply the component with a data source from a local storage or stream.

   C#

   myExcelSource.FileName = @"d:\Work\Excel DataSources\Book1.xlsx";
   

   VB.NET

   myExcelSource.FileName = "d:\Work\Excel DataSources\Book1.xlsx"
   

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.

   C#

   ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L100");
   myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);
   //or
   myExcelSource.SourceOptions = new CsvSourceOptions() { CellRange = "A1:L100" };
   

   VB.NET

   Dim worksheetSettings As New ExcelWorksheetSettings("SalesPerson", "A1:L100")
   myExcelSource.SourceOptions = New ExcelSourceOptions(worksheetSettings)
   'or
   myExcelSource.SourceOptions = New CsvSourceOptions() With {.CellRange = "A1:L100"}
   

4. Set additional import options if required.

   C#

   myExcelSource.SourceOptions.SkipEmptyRows = false;
   myExcelSource.SourceOptions.UseFirstRowAsHeader = true;
   

   VB.NET

   myExcelSource.SourceOptions.SkipEmptyRows = False
   myExcelSource.SourceOptions.UseFirstRowAsHeader = True
   

5. Assign your ExcelDataSource to the data-aware control and call the component’s Fill/FillAsync method to populate it with records.

   C#

   myExcelSource.Fill();
   gridControl1.DataSource = myExcelSource;
   

   VB.NET

   myExcelSource.Fill()
   gridControl1.DataSource = myExcelSource
   

   The complete code is listed below.

   C#

   using DevExpress.DataAccess.Excel;
   //. . .
   
   DevExpress.DataAccess.Excel.ExcelDataSource myExcelSource = new DevExpress.DataAccess.Excel.ExcelDataSource();
   myExcelSource.FileName = @"d:\Work\Excel DataSources\Book1.xlsx";
   ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L13");
   myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);
   //or
   myExcelSource.SourceOptions = new CsvSourceOptions() { CellRange = "A1:L100" };
   myExcelSource.SourceOptions.SkipEmptyRows = false;
   myExcelSource.SourceOptions.UseFirstRowAsHeader = true;
   myExcelSource.Fill();
   gridControl1.DataSource = myExcelSource;
   

   VB.NET

   Imports DevExpress.DataAccess.Excel
   '. . .
   
   Dim myExcelSource As New DevExpress.DataAccess.Excel.ExcelDataSource()
   myExcelSource.FileName = "d:\Work\Excel DataSources\Book1.xlsx"
   Dim worksheetSettings As New ExcelWorksheetSettings("SalesPerson", "A1:L13")
   myExcelSource.SourceOptions = New ExcelSourceOptions(worksheetSettings)
   'or
   myExcelSource.SourceOptions = New CsvSourceOptions() With {.CellRange = "A1:L100"}
   myExcelSource.SourceOptions.SkipEmptyRows = False
   myExcelSource.SourceOptions.UseFirstRowAsHeader = True
   myExcelSource.Fill()
   gridControl1.DataSource = myExcelSource
   

Convert ExcelDataSource to DataTable

The following example demonstrates how to ‘convert’ data from ExcelDataSource to a DataTable:

C#

public static class ExcelDataSourceExtension {  
    public static DataTable ToDataTable(this ExcelDataSource excelDataSource) {  
        IList list = ((IListSource)excelDataSource).GetList();  
        DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list;  
        List<PropertyDescriptor> props = dataView.Columns.ToList<PropertyDescriptor>();  
        DataTable table = new DataTable();  
        for(int i = 0; i < props.Count; i++) {  
            PropertyDescriptor prop = props[i];  
            table.Columns.Add(prop.Name, prop.PropertyType);  
        }  
        object[] values = new object[props.Count];  
        foreach(DevExpress.DataAccess.Native.Excel.ViewRow item in list) {  
            for(int i = 0; i < values.Length; i++) {  
                values[i] = props[i].GetValue(item);  
            }  
            table.Rows.Add(values);  
        }  
        return table;  
    }  
}