VB .NET - Gerenciando dados do Excel 2003

Este artigo tem como objetivo permitir que você, através do VB .NET, gerencie dados de uma planilha Excel na versão 2003.

Por que você faria isso ???

Boa pergunta, garoto...

Aprendi uma coisa na área de TI: nunca diga: "Jamais vou precisar disso..."

Dessa forma nosso objetivo é bem modesto e talvez você nem leia este artigo até essas linhas mas se um dia você precisar deste recurso vai dar conta do recado.

Antes de iniciar vou dar uma explicação resumida sobre o projeto:

Abaixo vemos a interface da nossa aplicação VB .NET:

Observe que temos algumas informações exibida no controle DataGridView e as mesmas informações exibidas como detalhes nas caixas de texto do formulário.

Note que temos botões de navegação que permitem que percorramos os registros e também botões que irão acionar rotinas que permitem: Incluir, Alterar, Procurar registros.

A fonte das informações que vemos exibidas na interface do nosso programa Visual Basic é uma planilha Excel versão 2003 chamada Clientes.xls que esta na pasta c:\dados.

Abaixo vemos a figura da planilha Excel - Clientes.xls que a nossa fonte de dados:

Como estamos acessando essa planilha ?

A planilha esta sendo acessada através de um provedor OleDb por  meio do qual estamos criando uma conexão com a planilha.

Por este motivo nosso projeto irá precisar usar o namespace : System.Data.Oledb

Com isso você não vai precisar ter o Excel instalado na sua máquina.

Outro ponto importante é que como não temos uma chave primária na coluna da planilha do Excel, e  temos que definir uma chave primária na tabela que estamos criando, pois vamos procurar registros na tabela usando o método Find. Veja o exemplo do código usado para definir uma chave primária:

ds.Tables(0).Constraints.Add("pk_id", ds.Tables(0).Columns(0), True)

Temos que fazer isso pois vamos usar o método Find da coleção Rows() para procurar um registro na planilha.

Dim drow As DataRow = ds.Tables(0).Rows.Find(n)

Nota: Se tivéssemos usando um banco de dados ao invés da planilha o código abaixo teria o mesmo efeito:

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Isso posto vamos ao projeto...

Eu estou usando o Visual Basic 2008 Express Edition e uma planilha do Excel 2003 no nosso caso o arquivo clientes.xls.

Abra o VB 2008 Express e crie um novo projeto do tipo Windows Application com o nome AcessoExcel;

No formulário padrão form1.vb inclua os controles :

• 1 DatagridView - gdvClientes
• 3 Labels e 3 TextBox : txtCodigo, txtNome e txtEmail;
• 4 Botões para navegação: btnPrimeiro,btnAnterior, btnProximo e btnUltimo
• 4 Botões para operações: btnIncluir, btnAlterar, btnProcurar , btnLimpar
• 1 Botão para Encerrar : btnSair

o leiaute do formulário pode ser visto na figura abaixo:

Vamos usar os eventos de cada botão para neles incluir o código que realizar uma operação.

Antes disso temos que declarar as variáveis usadas no projeto:

'variaveis usadas para acessar a planilha e executar os comandos de manutenção
Dim con As OleDbConnection
Dim da As OleDbDataAdapter
Dim cmd As OleDbCommand
Dim ds As DataSet
'usada para controlar a posição do registro
Dim reg As Integer = 0

1- Evento Load do formulário: Neste evento inclua o código:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;data source=C:\dados\Clientes.xls;Extended Properties=""Excel 8.0;HDR=Yes;"" ")
        'Clientes.xls ->MS.Excel-2003, a definição hdr=yes-> indica que a primeira linha é considerada cabeçalho
        carregadados()
        exibeDados()
    End Sub

Estabelecemos a conexão com a planilha e carregamos e exibimos os dados chamando as rotinas: carregaDados e exibeDados;

2- A seguir temos a rotina carregaDados:

Private Sub carregadados()
        da = New OleDbDataAdapter("select * from [Clientes$]", con)
        'Clientes-> nome da planilha no arquivo CLientes.xls, deve ser indicado como [Clientes$]
        ds = New DataSet()
        da.Fill(ds, "Clientes")
        'criando uma chave primária na tabela [Clientes] do dataset para usar o método Find
        ds.Tables(0).Constraints.Add("pk_id", ds.Tables(0).Columns(0), True)
        'exibindo os dados no datagridview
        gdvClientes.DataSource = ds.Tables(0)
    End Sub

Selecione os dados da planilha e cria um dataset preenchendo-o com os dados a seguir define a chave primária do datatable e exibe os dados no controle DataGridView;

3- Abaixo vemos o código da  rotina exibeDados;

Private Sub exibeDados()
        txtCodigo.Text = ds.Tables(0).Rows(reg)(0).ToString()
        txtNome.Text = ds.Tables(0).Rows(reg)(1).ToString()
        txtEmail.Text = ds.Tables(0).Rows(reg)(2).ToString()
    End Sub

