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.Application
02. 
03.$XL.Visible = $True
04. 
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 = 2
13. 
14.$processes = get-process | select processName,Id,Handles
15. 
16.foreach($proc in $processes) {
17.    $WS.cells.item($counter,1) = $proc.ProcessName
18.    $WS.cells.item($counter,2) = $proc.id
19.    $WS.cells.item($counter,3) = $proc.Handles
20.    $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.

Discuss this Blog Entry 2

on Jul 31, 2012
Hi, I would like to know how to export the content of a SQL server table to Excel file. Im a novice to powershell. Kindly briefly expling the process of connecting to SQL table & exporting data to an excel sheet. Thanks, Naveen J V
on Nov 2, 2010
Sam: Thanks for sharing your example.

Don: Thanks for sharing your knowledge in a great class.

FR



Please or Register to post comments.

What's PowerShell with a Purpose Blog?

Don Jones demystifies Windows PowerShell.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×