COMO: Obter metadados a partir do Excel utilizando o método GetOleDbSchemaTable no Visual Basic .NET

Este artigo passo a passo descreve como obter metadados de tabelas e colunas de origens de dados Microsoft Excel utilizando o método GetOleDbSchemaTable com o fornecedor Microsoft OLE DB geridos e o Microsoft OLE DB Provider for Jet.

O método GetOleDbSchemaTable exposto pela classe System.data.OLEDB do Microsoft .NET Framework é o sucessor do .NET para o método OpenSchema em versões anteriores do Microsoft ActiveX Data Objects (ADO).

Descrição da técnica

Depois de ligar a uma origem de dados do Excel utilizando o ADO.NET, que extrair uma lista de tabela de metadados utilizando GetOleDbSchemaTable e, em seguida, utilize o mesmo método com argumentos diferentes para obter metadados de coluna para a tabela seleccionada. Também pode utilizar um objecto DataGridTableStyle para esquematizar e formatar resultados da consulta na grelha de dados.

Requisitos

A lista seguinte descreve o hardware recomendado, software, infra-estrutura de rede e service packs, terá de:

• O Visual Studio .NET instalado num sistema operativo Microsoft Windows compatível
• Pelo menos um ficheiro do livro (.xls) do Microsoft Excel com algumas linhas e colunas de dados

Este artigo pressupõe que tenha, pelo menos, básica familiaridade com os seguintes tópicos:

• Visual Basic .NET
• Acesso a dados ADO.NET
• Livros do Excel e folhas de cálculo

Exemplo

1. Inicie o Microsoft Visual Studio .NET e crie um novo projecto de aplicação do Visual Basic .NET Windows.
2. Adicione três controlos do botão e dois controlos DataGrid ao formulário predefinido (Form1). Altere a propriedade texto dos controlos botão Obter metadados , Formato de lista de tabelas e Formato de lista de colunas respectivamente.
3. Mude para o módulo de código do formulário e adicionar as seguintes instruções importações na parte superior:

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

4. Inserir as seguintes declarações de nível de módulo da classe de formulário depois a linha "System.Windows.Forms.Form herda". Ajustar a cadeia de ligação conforme necessário para apontar para um ficheiro de livro do Excel que contém algumas linhas e colunas de dados.

       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. Insira o código seguinte na classe de formulário depois da região "Criador de formulários Windows gerado código". Este código chama GetOleDbSchemaTable para carregar as listas de tabelas e colunas, preenche os controlos DataGrid e actualiza a lista de colunas quando altera a tabela seleccionada.

       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. Insira o seguinte código para esquematizar e formatar tabelas DataGrid utilizando TableStyles . Tenha em atenção a utilização de PropertyDescriptor para facilitar a formatação não predefinida das colunas data.

       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. Insira o seguinte código para esquematizar e formatar as colunas DataGrid utilizando 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. Execute o projecto.
9. Clique em Obter metadados para preencher a lista de tabelas (DataGrid1) com todas as colunas de informações que são devolvidas para cada tabela no livro do Excel por GetOleDbSchemaTable . A lista de colunas (DataGrid2) é preenchida em simultâneo com todas as colunas de informações que são devolvidas para as colunas da primeira tabela na lista tabelas .
10. Seleccione uma tabela diferente na lista tabelas . As alterações de lista de colunas apresentam as colunas da tabela seleccionada devido o processador de eventos cm_PositionChanged é implementado.
11. Clique em Lista de tabelas de formatar para definir e aplicar um TableMappingStyle DataGrid1. Isto faz com que os cabeçalhos das colunas mais "amigável" e apresenta apenas as quatro colunas de dados útil das nove colunas retornadas pelo GetOleDbSchemaTable . Estas colunas são apresentadas:
    TABLE_NAME
    TABLE_TYPE
    DATE_CREATED
    DATE_MODIFIED
    Estas colunas em branco não são apresentadas:
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_GUID
    DESCRIÇÃO
    TABLE_PROPID
12. Clique em Lista de colunas de formatar para definir e aplicar um TableMappingStyle DataGrid2. Isto faz com que os cabeçalhos das colunas mais "amigável" e apresenta apenas as 5 útil colunas de dados das 28 colunas que são devolvidos pela GetOleDbSchemaTable .
    NOME_COLUNA
    ORDINAL_POSITION
    DATA_TYPE
    MAXIMUM_CHARACTER_LENGTH
    NUMERIC_PRECISION
    Estas colunas principalmente em branco não são apresentadas:
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_NAME (já conhecida)
    COLUMN_GUID
    COLUMN_PROPID
    COLUMN_HASDEFAULT (sempre false)
    COLUMN_DEFAULT
    COLUMN_FLAGS
    IS_NULLABLE (sempre 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
    NOME_DOMÍNIO
    DESCRIÇÃO
13. Clique numa linha diferente na DataGrid1 para seleccionar uma tabela diferente. A lista de colunas é actualizada automaticamente. TableStyles que aplicou anteriormente cada DataGrid permanecem em vigor.

Resolução de problemas

• Os tipos de dados de coluna que são devolvidos por GetOleDbSchemaTable para uma origem de dados do Excel não são idênticos em todos os casos os tipos de dados que são devolvidos pelo método OpenSchema no ADO clássico:
  Reduzir esta tabelaExpandir esta tabela

  Tipo de coluna	ADO clássico	ADO.NET (OLE DB)
  Numérico	5 adDouble	5 OleDbType.Double
  Moeda	adCurrency 6	OleDbType.Currency 6
  Data/hora	adDate 7	OleDbType.Date 7
  Valor boleano	11 adBoolean	11 OleDbType.Boolean
  Texto < 255	202 adVarWChar	130 OleDbType.WChar
  Nota	203 adLongVarWChar	130 OleDbType.WChar

• GetOleDbSchemaTable , tal como OpenSchema , devolve uma única coluna "F1" de uma folha de cálculo vazia do Excel, apesar de facto serem presentes não cabeçalhos de coluna ou dados.

Para obter informações adicionais, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base: