COMO: Recuperar metadados do Excel usando GetOleDbSchemaTable no Visual translation from VPE for Csharp .NET

Use este guia passo a passo para recuperar metadados de coluna e tabela de uma fonte de dados do Microsoft Excel usando o método GetOleDbSchemaTable com o provedor Microsoft OLE DB gerenciado e o Microsoft OLE DB Provider for Jet.

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

Descrição da técnica

Depois você se conectar a uma fonte de dados do Excel usando o Microsoft ADO.NET, extrai uma lista de tabela de metadados usando o método GetOleDbSchemaTable . Em seguida, use o mesmo método com argumentos diferentes para obter metadados de coluna para a tabela selecionada. Você também pode usar o método DataGridTableStyle para dispor e formatar os resultados da consulta em uma grade de dados.

Requisitos

A lista a seguir descreve o hardware recomendado, software, infra-estrutura de rede e service packs que você precisa:

• Microsoft Visual Studio .NET instalado em um compatível com sistema operacional Microsoft Windows.
• Pelo menos um arquivo do Microsoft Excel pasta de trabalho (.xls) com algumas linhas e colunas de dados.

Este artigo pressupõe que você esteja familiarizado com os seguintes tópicos:

• Microsoft Visual translation from VPE for Csharp .NET.
• Acesso de dados do Microsoft ADO.NET.
• Planilhas e pastas de trabalho do Microsoft Excel.

Exemplo

1. Inicie o Visual Studio.NET e crie um novo projeto do Visual translation from VPE for Csharp Windows Forms Application.
2. Adicione três controles Button e dois controles DataGrid ao Form1. Altere a propriedade Text dos controles de botão para nomear os botões Recuperar metadados , Lista de tabelas de formato e Lista de colunas de formato , respectivamente.
3. Alterne para o módulo de código para o formulário e adicione as instruções a seguir antes de qualquer outro código:

   using System.ComponentModel;
   using System.Data;
   using System.Data.OleDb;
   					

4. A classe de formulário , inserir as seguintes declarações de nível de módulo. A conexão, aponte para um arquivo de pasta de trabalho do Excel que contém algumas linhas e colunas de dados de seqüência.

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

5. Na classe de formulário , após a seção de código do Windows Form Designer gerada, insira o código a seguir. Este código chama GetOleDbSchemaTable para carregar a tabela e coluna lista, preenche DataGrids e atualiza a lista de colunas quando altera a tabela selecionada.

   private void button1_Click(object sender, System.EventArgs e)
   {
           GetTablesList();	
   }
   private void GetTablesList()
   {
   	try
   	{
   	        cn = new OleDbConnection(strCn);
   		cn.Open();
   		dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null,null,null,"TABLE"});
   		dataGrid1.DataSource = dtTables;
   		dataGrid1.ReadOnly = true;
   		cn.Close();
   	}
   	catch(System.Data.OleDb.OleDbException myException)
   	{
   		for (int i=0; i < myException.Errors.Count; i++)
   		{
   			MessageBox.Show("Index #" + i + "\n" +
   			"Message: " + myException.Errors[i].Message + "\n" +
   			"Native: " +                          
   myException.Errors[i].NativeError.ToString() + "\n" +
   			"Source: " + myException.Errors[i].Source + "\n" +
   			"SQL: " + myException.Errors[i].SQLState + "\n");
   		}
   	}
               
   	GetColumnsList();
   }
   
   private void GetColumnsList()
   {	
   	if( cm == null)
   		cm = (CurrencyManager)this.BindingContext [dtTables];
                   
   	cm.PositionChanged += new EventHandler(this.cm_PositionChanged );
   			
   	int r = cm.Position;
   
           String strTable = dtTables.Rows[r]["TABLE_NAME"].ToString ();
           cn = new OleDbConnection(strCn);
   	cn.Open();
   	dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]{null,null, strTable, null});
           dvColumns = new DataView(dtColumns);
           dvColumns.Sort = "ORDINAL_POSITION";
           dataGrid2.DataSource = dvColumns;
   	dataGrid2.ReadOnly = true;
   	cn.Close();
   }
   		
   private void cm_PositionChanged( object sender, System.EventArgs e)
   {
   	GetColumnsList();
   }
   								
   					

6. Insira o código a seguir para definir o layout e Formatar tabelas DataGrid usando TableStyles . Observe o uso do PropertyDescriptor para facilitar a formatação não padrão das colunas de data.

   private void button2_Click(object sender, System.EventArgs e)
   {
   	FormatTablesGrid(dtTables);
   }
   
   private void FormatTablesGrid(DataTable dt2format)
   {
   	DataGridTableStyle gs = new DataGridTableStyle();
           gs.MappingName = dt2format.TableName;
               
   	DataGridColumnStyle cs = new DataGridTextBoxColumn();
               
   	cs.MappingName = "TABLE_NAME";
   	cs.HeaderText = "Table Name";
   	cs.Width = 75;
   		
   	gs.GridColumnStyles.Add(cs);
   			
   	cs = new DataGridTextBoxColumn();
   	cs.MappingName = "TABLE_TYPE";
   	cs.HeaderText = "Table Type";
   	cs.Width = 75;
   		
   	gs.GridColumnStyles.Add(cs);
   
   	CurrencyManager cm = (CurrencyManager)this.BindingContext[dt2format];
   			
   	PropertyDescriptor pd = cm.GetItemProperties()["DATE_CREATED"];
   	
   	cs = new DataGridTextBoxColumn(pd, "d");
           cs.MappingName = "DATE_CREATED";
   	cs.HeaderText = "Date Created";
           cs.Width = 75;
   			
   	gs.GridColumnStyles.Add(cs);
   
   	cm = ( CurrencyManager)this.BindingContext[dt2format];
   	pd = cm.GetItemProperties()["DATE_MODIFIED"];
   
   	cs = new DataGridTextBoxColumn(pd, "d");
           cs.MappingName = "DATE_MODIFIED";
           cs.HeaderText = "Date Modified";
           cs.Width = 75;
   			
   	gs.GridColumnStyles.Add(cs);
   
   	dataGrid1.TableStyles.Add(gs);
   	button2.Enabled = false;
   
   }
   					

