Sunday 19 February 2012

Get-ProcessesAsExcel.ps1

  1. <# 
  2. .SYNOPSIS 
  3.     This script creates an Excel workbook using PowerShell and  
  4.     populates it with the results of calling Get-Process and  
  5.     copying across the key properties 
  6. .DESCRIPTION 
  7.     This script demonstrates manipulating Excel with PowerShell 
  8.     and the Excel.Application COM object. 
  9. .NOTES 
  10.     File Name  : .\Get-ProcessesAsExcel.ps1 
  11.     Author     : Thomas Lee - tfl@psp.co.uk 
  12.     Requires   : PowerShell Version 2.0 
  13. .LINK 
  14.     This script posted to:
  15. http://pshscripts.blogspot.com/2012/02/get-processesasexcelps1.html
  16. .EXAMPLE 
  17.     Left as an exercise to the reader. 
  18. #> 
  19. ##  
  20. # Start of Script 
  21. ## 
  22.  
  23. # First, create and single worksheet workbook 
  24.  
  25. # Create Excel object 
  26. $excel = new-object -comobject Excel.Application 
  27.     
  28. # Make Excel visible 
  29. $excel.visible = $true 
  30.    
  31. # Create a new workbook 
  32. $workbook = $excel.workbooks.add() 
  33.  
  34. # The default workbook has three sheets, remove 2,4 
  35. $S2 = $workbook.sheets | where {$_.name -eq "Sheet2"
  36. $s3 = $workbook.sheets | where {$_.name -eq "Sheet3"
  37. $s2.delete() 
  38. $s3.delete() 
  39.   
  40. # Get sheet and update sheet name 
  41. $s1 = $workbook.sheets | where {$_.name -eq 'Sheet1'
  42. $s1.name = "Processes" 
  43.    
  44. # Update workook properties 
  45. $workbook.author  = "Thomas Lee - Doctordns@gmail.com" 
  46. $workbook.title   = "Processes running on $(hostname)" 
  47. $workbook.subject = "Demonstrating the Power of PowerShell with Excel" 
  48.    
  49. # Next update Headers 
  50. $s1.range("A1:A1").cells="Handles" 
  51. $s1.range("B1:B1").cells="NPM(k)" 
  52. $s1.range("C1:C1").cells="PM(k)" 
  53. $s1.range("D1:D1").cells="WS(k)" 
  54. $s1.range("E1:E1").cells="VM(M)" 
  55. $s1.range("F1:F1").cells="CPU" 
  56. $s1.range("G1:G1").cells="ID" 
  57. $s1.range("H1:H1").cells="Process Name" 
  58.  
  59. $row = 2 
  60. Foreach ($Process in $(Get-Process)) { 
  61.  $s1.range("A$Row:A$Row").cells=$Process.handles 
  62.  $s1.range("b$Row:B$Row").cells=$Process.NPM 
  63.  $s1.range("c$Row:C$Row").cells=$Process.PM 
  64.  $s1.range("d$Row:D$Row").cells=$Process.WS 
  65.  $s1.range("e$Row:E$Row").cells=$Process.VM 
  66.  $s1.range("f$Row:F$Row").cells=$Process.CPU 
  67.  $s1.range("g$Row:G$Row").cells=$Process.ID 
  68.  $s1.range("h$Row:H$Row").cells=$Process.Name 
  69.  $row++ 
  70.  
  71. # And save it away: 
  72. $s1.saveas("c:\foo\process.xlsx"
  73. # end of script 

2 comments:

Aniarrg Ferrenzna said...

Thomas,

Many thanks for this post and the blog. I am beginning to take my child's steps with Powershell and this is going to be a treasure.

May I also take the liberty to ask for any sources that you could point me to have a more structured learning?

I mean I wanted to know the various methods you might have on an object (Excel.Application, InternetExplorer.Application) etc.

Many thanks.

Aniarrg Ferrenzna said...

Many thanks for the post. It is sure going to help me in the child's steps that I am taking with Powershell.

Is there a complete documentation on the various methods for the Excel object, please?