Comment : Récupérer des métadonnées à partir d'Excel à l'aide de la méthode GetOleDbSchemaTable dans Visual Basic .NET

Cet article étape par étape explique comment récupérer la table et les métadonnées de colonne à partir de sources de données Microsoft Excel en utilisant la méthode GetOleDbSchemaTable avec le fournisseur Microsoft OLE DB géré et le Fournisseur Microsoft OLE DB pour Jet.

La méthode GetOleDbSchemaTable exposée par la classe System.Data.OleDb de Microsoft.NET Framework est le successeur de .NET à la méthode OpenSchema dans les versions antérieures d'objets de données de Microsoft ActiveX (ADO).

Description de la Technique

Une fois que vous vous connectez à une source de données Excel à l'aide de ADO.NET, vous extraire la liste des métadonnées de table à l'aide de GetOleDbSchemaTableet puis utiliser la même méthode avec différents arguments pour obtenir métadonnées de colonne de la table sélectionnée. Vous pouvez également utiliser un objet DataGridTableStyle pour présenter et mettre en forme les résultats de votre requête de données de grille.

Configuration requise

La liste suivante met en évidence le matériel recommandé, logiciel, infrastructure réseau et service packs dont vous aurez besoin :

• Visual Studio .NET est installé sur un Microsoft compatible Système d'exploitation Windows
• Au moins un fichier de classeur (.xls) de Microsoft Excel avec certains lignes et colonnes de données

Cet article suppose que vous avez une connaissance de base au moins avec les rubriques suivantes :

• Visual Basic .NET
• Accès aux données ADO.NET
• Feuilles de calcul et classeurs Excel

Échantillon

1. Démarrez Microsoft Visual Studio .NET et créez un nouveau Visual Base du projet Application Windows .NET.
2. Ajoutez trois contrôles de bouton et deux contrôles DataGrid au formulaire par défaut (Form1). Modifiez la propriété Text des contrôles de bouton pour Récupérer les métadonnées, Liste des Tables de Formatet Liste des colonnes de Format respectivement.
3. Basculez vers le module de code du formulaire et ajoutez les instructions Imports suivantes en haut :

   Imports System.Data
   Imports System.Data.OleDb
   Imports System.ComponentModel

4. Insérez les déclarations suivantes au niveau du module dans la classe de formulaire après la ligne « Inherits System.Windows.Forms.Form ». Ajuster fichier de la chaîne de connexion que nécessaire pour pointer vers un classeur Excel contient certaines lignes et colonnes de données.

       Dim cn As OleDbConnection
       Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=c:\test.xls;Extended Properties=Excel 8.0"
       Dim dtTables As DataTable
       Dim WithEvents cm As CurrencyManager
       Dim dtColumns As DataTable
       Dim dvColumns As DataView

5. Insérez le code suivant dans la classe de formulaire après la région « Code généré par le Concepteur Windows Form ». Ce code appelle GetOleDbSchemaTable pour charger les listes de tableau et de colonnes, remplit les contrôles DataGrid et liste des colonnes mises à jour lorsque la table sélectionnée modifications.

       Private Sub Button1_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles Button1.Click
           Call GetTablesList()
       End Sub
   
       Private Sub GetTablesList()
           cn = New OleDbConnection(strCn)
           cn.Open()
           dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
           DataGrid1.DataSource = dtTables
           DataGrid1.ReadOnly = True
           cn.Close()
           Call GetColumnsList()
       End Sub
   
       Private Sub GetColumnsList()
           If cm Is Nothing Then
               cm = CType(Me.BindingContext(dtTables), CurrencyManager)
           End If
           Dim r As Integer = cm.Position
           Dim strTable As String = dtTables.Rows(r)("TABLE_NAME")
           cn = New OleDbConnection(strCn)
           cn.Open()
           dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
               New Object() {Nothing, Nothing, strTable, Nothing})
           dvColumns = New DataView(dtColumns)
           dvColumns.Sort = "ORDINAL_POSITION"
           DataGrid2.DataSource = dvColumns
           DataGrid2.ReadOnly = True
           cn.Close()
       End Sub
   
       Private Sub cm_PositionChanged(ByVal sender As Object, _
               ByVal e As System.EventArgs) Handles cm.PositionChanged
           Call GetColumnsList()
       End Sub

6. Insérez le code suivant pour mettre en forme et mettre en forme les Tables DataGrid en utilisant TableStyles. Notez l'utilisation de PropertyDescriptor pour faciliter la mise en forme par défaut des colonnes date.

       Private Sub Button2_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles Button2.Click
           Call FormatTablesGrid(dtTables)
       End Sub
   
       Private Sub FormatTablesGrid(ByVal dt2format As DataTable)
           Dim gs As DataGridTableStyle = New DataGridTableStyle()
           gs.MappingName = dt2format.TableName
   
           Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()
           With cs
               .MappingName = "TABLE_NAME"
               .HeaderText = "Table Name"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           cs = New DataGridTextBoxColumn()
           With cs
               .MappingName = "TABLE_TYPE"
               .HeaderText = "Table Type"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           Dim cm As CurrencyManager = CType(Me.BindingContext(dt2format), CurrencyManager)
           Dim pd As PropertyDescriptor = cm.GetItemProperties()("DATE_CREATED")
   
           cs = New DataGridTextBoxColumn(pd, "d")
           With cs
               .MappingName = "DATE_CREATED"
               .HeaderText = "Date Created"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           cm = CType(Me.BindingContext(dt2format), CurrencyManager)
           pd = cm.GetItemProperties()("DATE_MODIFIED")
   
           cs = New DataGridTextBoxColumn(pd, "d")
           With cs
               .MappingName = "DATE_MODIFIED"
               .HeaderText = "Date Modified"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           DataGrid1.TableStyles.Add(gs)
           Me.Button2.Enabled = False
   
       End Sub

