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.