IExcelSchemaProvider.GetSchema(String, Stream, ExcelDocumentFormat, ExcelSourceOptionsBase, CancellationToken) Method

Returns the schema of an Excel data source.

Namespace: DevExpress.DataAccess.Excel

Assembly: DevExpress.DataAccess.v24.1.dll

NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap

Declaration

C#

FieldInfo[] GetSchema(
    string fileName,
    Stream stream,
    ExcelDocumentFormat streamDocumentFormat,
    ExcelSourceOptionsBase optionsBase,
    CancellationToken token
)

VB.NET

Function GetSchema(
    fileName As String,
    stream As Stream,
    streamDocumentFormat As ExcelDocumentFormat,
    optionsBase As ExcelSourceOptionsBase,
    token As CancellationToken
) As FieldInfo()

Parameters

Name	Type	Description
fileName	String	A String value.
stream	Stream	A Stream object.
streamDocumentFormat	ExcelDocumentFormat	An ExcelDocumentFormat enumeration value.
optionsBase	ExcelSourceOptionsBase	An ExcelSourceOptionsBase descendant.
token	CancellationToken	A CancellationToken structure.

Returns

Type	Description
FieldInfo[]	An array of FieldInfo objects.

Example

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.

C#

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

VB.NET

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