ExcelDataSource Class
The data source that extracts data from Microsoft Excel workbooks or CSV files.
Namespace: DevExpress.DataAccess.Excel
Assembly: DevExpress.DataAccess.v24.1.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap
Declaration
[ToolboxBitmap(typeof(ExcelDataSource), "ExcelDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.ExcelDataSource.svg,DevExpress.DataAccess.v24.1, Version=24.1.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[ToolboxTabName("DX.24.1: Data & Analytics")]
[XRDesigner("DevExpress.DataAccess.UI.Design.XRExcelDataSourceDesigner,DevExpress.DataAccess.v24.1.UI, Version=24.1.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
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 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.
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();
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:
//...
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();
//...
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.