O código apenas atribui os valores das colunas da tabela referente ao registro atual aos controles do formulário;

4-  Para Incluir uma nova linha na planilha usamos  seguinte rotina:

Private Sub btnIncluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncluir.Click
        'inclui dados na planilha
        cmd = New OleDbCommand((("insert into [Clientes$] values(" + txtCodigo.Text & ",' ") + txtNome.Text & " ',' ") + txtEmail.Text & " ') ", con)
        con.Open()
        Dim n As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If n > 0 Then
            MessageBox.Show("Registro inserido com sucesso")
            carregadados()
        Else
            MessageBox.Show("Inclusão de registro falhou")
        End If
    End Sub

Definimos uma instrução SQL Insert into e excutamos o comando usando o método ExecuteNonQuery() para incluir as informações;

5- Para Alterar as informações de uma linha existente na planilha usamos o código a seguir:

 Private Sub btnAlterar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAlterar.Click
        'atualiza dados na planilha
        cmd = New OleDbCommand(((" update [Clientes$] set nome='" + txtNome.Text & "',email='") + txtEmail.Text & "' where id=") + txtCodigo.Text & " ", con)
        con.Open()
        Dim n As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If n > 0 Then
            MessageBox.Show("Registro atualizado com sucesso.")
            carregadados()
        Else
            MessageBox.Show("Atualização de registro falhou.")
        End If
    End Sub

Definimos uma instrução SQL Update/Set e excutamos o comando usando o método ExecuteNonQuery() para alterar as informações;

6- Para Procurar um registro usamos o seguinte código:

Private Sub btnProcurar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcurar.Click
        Try
            Dim n As Integer = Convert.ToInt32(InputBox("Informe o código do Cliente:", "Procurar", "2", 400, 400))
            Dim drow As DataRow = ds.Tables(0).Rows.Find(n)
            If drow IsNot Nothing Then
                txtCodigo.Text = drow(0).ToString()
                txtNome.Text = drow(1).ToString()
                txtEmail.Text = drow(2).ToString()
            Else
                MessageBox.Show("Registro não encontrado na planilha")
            End If
        Catch ex As Exception
            'O objetivo de não tratar erro e deixar a aplicação continuar a executar (isso não é uma boa pratica)
        End Try
    End Sub

Usamos a função InputBox para solicitar o código do cliente a ser procurado e usamos o método Find para localizá-lo exibindo as informações nas caixas de texto.

7- Para limpar as caixas de texto usamos esta rotina:

 Private Sub btnLimpar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLimpar.Click
        txtCodigo.Text = ""
        txtNome.Text = ""
        txtEmail.Text = ""
    End Sub

8- Navegação pelos registros é feita usando o evento Click dos botões de navegação através da verificação da existência de linhas na tabela e em seguida da obtenção da posição atual do registro. A rotina exibeDados é chamada para mostrar os dados.

Private Sub btnPrimeiro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrimeiro.Click
        If ds.Tables(0).Rows.Count > 0 Then
            reg = 0
            exibeDados()
            MessageBox.Show("Primeiro Registro")
        Else
            MessageBox.Show("Não há registros")
        End If
    End Sub

    Private Sub btnAnterior_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnterior.Click
        If ds.Tables(0).Rows.Count > 0 Then
            If reg > 0 Then
                reg -= 1
                exibeDados()
            Else
                MessageBox.Show("Primeiro Registro")
            End If
        Else
            MessageBox.Show("Não há registros")
        End If
    End Sub

    Private Sub btnProximo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProximo.Click
        If ds.Tables(0).Rows.Count > 0 Then
            If reg < ds.Tables(0).Rows.Count - 1 Then
                reg += 1
                exibeDados()
            Else
                MessageBox.Show("Último Registro")
            End If
        Else
            MessageBox.Show("Não há registros")
        End If
    End Sub

    Private Sub btnUltimo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUltimo.Click
        If ds.Tables(0).Rows.Count > 0 Then
            reg = ds.Tables(0).Rows.Count - 1
            exibeDados()
            MessageBox.Show("Último registro")
        Else
            MessageBox.Show("Não há registros")
        End If
    End Sub

E assim podemos realizar a manutenção dos dados da planilha em uma aplicação VB .NET sem ter o Excel instalado , precisamos apenas da planilha.

Eu sei é apenas VB .NET, mas eu gosto...

Pegue o projeto completo aqui: AcessoExcel.zip

Referências:

• Excel e VB - Importando e Exportando dados - Macoratti.net

• VB - Convertendo uma planilha Excel para Access - Macoratti.net

• Gerando planilhas no Excel - José Carlos Macoratti - ASP

• VBA - Cadastro de Clientes com envio de Email no Excel

José Carlos Macoratti