7. Insira o código a seguir para fazer o layout e formatar as colunas DataGrid usando TableStyle :

           private void button3_Click(object sender, System.EventArgs e)
   	{
   		FormatColumnsGrid(dtTables);
   	}
   		
   		
           private void FormatColumnsGrid(DataTable dt2format)
   	{
   	    DataGridTableStyle gs = new DataGridTableStyle();
               gs.MappingName = dtColumns.TableName;
               
   	    DataGridColumnStyle cs = new DataGridTextBoxColumn();
               cs.MappingName = "COLUMN_NAME";
               cs.HeaderText = "Column Name";
               cs.Width = 100;
   
   	    gs.GridColumnStyles.Add(cs);
   
   	    cs = new DataGridTextBoxColumn();
               cs.MappingName = "ORDINAL_POSITION";
               cs.HeaderText = "Ordinal Position";
               cs.Width = 100;
   
   			gs.GridColumnStyles.Add(cs);
   
               cs = new DataGridTextBoxColumn();
               cs.MappingName = "DATA_TYPE";
   	    cs.HeaderText = "Data Type";
   	    cs.Width = 75;
   
   	    gs.GridColumnStyles.Add(cs);
   
               cs = new DataGridTextBoxColumn();
               cs.MappingName = "CHARACTER_MAXIMUM_LENGTH";
               cs.HeaderText = "Text Length";
   	    cs.Width = 75;
   
   	    gs.GridColumnStyles.Add(cs);
   
               cs = new DataGridTextBoxColumn();
               cs.MappingName = "NUMERIC_PRECISION";
               cs.HeaderText = "Numeric Precision";
   	    cs.Width = 75;
   
   	    gs.GridColumnStyles.Add(cs);
               dataGrid2.TableStyles.Add(gs);
   
               button3.Enabled = false;
   
   	}
   					

8. Execute o projeto.
9. Clique em recuperar metadados (Button1).
   
   A lista de tabelas (DataGrid1) é preenchida com todas as colunas de informações retornadas para cada tabela na pasta de trabalho Excel pelo método GetOleDbSchemaTable . Preenche a lista de colunas (DataGrid2) ao mesmo tempo com todas as colunas de informações retornadas para as colunas da primeira tabela na lista tabelas .
10. Na lista tabelas , selecione uma tabela diferente.
    
    A lista de colunas mostra as colunas da tabela selecionada como resultado do manipulador de eventos cm_PositionChanged .
11. Clique em lista de tabelas de formato (Button2). Isso define e aplica um TableMappingStyle para DataGrid1, tornando os cabeçalhos de coluna mais amigável e exibir somente as quatro colunas de dados úteis
    TABLE_NAME
    TABLE_TYPE
    DATE_CREATED
    DATE_MODIFIED
    em vez das nove colunas retornadas pelo método GetOleDbSchemaTable .
    
    As colunas em branco que não são exibidas são:
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_GUID
    DESCRIÇÃO
    TABLE_PROPID
12. Clique em lista de colunas de formato (Button3). Isso define e aplica um TableMappingStyle para DataGrid2, tornando os cabeçalhos de coluna mais amigável e exibir somente as cinco colunas mais úteis de dados COLUMN_NAME ORDINAL_POSITION DATA_TYPE MAXIMUM_CHARACTER_LENGTH NUMERIC_PRECISION das 28 colunas retornado pelo método GetOleDbSchemaTable .
    
    As colunas que não são exibidas são:
    
    
    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_DO_DOMÍNIO
    DESCRIÇÃO
13. Clique em uma linha diferente na DataGrid1 para selecionar uma tabela diferente. A lista de colunas é atualizada automaticamente. TableStyles aplicadas anteriormente a cada DataGrid permanecerão em vigor.

Solução de problemas

• Os tipos de dados de coluna retornados pelo método GetOleDbSchemaTable para uma fonte de dados do Excel não são os mesmos em todos os casos, os tipos de dados retornados pelo método OpenSchema em versões anteriores do ADO:
  Recolher esta tabelaExpandir esta tabela

  Tipo de coluna	ADO Clássico	ADO.NET (OleDb)
  Numérico	5 adDouble	5 OleDbType.Double
  Moeda	6 adCurrency	6 OleDbType.Currency
  Data/hora	adDate 7	OleDbType.Date 7
  Booleano	adBoolean 11	OleDbType.Boolean 11
  Texto < 255	202 adVarWChar	130 OleDbType.WChar
  Memorando	203 adLongVarWChar	130 OleDbType.WChar

  
  
• GetOleDbSchemaTable , semelhante ao OpenSchema , retorna uma única coluna ("F1") de uma planilha do Excel vazia quando, na verdade, não títulos de coluna ou dados estão presentes.

Para obter informações adicionais, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft: