Integrando programas Delphi com o Excel - parte II

No post anterior, mostrei os passos iniciais e os conceitos básicos para se fazer uma aplicação Delphi que seja capaz de ler ou gravar informações em uma planilha Excel. Neste post eu avanço um pouco, mostrando como acessar as propriedades de uma célula ou grupo de células, seus valores, formatações e fórmulas.

Conforme escrevi em "Classes para automatização do Excel", o acesso a todas essas informações se dá através de objetos do tipo Range do Excel, não importa se você está trabalhando com uma única célula, uma linha, uma coluna ou um grupo qualquer de células dentro de uma planilha.

O exemplo abaixo mostra como preparar um Range que representa uma única célula, acessando seu valor e formatando-a como um título para o WorkSheet:

procedure TForm1.SetExcelTitle;
var ov: OleVariant;
lRange: ExcelRange;
begin
ov := _Sheet.Cells.Item[1, 1];
lRange := _Sheet.Range[ov, ov];
lRange.Font.Color := RGB(40, 40, 127);
lRange.Font.Size := 14;
lRange.Font.Bold := True;
lRange.Item[1, 1] := 'Conhecimento de Transporte eletrônico';
{ ... }

A variável _Sheet foi criada nos moldes descritos no post anterior. Para conseguirmos acessar um Range desse _Sheet, é preciso estipular quais são os limites que o definem, isto é, o Range deverá englobar uma área retangular da planilha, limitada por duas células: uma no canto superior esquerdo da área desejada e a outra no canto inferior direito dessa área. O acesso é conseguido através da propriedade Range da folha, à qual informamos essas duas células que definem a área.

E como obter essas duas células? A propriedade Cells da folha de trabalho é a resposta pois através dela conseguimos referências a localizações específicas dentro da planilha. No trecho de código no quadro anterior, é recuperada uma referência para a célula correspondente à linha 1, coluna 1 e esta referência é repassada ao Range para ganharmos acesso às propriedades que necessitamos. É o que acontece nas linhas seguintes do programa listado, onde uso o Range recuperado para alterar as caracteristicas da fonte (negrito, tamanho, etc.) e o texto a ser usado como título. Repare apenas que para mudar a cor foi preciso usar a função RGB ao invés de um dos TColor definidos pelo Delphi. Isso ocorre porque o formato de cores usado pelo Delphi é diferente do estabelecido para uso por objetos COM, com o Excel.

O conceito de acesso é o mesmo caso queira, por exemplo, mudar o formato de apresentação dos dados numa linha ou coluna:

procedure TForm1.SetExcelFormat;
var ov, ov2: OleVariant;
lRange: ExcelRange;
begin
ov := _Sheet.Cells.Item[3, 6];
ov2 := _Sheet.Cells.Item[10, 6];
lRange := _Sheet.Range[ov, ov2];
lRange.ColumnWidth := 12;
lRange.NumberFormatLocal := 'R$ #.##0,00_);[Red](R$ #.##0,00)';
lRange.HorizontalAlignment := xlRight;
{ ... }

No exemplo acima, eu recupero as localizações das células F3 e F10 (coluna 6, linhas 3 e 10) e monto com elas um Range abrangendo essas 7 células da coluna F. De posse do Range, eu modifico o comprimento da coluna para exibir 12 caracteres, instruo-o para alinhar à direita o conteúdo das células e para que esses conteúdos sejam apresentados como valores monetários (com o símbolo de Reais e duas casas decimais). Observe que para ajustar essa formatação eu usei a propriedade NumberFormatLocal de forma que eu pudesse estipular uma máscara de apresentação compatível com minha localização (ponto como separador de milhar e vírgula para separar as decimais).

Esse mesmo tipo de cuidado deve considerado quando for ajustar fórmulas em seus Ranges pois funções que podem ser usadas em fórmulas são traduzidas. Por exemplo, a função para somar o conteúdo de células tem o nome de SUM mas para o Excel em português ela pode ser inserida como SOMA. As linhas abaixo são equivalentes para quem tem o Excel em português:

lRange.Formula := '=SUM(B2:B31)';
lRange.FormulaLocal := '=SOMA(B2:B31)';

Veja outras considerações a respeito da manipulação de fórmulas neste post.

Mais Informações
Integrando programas Delphi com o Excel - parte I, Objeto Range, Classes para automatização do Excel

Integrando programas Delphi com o Excel - parte I

Já mostrei aqui no Blog quais são as principais classes disponíveis para se automatizar tarefas envolvendo o Excel, além de exemplos em VBScript de como usar essas classes para ler dados de uma planilha ou criar e formatar planilhas. Mas, como realizar essas mesmas tarefas diretamente em um programa feito em Delphi ? Esta questão é pertinente se você já tem programas Delphi pois, para o usuário, é mais prático (e elegante) manipular dados que já se tenha à mão. Caso contrário, o usuário terá que sair do programa para executar um script externo.

Também já abordei em outras ocasiões que o Delphi é capaz de gerar códigos fontes para permitir que um programa trabalhe com classes COM publicadas por qualquer aplicativo e que o Excel é um aplicativo que publica suas funções com essa tecnologia. Mas, para facilitar, o Delphi traz uma paleta de componentes chamada Servers que já traz encapsuladas as classes do Office (Word, Excel, Outlook, etc.), poupando-nos o trabalho de gerá-las manualmente.

