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,
ISupportFillAsyncRemarks
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.
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.
- 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.
- Assign the path to the required file using the ExcelDataSource.FileName property. As an alternative, load the required document from a stream using the ExcelDataSource.Stream property. In this case, you need to specify the required file format using 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.
- Use the ExcelDataSource.Fill method to fill the created data source with data.
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();