VB.NET - Importando dados de uma planilha Excel

No artigo  - VB.NET - Acessando dados de aplicações Office - mostrei como acessar dados de uma planilha Excel, entre outras tarefas. Neste outro artigo - VB .NET - Exibindo dados de uma planilha Excel em uma página ASP.NET. -  mostrei como fazer a mesma tarefa com ASP.NET. Atendendo milhares de pedidos vou mostrar como fazer a mesma coisa usando VB.NET.

Suponha que você tenha uma planilha chamada categorias.xls que contém os dados (que eu ajeitei) da tabela Categorias. Abaixo uma visão da planilha aberta no Excel:

planilha categorias.xls

Suponha que você precisa por que precisa acessar estes dados via VB.NET e exibir os dados em um componente datagrid.

Bom, a primeira coisa que você terá que fazer é criar um novo projeto no VS.NET do tipo Windows Application usando a linguagem VB.NET.

No formulário padrão você vai incluir um componente DataGrid e um Botão de comando conforme a figura abaixo:

Nome dos componentes usados:

DataGrid - dgExcel

Button - btnExcel

No início do formulário inclua a declaração imports que irá usar o provedor OleDb pois vamos acessar os dados via DataAdapter usando um DataTable.

Imports System.Data.OleDb

A seguir logo abaixo da declaração do formulário inclua o código que irá criar as variáveis objeto e definir a string de conexão com o Excel.

Private da As OleDbDataAdapter

Private dt As DataTable

Dim conexao_Excel As String = "Provider=Microsoft.Jet.OleDb.4.0;data source=d:\teste\Categorias.xls;Extended Properties=Excel 8.0;"

Na declaração da string de conexão observe que eu apenas defini o caminho de localização da planilha e usei a propriedade Extended Properties=Excel 8.0;

Eu estou usando o Excel 2000 , se você estiver usando uma versão mais recente pode alterar para Extended Properties=Excel 9.0; ou posterior.

Finalmente o código que vai fazer a importação será colocado no evento Click do botão de comando Atualizar:

Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click


' cria um novo dataadapter

da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conexao_Excel)

'

' preenche a tabela com dados da planilha Excel

dt = New DataTable

da.Fill(dt)


' define a chave primaria

dt.PrimaryKey = New DataColumn() {dt.Columns(0)}


' registros somente podem ser inseridos usando esta tecnica

dt.DefaultView.AllowDelete = False

dt.DefaultView.AllowEdit = True

dt.DefaultView.AllowNew = True
 

' vincula o modo de visao padrão da tabela ao datagrid

dgExcel.DataSource = dt.DefaultView

End Sub

 

Eu apenas estou acessando a planilha - Sheet1$ - (se o seu Excel for em português será Plan1) e usando uma instrução SQL Select para selecionar todos os dados da planilha usando a conexão com o Excel - conexao_Excel - já definida.

A seguir eu estou criando um objeto DataTable e preenchendo a tabela via DataAdapter ja criado anteriormente.

Para saber mais sobre o DataTable leia o artigo : VB .NET - ADO.NET - Uma visão geral II : DataTable.

Defini a chave primária como sendo a coluna zero ou seja primeira coluna , e , atribui algumas propriedades ao DataTable que permitirão a edição de dados.

Para exibir os dados no datagrid é só usar a linha de código  :  dgExcel.DataSource = dt.DefaultView

O resultado será :

Pegue o projeto completo aqui : ExcelNet.zip

Da mesma forma que eu acessei os dados eu posso também atualizar e incluir dados na planilha via DataTable. Por isto defini as propriedades AllowEdit e AllowNew como True. Se você quiser implementar isto basta usar como exemplos os comandos abaixo:

1- Incluindo dados

Comando para inserir dados na planilha

'' cria o comando para inserir

Dim insertSql As [String] = "INSERT INTO [Sheet1$] " & "(Categoria, Nome, Descricao) " & "VALUES (?, ?, ?)"

da.InsertCommand = New OleDbCommand(insertSql, da.SelectCommand.Connection)

da.InsertCommand.Parameters.Add("@Categoria", OleDbType.Integer, 0, "Categoria")

da.InsertCommand.Parameters.Add("@Nome", OleDbType.Char, 15, "Nome")

da.InsertCommand.Parameters.Add("@Descricao", OleDbType.VarChar, 100, "Descricao")

 

2- Atualizando dados

 

Código para atualizar dados da planilha

'' cria o comando para atualizar

Dim updateSql As [String] = "UPDATE [Sheet1$] " & "SET Nome=?, Descricao=? " & "WHERE Categoria=?"

da.UpdateCommand = New OleDbCommand(updateSql, da.SelectCommand.Connection)

da.UpdateCommand.Parameters.Add("@Nome", OleDbType.Char, 15, "Nome")

da.UpdateCommand.Parameters.Add("@Descricao", OleDbType.VarChar, 100, "Descricao")

da.UpdateCommand.Parameters.Add("@Categoria", OleDbType.Integer, 0, "Categoria")

O resto é com você ...

Eu sei é apenas VB.NET , e daí , eu gosto ....

Referências:

José Carlos Macoratti