Num artigo anterior, foi mostrado como Contar Cores das Células, de acordo com a indicação da área (range) e o código do cor. Esta função, embora simples, é muito útil, funcionando no entanto apenas se o utilizador aplicar a cor manualmente e não com o uso de formatação condicional.
Com a formatação condicional podemos criar várias formatações para a mesma área, interferindo em diferentes aspectos (fonte, preenchimento, etc), ficando a formatação base inalterável. O que acontece é que o Excel cria camadas nas células, definidas na formatação, ficando a informação original preservada. Por exemplo, se tivermos uma tabela com todas as células com a cor de fundo verde e se a formatação condicional colocar algumas células com a cor de fundo azul, na prática a cor de fundo de todas as células é verde e existe uma camada azul por cima das células que correspondem ao critério definido para a formatação condicional.
Além disso, a versão 2007 ou superior do Excel, sofreu um conjunto de alterações na formatação condicional, sendo agora possível utilizar ícones, shapes, tops, etc, definidos pelo tipo XlFormatConditionType, o que pode obrigar a mais algumas validações (não será no entanto abordado neste artigo)
Em termos práticos não é possível verificar directamente se a formatação está aplicada ou não, o que é possível verificar são os critérios da formatação condicional e com isso, validarmos se a célula corresponde a esses critérios ou não.
Neste exemplo, vamos verificar se os valores das células (xlCellValue) correspondem a um critério definido (maior do que, menor do que, entre, etc), definidos pelo tipo XlFormatConditionOperator, e se corresponderem, então a formatação condicional está visível e usamos essa informação para contar, somar, etc.
Para começar esta pequena função auxiliar, que serve simplesmente para remover o sinal de igual e garantir que o resultado final é numérico.
' Limpa o sinal de igual da fórmula
Public Function ClearValue(ByVal value As String) As Double
Dim tempValue As String
On Error Resume Next
tempValue = Replace(value, "=", "")
If IsNumeric(tempValue) Then
ClearValue = CDbl(tempValue)
Else
ClearValue = 0
End If
End Function
Depois, a função GetFormatConditionIndex() que verifica se a célula corresponde ao critério definido na formatação condicional, retornando 0 caso não corresponda e o número da formatação, caso corresponda (como já foi dito uma célula pode ter diversas formatações condicionais).
Public Function GetFormatConditionIndex(ByVal rng As Range) As Integer
On Error GoTo errorGetFormatConditionIndex
' Caso não tenha qualquer formatação condicional
If rng.FormatConditions.Count = 0 Then
Exit Function
Else
Dim x As Long
Dim cf As FormatCondition
' Ciclo nas formatações condicionais atribuidas
For x = 1 To rng.FormatConditions.Count
' Verifica qual a FormatConditions actual
Set cf = rng.FormatConditions(x)
' Verifica o tipo de formatação
If cf.Type = xlCellValue Then
' Verifica se algumas da opções escolhidas para a
' formatação condicional é valida, ou seja, está visivel
Select Case cf.Operator
Case xlGreater
If rng.value > ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlLess
If rng.value < ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlEqual
If rng.value = ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlNotEqual
If rng.value <> ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlGreaterEqual
If rng.value >= ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlLessEqual
If rng.value <= ClearValue(cf.Formula1) Then
GetFormatConditionIndex = x
End If
Case xlBetween
If rng.value >= ClearValue(cf.Formula1) And _
rng.value <= ClearValue(cf.Formula2) Then
GetFormatConditionIndex = x
End If
Case xlNotBetween
If rng.value < ClearValue(cf.Formula1) Or _
rng.value > ClearValue(cf.Formula2) Then
GetFormatConditionIndex = x
End If
End Select
End If
' Caso tenha encontrada uma opção válida
If GetFormatConditionIndex > 0 Then Exit Function
Next
' Caso não encontre retorna 0
GetFormatConditionIndex = 0
End If
Exit Function
errorGetFormatConditionIndex:
' Em caso de erro retorna 0
GetFormatConditionIndex = 0
End Function
Finalmente as funções que contam (neste caso podendo ser adaptado para outro objectivo), o número de células com a fonte igual a um determinado número (ColorIndex), com ou sem formatação condicional:
Public Function CountFontColor( _
ByVal rng As Range, _
ByVal ColorIndex As Integer, _
Optional ByVal ConditionalFormat As Boolean = False) As Long
Dim r As Range
Dim result As Integer
On Error GoTo errorCountFontColor
' Verifica na formatação condicional
If ConditionalFormat Then
For Each r In rng
Dim cfIndex As Integer
cfIndex = GetFormatConditionIndex(r)
If cfIndex > 0 Then
Dim temp As FormatCondition
Set temp = r.FormatConditions(cfIndex)
If temp.Font.ColorIndex = ColorIndex Then
result = result + 1
End If
End If
Next
Else
For Each r In rng
If r.Font.ColorIndex = ColorIndex Then
result = result + 1
End If
Next
End If
CountFontColor = result
Exit Function
errorCountFontColor:
CountFontColor = 0
End Function
E a função que conta o número de células com a fundo (background) igual a um determinado número (ColorIndex), com ou sem formatação condicional:
Public Function CountFillColor( _
ByVal rng As Range, _
ByVal ColorIndex As Integer, _
Optional ByVal ConditionalFormat As Boolean = False) As Long
Dim r As Range
Dim result As Integer
On Error GoTo errorCountFillColor
' Verifica na formatação condicional
If ConditionalFormat Then
For Each r In rng
Dim cfIndex As Integer
cfIndex = GetFormatConditionIndex(r)
If cfIndex > 0 Then
Dim temp As FormatCondition
Set temp = r.FormatConditions(cfIndex)
If temp.Interior.ColorIndex = ColorIndex Then
result = result + 1
End If
End If
Next
Else
For Each r In rng
If r.Interior.ColorIndex = ColorIndex Then
result = result + 1
End If
Next
End If
CountFillColor = result
Exit Function
errorCountFillColor:
CountFillColor = 0
End Function
Com estas funções, podemos facilmente usar em qualquer célula:
=CountFontColor(A1:A10;3;TRUE)
=CountFillColor(A1:A10;3;TRUE)
Onde A1:A10 é a área a verificar, 3 o número da cor(neste caso vermelho) e TRUE indicando que a verificação é na formatação condicional. Para não ser verificada na formatação condicional podemos fazer:
=CountFontColor(A1:A10;3)
=CountFillColor(A1:A10;3)
As cores, são definidas na propriedade ColorIndex (podem ser mais detalhes e a palete de cores neste link)
NOTA: Embora este artigo refira as versões 2007 e superior do Excel, também funciona em versões mais antigas.
Espero que este artigo vos seja útil!
A Microsoft All-In-One Code Framework é uma biblioteca de exemplos gratuitos de aplicações .NET (VB.NET, C# e C++), categorizados em diversas áreas de desenvolvimento, para as versões do Visual Studio 2008 e 2010.
Existem diversos exemplos de áreas como ASP.NET, Silverlight, Windows Azure, Office, Windows 7, Windows Forms, WPF, XML, Segurança, entre muitas outras.
A Microsoft Community Team recolhe informação de necessidades frequentes e prepara exemplos com as melhores praticas de desenvolvimento. Todos podem contribuir com pedidos de exemplos ou votando nos pedidos já efectuados.
Está disponível ainda um ficheiro *.docx com os Coding Standards utilizados nos exemplos, com algumas boas referências e bases a seguir nas aplicações.
Mais informação e downloads: http://1code.codeplex.com/
Para acompanhar as novidades pode seguir o blog: http://blogs.msdn.com/b/codefx/
O ADO.NET Entity Framework permite criar aplicações em que o acesso a dados é feito com base em um modelo conceitual e não utilizando comandos directos à base de dados. Isto permite que o programador se abstraia totalmente da base de dados (criação de ligações de acesso, comandos, parâmetros, etc.) e utilize apenas objectos durante o desenvolvimento.
A versão 4.0 do Entity Framework (actual), que está disponível na .NET Framework 4.0, tem um conjunto de novas funcionalidades e melhorias, como é o caso de suporte a POCO - Plain Old CLR Objects (permite criar classes que não herdam, nem implementam nenhuma outra classes ou interface), abordagem Model-First (permite criar primeiro o modelo conceptual e, com base nele, criar a base de dados), suporte para o uso de funções em LINQ-to-Entities, Complex Types (criação de tipos de dados complexos), Deferred Loading ou Lazy Loading (capacidade de carregar as propriedades de associação das entidades no momento em são chamadas, se forem chamadas), etc.
Não é especifica apenas para o SQL Server, pois existem providers que permitem usar outras bases de dados, como Oracle, MySql, PostgreSQL, DB2, SQL Anywhere, Ingres, Progress, Firebird, Synergy, etc.
Existem várias vantagens na utilização de um ORM (Object-relational mapping), que tornam a sua adopção quase inevitável, mas vejamos o seguinte exemplo:
“SELECT * FROM utilizadores WHERE nome = ‘Jorge’ AND morada = ‘Moita’”
Este exemplo irá listar todos os utilizadores que tenham como nome “Jorge” e que tenham como morada a “Moita”.
Imaginemos que o programador ou o DBA (database administrator) altera o campo “nome” para “nomecompleto”, pois quer que se registe o nome completo do utilizador e, desta forma, o nome do campo fica mais coerente. Se aplicação for compilada não é detectado qualquer problema e só no momento de execução irá ocorrer um erro. Situações como estas são muito frequentes e podem representam vários erros na aplicação.
Este é apenas um exemplo de como se pode beneficiar, e muito, da utilização do Entity Framework, mas existem muito mais vantagens como a utilização de LINQ to Entities, intellisense no momento em que se está a escrever o código, código mais fácil de manter e actualizar, etc, etc.
Neste artigo, que será uma breve introdução ao Entity Framework, irá apenas mostrar como construir um modelo relacional de uma base de dados SQL, uma abordagem database-first, e como executar algumas operações CRUD.
CRIAÇÃO DO MODELO RELACIONAL (database-first)
Para criação de um modelo relacional iremos usar a seguinte base de dados SQL:
Como é possível ver nesta imagem, existem apenas 3 tabelas, com as respectivas chaves primárias/estrangeiras: produtos, clientes e uma terceira tabela onde se registam os movimentos.
O primeiro passo é adicionar um novo item ao projecto (menu Project – Add New Item), e seleccionar no grupo de templates “Data” o item “ADO.NET Entity Data Model”. Isto irá iniciar um Wizard que permitirá criar o modelo final. 
De seguida seleccionar “Generate from database” e carregar em “Next”. A próxima opção permite fazer a ligação à base de dados, escolhendo uma já existente ou criando uma nova ligação. 
Para o nome da ligação, e para este exemplo, utilize “vendasEntities“.
A última opção deste Wizard é onde são seleccionados os objectos a utilizar no modelo. Neste exemplo são escolhidas as três tabelas já referidas. 
Após este processo está criado o ficheiro EDMX, que não é mais do que um conjunto de classes e métodos que nos permitirá aceder à base de dados de uma forma simples, como iremos ver de seguida.
OPERAÇÕES CRUD
As operações CRUD (acrónimo de Create, Read, Update e Delete em Inglês) são quatro operações básicas e fundamentais para a manipulação de bases de dados. São por isso bastante importantes e requerem, sem a utilização de um ORM como o Entity Framework, algum trabalho que é normalmente complexo.
Com o Entity Framwork este trabalho está bastante simplificado para o programador, necessitando apenas de algumas linhas de código, com recurso a IntelliSense, para as executar.
O funcionamento para todas as operações é semelhante: é criada uma nova instância da nossa entidade (neste exemplo “vendasEntities”) que herda de uma classe da EntityFramework chamada ObjectContext, que a classe primária e responsável de gerir a informação como objectos e a ligação dos objectos aos dados.
Inserir registos
Para inserir novos registos, neste caso um novo cliente, criamos uma nova instância da respectiva classe, definindo as suas propriedades, que correspondem aos respectivos campos. Depois adicionamos o objecto através do método AddObject() e, finalmente, executamos o método SaveChanges() que irá efectuar as respectivas alterações, neste caso inserir um registo, na base de dados
Using context As New vendasEntities
' Cria um novo cliente
Dim cliente As New clientes With
{
.nome = "Rui Paulo",
.morada = "Lisboa",
.datanascimento =
New DateTime(1980, 10, 31)
}
context.clientes.AddObject(cliente)
context.SaveChanges()
' Podemos verificar logo o número (ID) do registo inserido
Debug.WriteLine("Número de registo: " & cliente.id.ToString())
End Using
Apagar registos
Para apagar um registo é necessário seleccionar o registo correcto, neste caso usando uma Lambda Expression, e caso o resultado seja válido, executamos o método DeleteObject(), indicando o registo a apagar. Finalmente gravamos as alterações na base de dados.
Using context As New vendasEntities
' Procura o registo com o número de cliente (ID) igual a 1
Dim cliente = context.clientes.Where(
Function(c) c.id = 1).FirstOrDefault()
If cliente IsNot Nothing Then
context.clientes.DeleteObject(cliente)
context.SaveChanges()
Else
MessageBox.Show(
"Número de cliente inválido")
End If
End Using
Modificar Registos
Para modificar um registo, e à semelhança da operação anterior, é necessário seleccionar o registo. Neste exemplo utilizamos LINQ to Entities para fazer essa selecção. Depois, modificamos o que for para modificar e finalmente gravamos as alterações.
Using context As New vendasEntities
Dim cliente =
(From c In context.clientes
Where c.id = 1
Select c).FirstOrDefault()
If cliente IsNot Nothing Then
With cliente
.nome = "Rui Paulo"
.morada = "Setúbal"
.datanascimento = New DateTime(1979, 10, 31)
End With
context.SaveChanges()
Else
MessageBox.Show(
"Número de cliente inválido")
End If
End Using
Listar Registos
A listagem de registo é também muito semelhante às operações anteriores. Neste exemplo, usando LINQ to Entities, vamos seleccionar todos os registo com base num critério, para uma variável do tipo IQueryable(Of T), e depois efectuamos um ciclo para mostrar os resultados obtidos.
Using context As New vendasEntities
Dim listaClientes =
From c In context.clientes
Where c.morada.Contains("Lisboa") And
c.datanascimento <
New DateTime(1985, 1, 1)
Select c
For Each c As clientes In listaClientes
Debug.WriteLine(c.nome)
Next
End Using
Entre entidades existir normalmente associações, e essas associações (também representadas na última imagem), têm algo que se designa por Navigation Properties. As Navigation Properties permitem uma navegação bidireccional entre entidades, de acordo com as suas associações. Isto é algo bastante prático pois permite-nos criar queries entre várias entidades usando LINQ to Entities.
O seguinte exemplo mostra esta abordagem, definindo depois o resultado como DataSource num controlo DataGridView.
Using context As New vendasEntities
Dim listaClientes =
From c In context.clientes
Join m In context.movimentos
On m.clienteid Equals c.id
Join p In context.produtos
On m.produtoid Equals p.id
Select m.datavenda,
c.nome,
p.produto,
m.preco
Me.DataGridView1.DataSource = listaClientes
End Using
NOTA: Se utilizar uma base de dados anexa ao projecto (AttachDb), a base de dados é copiada para as pastas Debug/Release. Pode seleccionar no menu Project a opção Show All Files e verificar as alterações nessa localização.
CONCLUSÃO
Como foi possível ver ao longo do artigo, que abordou de uma forma muito superficial algumas das funcionalidades deste ORM, o Entity Framework é uma excelente opção para se trabalhar com bases de dados.
É simples de utilizar, permite-nos trabalhar apenas com objectos e com intellisense, resolve inúmeros problemas que são habituais quando trabalhamos directamente com a base de dados (formatos de datas, números, caracteres especiais), etc.
Estas são apenas algumas, das muitas vantagens, que nos devem fazer de uma vez por todas, deixar de usar SqlCommands, SqlConnections, DataSets, etc.
Alguns recursos interessantes:
Data Developer Center
http://msdn.microsoft.com/en-us/data/ef.aspx
Data Access Hands-on Labs
http://msdn.microsoft.com/enus/data/gg427655.aspx
ADO.NET team blog
http://blogs.msdn.com/b/adonet/
Julie Lerman's Blog
http://thedatafarm.com/blog/
NOTA: Este artigo está também disponível na revista PROGRAMAR – Edição nº 26 de Dezembro 2010
