ExcelDataSource Class

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

Namespace: DevExpress.DataAccess.Excel

Assembly: DevExpress.DataAccess.v19.2.dll

Declaration

[XRDesigner("DevExpress.DataAccess.UI.Design.XRExcelDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.ExcelDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.ExcelDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
public class ExcelDataSource :
    DataComponentBase,
    IListSource,
    IListAdapterAsync,
    IListAdapter,
    ISupportFillAsync
<XRDesigner("DevExpress.DataAccess.UI.Design.XRExcelDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", GetType(IDesigner))>
<ToolboxBitmap(GetType(ResFinder), "Bitmaps256.ExcelDataSource.bmp")>
<ToolboxSvgImage("DevExpress.DataAccess.Images.ExcelDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")>
Public Class ExcelDataSource
    Inherits DataComponentBase
    Implements 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();
//...

Examples

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();

Implements

Inheritance

Object
MarshalByRefObject
Component
DevExpress.DataAccess.DataComponentBase
ExcelDataSource

Extension Methods

DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource()
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource(UserLookAndFeel)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource(UserLookAndFeel, IWin32Window, IExcelSchemaProvider)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource(IWizardRunnerContext, IExcelSchemaProvider)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource(EditDataSourceContext)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.EditDataSource<TModel>(EditDataSourceContext, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.UpdateSchema(IWin32Window, UserLookAndFeel, IExcelSchemaProvider)
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.UpdateSchema()
DevExpress.DataAccess.UI.Excel.ExcelDataSourceUIHelper.UpdateSchema(UpdateSchemaContext)
See Also