corelibraries-devexpress-dot-dataaccess-dot-excel-11d0c10f.md
When implemented, provides the capability to customize the schema retrieved for an Excel/CSV file.
Namespace : DevExpress.DataAccess.Excel
Assembly : DevExpress.DataAccess.v25.2.dll
NuGet Package : DevExpress.DataAccess
public interface IExcelSchemaProvider
Public Interface IExcelSchemaProvider
The following members return IExcelSchemaProvider objects:
Tip
The ExcelSchemaProvider class is registered as a default implementation of the IExcelSchemaProvider service. You can inherit from this class to customize the settings required for generating an Excel data source.
This example shows how to customize the schema of the ExcelDataSource using the ExcelDataSource.Schema property. To do this, perform the following steps.
Create an ExcelDataSource with the required settings.
Retrieve the available fields (FieldInfo objects) using the IExcelSchemaProvider.GetSchema method.
Iterate through the collection of fields, specify their properties and add all fields to the ExcelDataSource.Schema collection.
// Creates an Excel data source and selects 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);
// Specifies the fields that will be available for the created data source.
IExcelSchemaProvider schemaProvider = excelDataSource.GetService(typeof(IExcelSchemaProvider))
as IExcelSchemaProvider;
FieldInfo[] availableFields = schemaProvider.GetSchema(excelDataSource.FileName, null,
ExcelDocumentFormat.Xlsx, excelDataSource.SourceOptions, System.Threading.CancellationToken.None);
List<string> fieldsToSelect = new List<string>() { "CategoryName", "ProductName", "Country", "Quantity",
"Extended Price"};
foreach (FieldInfo field in availableFields) {
if (fieldsToSelect.Contains(field.Name)) {
excelDataSource.Schema.Add(field);
}
else {
field.Selected = false;
excelDataSource.Schema.Add(field);
}
}
excelDataSource.Fill();
' Creates an Excel data source and selects the specific cell range from the SalesPerson worksheet.
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)
' Specifies the fields that will be available for the created data source.
Dim schemaProvider As IExcelSchemaProvider = TryCast(excelDataSource.GetService(GetType(IExcelSchemaProvider)), IExcelSchemaProvider)
Dim availableFields() As FieldInfo = schemaProvider.GetSchema(excelDataSource.FileName, Nothing, ExcelDocumentFormat.Xlsx, excelDataSource.SourceOptions, System.Threading.CancellationToken.None)
Dim fieldsToSelect As New List(Of String) (New String() {"CategoryName", "ProductName", "Country", "Quantity", "Extended Price"})
For Each field As FieldInfo In availableFields
If fieldsToSelect.Contains(field.Name) Then
excelDataSource.Schema.Add(field)
Else
field.Selected = False
excelDataSource.Schema.Add(field)
End If
Next field
excelDataSource.Fill()
See Also