O NeoMatrix Tech está de casa nova!

Você deverá ser redirecionado em 6 segundos. Se não, visite:
http://www.leonelfraga.com/neomatrixtech
e atualize seus favoritos.

Aviso IMPORTANTÍSSIMO!

Aviso aos navegantes:

O NeoMatrix Tech mudou de casa!!!

A partir de agora, acessem pelo novo endereço:

http://www.leonelfraga.com/neomatrixtech

Ué... mas é só o domínio mudou de lugar?

R: Na verdade, não é bem assim hehe. Este domínio que você acessa agora aponta para um blog hospedado no Blogger, enquanto no novo, aponta para um blog na plataforma Wordpress, hospedagem própria, muito mais rápida e com um layout mais agradável de ler ;)

Não vou fechar este domínio igual ao que eu fiz com o NM Light (que já está 100% na nova plataforma). Talvez beeeeeeem depois eu faça isso.

Todos os posts daqui se encontram lá, e novos posts serão colocados somente no novo endereço.
A única coisa que não consegui importar foram os comentários. Mas em breve vai ter um post contando sobre a epopéia que foi migrar o NeoMatrix Tech!

Somente vou fechar a área de comentários daqui. Caso queiram comentar, favor ver o post correspondente no "Novo NeoMatrix Tech" e comentem por lá. É bem melhor! (pena que os permalinks "amigáveis para SEO" não funcionam lá, dá erro 404 e não consigo fazer a configuração funcionar. E olha que eu já vi vários artigos falando desse assunto :( ).

Quem assina o feed, já está lendo o conteúdo do novo NeoMatrix Tech!

sábado, 31 de janeiro de 2009

Lendo um arquivo Excel via ADO.NET (C#, VB...) e importando-o para um DataTable

excel Muitas vezes os sistemas necessitam que dados sejam atualizados por uma planilha do Excel.

Isto acontece porque a visualização e a edição de dados neste programa (e semelhantes) é mais produtiva para o usuário do que pelo nosso sistema. E quando muitos dados precisam ser inseridos ou atualizados, a produtividade com certeza é muito maior mesmo.

Então, nada mais lógico do que implementar uma funcionalidade para importar dados desta fonte para os nossos sistemas!

Criaremos um método em .NET (usarei o C# para demonstrar) que lê uma planilha Excel e importa seus dados em um DataTable, estrutura a qual já estamos bastante familiarizados, e a partir dele, podemos atualizar nosso banco de dados.

Acompanhe o código deste método:

   1: /// <summary>
   2: /// Importa os dados de uma planilha Excel para um datatable
   3: /// </summary>
   4: /// <param name="pWorksheetPath">Caminho do arquivo xls</param>
   5: /// <param name="pPlanName">Nome da planilha a ser importada.</param>
   6: /// <returns></returns>
   7: protected DataTable ExcelPlan2DataTable(string pWorksheetPath, string pPlanName)
   8: {
   9:     string cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", pWorksheetPath);
  10:     string isql = "select * from [{0}$]";
  11:     System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString);
  12:     System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(String.Format(isql, pPlanName), cnn);
  13:     DataSet ds = new DataSet();
  14:     DataTable dt = new DataTable();
  15:     try
  16:     {
  17:         cnn.Open();
  18:         da.Fill(ds);
  19:         dt = ds.Tables[0];
  20:     }
  21:     finally
  22:     {
  23:         cnn.Close();
  24:         cnn.Dispose();
  25:         da.Dispose();
  26:         ds.Dispose();
  27:     }
  28:     return dt;
  29: }

Como você pode ver, a coisa é bem mais simples do que parece, o segredo mesmo está na string de conexão utilizada:

   1: @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;"""

Ela utiliza o driver OLEDB do Microsoft Excel versão 8 (podem ser lidas planilhas até a versão 2003 - formato XLS -, o 2007 usa outro formato, o XLSX - OO XML), onde o parâmetro Data Source é o path completo da planilha a ser lida. Esta planilha NÃO deve estar aberta quando fomos requisitar ela; a mesma coisa ocorre quando esta planilha está aberta em nossa aplicação, o Windows Explorer dá uma "travada" quando vamos ver este arquivo.

Com a string de conexão, criamos os objetos do ADO.NET Connection e DataAdapter (do namespace System.Data.OleDb) da mesma forma como fazemos com um banco de dados qualquer.

No construtor do DataAdapter passamos dois parâmetros (na sobrecarga que estamos utilizando): o primeiro é a instrução SQL de Select, e o segundo é o objeto Connection.

Nesta instrução SQL para obter os dados, o nome da tabela na cláusula FROM será o nome de uma planilha da pasta de trabalho (um arquivo XLS, que é uma pasta de trabalho, pode ter N planilhas dentro dele), e o mesmo deve ser acrescido de cifrão ($) no final, e estar entre colchetes. Supondo que você queira pegar os dados da planilha PLAN1, seu nome deve estar escrito assim na cláusula FROM: [PLAN1$].

Depos disso, criamos um DataSet, e através do DataAdapter preenchemos este DataSet com o método Fill.

Com isso, será criada uma tabela no DataSet com os dados da planilha, e enfim, o retorno do método será esta tabela. No código acima, eu criei um objeto DataTable para pegar esta tabela e retornar.

Simples demais, não é? Veremos mais para frente como inserir e atualizar dados, e até criar novas planilhas via ADO.NET. E também fazer uma aplicação ASP.NET para importar e exportar planilhas. Mas já vou adiantando: trabalhe IGUAL como trabalha com um banco de dados! Simples assim mesmo. As únicas coisas que mudaram foram a string de conexão e a maneira como escrevemos o nome da tabela ;-)

PS: Quando procurei por esta solução quando eu estava precisando, achei ela em primeiro lugar neste site, do David Hayden. Um outro adendo interessante, é que antes já fiz coisa igual no Delphi, utilizando o driver ODBC na string de conexão e os componentes da palheta ADO (ADOConnection, ADOQuery, etc). O mesmíssimo processo, a mesma forma de fazer o Select...

5 comentários:

João Henrique 23 de set. de 2009, 18:03:00  

Eu utilizei este seu codigo em uma planilha de teste para depois implantar em meu sistema. Mas a planilha só tem 4 linhas(registro) no excel e atraves do dt.Rows.Count eu percebi que ele carrega 16 linhas, ou seja, 12 vazias.

Tem alguma dica para tal?

Leonel Fraga de Oliveira 24 de set. de 2009, 21:45:00  

@João Henrique:

Você pode fazer o seguinte: Abra a planilha no Excel, selecione as doze linhas em branco (selecione por inteiro, clicando no número da linha, e não somente as células que fazem parte da tabela!) e exclua (botão direito no número das linhas selecionadas). Pode parecer "surreal" isso, mas acho que deve funcionar.

A 1ª linha da planilha é um "cabeçalho", onde estarão os nomes dos campos. O restante para baixo, os registros.

Caso isso não funcione, você poderá tratar os campos via código mesmo, evitando que ele seja inserido na tabela.

Um abraço e valeu pela mensagem!

Anônimo,  19 de nov. de 2009, 10:59:00  

Amigão show de bola !!! Vai me livrar de uma dor de cabeça grande hehehehe !!! Parabéns !!!

Abraços
Glauber

Troyhost 28 de nov. de 2009, 10:32:00  

Amigo seria possivel criar uma conexão para .xlsx (excel 2007)??? e postar ai pra nos.


Postar um comentário

Para tornar este artigo ainda mais interessante, escreva suas críticas (desde que construtivas e sem ofenças), elogios, sugestões, complementos, dúvidas, etc, etc, etc!!!

  © Blogger templates ProBlogger Template by Ourblogtemplates.com 2008 - Editado e configurado por Leonel F.

Voltar ao TOPO