Manipulating Excel files with PowerShell

During last week's PowerShell class, several students wanted to know if they could manipulate Excel files the same way that they used to do in VBScript. Of course! In fact, the syntax is almost identical, because PowerShell can use the same COM object that you used in VBScript. One student, Sam Hays, was kind enough to share this brief example of how to do it:

01.$XL = New-Object -comobject Excel.Application02. 03.$XL.Visible = $True04. 05.$WB = $XL.Workbooks.Add()06.$WS = $WB.Worksheets.Item(1)07. 08.$WS.Cells.Item(1,1) = "ProcessName"09.$WS.Cells.Item(1,2) = "Id"10.$WS.Cells.item(1,3) = "Handles"11. 12.$counter = 213. 14.$processes = get-process | select processName,Id,Handles15. 16.foreach($proc in $processes) {17.    $WS.cells.item($counter,1) = $proc.ProcessName18.    $WS.cells.item($counter,2) = $proc.id19.    $WS.cells.item($counter,3) = $proc.Handles20.    $WS.columns.autofit()21.    $counter++22.}
Line 1 starts the process by instantiating the COM object - note that Excel must be installed in order for this to work. Lines 5 and 6 create a new workbook (XLS file) and add a spreadsheet to that workbook.

Lines 8-10 put some text in three cells across the top of the page. Note the (row,column) references, and that rows and columns start counting from 1, not zero. Line 12 sets a counter variable to 2, which is the row that we'll start putting data in.

Line 14 retrieves PowerShell processes using Get-Process.

Lines 16-22 run through those processes and put process information into the spreadsheet. You'll notice on line 21 that the counter is incremented, so that each subsequent process goes on a new line.

Now, I do have to point out that for such a simple example, there's a much easier way:

Get-Process | Select ProcessName,ID,Handles | Export-CSV procs.csv
I know, it's not an XLS file, but if you double-click it, it'll look pretty much the same, won't it? If you need to get more complex, then manipulating Excel directly by means of its COM object is a good solution.