ExcelSourceOptions Class
Contains options used to extract data from the Microsoft Excel workbook.
Namespace: DevExpress.DataAccess.Excel
Assembly: DevExpress.DataAccess.v23.2.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap
Declaration
Remarks
The ExcelDataSource class exposes the ExcelDataSource.SourceOptions property that accepts the ExcelSourceOptionsBase descendants (for instance, the ExcelSourceOptions objects). The ExcelSourceOptions class contains different options used to import data from the Microsoft Excel workbook.
The ExcelSourceOptions.ImportSettings property exposed by the ExcelSourceOptions class allows you to specify the approach used to import data from a workbook. For instance, you can select all data from the specified worksheet or specify the required cell range. To import data from the workbook, create the required ExcelSettingsBase object descendant and specify its settings.
- To select all data from the entire worksheet, create the ExcelWorksheetSettings object and specify the worksheet name using the ExcelWorksheetSettings.WorksheetName property. If necessary, you can select the required range of cells using the ExcelWorksheetSettings.CellRange property.
- To select a range of cells specified using the defined name, create the ExcelDefinedNameSettings object and specify the defined name using the ExcelDefinedNameSettings.DefinedName property. If applicable, specify a defined name scope using the ExcelDefinedNameSettings.Scope property.
- To select data from the predefined table, create the ExcelTableSettings object and specify the table name using the ExcelTableSettings.TableName property.
The ExcelSourceOptions class also exposes the ExcelSourceOptions.Password property allowing you to specify the password used to access the protected workbook.
The following properties affect different options related to data extracting.
- The ExcelSourceOptionsBase.UseFirstRowAsHeader property specifies whether to process the values of the first row as headers.
- The ExcelSourceOptionsBase.SkipEmptyRows property allows you to skip empty rows.
- The ExcelSourceOptions.SkipHiddenColumns and ExcelSourceOptions.SkipHiddenRows properties allow you to ignore hidden columns and rows when importing data.
Example
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();