VB.NET - Acessando dados em aplicações Office

VB.NET - Acessando dados de aplicações Office

Acessar dados de aplicações Office (neste artigo estarei me reportando apenas ao Access e ao Excel) não tem segredo. Você vai seguir os mesmos procedimentos usados para acessar dados no .NET : criar uma conexão , criar DataReaders ou DataAdapters , criar DataSets , etc.

Acessando dados do Microsoft Access

Para se conectar com um banco de dados Access você vai precisar criar um objeto OleDbConnection e usar uma string de conexão que indica a fonte de dados que você deseja acessar e o nome do provedor que você esta usando. A string de conexão básica para acessar um banco de dados Access é :

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\teste\Clientes.mdb"

aqui estamos definindo duas propriedades : Provider e Data Source.

Mas e se o seu banco de dados Access estiver protegido com senha ? Qual a string de conexão ?

Neste caso a string de conexão ficaria assim :

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\teste\Clientes.mdb ; " & _
"Jet OLEDB:System database=C:\teste\system.mdw; " & _
" User ID=IdentificacaoUsuario;Password=SenhaUsuario"

aqui estamos definindo o arquivo .mdw responsável pela segurança do arquivo mdb usando a propriedade Jet OLE DB e definindo a identificação do usuário e sua senha nas propriedades : User ID e Password.

Definida a string de conexão para obter dados da aplicação Office , no caso o arquivo mdb , vou utilizar os objetos : OleDbConnection , OleDbCommand e OleDbDataReader. (Não vou utilizar DataAdapter e DataSets pois vou apenas obter e exibir os dados)

Para obter os dados vou usar instruções SQL e como vamos acessar uma base de dados Access é bom que você tenha em mente que a sintaxe para instruções SQL para o Access não é 100% compatível com a do SQL Server.

O problema principal esta nas expressões de critério , onde você vai decidir qual dados deseja obter. Geralmente nestas expressões usamos a cláusula WHERE seguido das expressões com valores.

