O XML (eXtensible Markup Language) ou linguagem de marcação estendida, é um formatos cada vez mais populares e utilizado por diversos tipos de aplicações. O XML é muito semelhante ao HTML e a principal diferença é que o HTML serve para descrever a aparência e as acções em uma página e o XML serve para guardar dados. Além disso o HTML utiliza tags pré-definidas e o XML não, mas a estrutura é idêntica - as tags (palavras encapsuladas por sinais '<' e '>') e os atributos (definidos com nome="valor").
Existem muitos benefícios na utilização do formato XML, como por exemplo, funcionam sem pré-requisitos, grande capacidade e rapidez de utilização, múltiplas formas de visualizar os dados, fácil distribuição, utilização e compatibilidade com diversos sistemas, etc. Para mais informações podem ler World Wide Web Consortium (W3C)
Existem diversas aplicações e operações bastante fáceis. Pode-se por exemplo gravar todos os dados de uma DataTable para XML e ler os dados para uma DataGridView utlizando poucos comandos:
‘ Grava a informação de uma DataTable para um ficheiro XML
myDataTable.WriteXml("c:\MyData.xml", Data.XmlWriteMode.WriteSchema)
' Lê a informação do ficheiro para uma DataGridView utilizando um DataSet
Dim ds As New DataSet()
ds.ReadXml("c:\MyData.xml")
Me.DataGridView1.DataSource = ds.Tables("MyTable")
Tão simples como isto!
Mas o objectivo deste artigo é mostrar como criar um ficheiro simples de XML (para guardar configurações), inserir, listar e apagar elementos. Para criar um ficheiro XML usa-se a XmlTextWriter do namespace System.Xml
Imports System.Xml
Private xmlPath As String = "C:\MyData.xml"
' Cria um novo ficheiro XML através do clique de um botão
Private Sub btnCriarXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriarXML.Click
' Cria um novo ficheiro XML com a codificação UTF8
Dim xmlw As New XmlTextWriter(xmlPath, System.Text.Encoding.UTF8)
xmlw.Formatting = Formatting.Indented
xmlw.WriteStartDocument()
' Adiciona um comentário geral
xmlw.WriteComment("Configuração Geral")
' Criar um elemento geral
xmlw.WriteStartElement("config")
' Criar o elemento "Favoritos" e alguns dados
With xmlw
.WriteStartElement("favoritos")
.WriteElementString("fav", http://vbtuga.blogspot.com)
.WriteElementString("fav", http://www.microsoft.com)
.WriteElementString("fav", http://www.google.com)
.WriteElementString("fav", http://www.asp.net)
.WriteEndElement()
End With
' Criar o elemento "Utilizadores" e alguns dados
With xmlw
.WriteStartElement("utilizadores")
.WriteElementString("user", "Jorge Paulino")
.WriteElementString("user", "João Costa")
.WriteElementString("user", "Nuno Andrade")
.WriteEndElement()
End With
xmlw.WriteEndElement() ' <- config
xmlw.WriteEndDocument()
' Fecha o documento XML
xmlw.Flush()
xmlw.Close()
End Sub
O resultado final deverá ser um ficheiro com este:
<?xml version="1.0" encoding="utf-8" ?>
<!-- Configuração Geral –>
<config>
<favoritos>
<fav>http://vbtuga.blogspot.com</fav>
<fav>http://www.microsoft.com</fav>
<fav>http://www.google.com</fav>
<fav>http://www.asp.net</fav>
</favoritos>
<utilizadores>
<user>Jorge Paulino</user>
<user>João Costa</user>
<user>Nuno Andrade</user>
</utilizadores>
</config>
Com podem ver este ficheiro tem dois “grupos”: Favoritos e Utilizadores. Esses grupos são onde se guarda informação com as suas respectivas tags. Para ler um desses grupos e listar os dados em uma ListBox é apenas necessário:
' Cria um novo documento XML e lê o ficheiro criado anteriormente
Dim xmlDoc As New XmlDocument
xmlDoc.Load(xmlPath)
' Selecciona os “favoritos” e faz um ciclo em todos
' nodes preenchendo a ListBox1
Dim node As XmlNode = xmlDoc.SelectSingleNode("//favoritos")
If node IsNot Nothing Then
For x As Integer = 0 To nodeA.ChildNodes.Count – 1
Me.ListBox1.Items.Add(node.ChildNodes.Item(x).InnerText.ToString)
Next
End If
Para apagar e inserir novos dados criei duas funções auxiliares. Estas funções servem para reduzir código e simplificar este processo.
''' <summary>
''' Remove um node de um ficheiro XML retornando
''' como resultado 'True' caso tenha conseguido
''' </summary>
''' <param name="nodeGroup">Nome do Grupo</param>
''' <param name="nodeText">Texto a Remover</param>
Private Function RemoveXMLNode(ByVal nodeGroup As String, ByVal nodeText As String) As Boolean
Dim result As Boolean = False
Try
' Verifica se o ficheiro existe
If Not IO.File.Exists(xmlPath) Then
Return False
End If
' Cria um novo XmlDocument e carrega o ficheiro XML
Dim xmlDoc As New XmlDocument
xmlDoc.Load(xmlPath)
' Selecciona o grupo escolhido
Dim node As XmlNode = xmlDoc.SelectSingleNode("//" + nodeGroup)
' Apaga todos os nodes (caso encontre na lista)
For x As Int16 = node.ChildNodes.Count - 1 To 0 Step -1
If node.ChildNodes.Item(x).InnerText.ToString = nodeText Then
node.RemoveChild(node.ChildNodes.Item(x))
result = True
End If
Next
' Caso tenha eliminado o(s) node(s) grava o ficheiro
If result = True Then
xmlDoc.Save(xmlPath)
Return True
Else
Return False
End If
Catch ex As Exception
' Em caso de erro retorna False
Return False
End Try
End Function
''' <summary>
''' Adiciona um node de um ficheiro XML retornando
''' como resultado 'True' caso tenha conseguido
''' </summary>
''' <param name="nodeGroup">Nome do Grupo</param>
''' <param name="nodeName">Nome que fica na tag</param>
''' <param name="nodeText">Texto a Inserir</param>
Private Function AddXMLNode(ByVal nodeGroup As String, ByVal nodeName As String, ByVal nodeText As String) As Boolean
Dim result As Boolean = False
Try
' Verifica se o ficheiro existe
If Not IO.File.Exists(xmlPath) Then
Return False
End If
' Cria um novo XmlDocument e carrega o ficheiro XML
Dim xmlDoc As New XmlDocument
xmlDoc.Load(xmlPath)
' Selecciona o grupo escolhido
Dim node As XmlNode = xmlDoc.SelectSingleNode("//" + nodeGroup)
' Cria um novo elemento e define o texto
Dim newNode As XmlNode = xmlDoc.CreateElement(nodeName)
newNode.InnerText = nodeText
' Insere o novo XmlNode
node.AppendChild(newNode)
' Grava as alterações
xmlDoc.Save(xmlPath)
Return True
Catch ex As Exception
' Em caso de erro retorna False
Return False
End Try
End Function
Para utilizar estas funções e manipular o ficheiro XML é só necessário:
' Adicionar informação
Dim result As Boolean = AddXMLNode("utilizadores", "user", "Ana Dias")
If result Then
Debug.WriteLine("Ficheiro XML actualizado ...")
End If
' Remover informação
Dim result As Boolean = RemoveXMLNode("favoritos", http://www.google.com)
If result Then
Debug.WriteLine("Ficheiro XML actualizado ...")
End If
Estes são apenas alguns exemplos de como utilizar um ficheiro XML para guardar informação. Neste caso foi utilizado uma estrutura muito simples, podendo-se no entanto criar um ficheiro mais complexo com a utilização de atributos, mais tags, etc.
O objectivo deste artigo e mostrar que é simples e prático guardar informação, podendo ser utilizado em inúmeras situações como guardar definições, fazer backups de bases de dados, copiar dados, etc.
PS: Como sempre, qualquer dúvida, comentário ou correcção ao artigo é sempre bem vinda!
Um Excel Add-In é um ficheiro com a extensão *.xla que tem funções personalizadas, utilizando código VBA, e que inicia automaticamente quando o Excel é aberto. Estas funções personalizadas, também designadas por UDF’s (User Defined Functions), permitem ser executadas independentemente do documento que esteja aberto. Ou seja, podemos criar uma macro/função para um determinado ficheiro de Excel, mas se necessitarmos de uma macro/função que seja necessário funcionar em qualquer documento, usamos um Add-In.
Para mostrar a criação e implementação de um Add-In irá ser mostrado um exemplo de como criar um novo menu e executar um ciclo que irá apagar datas, numa lista, inferiores à data actual.
Para criarmos então um Add-In no Excel deveremos fazer o seguinte:
1 – Iniciar o Excel e abrir o Editor de Visual Basic (Tools – Macros – Visual Basic Editor ou ALT+F11)
2 – Com o Visual Basic Editor aberto fazer um duplo clique no ThisWorkbook (que é onde se insere o código comum a todas as folhas ou WorkSheets) e insere-se o seguinte código:
' Quando o Workbook é aberto, usando o evento Open()
Private Sub Workbook_Open()
' Insere um novo separador e um menu no final da posição 6 (Menu Tools)
With Application.MenuBars(xlWorksheet).Menus(6).MenuItems
.Add Caption:="-"
.Add Caption:="&Apagar Datas", OnAction:="ApagarDatas"
End With
End Sub
Este código irá criar um novo separador e um novo menu que executa o Sub (procedimento) ApagarDatas().
3 – Adiciona-se um novo módulo e inserir o seguinte código:
' -------------------------------------------------------------------------------------------------
' Apaga as linhas em que a data da coluna A é inferior à data actual
' -------------------------------------------------------------------------------------------------
Public Sub ApagarDatas()
Dim msg As String
msg = "Deseja apagar as datas da lista inferiores à actual ?"
' Pergunta ao utilizar se pretende continuar, seleccionando por defeito o botão "No"
If MsgBox(msg, vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
' Desactiva as actualizações no ecrã e altera os cálculos
' para manual. Como existem dados aleatórios na folha, sempre
' que se apagava uma linha actualizava tudo
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Declaração de variáveis
Dim firstRow As Long, lastRow As Long
Dim x As Long
firstRow = 2
' Calcula qual a ultima linha preenchida
lastRow = Cells(65536, 1).End(XlDirection.xlUp).Row()
' Inicia um ciclo que irá percorrer os dados. Como se trata de
' apagar linha deve-se começar de baixo para cima
For x = lastRow To firstRow Step -1
' Verifica se é uma data válida
If IsDate(Cells(x, 1).Value) Then
' Compara com a data actual e caso seja inferior apaga a linha
If CDate(Cells(x, 1).Value) < Date Then
Cells(x, 1).EntireRow.Delete
End If
End If
Next x
' Repõe as actualizações/cálculos
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub
Este código fará então um ciclo na folha ou WorkSheet seleccionada, em todas as linhas, e verificará na coluna “A” se a data existente é inferior à actual. Caso seja inferior apagará a linha.
4 – No Visual Basic Editor ou na folha de cálculo fazer um Save As e no tipo de ficheiro seleccionar “Microsoft Office Excel Add-In (*.xla). Podemos ainda no menu Tools – VBAProject Properties indicar detalhes sobre o nosso Add-In e definir uma password. Com a password o Add-In funcionará na mesma apenas bloqueia a visualização do código.
5 – Ir ao Menu Tools – Add-Ins, seleccionar Browse e escolher o ficheiro criado.
6 – Reiniciar o Excel
Se todos os passos forem cumpridos, quando iniciarmos o Excel estará disponível um novo menu com a descrição “Apagar Datas”. Para testar esta nova opção vamos construir um ficheiro com as seguintes formulas:
a) Inserir na célula A1 a seguinte formula e copiar até à A50 para criar uma data aleatória entre os dias 1 e 28 de qualquer mês do corrente ano:
=DATE(2008;RANDBETWEEN(1;12);RANDBETWEEN(1;28))
b) Inserir na célula B2 a seguinte formula e copiar até B50 e depois para as colunas C,D,E, etc. Esta formula irá criar um número aleatório entre 1 e 1000.
=RANDBETWEEN(1;1000)
Executar a nova opção – ApagarDatas() – e verificar os resultados/funcionamento.
O objectivo deste artigo é mostrar como criar um Add-In no Excel, a sua utilidade e aplicação. Mostrar também como fazer um ciclo numa lista de valores, verificando qual a última linha preenchida e efectuar algumas verificações para eliminar dados. Este exemplo serve como demonstração e poderá ser adaptado de acordo com as diversas necessidades.
PS: Como sempre, qualquer dúvida, comentário ou correcção ao artigo é sempre bem vinda!
O Microsoft Excel é uma ferramenta muito utilizada e normalmente é um dos formatos que se disponibiliza quando se exporta informação. Esta opção permite ao utilizador modificar dados, criar gráficos personalizados, alterar a informação, etc.
Early Binding vs Late Binding
Para realizar automação através do VB.NET, e para explicar um pouco as principais diferenças, existem dois métodos que se podem utilizar: Late Binding e Early Binding. O Late Binding utiliza o CreateObject() para criar uma nova instância do Excel ou o GetObject() para manipular uma instância já criada. Um exemplo das suas utilizações:
‘ Criar uma nova instância do Excel
Dim objExcelApp As Object
objExcelApp = CreateObject("Excel.Application")
‘ Manipular uma instância já criada
Dim objExcelApp As Object
objExcelApp = GetObject(, "Excel.Application")
As suas principais vantagens são:
- Independente da versão utilizada, ou seja, não é específico para uma versão e pode ser utilizado tanto na versão 97 do Excel com na 2007. Existem incompatibilidades entre versões e um código desenvolvido em Excel 2003 provavelmente não funcionará em Excel 2007 (por exemplo);
- Redução do tamanho do projecto, ou seja, quanto mais referências forem adicionadas ao projecto, maior a tamanho do mesmo e mais demorado é o tempo de compilação;
- Alguns ambientes de programação não permitem adicionar referências ao projecto;
Para utilizar o método Early Binding é necessário adicionar a referência à livraria a utilizar (ex. Microsoft Excel 11.0 Object Library).
‘ Criar uma nova instância do Excel
Dim objExcelApp As New Excel.Application
As principais vantagens são:
- O código é mais rápido uma vez que é compilado antes da execução. Utilizando o Late Binding o código é compilado quando é executado reduzindo o performance;
- Acesso ao intellisense o que permite aceder às constantes, métodos, objectos, etc. Com o Late Binding o programar é “às cegas”.
- Permite detectar erros no código antes da execução, através do debug, uma vez que este é compilado.
Por estas diferenças, e sempre que possível, é recomendável a utilização do Early Binding. O seguinte exemplo será efectuado usando o Early Binding.
Exportação para Excel
A automação para Excel permite fazer tudo (ou quase tudo) o que se pode fazer no Excel. Desde formações, gráficos, fórmulas, etc, etc. Neste exemplo será mostrado como exportar o conteúdo de uma DataGridView permitindo gravar para o disco ou abrir o Excel com os resultados.
Para utilizar este exemplo é necessário adicionar como referência ao nosso projecto o “Microsoft Excel XX.0 Object Library”. Dependendo da versão do Excel poderá ser necessário (para facilitar) importar as classes Microsoft.Office.Interop ou não.
Imports Microsoft.Office.Interop
Imports System.Threading.Thread
Imports System.Globalization
Enum xlsOption
xlsSaveAs
xlsOpen
End Enum
''' <summary>
''' Exporta a informação de uma DataGridView para o Microsoft Excel
''' </summary>
''' <param name="dgvName">Nome da DataGridView </param>
''' <param name="option">SaveAs ou Open</param>
''' <param name="fileName">Nome completo do ficheiro</param>
Public Sub ExportToExcel(ByVal dgvName As DataGridView, ByVal [option] As xlsOption, Optional ByVal fileName As String = "")
Dim objExcelApp As New Excel.Application()
Dim objExcelBook As Excel.Workbook
Dim objExcelSheet As Excel.Worksheet
Try
' Se foi seleccionada a opção xlsSaveAs e não foi indicado ficheiro
If [option] = xlsOption.xlsSaveAs And fileName = String.Empty Then
MessageBox.Show("É necessário indicar um nome para o ficheiro")
Exit Sub
End If
' Altera o tipo/localização para Inglês. Existe incompatibilidade
' entre algumas versões de Excel vs Sistema Operativo
Dim oldCI As CultureInfo = CurrentThread.CurrentCulture
CurrentThread.CurrentCulture = New CultureInfo("en-US")
' Adiciona um workbook e activa a worksheet actual
objExcelBook = objExcelApp.Workbooks.Add
objExcelSheet = CType(objExcelBook.Worksheets(1), Excel.Worksheet)
' Ciclo nos cabeçalhos para escrever os títulos a bold/negrito
Dim dgvColumnIndex As Int16 = 1
For Each col As DataGridViewColumn In dgvName.Columns
objExcelSheet.Cells(1, dgvColumnIndex) = col.HeaderText
objExcelSheet.Cells(1, dgvColumnIndex).Font.Bold = True
dgvColumnIndex += 1
Next
' Ciclo nas linhas/células
Dim dgvRowIndex As Integer = 2
For Each row As DataGridViewRow In dgvName.Rows
Dim dgvCellIndex As Integer = 1
For Each cell As DataGridViewCell In row.Cells
objExcelSheet.Cells(dgvRowIndex, dgvCellIndex) = cell.Value
dgvCellIndex += 1
Next
dgvRowIndex += 1
Next
' Ajusta o largura das colunas automaticamente
objExcelSheet.Columns.AutoFit()
' Caso a opção seja gravar (xlsSaveAs) grava o ficheiro e fecha
' o Workbook/Excel. Caso contrário (xlsOpen) abre o Excel
If [option] = xlsOption.xlsSaveAs Then
objExcelBook.SaveAs(fileName)
objExcelBook.Close()
objExcelApp.Quit()
MessageBox.Show("Ficheiro exportado com sucesso para: " & fileName)
Else
objExcelApp.Visible = True
End If
' Altera a tipo/localização para actual
CurrentThread.CurrentCulture = oldCI
Catch ex As Exception
MessageBox.Show("Erro não identificado. Mensagem original:" & vbNewLine + ex.Message)
Finally
objExcelSheet = Nothing
objExcelBook = Nothing
objExcelApp = Nothing
' O GC(garbage collector) recolhe a memória não usada pelo sistema.
' O método Collect() força a recolha e a opção WaitForPendingFinalizers
' espera até estar completo. Desta forma o EXCEL.EXE não fica no
' Task Manager(gestor tarefas) ocupando memória desnecessariamente
' (devem ser chamados duas vezes para maior garantia)
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
End Try End Sub
Finalmente para utilizar no programa:
' Exporta e abre o Microsoft Excel
ExportToExcel(Me.DataGridView1, xlsOption.xlsOpen)
' Exporta e grava o ficheiro na localização indicada
ExportToExcel(Me.DataGridView1, xlsOption.xlsSaveAs, "c:\meuFicheiroExcel.xls")
O objectivo deste artigo é mostrar como utilizar a automação do Excel através do VB.NET e alguns truques que facilitam e optimizam este processo. O exemplo de exportar uma DataGridView é também muito util uma vez que é um controlo bastante utilizado.
PS: Como sempre, qualquer dúvida, comentário ou correcção ao artigo é sempre bem vinda!
DataGridView – Sequência de Movimento (Após tecla ENTER)
A DataGridView tem como movimento
após a tecla ENTER, de linha em linha, e não de coluna em coluna. Quando se está
a preencher registos, em que se preenche um registo de cada vez, é prático que o
utilizador após pressionar a tecla ENTER desloque a selecção para a coluna seguinte
e não para o próximo registo (próxima linha).
Para conseguirmos alterar este comportamento
podemos, no evento KeyDown, verificar qual a tecla pressionada e alterá-la. No entanto,
e se a célula estiver a ser editada, é necessário fazer o
override à ProcessCmdKey.
Fica um exemplo de como fazer:
Protected Overrides
Function ProcessCmdKey(ByRef msg
As System.Windows.Forms.Message, ByVal
keyData As System.Windows.Forms.Keys)
As Boolean
'
Verifica se a tecla pressionada foi a ENTER e se a célula está a ser editada
If
keyData = Keys.Enter And
Me.DataGridView1.IsCurrentCellInEditMode Then
'
Executa um TAB
SendKeys.Send("{TAB}")
' Ignora a tecla
pressionada
Return True
Else
' Executa o processo normal
Return
MyBase.ProcessCmdKey(msg, keyData)
End If
End Function
' Quando é pressionada uma tecla
Private Sub DataGridView1_KeyDown(ByVal
sender As Object,
ByVal e As System.Windows.Forms.KeyEventArgs)
Handles DataGridView1.KeyDown
'
Verifica se a tecla é a ENTER
If
e.KeyCode = Keys.Enter Then
'
Desabilita a tecla pressionada
e.SuppressKeyPress = True
With
Me.DataGridView1
' Caso seja a ultima coluna
salta para a primeira da próxima linha
If .CurrentCell.ColumnIndex
= .ColumnCount - 1 Then
' Caso não seja
a última linha
If .CurrentRow.Index < .RowCount - 1 Then
.CurrentCell = .Item(0, .CurrentRow.Index + 1)
End If
Else
' Move para a próxima coluna
.CurrentCell = .Item(.CurrentCell.ColumnIndex + 1, .CurrentRow.Index)
End If
End With
End If
End Sub
@@IDENTITY – Verificando Registo Inserido
Quando se inserem registos, é frequente
necessitarmos do último número de identificação inserido (ID) para mostrarmos ao
utilizador ou para utilizarmos na inserção de outros registos. Por exemplo se inserirmos
um novo utilizador e de seguida usarmos o seu número de registo para inserir a sua
lista de favoritos, temos de efectuar os seguintes passos:
1 – Inserir o registo do utilizador
2 – Verificar-mos qual o registo inserido
(ID)
3 – Inserir os favoritos utilizando
o ID recolhido
Para simplificar este processo podemos
utilizar o comando T-SQL -
@@IDENTITY - que retorna o valor gerado pelo
SQL Statement. Ou seja, no momento que inserimos o registo, verificamos
qual o número do registo inserido.
Fica aqui um exemplo de como simplificar
este processo:
' Texto de ligação à base de dados
Dim myConnectionString As String = _
"Data Source=.\SQLEXPRESS;AttachDbFilename='c:\MYDATABASE.MDF';"
& _
";Integrated Security=True;User Instance=True"
'
Comando que irá inserir dados na tabela "MyTable" em que o campo "username" será
' passados através de parâmetros e retornará
o ID do registo inserido
Dim SQL As
String = "INSERT INTO myTable([username]) VALUES
(@username); Select @@IDENTITY;"
'
Cria uma nova ligação à base de dados
Dim connection As
New SqlConnection(myConnectionString)
'
Criação do comando indicando a instrução e a ligação
Dim command As
New SqlCommand(SQL, connection)
command.Parameters.Add("@username", SqlDbType.VarChar).Value =
"teste"
connection.Open()
' Insere o registo e guarda na variável
IdRegistoInserido o ID
Dim IdRegistoInserido As Integer = command.ExecuteScalar()
Debug.WriteLine("Registo Inserido:
" + IdRegistoInserido.ToString)
' Fecha a ligação e limpa as variáveis
connection.Close()
connection = Nothing
command = Nothing
Deste modo reduzimos
um processo, de verificação do registo inserido, melhorando a performace da aplicação.
PS: Como sempre, qualquer
dúvida, comentário ou correcção ao artigo é sempre bem vinda!
