Lendo um arquivo Excel via ADO.NET (C#, VB...) e importando-o para um DataTable
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...
Leia o restante deste post...
