ExcelDataSource Class

The data source that extracts data from Microsoft Excel workbooks or CSV files.

Namespace: DevExpress.DataAccess.Excel

Assembly: DevExpress.DataAccess.v21.2.dll

Declaration

[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.ExcelDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.ExcelDataSource.svg,DevExpress.DataAccess.v21.2, Version=21.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[XRDesigner("DevExpress.DataAccess.UI.Design.XRExcelDataSourceDesigner,DevExpress.DataAccess.v21.2.UI, Version=21.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
public class ExcelDataSource :
    DataComponentBase,
    IListSource,
    IListAdapterAsync,
    IListAdapter,
    ISupportFillAsync

Remarks

The ExcelDataSource component allows you to extract data from Microsoft Excel workbooks (XLS, XLSX or XLSM files) or CSV files stored on the disk or stream. Use it to provide data access to a GridControl, TreeList, ChartControl or any other DevExpress control that can be configured via the Data Source Configuration Wizard.

excel-data-source-toolbox

Note

The ExcelDataSource works in read-only mode.

Design-Time Specifics

After you have added the ExcelDataSource component to an application, you can configure the data connection by using the commands available in the component’s smart tag.

ExcelDataSource_SmartTag

  • Edit… - Invokes the Excel Data Source editor that allows you to select the required Excel workbook/CSV file and configure various import settings.
  • Rebuild Result Schema - Updates the result schema of a data source available on the client according to the current data source structure.

Run-Time Specifics

To extract data from the specified workbook or CSV file in code, do the following.

You can use a custom Excel schema provider implementing the IExcelSchemaProvider interface. In addition, you can change settings the predefined Excel schema provider uses. For this purpose, inherit from the ExcelSchemaProvider class and override its methods.

//...
var serviceContainer = (System.ComponentModel.Design.IServiceContainer)excelDataSource;
serviceContainer.RemoveService(typeof(DevExpress.DataAccess.Excel.IExcelSchemaProvider));
serviceContainer.AddService(typeof(DevExpress.DataAccess.Excel.IExcelSchemaProvider), new MyExcelSchemaProvider());

excelDataSource.Fill();
//...

Example

This example shows how to create an ExcelDataSource and select the specific cell range from the SalesPerson worksheet.

ExcelDataSource excelDataSource = new ExcelDataSource();
excelDataSource.Name = "Excel Data Source";
excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/ExcelDataSource.xlsx");
ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L2000");
excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);
excelDataSource.Fill();

The following code snippets (auto-collected from DevExpress Examples) contain references to the ExcelDataSource class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

Implements

See Also