Então lembre-se que no Access valores para datas e horas devem ser envolvidos com sinal (#) . Ex: SELECT nome FROM Clientes WHERE datapedido > #10/10/04# ; valores não numéricos devem ser envolvidos por aspas simples (') - Ex: SELECT Pedido FROM Clientes WHERE nome = ' " & txtNome.text & " ' ".

Vamos agora criar uma aplicação VB.NET no Visual Studio .NET do tipo Windows Application para extrair dados da tabela clientes do banco de dados Access Northwind.mdb. O formulário da aplicação irá conter uma combobox , um ListBox e um button.

Não esqueça de incluir o import para o namespace System.Data.OleDb para ter acesso aos dados:

imports System.Data.OleDb

A aplicação irá exibir no controle ListBox o nome das empresas e dos contatos selecionados para uma cidade informada no combobox.

No evento Load do formulário eu vou preencher a combobox com os nomes de algumas cidades que eu sei que a tabela Clientes possui:

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ComboBox1.Items.Add("São Paulo")
        ComboBox1.Items.Add("Rio de Janeiro")
        ComboBox1.Items.Add("Resende")
        ComboBox1.Items.Add("Lisboa")
        ComboBox1.Items.Add("London")
        ComboBox1.Items.Add("Madrid")
        ComboBox1.Items.Add("México D.F.")
        ComboBox1.SelectedIndex = 0
    End Sub

No evento Click do botão de comando - Button1 - vou chamar a função - ObterDadosAccess() - que irá preencher o listbox com os dados selecionados:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'se houver um item selecionado então chama a função
        If ComboBox1.SelectedIndex <> -1 Then
             ObterDadosAccess("", "", ComboBox1.SelectedItem)
        End If
    End Sub

Finalmente o código da função ObterDadosAccess() . Esta função recebe três parâmetros :

UID - o nome do usuário do banco de dados
pwd - a senha do usuário do banco de dados
cid - o nome da cidade pela qual vamos selecionar os dados

    Public Function ObterDadosAccess(ByVal UID As String, ByVal pwd As String, ByVal cid As String)

        Dim conn As New OleDbConnection
        Dim clientesReader As OleDbDataReader
        Dim cmd As New OleDbCommand
        Dim connString As String
        Dim i As Integer

        Try
            ' define a string de conexao
            connString = "Jet OLEDB:System database=" & _
                "C:\teste\system.mdw;" & _
                "Data Source=C:\teste\Northwind.mdb;" & _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "User ID=" & UID & ";Password=" & pwd

            ' abre a conexao
            conn.ConnectionString = connString
            conn.Open()

            'define as propriedades do objeto command
            cmd.Connection = conn
            cmd.CommandText = "SELECT CódigoDoCliente,NomeDaEmpresa,NomeDoContato from Clientes WHERE cidade = '" 
& cid & "'"

            ' cria o objeto DataReader
            clientesReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            ListBox1.Items.Clear()

            Try
                'exibe os dados : nome da empresa(1) , nome do contato(2)
                While (clientesReader.Read)
                    ListBox1.Items.Add(clientesReader.Item(1) & " ( " & clientesReader.Item(2) & " )")
                End While
            Finally
                clientesReader.Close()
            End Try

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Function

Executando a aplicação teremos :

Acessando dados do Microsoft Excel

A conexão com o Excel também segue os mesmos passos. A string de conexão neste caso é :

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\teste\teste.xls;" & _
"Extended Properties=Excel 8.0"

Para realizar a conexão com o Excel além de indicar o provedor e o local onde esta o arquivo XLS estamos usando a propriedade Extended Properties e informando Excel 8.0. Se o arquivo Excel estiver protegido com senha você não vai conseguir abrir uma conexão com o arquivo a menos que ele já esteja aberto no Excel. (leia mais no artigo : XL2000: "Could Not Decrypt File" Error with Password Protected File. )

A planilha Excel chamada teste.xls esta diretório c:\teste e tem os seguintes dados:

Como eu vou criar a instrução SQL para selecionar os dados da planilha que desejo exibir ?

Para resolver este problema a primeira coisa a fazer é um ajuste na tabela Excel. Calma , nada complicado. Você deve apenas selecionar os dados que deseja exibir ( lembrando que a primeira linha será tomado como o cabeçalho da tabela) e usar a propriedade Range para dar um nome ao conjunto de células selecionada. Este nome será tomado como nome da nossa 'tabela' de dados. Você então usa um provedor OLE DB e faz o acesso aos dados normalmente. Simples !! não é mesmo ???

Selecione as células da planilha teste.xls e no menu Inserir do Excel selecione Nome e a seguir Definir:(ver figura abaixo)

Informe o nome para a seleção. Vou usar o nome Agenda. Este nome será a nossa ´tabela'.

Pronto ! Agora podemos usar a seguinte instrução SQL : "Select Nome, Endereco, Cidade From Agenda"

Perceba que Agenda funciona como sendo a tabela de onde iremos extrair os dados informando o nome de cada coluna da planilha Excel.

O código associado ao evento Click do botão de comando Button1 é :(ele apenas chama a função que irá obter os dados do Excel)


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

             ObterDadosExcel()

End Sub

O código da função ObterDadosExcel() e dado a seguir :(Ele irá selecionar apenas o nome , endereço e a cidade da planilha)

Public Function ObtemDadosExcel()

        Dim conn As New OleDbConnection
        Dim agendaReader As OleDbDataReader
        Dim connString As String
        Dim cmd As New OleDbCommand
        Try
            ' Define a string de conexão
            connString = "Data Source=" & _
                              "C:\teste\teste.xls;" & _
                              "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Extended Properties=Excel 8.0;"
            ' Abre a conexao.
            conn.ConnectionString = connString
            conn.Open()
            'Define as propriedades do objeto COmmand
            cmd.Connection = conn
            cmd.CommandText = "Select Nome, Endereco, Cidade From Agenda"
            ' Cria um DataReader
            agendaReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Try
                'exibe o nome (0) , endereco(1) e a cidade(2)
                While (agendaReader.Read)
                    ListBox1.Items.Add(agendaReader.Item(0) & " - " & agendaReader.Item(1) & " -" & agendaReader.Item(2))
                End While
            Finally
                agendaReader.Close()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message())
        End Try
    End Function

O resultado da execução do projeto é mostrado na tela abaixo: