ExcelSchemaProvider Class

A default implementation of the IExcelSchemaProvider service.

Namespace: DevExpress.DataAccess.Excel

Assembly: DevExpress.DataAccess.v18.2.dll

Declaration

C#

public class ExcelSchemaProvider :
    IExcelSchemaProvider

VB.NET

Public Class ExcelSchemaProvider
    Implements IExcelSchemaProvider

Remarks

This service prepares schema for an ExcelDataSource using the following settings:

• TestRowCount

  Specifies the count of rows used to determine the column’s type. The GetSchema method sets a column type to the rows’ type if it is unique. Otherwise, string is set as the column’s type.

• ColumnBaseName

  Used to generate a column name in the following format: ColumnBaseName + column index.

You can inherit from the ExcelSchemaProvider class to provide custom settings for Excel data source schema generation. The following code demonstrates how to do this:

C#

public class CustomExcelSchemaProvider : DevExpress.DataAccess.Excel.ExcelSchemaProvider {
    public override int TestRowCount {
        get { return 5; }
    }
    public override int ColumnBaseName {
        get { return "MyColumn"; }
    }
}

VB.NET

Public Class CustomExcelSchemaProvider
    Inherits DevExpress.DataAccess.Excel.ExcelSchemaProvider

    Public Overrides ReadOnly Property TestRowCount() As Integer
        Get
            Return 5
        End Get
    End Property
    Public Overrides ReadOnly Property ColumnBaseName() As Integer
        Get
            Return "MyColumn"
        End Get
    End Property
End Class

The following code demonstrates how to register a custom Excel schema provider:

C#

using DevExpress.DataAccess.Excel;

private SqlDataSource BindToData() {
    var excelDataSource = new ExcelDataSource("Excel Data Source");
    excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/ExcelDataSource.xlsx");
    var worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L2000");
    excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);

    var serviceContainer = (System.ComponentModel.Design.IServiceContainer)excelDataSource;
    serviceContainer.RemoveService(typeof(DevExpress.DataAccess.Excel.IExcelSchemaProvider));
    serviceContainer.AddService(typeof(DevExpress.DataAccess.Excel.IExcelSchemaProvider), new CustomExcelSchemaProvider());

    excelDataSource.Fill();
}

VB.NET

Imports DevExpress.DataAccess.Excel

Private Function BindToData() As SqlDataSource
    Dim excelDataSource = New ExcelDataSource("Excel Data Source")
    excelDataSource.FileName = HostingEnvironment.MapPath("~/App_Data/ExcelDataSource.xlsx")
    Dim worksheetSettings = New ExcelWorksheetSettings("SalesPerson", "A1:L2000")
    excelDataSource.SourceOptions = New ExcelSourceOptions(worksheetSettings)

    Dim serviceContainer = DirectCast(excelDataSource, System.ComponentModel.Design.IServiceContainer)
    serviceContainer.RemoveService(GetType(DevExpress.DataAccess.Excel.IExcelSchemaProvider))
    serviceContainer.AddService(GetType(DevExpress.DataAccess.Excel.IExcelSchemaProvider), New CustomExcelSchemaProvider())

    excelDataSource.Fill()
End Function

Implements

IExcelSchemaProvider

Inheritance

Object

ExcelSchemaProvider