ExcelDataSource Class

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

Namespace : DevExpress.DataAccess.Excel

Assembly : DevExpress.DataAccess.v25.2.dll

NuGet Package : DevExpress.DataAccess

Declaration

C#
VB.NET

csharp

[ToolboxBitmap(typeof(ExcelDataSource), "ExcelDataSource.bmp")]
public class ExcelDataSource :
    DataComponentBase,
    IListSource,
    IListAdapterAsync,
    IListAdapter,
    ISupportFillAsync

<ToolboxBitmap(GetType(ExcelDataSource), "ExcelDataSource.bmp")>
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 in a stream.

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

The ExcelDataSource can only read plain text data from Excel worksheets. It cannot load formatted text, formulas, or floating elements such as images.

The ExcelDataSource supports only flat Excel tables.

You can use the ExcelDataSource to supply data to the GridControl, TreeList, ChartControl or any other DevExpress control that that you can set up with Data Source Configuration Wizard.

Design-Time Specifics

After you add the ExcelDataSource component to an application, you can configure the data connection with commands available in the component’s smart tag.

Edit Invokes the Excel Data Source editor that allows you to select the Excel workbook/CSV file and specify import settings. Rebuild Result Schema Updates the data source result schema to match the current data source structure.

Run-Time Specifics

Steps to Configure the ExcelDataSource

Do the following to extract data from the specified workbook or CSV file in code:

Assign the file path to the ExcelDataSource.FileName property. As an alternative, load the required document from a stream using the ExcelDataSource.Stream property. In this case, you should specify the file format with the ExcelDataSource.StreamDocumentFormat property.
Depending on the target file format, create the ExcelSourceOptions or CsvSourceOptions class instance, specify its settings, and assign the resulting object to the ExcelDataSource.SourceOptions property.
Call the ExcelDataSource.Fill method to fill the created data source with data.

The following code creates the ExcelDataSource and selects a cell range in the SalesPerson worksheet.

C#
VB.NET

csharp

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

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

How to Implement a Custom Excel Schema Provider

You can use a custom Excel schema provider that implements the IExcelSchemaProvider interface.

You can change settings of the built-in Excel schema provider. For this, create the ExcelSchemaProvider class descendant and override its methods.

The following code registers the MyExcelSchemaProvider custom provider:

C#
VB.NET

csharp

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

'...
Dim serviceContainer = DirectCast(excelDataSource, System.ComponentModel.Design.IServiceContainer)
serviceContainer.RemoveService(GetType(DevExpress.DataAccess.Excel.IExcelSchemaProvider))
serviceContainer.AddService(GetType(DevExpress.DataAccess.Excel.IExcelSchemaProvider), New MyExcelSchemaProvider())

excelDataSource.Fill()
'...

Web Specifics

In web applications, the Resources.AccessSettings.DataResources option manages access to the source XLS, XLSX, and CSV files for the ExcelDataSource instance.

The DevExpress ASP.NET Project Wizard creates a Web Forms application that specifies the AccessSettings.DataResources option and allows you to use only the Content folder as the data source file location.

Implements

IDataComponent

Inheritance

Object MarshalByRefObject Component DataComponentBase ExcelDataSource DashboardExcelDataSource