Ei, Equipe de Scripts!Aprendizado no Excel
A Equipe de Scripts da Microsoft
Depois de ficar o mês passado morando e trabalhando na Austrália, este membro da Equipe de Scripts está impressionado com a criatividade e a flexibilidade de seus colegas australianos em geral. Há algumas noites, um amigo australiano convidou este membro da Equipe de Scripts e sua esposa para jantarem. Ele disse que o restaurante ficava perto do nosso hotel.
Quarenta minutos depois, chegamos a um belo restaurante com vista para o Porto de Sidney, com a Casa da Ópera de Sidney iluminada como uma concha do mar lavada em uma praia multicolorida. A Harbour Bridge – a maior ponte de um só vão no mundo – impõe ordem unindo as duas penínsulas, como um grande grampo prendendo duas placas arqueadas.
De volta em casa, não se pode dizer que um restaurante seja perto depois de 40 minutos de caminhada. É um exercício diário, precedido de 15 minutos de aquecimento e alongamento.
Um dos motivos pelos quais este membro da Equipe de Scripts está gostando desta viagem é a diferente perspectiva que meus colegas australianos têm. Eles parecem ter um modo exclusivo de ver as coisas, que freqüentemente desafia minhas noções preconcebidas de como as coisas deveriam ser feitas.
Em scripts, é muito fácil para que tudo vire rotina à medida que você persevera com as metodologias de tentativa e acerto. Muitas vezes, usar a mesma abordagem para obter os mesmos resultados é exatamente o que se quer.
Por exemplo, de uma perspectiva do monitoramento, usando o Windows PowerShell você pode obter facilmente um interessante instantâneo da utilização do processo no seu computador local. Você usa o cmdlet Get-Process e é saudado com um excelente resultado ordenado, como mostra a Figura 1.
Figura 1 Análise dos processos locais usando o Get-Process
Os resultados do cmdlet Get-Process são úteis em muitas circunstâncias. Eles mostram o número de identificadores abertos, algumas exibições diferentes do uso de memória e um instantâneo da utilização da CPU. E, quando acompanhado do Windows PowerShell 2.0, você poderá usar o Get-Process até mesmo com um parâmetro –computername para recuperar essa visão geral de um computador remoto. Com toda essa benção, por que alguém se importaria em investigar algo além disso?
O problema é que há muitos detalhes ocultos nas longas colunas de dados. Todos esses dados tendem a mascarar os detalhes mais importantes. E, embora seja excelente que, no futuro, o Windows PowerShell 2.0 ofereça suporte ao parâmetro –computername, agora ele não faz absolutamente nada para um administrador de rede. Portanto, teremos que usar apenas o WMI (Windows Management Instrumentation) e a classe WMI Win32_Process para fazer o nosso monitoramento de sistemas remotos e apresentar as informações de um modo que seja verdadeiramente útil. Se você pensou que o resultado do Get-Process seria amplo, analise o resultado do Win32_Process, mostrado na Figura 2.
Figura 2 Uso do WMI para visualizar processos
Então, o que o pobre do administrador de rede faz quando tudo o que ele quer é um relatório fácil de ler sobre a quantidade de memória que está sendo usada? É em casos como esse que você precisa pensar criativamente, mudar de perspectiva e começar a usar a automação no Excel. É mais do que provável que você tenha o Microsoft Office Excel instalado no computador. Se você for como nós, você não é um especialista, mas ele faz parte do Microsoft Office system e você também pode tirar proveito disso.
É muito difícil automatizar o Excel? Na verdade, é fácil porque a Microsoft criou um modelo de automação especificamente para trabalhar com o Excel. A ID do programa é Excel.Application e é um objeto COM. Quando você cria uma instância do objeto Excel.Application, por padrão, o Excel é iniciado e executado, mas ele não está visível. No entanto, é possível usar a propriedade visible para tornar o Excel visível.
Vejamos como criar o objeto Excel.Application, consultar o status da propriedade visible e então definir a propriedade visible como true.
PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true
Quando você tiver feito isso, verá uma exibição um tanto esquisita do Excel, que se parecerá com um shell do aplicativo Excel (veja a Figura 3). Não há pastas de trabalho, não há planilhas – nada, apenas o Excel “descoberto”.
Figura 3 Excel “descoberto” – sem pastas de trabalho ou planilhas
Precisamos adicionar uma pasta de trabalho ao aplicativo. Para fazer isso, usamos o método add do objeto workbook. O objeto workbook é acessado a partir do objeto principal Excel.Application, como você pode ver aqui onde armazenamos o objeto workbook em uma variável denominada $workbook:
$workbook = $excel.Workbooks.add()
Agora, precisamos conectar a uma planilha específica. Por padrão, quando uma pasta de trabalho é adicionada ao Excel, três planilhas são adicionadas à pasta de trabalho. Essas planilhas podem ser tratadas por número. Nessa linha do código, conectamos à primeira planilha e armazenamos o objeto retornado da planilha em uma variável nomeada $sheet:
$sheet = $workbook.worksheets.Item(1)
Agora, podemos gravar dados nessa planilha. As informações em planilhas do Excel são armazenadas em células. Como as células residem em planilhas, você usa o objeto spreadsheet armazenado na variável $sheet para ter acesso a uma célula específica. Você faz isso usando números que se referem a linhas e colunas na planilha. Uma coisa um pouco confusa é que, nas planilhas do Excel, as linhas são números e as colunas são letras. No entanto, usando o modelo de automação, as linhas e as colunas são números. O primeiro número é a linha e o segundo número é a coluna. É possível gravar na célula simplesmente fazendo uma atribuição de valor a essa célula específica:
$sheet.cells.item(1,1) = "Test"
Após adicionar uma pasta de trabalho ao nosso objeto Excel.Application e dados a uma célula na planilha, a nossa pasta de trabalho do Excel será semelhante à mostrada na Figura 4.
Figura 4 Acréscimo de valor a uma célula
Com isso em mente, vamos criar algo útil. Vamos obter uma coleção de informações de processo do WMI, gravar o nome e o uso de memória de cada processo em uma planilha do Excel e, em seguida, criar um gráfico para destacar a memória utilizada. Isso é exatamente o que o WriteProcessInformationToExcel.ps1 faz. Você encontrará o script completo no site da TechNet Magazine.
Iniciamos o script usando o cmdlet Get-WmiObject para recuperar uma coleção de informações sobre processos. Usamos a classe WMI Win32_Process para obter essas informações e as armazenamos na variável $processes:
$processes = Get-WmiObject -class Win32_Process
Agora, criamos uma instância do objeto Excel.Application e a armazenamos na variável $excel; em seguida, tornamos o aplicativo visível e adicionamos uma pasta de trabalho. Normalmente, você realizaria essas etapas para qualquer automação do Excel que decidisse fazer. Aqui estão as linhas do código:
$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()
Uma de nossas chateações com o Excel é que uma pasta de trabalho sempre cria três planilhas. Consideramos isso um desperdício, pois raramente usamos uma única planilha, muito menos três. Felizmente, com a automação, podemos simplesmente excluir as planilhas extras usando a coleção de planilhas para conectar à terceira planilha e chamar o método delete. O mesmo pode ser feito para excluir a segunda planilha:
$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()
Em seguida, renomeamos a planilha restante. Isso é importante porque se você decidir usar o ADO (ActiveX data objects) para consultar a planilha do Excel, você utilizará o nome da planilha na cadeia de conexão. Portanto, para tornar o código legível e intuitivo, a planilha deverá ter um nome lógico. Para renomear a planilha, basta atribuir um novo valor à propriedade name da planilha específica. Aqui, renomeamos a primeira planilha como "Processes":
$workbook.WorkSheets.item(1).Name = "Processes"
Agora, precisamos conectar à planilha renomeada. Use o método Item do objeto worksheets e dê a ele o nome da planilha.
$sheet = $workbook.WorkSheets.Item("Processes")
A primeira linha da planilha terá as informações do cabeçalho. Desenharemos uma borda e colocaremos a propriedade names em negrito. Como resultado, os dados iniciarão na linha dois, de forma que atribuiremos o valor de 2 à variável de contador $x:
$x = 2
Em seguida, quatro linhas de código criam quatro tipos de enumeração. Os tipos de enumeração são usados para informar ao Excel quais valores são permitidos para tipos específicos de opções. Como exemplo, a enumeração xlLineStyle é usada para determinar o tipo de linha a ser desenhada: dupla, tracejada e assim por diante. Esses valores de enumeração são documentados no MSDN.
Para facilitar a leitura do código, criamos aliases de atalho para cada um dos quatro tipos de enumeração que utilizaremos. Essencialmente, estamos lançando uma cadeia que representa o nome da enumeração como um [type]. Essa técnica é, na verdade, um truque bem interessante:
$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
Agora, precisamos formatar a primeira linha. Colocamos a fonte em negrito, definimos a linha como xlDashDot, permitimos que a cor seja automaticamente especificada e definimos a espessura da borda como média:
For($b = 1 ; $b -le 2 ; $b++)
{
$sheet.cells.item(1,$b).font.bold = $true
$sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
$sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}
Quando isso for concluído, atribuiremos valores à primeira linha, selecionando a célula com o método item e atribuindo as coordenadas de linha e coluna. Em seguida, realizamos uma atribuição de valor direta para gravar os cabeçalhos de coluna:
$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"
Agora, precisamos inserir nas células apropriadas as informações do processo, armazenadas na variável $processes, que foi criada como resultado de nossa consulta ao WMI. Usamos a instrução foreach para percorrer a coleção de informações do processo. Definimos a variável $process como sendo o nosso enumerador (espaço reservado) para a coleção e escolhemos as propriedades name e workingSetSize para gravar na primeira e na segunda colunas, respectivamente.
Aqui entra em cena a variável $x. Iniciamos com a segunda linha e, à medida que percorremos a coleção de processos, incrementamos o valor da variável $x, de modo que sempre aponte para a linha atual na coleção. Essa é a forma pela qual podemos classificar todos os dados armazenados na coleção $processes de informações do processo:
Foreach($process in $processes)
{
$sheet.cells.item($x, 1) = $process.name
$sheet.cells.item($x,2) = $process.workingSetSize
$x++
} #end foreach
Depois de preencher a planilha do Excel, queremos ajustar o tamanho das colunas, de modo que as células tenham o mesmo tamanho dos dados armazenados nelas. Para isso, criamos um intervalo especificando as coordenadas de coluna a serem usadas; no entanto, podemos simplesmente usar a propriedade usedRange a partir da planilha. Quando criamos o nosso objeto range, escolhemos a propriedade EntireColumn e usamos o método AutoFit para redimensionar as colunas. Como esse método sempre retorna dados, canalizamos os resultados para o cmdlet Out-Null. Isso não confunde o console com informações inúteis. Este é o código que usamos:
$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null
Nesse ponto, poderíamos parar e teríamos uma excelente planilha com o conjunto de trabalho de memória e nome de todos os nossos processos. Mas vamos continuar e criar um gráfico. É fácil. Usamos o método add do objeto charts a partir da pasta de trabalho. Como esse método também retorna informações que não queremos ver, canalizamos os resultados para o cmdlet Out-Null, como mostrado aqui:
$workbook.charts.add() | out-null
O comando acima adiciona um gráfico de linha. Se você quiser definir outro tipo de gráfico, você precisa usar um dos valores de enumeração de tipo de gráfico. Para isso, você pode fazer uso dos valores de enumeração microsoft.office.interop.excel.xlChartType, como o tipo xl3DPieExploded. O tipo xl3DPieExploded cria, não surpreendentemente, um gráfico de pizza tridimensional que é explodido. Atribuímos esse tipo de enumeração à propriedade chartType do objeto ActiveChart. Em seguida, atribuímos a fonte de dados ao gráfico para ser o intervalo que definimos na variável $range. O resultado é que você vê o gráfico de linha muito rapidamente e, em seguida, o gráfico de pizza 3D é explodido (ampliado) na tela. Este é o código:
$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)
Como estamos fazendo testes, vamos girar o gráfico de pizza. Podemos fazer isso usando a propriedade rotation do objeto ActiveChart. Usamos uma instrução for para incrementar a contagem para 360 por 15. 360 é o número de graus em um círculo; o gráfico irá girar um círculo completo de 15 graus por vez. Isso é realmente interessante. Este é o código para executar essa tarefa:
For($i = 1 ; $i -le 360 ; $i +=15)
{
$workbook.ActiveChart.rotation = $i
}
A última coisa que precisamos fazer é salvar a planilha. Para fazer isso, usamos o cmdlet Test-Path para ver se a planilha já existe. Caso já exista, excluímos a antiga usando o cmdlet Remove-Item e, em seguida, salvamos a pasta de trabalho atual no local armazenado na variável $strPath. Usamos o objeto ActiveWorkbook do objeto Excel.Application e o método SaveAs para salvar a pasta de trabalho. Se ainda não houver uma cópia da planilha salva, usamos o método SaveAs do objeto ActiveWorkbook e a salvamos diretamente:
IF(Test-Path $strPath)
{
Remove-Item $strPath
$Excel.ActiveWorkbook.SaveAs($strPath)
}
ELSE
{
$Excel.ActiveWorkbook.SaveAs($strPath)
}
Quando o script for executado, você verá um gráfico semelhante ao mostrado na Figura 5.
Figura 5 O gráfico de pizza Processes explodido
A planilha em si está na guia Processes. A Figura 6 mostra os cabeçalhos de coluna, o estilo de linha pontilhada/tracejada que selecionamos para a borda e os cabeçalhos da coluna em negrito. As propriedades de tamanho do conjunto de trabalho e nomes de processo são as duas colunas de dados que exibimos.
Figura 6 A planilha concluída
Como você pode ver, com o modelo de automação Excel.Application, você tem as ferramentas necessárias que permitem processar dados de seus servidores de um modo que permita aproveitar as ferramentas de análise e gráfico desse avançado e poderoso aplicativo.
Ed Wilson é consultor sênior na Microsoft e especialista renomado em scripts. Ele é o Microsoft Certified Trainer que apresenta o conhecido workshop do Windows PowerShell para clientes Microsoft Premier no mundo inteiro. Ed escreveu oito livros, incluindo vários sobre scripts do Windows, e contribuiu em praticamente dezenas de outros livros. Ed tem mais de 20 certificações do setor.
Craig Liebendorfer é escritor profissional e editor da Web da Microsoft há muito tempo. Craig ainda não acredita que tem um trabalho que pague a ele para trabalhar com palavras diariamente. Uma de suas coisas favoritas é o humor irreverente, então ele deve se encaixar bem aqui. Ele considera que sua maior realização na vida é sua linda filha.