No entanto, esses componentes foram criados de uma forma na qual não é possível determinar visualmente a relação precisa entre eles, exigindo do programador um conhecimento prévio dessas relações para fazê-las funcionar corretamente. No caso do Excel, o ponto de entrada para a integração é a classe Application, representada pelo componente TExcelApplication. Outros dois componentes importantes são TExcelWorkbook (uma planilha) e TExcelWorksheet (uma folha ou tabela dentro da planilha). Portanto, construir um programa Delphi que crie uma planilha exigirá que incluamos os três em um Form:

TForm1 = class(TForm)
_AppExcel: TExcelApplication;
_Book: TExcelWorkbook;
_Sheet: TExcelWorksheet;
{ ... }

Os 3 componentes possuem poucas propriedades publicadas: AutoConnect indica se a conexão com o servidor COM (neste caso, o Excel) será ou não feita automaticamente – o padrão é que o programa estabeleça a conexão somente quando for precisar do servidor, ficando mais fácil de tratar eventuais erros de conexão, principalmente se o servidor não existir (Excel não está instalado).
ConnectKind estabelece como será feita a conexão do componente com o Excel (servidor COM), sendo padrão o valor ckRunningOrNew. Com esse valor, a conexão é feita com um Excel que já esteja em execução. Se não houver nenhum, o programa o executa automaticamente antes de se conectar. Para esse exemplo, vou manter as propriedades desses componentes com seus valores padrões.

O segredo para utilizar esses componentes é conectá-los corretamente entre si, preparando o ambiente em que eles atuarão. Basicamente, o código para preparar o ambiente é o seguinte:

procedure TForm1.InitExcel;
var lExcelWorkBook : _WorkBook;
lSheetInterface : IDispatch;
begin
try
_AppExcel.Connect;
_AppExcel.DisplayAlerts[[0] := false;

lExcelWorkBook := _AppExcel.Workbooks.Add(xlWBATWorksheet, 0);
_Book.ConnectTo(lExcelWorkBook);

lSheetInterface := _Book.Worksheets.Item[1];
_Sheet.ConnectTo(lSheetInterface As _Worksheet);
_Sheet.Name := 'Minha Planilha';
except
on Exc: Exception do
_Erro := 'Não foi possível iniciar o Excel.'#13''#10'' + Exc.Message;
end;
end;

O que acontece no código acima é o seguinte :
Como o ponto de partida para a integração é o componente TExcelApplication, primeiro conecto-o ao servidor COM, isto é, ao próprio Excel. O componente "sabe" quem é o servidor correto porque essa informação está embutida nele, bastando que se chame o método Connect.
Por achar mais apropriado, configuro a propriedade DisplayAlerts para evitar que o Excel mostre suas caixas de diálogo pedindo confirmações ao usuário do meu programa.
Uso a propriedade Workbooks para criar uma nova planilha vazia, o que é feito através do método Add. O valor retornado pelo Add representa a planilha (Workbook) que foi adicionada.
O valor representando a nova planilha é recuperado num variável e, em seguida, usado para conectar ao componente TExcelWorkbook que agora representa exatamente a mesma planilha, podendo ser usado para manipulá-la ao longo do programa.
Um Workbook é composto de uma ou mais folhas de trabalho, que é quem mantem as células, seus valores, fórmulas, formatações, etc. Por isso, o passo seguinte é recuperar a primeira dessas folhas e conectá-la ao componente TExcelWorksheet, disponibilizando as informações contidas.
A última operação renomeia a folha de trabalho, ajustando-lhe o título para 'Minha Planilha'.

Essa é a "receita de bolo" ! Agora, basta utilizar esses 3 componentes para alimentar os dados numa planilha, formatá-la, etc. Quando tiver terminado as operações com a planilha e com o Excel, é importante fazer a desconexão e encerrar a aplicação para devolver ao Windows os recursos utilizados:

var vip: Variant;
begin
_Book.SaveAs ('Planilha.xls', xlExcel7, vip, vip, false, false, xlNoChange, false, false, vip, vip, vip, 0);
_Sheet.Disconnect ();
_Book.Disconnect ();
_AppExcel.Quit ();
_AppExcel.Disconnect ();

A primeira parte do código anterior salva a nova planilha com o nome de Planilha.xls. Repare ainda o uso da variável chamada vip, do tipo Variant : ela não é iniciada com valor algum. Desse modo, os parâmetros não-obrigatórios da função SaveAs são forçados a assumir seus valores padrões, livrando-nos de ter que providenciá-los.

Volto em outro post para mostrar como trabalhar com uma célula específica ou uma faixa de células, seus valores, formatações e fórmulas de dentro de um programa Delphi.

Mais Informações
Classes para automatização do Excel

Atualizando Pivot Tables do Excel com scripts

Na semana passada me colocaram uma questão prática envolvendo Excel. Foram incluídas numa planilha diversas Pivot Tables conectadas a tabelas no banco de dados e essas Pivot Tables deveriam ser atualizadas automaticamente num determinado dia/hora, uma vez por semana.

As Pivot Tables são um recurso encontrado no Excel (e em outros programas de manipulação de dados, como o Lotus 1-2-3 e o Calc do Open Office) que permitem a criação de visões multidimensionais dos dados através de operações do tipo arrasta-e-solta nas colunas de valores. O resultado é uma poderosa ferramenta gerencial de análise que permite fazer o cruzamento dos dados das diversas colunas, sumarizando-os. O conceito é mais ou menos como os cubos de informações dos data-minings. Há uma matéria sobre o uso de Pivot Tables neste link.

Como escrevi uma série de posts mostrando como automatizar operações no Excel através de scripts VB, me pareceu que a solução estava bem próxima. Os scripts podem ser considerados como programas, de modo que é possível agendá-los sem problemas no Windows, resolvendo a questão do dia/hora especifico para atualização. Restava, então, desvendar a atualização dos dados em si.

Uma das características das Pivot Tables do Excel é que elas podem ser configuradas para reter as informações usadas para conectar com a fonte de dados, isto é, o nome do servidor e o usuário/senha para acessar o banco de dados. Você pode saber se isso já está configurado abrindo a planilha, selecionando a Pivot Table e pedindo para dar o Refresh nos dados manualmente. Se nenhuma informação sobre a conexão for solicitada pelo Excel, a configuração já está pronta para ser usada pelo script. Considerando que a planilha em questão tenha sido criada com as Pivot Tables associadas a uma fonte de dados externa (uma query num banco de dados, por exemplo), o problema fica restrito a criar um script VB que carregue a planilha, localize as Pivot Tables existentes, atualize os dados de cada uma delas e então salve a planilha com os novos valores.

Estudando o Modelo de Objetos do Excel, encontrei o método PivotTables do WorkSheet, que dá acesso à lista de todas as Pivot Tables existentes numa folha de trabalho. Também encontrei o método que faz a atualização dos dados (RefreshTable). A sintaxe para atualizar a primeira Pivot Table de uma folha de trabalho é como segue.

folha.PivotTables(1).RefreshTable

Como pode ser que não haja Pivot Tables numa determinada folha, é conveniente verificar a existência antes. PivotTables retorna uma lista e a propriedade folha.PivotTables.Count indica quantos elementos há nessa lista. A solução será mais efetiva se usar esse Count para dar Refresh em todas as PivotTables que encontrar.

For I = 1 To objWorkBook.Sheets.Count
Set folha = objWorkBook.Sheets.Item(I)
For j = 1 to folha.PivotTables.Count
folha.PivotTables(j).RefreshTable
Next
Next

objWorkBook.Save
objWorkBook.Close

No exemplo acima, todas as folhas da planilha aberta são percorridas e, para cada folha, todas as Pivot Tables são atualizadas. A instrução Save salva as alterações feitas de volta na mesma planilha que foi aberta. Se quiser salvar numa planilha diferente, use SaveAs - isso permitirá também salvar a planilha num formato diferente, preservando aquela que foi originalmente aberta.

Para ver como abrir uma planilha já existente e como obter a lista de folhas de trabalho dela, veja o post Automatizando a leitura de planilhas Excel.

Para fazer download do script de exemplo, clique aqui.

Mais Informações
Posts sobre automatização do Excel, Referência do Modelo de Objetos do Excel, download do script de exemplo.

Exportando dados para Excel com ADO

Agora que postei um pouco da teoria básica sobre o modelo de classes do ADO, posso cumprir a promessa que fiz de mostrar interação entre o Excel e o ADO através de script, populando uma planilha com os dados trazidos por uma query num banco de dados. Para ver como criar a planilha em si, consulte o post Criando uma planilha através de script já que neste eu vou focar mais a parte do uso do ADO.

De início, vou criar uma conexão com o banco de dados SQL Server pois esse é o banco mais comum entre os Clientes da ABC71. Veja o código usando VBScript:

Dim Cnxn, strCnxn, rs, query
' Conectar ao banco SQL
SetCnxn = CreateObject("ADODB.Connection")
strCnxn = "Provider=SQLOLEDB.1;Data Source=ABCVMS;Initial Catalog=DBTP;Locale Identifier=1046;"
Cnxn.Open strCnxn, "gustavo", ""
Cnxn.Execute ("set language us_english ")
Cnxn.Execute ("set dateformat dmy ")

O comando CreateObject cria uma instância do objeto "ADODB.Connection", isto é, aloca a memória necessária para que possa usar o objeto de conexão. O comando Open que vem logo em seguida estabelece a conexão com o banco de dados. Passo a ele 3 valores: a string de conexão, o nome do usuário do banco de dados e a senha deste usuário. O exemplo traz uma string de conexão para o MS SQL Server; para saber como montar essa string para outros bancos, clique aqui. Os dois comandos Execute que encerram o trecho de código configuram a linguagem e o formato de datas retornados pelo SQL Server.

Já temos a conexão, podemos então submeter uma query no banco de dados:

query = "SELECT * FROM TPUF"
Set rs = Cnxn.Execute (query)
Set folha = objWorkBook.WorkSheets (1)
for j = 0 to rs.Fields.Count - 1
folha.Cells (1,j+2) = rs.Fields.Item(j).Name
next

' Configura o cabeçalho
Set lRng = folha.Range (folha.Cells (1,1), folha.Cells (1,j+1))
lRng.Interior.Color = RGB(240,240,127)
lRng.Interior.Pattern = 1 'xlPatternSolid
lRng.Font.Name = "Tahoma"
lRng.Font.Bold = True
lRng.Font.Size = 11

Neste trecho do código, o comando Execute da conexão submete um SELECT e obtem um Recordset com os registros e campos encontrados. As definições dos campos podem ser consultadas na propriedade Fields, que é uma lista. Através do laço for, esta lista de campos é percorrida e o nome de cada campo é colocado numa coluna da primeira linha da folha de trabalho do Excel. Após o laço for, a coluna com os nomes dos campos é formatada de modo a representar um cabeçalho para os valores a serem recuperados dos registros.

Falta, então, percorrer os registros encontrados e lançar os valores sob a coluna correta na folha da planilha:

i = 2
Do While Not rs.EOF
folha.Cells (i,1) = i - 1 'Número do registro
for j = 0 to rs.Fields.Count - 1
folha.Cells (i,j+2) = rs.Fields.Item(j).Value
next
i = i + 1
rs.MoveNext
Loop

O laço Do While percorre todos os registros retornados pela query, testando se o fim foi atingido através da propriedade EOF (fim de arquivo). O laço for interno percorre a lista de campos do registro atual, lançando o valor de cada um na coluna certa da planilha. Cada registro ocupa uma linha na folha da planilha, sendo que a variável i determina qual é o número da linha. Ela é iniciada com a segunda linha já que a primeira foi preenchida com o cabeçalho.

Como arremate, inclui mais um laço para formatar cada coluna da folha com base no tipo de dado especificado na definição do campo:

for j = 0 to rs.Fields.Count - 1
Set lRng = folha.Range (folha.Cells (1,j+2), folha.Cells (i,j+2))

Select Case rs.Fields.Item(j).Type
Case 4, 5, 6, 14, 131, 139 ' Números c/ decimais
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize
lRng.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
lRng.HorizontalAlignment = -4152 ' xlRight'

Case 2, 3, 16, 17, 18, 19, 20, 21 ' Números Inteiros
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize * 4
lRng.NumberFormat = "#,##0_);[Red](#,##0)"
lRng.HorizontalAlignment = -4152 ' xlRight'

Case 7, 64, 133, 135 ' Datas
lRng.ColumnWidth = 12
lRng.NumberFormat = "m/d/yyyy"
lRng.HorizontalAlignment = -4108 ' xlCenter'

Case Else ' Textos e outros
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize + 3
lRng.NumberFormat = "General"
lRng.HorizontalAlignment = -4131 ' xlLeft'
End Select
next

Neste ponto, a variável i armazena o número da última linha que foi alimentada com valores dos registros. Então, percorro novamente a definição de todos os campos, obtenho a propriedade Type de cada um e determino a formatação mais conveniente. Os tipos de dados são definidos por um enumerado do ADO chamado DataTypeEnum mas nem todos foram incluídos no exemplo. Consulte a documentação desse enumerado para mais detalhes.

Mais Informações
Modelo de Classes do ADO, Referência de Classes do Excel, Criando uma planilha através de script, Formatação de planilhas através de script, Download do script de exemplo.

Incluindo gráfico numa planilha Excel com scripts

Publiquei em junho uma série de posts sobre como automatizar a criação de planilhas Excel através de VBScript. Inclui informações sobre os principais objetos do Excel, um exemplo de como criar uma planilha básica, exemplo mostrando formatação de planilhas e criação de fórmulas, tudo utilizando scripts. Dentro do que eu tinha proposto, faltou incluir informações e exemplo sobre como trabalhar com gráficos numa planilha.

Para isso, vou incrementar o cenário descrito nos outros posts e o exemplo incluído neles. O objetivo é criar um gráfico de linhas que mostre a evolução por dia dos valores das colunas Mês 1 e Mês 2, conforme criados naqueles exemplos.

Há duas formas de se ter gráficos numa planilha: incluir uma nova folha de trabalho que contenha um gráfico ou embutir um Chart dentro de um Worksheet já existente. Vou usar aqui este último método mas, do ponto de vista dos scripts, há poucas diferenças entre um e outro.

Dentro do objeto WorkSheet há uma propriedade chamada Shapes que permite gerenciar todos os objetos desenhados dentro da folha, tais como AutoShapes, FreeForms, imagens e objetos OLE. Como os gráficos são objetos OLE, podem ser facilmente inseridos na folha usando Shapes. O método AddChart do Shapes serve para isso e aceita como parâmetros o tipo de gráfico a ser criado e o posicionamento do gráfico dentro da folha. Apesar do nome da função ser AddChart, o objeto resultante é, na verdade, um Shape. Para obter o gráfico propriamente dito, usamos a propriedade Chart do Shape criado. Veja o exemplo:

Dim grafico
Set grafico = folha.Shapes.AddChart (4, 200, 10).Chart

O número 4 passado como parâmetro indica que o tipo de gráfico a ser criado é o de linhas. Para mais informações sobre os códigos dos tipos válidos, veja a documentação do enumerado XlChartType. Os outros dois números indicam a posição em pontos (pixels) do canto superior esquerdo (a esquerda e o topo, respectivamente) do gráfico. Vou deixar que o próprio Excel calcule o comprimento e altura mas também estes valores poderiam ter sido informados como parâmetros.

O próximo passo é informar ao Excel quais são os dados a serem considerados pelo gráfico. Agora que tenho a variável grafico do tipo Chart posso usar os métodos dessa classe para fazer todas as configurações necessárias. Os dados, por exemplo, consigo ajustar com a função SetSourceData.

grafico.SetSourceData folha.Range("A1:C31"), 2

Com esse comando, o gráfico é configurado para mostrar os dados existentes nas colunas A, B e C, linhas 1 a 31. O número 2 no segundo parâmetro indica que as séries devem ser criadas usando colunas (veja o enumerado XlRowCol) de modo que a primeira coluna (A) contém os valores para o eixo horizontal do gráfico e as outras duas colunas B e C são as séries de dados propriamente ditas. Obs: a Coluna A foi alimentada com os dias e as colunas B e C têm valores para os meses 1 e 2.

O código abaixo faz outras configurações e formatações ao gráfico, como acrescentar Legendas na parte de baixo e um título:

grafico.HasTitle = True
grafico.HasLegend = True
grafico.ChartTitle.Text = "Distribuição por Mês"
grafico.Legend.Position = -4107 ' xlLegendPositionBottom

Como já havia falado em outro post, tudo que você consegue fazer com o Excel através da interface visual você também consegue fazer através de script.

Dica: Uma boa forma de se descobrir como implementar por script uma sequência de passos dentro do Excel é gravar uma macro. Ligue o gravador de macros e execute a sequência de passos desejada. Depois que encerrar a gravação, edite o código VBA gerado para ver as funções que foram chamadas.

Estou usando o Office 2007 para essas implementações mas acredito que não haja diferenças importantes na forma de trabalhar.

O VBScript com o exemplo completo - incluindo os conceitos e exemplos dos outros posts meus - pode ser baixado aqui.

Mais Informações
Objetos para automatização do Excel, criação de planilhas através de script, formatação de planilhas e inclusão de fórmulas com scripts, Exemplo completo de automação do Excel

Fórmulas no Excel através de programação

Certamente o recurso mais utilizado do Excel são as fórmulas, isto é, cálculos envolvendo uma ou mais células. As fórmulas podem ser desde a simples referência ao conteúdo de uma única célula até a inclusão de funções avançadas, como tratamentos estatísticos.

Para falar desse assunto, vou assumir o cenário descrito nos posts sobre criação e formatação de planilhas Excel através de scripts.

Por exemplo, a primeira linha do código abaixo configura a célula B32 (linha 32 da coluna B) para que ela simplesmente exiba o conteúdo da célula B2 (linha 2 da coluna B). Depois, configuro a célula C32 (linha 32 da coluna C) para mostrar a somatória das linhas 2 e 3 da coluna C:

folha.Cells (32,"B").Formula = "=$B$2"
folha.Cells (32,"C").Formula = "=$C$2+$C$3"
folha.Cells (33,"C").Formula = "=$C$2+$C$3+Sheet2!A1"

Veja que o valor da fórmula sempre começa com o sinal de = e que não há espaços separando as referências envolvidas. O sinal $ indica que a referência é fixa, de forma que se esta fórmula for copiada para outra célula, a referência permanece a mesma. Portanto, o sinal $ não é obrigatório e, se for omitido, a referência é modificada automaticamente para refletir a nova localização sempre que a fórmula for copiada.

Também é permitido referenciar células de outras folhas de trabalho, bastando incluir o nome dessa folha seguido do sinal !, como na última linha do trecho acima.

A propriedade Formula usada nos exemplos vem do objeto Range mas até agora só a usamos para acessar a fórmula de células individuais. O que acontece se atribuirmos uma fórmula para um Range que englobe mais de uma célula, como no exemplo que segue?

folha.Range("B32:C32").Formula = "=SUM(B2:B31)"
folha.Range("B33:C33").Formula = "=SUM($B$2:$B$31)"

Se as referências contidas na fórmula possuirem o sinal $ (como na 2a linha do exemplo), todas as células do Range terão exatamente a mesma fórmula e, portanto, o mesmo valor. Se não tiverem esse sinal, o Excel modificará a fórmula para cada célula, substituindo a referência original por outra de acordo com a nova linha/coluna, do mesmo modo que acontece quando copiamos e colamos fórmulas pela interface gráfica do Excel.

Uma última coisa a observar é que a Microsoft tem versões traduzidas do Office para outras linguagens. Se seu Excel está em português, quando você monta fórmulas através da interface gráfica usa os nomes das funções traduzidos para português. Para montar o último exemplo pela interface, ao invés de SUM você usaria SOMA, por exemplo.

Então este script só funcionaria para o Excel em inglês? A resposta é não. A Microsoft mantem mais de uma propriedade com fórmulas. A propriedade Formula contem sempre funções com o nome original, em inglês. Se quiser configurar fórmulas com nome traduzido, use a propriedade FormulaLocal pois nela se usa funções cujos nomes estão na linguagem local. As linhas abaixo são equivalentes para quem tem o Excel em português:

folha.Range ("B32:C32").Formula = "=SUM(B2:B31)"
folha.Range("B33:C33").FormulaLocal = "=SOMA(B2:B31)"

Tenha em mente, no entanto, que atribuir uma fórmula usando a propriedade FormulaLocal limita o script a ser usado exclusivamente em ambientes onde o Excel usa a mesma lingua que você.

Mais Informações
Modelo de objetos do Excel, Leitura e Gravação de planilhas Excel através de scripts, Download do Exemplo.

Formatando planilhas Excel através de programação

Já descrevi em posts anteriores como automatizar a leitura e a criação de planilhas Excel, bem como fiz um overview dos principais objetos que compõem a estrutura dessa ferramenta. Agora, vou mostrar objetos auxiliares para formatar o conteúdo das planilhas.

Há diversas partes de uma célula ou de um grupo de células (Range) que podem ser formatadas. Essa formatação inclui o tipo de letra (fontes), formato de apresentação para números e datas, bordas, cor de fundo, etc.

Usando como exemplo a planilha do post sobre criação de planilhas, o código abaixo mostra uma parte da formatação de um cabeçalho:

folha.Cells (1,"B") = "Mês 1"
folha.Cells (1,"C") = "Mês 2"
folha.Range ("B1:C1").ColumnWidth = 12
folha.Range ("B1:C1").Interior.Color = RGB(210, 220, 255)
folha.Range ("B1:C1").Interior.Pattern = 1 'xlPatternSolid

A propriedade Interior, que é acessível através através de um Range, representa a parte interna de cada célula contida no Range, permitindo configurar a cor de fundo e o padrão de preenchimento dessas células. Usei também a propriedade ColumnWidth do Range para modificar o comprimento das 2 colunas utilizadas.

Outro objeto importante para a formatação é o Font, que permite modificar a aparência das letras de cada célula. No trecho abaixo, instruo o Excel para que use o tipo de Fonte Arial Bold com tamanho 14 na faixa de células que engloba as colunas B e C da primeira linha.

folha.Range ("B1:C1").Font.Name = "Arial"
folha.Range ("B1:C1").Font.Bold = True
folha.Range ("B1:C1").Font.Size = 14

Use o objeto Borders para modificar simultaneamente a aparência das 4 bordas de todas as céulas de um Range. Para configurar bordas de forma independente, use a propriedade Items usando como índice um dos valores do enumerado XlBordersIndex.

folha.Range ("B1:C31").Borders.LineStyle = 1 'xlContinuous
folha.Range ("B1:C31").Borders.Weight = 2 'xlThin
folha.Range ("B1:C31").Borders.Color = RGB(0,0,0)
' Linha embaixo do cabeçalho mais grossa
' (xlEdgeBottom = 9)
folha.Range ("B1:C1").Borders(9).Weight = 4

Na primeira parte, indico que quero bordas em todas as células pertencentes à àrea que vai da linha 1 à 31, colunas B e C. Essa é a região que foi alimentada no outro post. Na última linha desse trecho, indico que a borda inferior da área que é meu cabeçalho deve ter uma linha mais grossa. Como Items é a propriedade padrão de Borders, não é preciso explicitá-la no script, embora outras linguagens possam exigir isso.

Há no Range ainda uma propriedade para configurar o formato de apresentação do conteúdo das células. Trata-se de NumberFormat, que aceita como valor um texto representando a formatação desejada.

folha.Range ("B1:C1").NumberFormat = "General"
folha.Range ("B2:C31").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
folha.Range ("B1:C31").HorizontalAlignment = 4 ' xlRight

Neste exemplo, "General" indica que o contéudo é um texto simples. Já a sequência #,##0.00_) significa que o Excel deve formatar as células indicadas no Range com 2 casas decimais, que não deve mostrar zeros à esquerda e que deve usar o separador de milhar para números maiores que 999. O que está depois do ponto-e-vírgula é a formatação a ser usada caso o valor da célula seja negativo.

Mais uma vez, esse exemplo foi construído com a versão 2007 do Office e pode ser necessário modificar uma ou outra linha do código para trabalhar com versões anteriores.

Volto depois com o uso de fórmulas e outros detalhes. Clique aqui para fazer o download do exemplo.

Mais Informações
Modelo de objetos do Excel, Leitura e Gravação de planilhas Excel através de scripts, objeto Interior, objeto Font, objeto Borders.

Criando uma planilha Excel através de script

Neste post, pretendo mostrar na prática os objetos do Excel que descrevi no post anterior e montar dinamicamente uma planilha usando VBScript. O ideal seria trabalhar com dados vindos de um banco de dados como o SQL Server, por exemplo. Mas, como também estou preparando posts para mostrar o uso de ADO e banco de dados, o exemplo que usarei aqui vai trabalhar com informações geradas localmente. Após os posts sobre ADO volto à carga para melhorar o exemplo.

Da mesma forma que já havia mostrado no post sobre leitura de uma planilha Excel, devemos começar o script criando uma instância do objeto Application pois é a partir dele que conseguiremos acesso aos demais recursos do Excel:

Dim objExcel
Dim objWorkBook
Dim folha, i
Set objExcel = CreateObject("EXCEL.APPLICATION")

O próximo passo é disponibilizar uma planilha para podermos trabalhar. No exemplo do outro post, disponibilizei uma planilha através do comando Open da lista de Workbooks mas aqui eu quero criar um nova planilha, vazia. A instrução abaixo comanda essa criação, como se tivéssemos entrado no Excel e selecionado New no menu File e pedido para criar uma nova planilha em branco.

Set objWorkBook = objExcel.Workbooks.Add

Com isso, temos agora uma planilha com 3 folhas (Worksheets). Se for necessário, podemos adicionar outras folhas ou remover aquelas que não formos usar.

A seguir, vou usar as rotinas do VBScript que tratam números aleatórios para gerar duas colunas e 30 linhas com dados:

i = 2
Randomize
Set folha = objWorkBook.WorkSheets (1)

while (i <= 31)
folha.Cells (i,2) = Rnd * 100
folha.Cells (i,"C") = Rnd * 100
i = i + 1
wend

Algumas explicações sobre o que está acontecendo acima:

• Randomize inicia o gerador interno de números aleatórios (randômicos) do vbScript, evitando que se gere sempre a mesma sequência.
  
• objWorkBook.WorkSheets (1) referencia a primeira folha de trabalho (tabela ou Worksheet) do Workbook que criamos. Essa referência é salva numa variável chamada folha por questão de comodidade e clareza no código, mas não é obrigatório que se acesse dessa forma.
  
• A propriedade Cells dá acesso ao conteúdo e formatação de células individuais numa folha. Posso referenciar uma célula ou grupo de células informando o número da linha e da coluna. Também posso fazer essa referência através da(s) letra(s) que identifica(m) cada coluna. Os dois métodos são equivalentes e aparecem no exemplo acima para alimentar as linhas da coluna 2 (ou B) e C (ou 3), respectivamente.
  
• A função Rnd gera um número aleatório entre 0 e 1. Multiplicando por 100, obtenho números entre 0 e 100.

Agora que os dados da planilha estão lançados, podemos salvar o conteúdo e encerrar a automação do Excel.

objWorkBook.SaveAs "c:\temp\planilha.xlsx", 51
objWorkBook.Close
Set objWorkBook = Nothing
Set objExcel = Nothing

Passei dois parâmetros para a função que salva a planilha: o nome do arquivo para onde quero que a planilha seja salva e um código que indica o formato com que o arquivo será criado - pode ser, por exemplo, uma versão anterior do Excel, HTML, modelo de planilha (template), XML, etc. O valor 51 indica que desejo utilizar o formato padrão de planilhas. Outros códigos aceitos podem ser encontrados aqui. Caso já exista um arquivo com o nome dado, o Excel vai mostrar uma caixa de diálogo solicitando confirmação antes de sobrepor o arquivo existente. Usei o Excel 2007 para montar esse exemplo mas creio que as propriedades e métodos usados aqui estão disponíveis também em versões anteriores. Em posts futuros, mostro como incluir fórmulas, formatações, gráficos e outros recursos que forem oportunos.

Mais Informações Excel Object Model Reference, Conceitos básicos das classes do Excel, Automatização da leitura de uma planilha Excel.

Classes para automatização do Excel

Escrevi há alguns dias um post mostrando como automatizar a leitura de uma planilha Excel mas não cheguei a falar muito sobre os objetos que são expostos pelo Excel para uso em automação. Como pretendo colocar outros posts sobre o assunto - incluindo a criação de planilhas - achei melhor dar um overview desses objetos antes.

Segundo a documentação da Microsoft no MSDN, tudo que você pode fazer através da interface gráfica do usuário também pode fazer via automação. Aliás, usando automação há coisas que você pode fazer que não estão disponíveis na interface.

Quando falo automação, me refiro a qualquer forma de acessar por programação o Excel, seja através do VBA embutido na aplicação, VBScripts externos ou através de programas Delphi ou C#. Os objetos que vou descrever aqui são os mesmos para todos esses cenários. Lembro, entretanto, que em todos eles é preciso ter o Excel instalado para que os objetos estejam acessíveis.

Application
Este objeto é a porta de entrada para automatizar o Excel. A grosso modo, equivale a carregar o programa Excel. É através dele que se controla todas as funções e configurações, permitindo acesso às planilhas propriamente ditas. Realiza, ainda, a carga e gravação de arquivos.
Se você vai automatizar usando VBA dentro do Excel, uma instância de Application está sempre disponível, automaticamente. Qualquer das outras formas, será preciso antes criar (ou obter) a instância antes de sair usando.

Workbook
Um Workbook corresponde a um arquivo XLS (ou XLSX, no Excel 2007), isto é, é a representação computacional de uma planilha Excel. Um objeto desse tipo pode ser acessado através da propriedade ActiveWorkbook do Application, que representa a planilha atualmente ativa dentro do Excel. Application tem ainda uma lista chamada Workbooks que dá acesso a cada uma das planilhas que estiverem carregadas no Excel num determinado momento.

Worksheet
Cada Workbook é composto por uma ou mais tabelas, com diversas linhas e colunas. A classe que manipula essas tabelas é chamada Worksheet e, por padrão, um Workbook é criado com três Worksheets (ou folhas de trabalho), nomeadas como Sheet1, Sheet2 e Sheet3. Esses nomes e outras propriedades de cada folha podem ser alterados por automação e estão disponíveis na propriedade Worksheets, que é uma lista com todas as folhas que compõe o Workbook com o qual se está trabalhando.

Range
Dentre os conceitos de objetos do Excel, o de Range é provavelmente o mais utilizado pois é a forma mais básica de organização e acesso das informações. É através de Ranges que se acessa o valor individual de uma célula, sua formatação, a fórmula que eventualmente esteja associada a ela, etc..
Quando montamos fórmulas de cálculo ou gráficos, teremos que nos referir a um conjunto de células agrupadas em um ou mais Ranges. A formatação de células também é feita através de Range, quer ele represente uma única célula dentro de uma folha (Worksheet), um região englobando diversas células, uma linha ou uma coluna inteiras ou até mesmo um arranjo tridimensional de células espalhas por múltiplas folhas.

Mostro em outros posts como usar esses conceitos para criar uma planilha.

Mais Informações
Working with Microsoft Excel Objects, Referência do Modelo de Objetos do Excel.

Automatizando o Excel com scripts - Leitura

Há uma máxima que corre entre os fabricantes de ERP que diz que o maior concorrente dos ERPs é o Excel. Por sua versatilidade em fazer cálculos, sumarizar informações e apresentar resultados em gráficos, as planilhas Excel são uma opção difícil de bater. Mesmo quando o próprio ERP disponibiliza de forma nativa os mesmos recursos, sempre há os que se sentem mais confortáveis manipulando planilhas.

Com a ABC71 isso não é diferente. E como em casa de ferreiro o espeto é de pau, nós também temos nossas planilhas. Vou usar como exemplo um VBScript que usamos para carregar no banco de dados uma planilha de previsão de tempos de migração de fontes para Web, mostrando como ler o conteúdo. Um VBScript é um arquivo texto comum - a única diferença é que ao invés de TXT ele tem extensão VBS, podendo ser executado com um duplo-clique pelo Windows Explorer.

O primeiro passo é criar um objeto que representa a aplicação Excel.

Set objExcel = CreateObject("EXCEL.APPLICATION")

Isso carrega o Excel, permitindo que o script acesse seus comandos. Como quero trabalhar com um planilha que já existe, o passo seguinte é instruir o Excel a carregá-la. O Excel mantem uma lista das planilhas abertas em sua propriedade Workbooks; ela também é responsável pela abertura de planilhas:

Set objWorkBook = objExcel.Workbooks.Open("c:\Web\tempos.xls")

O layout da minha planilha incluir uma folha (Sheet) para cada um dos módulos do ERP da ABC71. Vou, então, montar um laço que me permita percorrer as informações contidas em cada uma das folhas:

For I = 1 To objWorkBook.Sheets.Count
Set folha = objWorkBook.Sheets.Item(I)
' Incluir aqui o processamento
' dos dados da folha atual
Next

Nesse trecho, usei a propriedade Sheets do Workbook (planilha) aberto. Ela lista todas as folhas da planilha atual. Count é a quantidade de folhas existentes na lista e Item permite acessar cada uma delas de forma independente, através da posição (índice) que ocupam na lista. Esse índice vai de 1 até a quantidade de folhas na lista.

Dentro de cada folha, tenho colunas com as previsões para a migração dos fontes pertencentes ao módulo. Há um cabeçalho na primeira e na segunda linha; portanto, vou começar a leitura na linha 3. Como não sei de antemão quantas linhas têm que ser processadas, vou percorrê-las até que não possuam mais dados.

lin = 3
while Trim (folha.Cells (lin, 1)) <> ""
on error resume Next

' Tratar dados da linha aqui

lin = lin + 1
wend

Uso a sintaxe Cells(lin,col) para obter o valor inserido numa célula específica - aquela que está onde a linha lin cruza com a coluna col. É importante observar que o valor retornado por Cells(lin,col) deve ser tratado com o tipo de dado apropriado. Isto é, as datas são do tipo Date, números são de um dos tipos numéricos (inteiros ou com decimais) e textos são do tipo string. Dependendo do contexto, será preciso realizar conversões entre os tipos de dados. Por exemplo, um comando SQL a ser submetido ao banco de dados é necessariamente um texto e dados que são numéricos - como Tempo em minha planilha - devem ser convertidos através da função CStr antes que possam ser concatenados ao comando:

tempoStr = CStr (folha.Cells (lin, 5))

Para finalizar, é preciso fechar o Workbook que foi aberto e descarregar o Excel quando ambos não forem mais necessários ao script:

objWorkBook.Close True
Set objWorkBook = Nothing
objExcel.Quit
Set objExcel = Nothing

Como isso é um script, também pode ser agendado como uma tarefa no Windows ou ainda ser incluído numa aplicação HTML.

Mais Informações
Objetos do Excel, Referência do VBScript