PowerShell :: Gerando Gráficos no Excel

Da mesma forma que podemos gerar e extrair dados do Excel a partir do PowerShell podemos, também, gerar e manipular gráficos.

Para isso vou usar um exemplo extraido de “Powershell - export info to Excel”. Outros exemplos poderão ser obtidos em: “Modify an embeded Excel Chart using PowerShell”. As constantes, para o tipo de gráfico a ser gerado, obtive em: “Microsoft Excel Constants [Excel 2003 VBA Language Reference]”

Para a montagem do gráfico “popularemos” uma planilha do Excel com dados de CPU dos processos ativos.

Vamos ao que interessa:

#Definindo a "Cultura" para pt-BR $ptBR = [System.Globalization.CultureInfo]::CreateSpecificCulture("pt-BR") [System.Threading.Thread]::CurrentThread.CurrentCulture = $ptBR

#CultureInfo Class

#Criando o Objeto Excel e tornando-o visível $objExcel = New-Object -comobject Excel.Application $objExcel.Visible = $True

#Criando uma nova Pasta de Trabalho $objWorkbooks = $objExcel.Workbooks.Add()

#Selecionando a Planilha 1 $objWorksheets = $objWorkbooks.Worksheets $objWorksheet = $objWorksheets.Item(1)

#Obtendo a coleção de dados a partir dos processos ativos e "exportando-os" para a Planilha ativa $processes = Get-Process $lastcell = $processes.Count for ($index = 0; $index -lt $lastcell; $index++) {         $objWorksheet.Cells.Item($index+1,1) = $processes[$index].ProcessName         $objWorksheet.Cells.Item($index+1,2) = $processes[$index].CPU }

#Selecionando o intervalo de dados $range = $objWorksheet.usedRange

#Adicionando um Grafico na Pasta de Trabalho $objWorkbooks.Charts.Add() | out-null

#Selecionando o Tipo de Gráfico $chartType = "microsoft.office.interop.excel.xlChartType" -as [type] $objWorkbooks.ActiveChart.chartType = $chartType::xl3DArea

#Alterado o Tipo de Gráfico $objWorkbooks.ActiveChart.chartType = $chartType::xlBarOfPie

#Setando/Definindo a origem dos Dados $objWorkbooks.ActiveChart.SetSourceData($range)

 

#Salvando a Planilha $objWorkbooks.SaveAs('d:\tmp\Process.xlsx')

 

#Finalizando o Objeto $objExcel.Quit()

 

#Removendo os Vínculos $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorksheet) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorksheets) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorkbooks) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel)

 

#Existindo processo pendente, finalizamo-o if (ps excel) { kill -name excel}

O “Script” completo, com algums opções de Gráfico:

#Definindo a "Cultura" para pt-BR $ptBR = [System.Globalization.CultureInfo]::CreateSpecificCulture("pt-BR") [System.Threading.Thread]::CurrentThread.CurrentCulture = $ptBR #Criando o Objeto Excel e tornando-o visível $objExcel = New-Object -comobject Excel.Application $objExcel.Visible = $True #Criando uma nova Pasta de Trabalho $objWorkbooks = $objExcel.Workbooks.Add() #Selecionando a Planilha 1 $objWorksheets = $objWorkbooks.Worksheets $objWorksheet = $objWorksheets.Item(1) #Obtendo a coleção de dados a partir dos processos ativos e "exportando-os" para a Planilha ativa $processes = Get-Process $lastcell = $processes.Count for ($index = 0; $index -lt $lastcell; $index++) {         $objWorksheet.Cells.Item($index+1,1) = $processes[$index].ProcessName         $objWorksheet.Cells.Item($index+1,2) = $processes[$index].CPU } #Selecionando o intervalo de dados $range = $objWorksheet.usedRange #Adicionando um Grafico na Pasta de Trabalho $objWorkbooks.Charts.Add() | out-null #Selecionando o Tipo de Gráfico $chartType = "microsoft.office.interop.excel.xlChartType" -as [type] $objWorkbooks.ActiveChart.chartType = $chartType::xl3DArea $objWorkbooks.ActiveChart.chartType = $chartType::xl3DAreaStacked $objWorkbooks.ActiveChart.chartType = $chartType::xl3DAreaStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xl3DBarClustered $objWorkbooks.ActiveChart.chartType = $chartType::xl3DBarStacked $objWorkbooks.ActiveChart.chartType = $chartType::xl3DBarStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xl3DColumn $objWorkbooks.ActiveChart.chartType = $chartType::xl3DColumnClustered $objWorkbooks.ActiveChart.chartType = $chartType::xl3DColumnStacked $objWorkbooks.ActiveChart.chartType = $chartType::xl3DColumnStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xl3DLine $objWorkbooks.ActiveChart.chartType = $chartType::xl3DPie $objWorkbooks.ActiveChart.chartType = $chartType::xl3DPieExploded $objWorkbooks.ActiveChart.chartType = $chartType::xlArea $objWorkbooks.ActiveChart.chartType = $chartType::xlAreaStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlAreaStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlBarClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlBarOfPie $objWorkbooks.ActiveChart.chartType = $chartType::xlBarStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlBarStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlBubble $objWorkbooks.ActiveChart.chartType = $chartType::xlBubble3DEffect $objWorkbooks.ActiveChart.chartType = $chartType::xlColumnClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlColumnStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlColumnStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlConeBarClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlConeBarStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlConeBarStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlConeCol $objWorkbooks.ActiveChart.chartType = $chartType::xlConeColClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlConeColStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlConeColStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderBarClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderBarStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderBarStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderCol $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderColClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderColStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlCylinderColStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlDoughnut $objWorkbooks.ActiveChart.chartType = $chartType::xlDoughnutExploded $objWorkbooks.ActiveChart.chartType = $chartType::xlLine $objWorkbooks.ActiveChart.chartType = $chartType::xlLineMarkers $objWorkbooks.ActiveChart.chartType = $chartType::xlLineMarkersStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlLineMarkersStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlLineStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlLineStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlPie $objWorkbooks.ActiveChart.chartType = $chartType::xlPieExploded $objWorkbooks.ActiveChart.chartType = $chartType::xlPieOfPie $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidBarClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidBarStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidBarStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidCol $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidColClustered $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidColStacked $objWorkbooks.ActiveChart.chartType = $chartType::xlPyramidColStacked100 $objWorkbooks.ActiveChart.chartType = $chartType::xlRadar $objWorkbooks.ActiveChart.chartType = $chartType::xlRadarFilled $objWorkbooks.ActiveChart.chartType = $chartType::xlRadarMarkers #$objWorkbooks.ActiveChart.chartType = $chartType::xlStockHLC #$objWorkbooks.ActiveChart.chartType = $chartType::xlStockOHLC #$objWorkbooks.ActiveChart.chartType = $chartType::xlStockVHLC #$objWorkbooks.ActiveChart.chartType = $chartType::xlStockVOHLC $objWorkbooks.ActiveChart.chartType = $chartType::xlSurface #$objWorkbooks.ActiveChart.chartType = $chartType::xlSurfaceTopView #$objWorkbooks.ActiveChart.chartType = $chartType::xlSurfaceTopViewWireframe #$objWorkbooks.ActiveChart.chartType = $chartType::xlSurfaceWireframe $objWorkbooks.ActiveChart.chartType = $chartType::xlXYScatter $objWorkbooks.ActiveChart.chartType = $chartType::xlXYScatterLines $objWorkbooks.ActiveChart.chartType = $chartType::xlXYScatterLinesNoMarkers $objWorkbooks.ActiveChart.chartType = $chartType::xlXYScatterSmooth $objWorkbooks.ActiveChart.chartType = $chartType::xlXYScatterSmoothNoMarkers  #Setando/Definindo a origem dos Dados $objWorkbooks.ActiveChart.SetSourceData($range) #Salvando a Planilha $objWorkbooks.SaveAs('d:\tmp\Process.xlsx') #Finalizando o Objeto $objExcel.Quit() #Removendo os Vínculos $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorksheet) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorksheets) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorkbooks) $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) #Existindo processo pendente, finalizamo-o if (ps excel) { kill -name excel}

“E lá vamos nós!!”

[]s
иαldσ dj