7. Insérez le code suivant pour disposer et formater les colonnes DataGrid à l'aide de TableStyle:

       Private Sub Button3_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles Button3.Click
           Call FormatColumnsGrid(dtColumns)
       End Sub
   
       Private Sub FormatColumnsGrid(ByVal dt2format As DataTable)
           Dim gs As DataGridTableStyle = New DataGridTableStyle()
           gs.MappingName = dtColumns.TableName
   
           Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()
           With cs
               .MappingName = "COLUMN_NAME"
               .HeaderText = "Column Name"
               .Width = 100
           End With
           gs.GridColumnStyles.Add(cs)
   
           cs = New DataGridTextBoxColumn()
           With cs
               .MappingName = "ORDINAL_POSITION"
               .HeaderText = "Ordinal Position"
               .Width = 100
           End With
           gs.GridColumnStyles.Add(cs)
   
           cs = New DataGridTextBoxColumn()
           With cs
               .MappingName = "DATA_TYPE"
               .HeaderText = "Data Type"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           cs = New DataGridTextBoxColumn()
           With cs
               .MappingName = "CHARACTER_MAXIMUM_LENGTH"
               .HeaderText = "Text Length"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           cs = New DataGridTextBoxColumn()
           With cs
               .MappingName = "NUMERIC_PRECISION"
               .HeaderText = "Numeric Precision"
               .Width = 75
           End With
           gs.GridColumnStyles.Add(cs)
   
           DataGrid2.TableStyles.Add(gs)
           Me.Button3.Enabled = False
   
       End Sub

8. Exécutez le projet.
9. Cliquez sur Récupérer les métadonnées pour remplir la liste des Tables (DataGrid1) avec toutes les colonnes d'informations qui sont renvoyées pour chaque table dans le classeur Excel par GetOleDbSchemaTable. La liste des colonnes (DataGrid2) est remplie en même temps avec toutes les colonnes d'informations qui sont retournées pour les colonnes du premier tableau dans la liste des Tables .
10. Sélectionnez une autre table dans la liste Tables . Les modifications de liste de colonnes pour afficher les colonnes de la table sélectionnée en raison de gestionnaire d'événements cm_PositionChanged que vous avez implémenté.
11. Cliquez sur Liste des Tables de Format pour définir et appliquer un TableMappingStyle à DataGrid1. Ainsi les en-têtes de colonne plus « conviviales » et affiche les quatre colonnes d'utile données à partir des neuf colonnes qui sont retournées par GetOleDbSchemaTable. Ces colonnes sont affichées :
    NOM_TABLE
    TABLE_TYPE
    DATE_CREATED
    DATE_DE_MODIFICATION
    Ces colonnes de cellules vides ne sont pas affichées :
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_GUID
    DESCRIPTION
    TABLE_PROPID
12. Cliquez sur Liste des colonnes de Format pour définir et d'appliquer un TableMappingStyle à DataGrid2. Ceci rend les en-têtes de colonne plus « convivial » et affiche uniquement les 5 plus utiles colonnes de données à partir de 28 colonnes qui sont retournées par GetOleDbSchemaTable.
    NOM_COLONNE
    POSITION ORDINALE
    TYPE_DE_DONNÉES
    MAXIMUM_CHARACTER_LENGTH
    NUMERIC_PRECISION
    Ces colonnes essentiellement vides ne sont pas affichées :
    TABLE_CATALOG
    TABLE_SCHEMA
    Table_name (déjà connue)
    COLUMN_GUID
    COLUMN_PROPID
    COLUMN_HASDEFAULT (toujours la valeur false)
    COLUMN_DEFAULT
    COLUMN_FLAGS
    IS_NULLABLE (toujours true)
    TYPE_GUID
    CHARACTER_OCTET_LENGTH
    NUMERIC_SCALE
    DATETIME_PRECISION
    CHARACTER_SET_CATALOG
    CHARACTER_SET_SCHEMA
    CHARACTER_SET_NAME
    COLLATION_CATALOG
    COLLATION_SCHEMA
    COLLATION_NAME
    DOMAIN_CATALOG
    DOMAIN_SCHEMA
    NOM_DOMAINE
    DESCRIPTION
13. Cliquez sur une autre ligne dans DataGrid1 pour sélectionner une autre table. La liste de colonnes est automatiquement mis à jour. Le TableStyles que vous DataGrid précédemment appliquée à chaque restent en vigueur.

Résolution des problèmes

• Les types de données de colonne sont retournées par GetOleDbSchemaTable pour une source de données Excel ne sont pas identiques dans tous les cas à la types de données retournés par la méthode OpenSchema dans ADO classique :
  Réduire ce tableauAgrandir ce tableau

  Type de colonne	ADO classique	ADO.NET (OLE BASE DE DONNÉES)
  Numérique	5 - adDouble	5- OleDbType.Double
  Devise	6 - adCurrency	6- OleDbType.Currency
  Date/heure	7 - adDate	7- OleDbType.Date
  Valeur booléenne	11 - adBoolean	11- OleDbType.Boolean
  Texte<>	202 - adVarWChar	130- OleDbType.WChar
  Mémo	203 - adLongVarWChar	130- OleDbType.WChar

• GetOleDbSchemaTablecomme OpenSchema, renvoie une seule colonne "F1" à partir d'une feuille de calcul Excel vide Bien qu'en réalité aucun en-tête de colonne ou de données n'est présents.

Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article dans la Base de connaissances Microsoft :