Export to Excel
One of the great things about Windows PowerShell is that you can manage practically your entire system without ever leaving the command line. (Yes, we know, not everyone thinks spending all their time at the command prompt is a great thing, but obviously these few people haven’t spent enough time working with PowerShell yet.) With Windows PowerShell you can type your commands at the command prompt and voilà, your output spews out into the command window right before your eyes. There’s nothing like some instant gratification to brighten up your day.
All right, we’ll admit, there is a small downside to this. Sometimes the output goes spewing by so fast you can’t read it. And if there’s a lot of it, you might not even be able to scroll up and see all of that output. And here’s another problem with this type of output: suppose you want to show it to someone else? Do you call them into your office to take a look at your screen? This is a little inconvenient, especially if they work in a different location. (Plus you might forget to close down your Mahjong game before they see it on your screen.) You also might want to refer to the output later. There are actually a lot of reasons why you might want the output from your command to go somewhere other than the command window.
One of these somewheres might be a Microsoft Excel spreadsheet. If you export to Excel, you can then save the spreadsheet, sort and re-sort the data, format it, and, well, do all the things that you usually do with data in an Excel spreadsheet. And, you can send the spreadsheet to a coworker and continue on with your game of Mahjong.
Exporting output to Excel from PowerShell is actually pretty simple. Here’s a script that retrieves all the processes running on your computer, then exports the process name and ID to Excel.
$objExcel = New-Object -ComObject Excel.Application
$wb = $objExcel.Workbooks.Add()
$item = $wb.Worksheets.Item(1)
$objExcel.Visible = $True
$procs = Get-Process
$i = 1
foreach ($p in $procs)
{
$item.Cells.Item($i,1) = $p.Name
$item.Cells.Item($i,2) = $p.ID
$i++
}
$wb.SaveAs("C:\Scripts\Processes.xlsx")
# Close Excel and clean up
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Remove-Variable objExcel
Remove-Variable wb
Remove-Variable item
If you just look at everything before the comment (# Close Excel and clean up) it doesn’t look too difficult, and it isn’t. We’ll worry about that cleanup stuff at the end.
To start with, the first thing we do in this script is open Excel. We do this by creating a new Excel.Application object:
$objExcel = New-Object -ComObject Excel.Application
This gives us a running instance of Excel. By default, when you open Excel from the Start menu (or by running the Excel.exe application file) Excel opens with a workbook containing three worksheets. However, when you start Excel by creating an Excel.Application object, all you get is an empty instance of Excel; there are no workbooks or worksheets open. That means that before we do anything else we need to create a workbook and open a worksheet in that workbook. We do that like this:
$wb = $objExcel.Workbooks.Add()
$item = $wb.Worksheets.Item(1)
The first line calls the Add method of the Workbooks collection to add a workbook to Excel. You might think that, in the next line, we could call the Add method of the Worksheets collection to add a worksheet. That makes sense, but what makes sense doesn’t always work (a fact we’re very familiar with). The reason this doesn’t work is because adding the workbook automatically adds the worksheets as well. So all we need to do is use the Item property of the Worksheets collection (with the index 1) to grab hold of the first worksheet in the workbook.
Now, at this point Excel is running and it has an open workbook and worksheets. We can start exporting our data to Excel, save the worksheet, and do whatever else we need to do. But without this next line, you’ll never actually see Excel on your screen:
$objExcel.Visible = $True
Without this line everything you do with Excel will happen in memory and you won’t ever see that Excel is running. This can actually be a very good thing; the script will run faster without the display, and sometimes there really isn’t any need to see what’s happening. But for this example we want to see that data really is being written to the spreadsheet, so we set the Visible property to True to display Excel on the screen.
Now it’s time to retrieve our processes. You’re probably familiar with the Get-Process cmdlet, which retrieves a list of all the running processes on the computer. We call Get-Process and save the output to the variable $procs:
$procs = Get-Process
After setting a counter variable ($i) we then enter a foreach loop to loop through our collection of processes:
foreach ($p in $procs)
The foreach loop is going to go through each process one at a time. Inside the loop we’re going to put the name and ID of each process into our worksheet. We do that with these two lines of code:
$item.Cells.Item($i,1) = $p.Name
$item.Cells.Item($i,2) = $p.ID
Remember that the $item variable holds the worksheet we’re writing to, Sheet 1. We reference the cells of the worksheet with the Cells property. To specify exactly which cell we want to work with we use the Item property, passing it the row and column of the cell. In line 1 we pass ($i, 1) , which means row $i (the value of our counter, which we initially set to 1) and column 1. We assign that cell the value of the Name property of our current process ($p.Name). In the second line we assign the value of the process’s ID property to cell $i, 2; the first time through the loop this is the cell in row 1, column 2.
We then add one to our counter (that’s what $i++ means: add 1 to the value in $i), and loop back around to put the next process into row 2. The counter then gets incremented again (for a value of 3) and we do the same thing with the next process, and so on.
Depending on how fast your computer is and how many processes you have running, you can probably watch the rows and columns filling in as the script runs. When all the processes have been added to the worksheet the foreach loop ends. At this point we’re ready to save. We do that by calling the workbook’s SaveAs method:
$wb.SaveAs("C:\Scripts\Processes.xlsx")
We pass the SaveAs method the full path and filename where we want to save the Excel file. We now have an Excel file saved to our computer that contains a list of all the processes running on our computer.
Note. If the file exists, Excel will prompt you to confirm that you want to overwrite the existing file. To have the script overwrite an existing file without prompting, add the following line to your script:
$objExcel.DisplayAlerts = $False
Then change your SaveAs call to look like this:
$wb.SaveAs("C:\Scripts\Processes.xlsx", 1)
We could end the script here and leave you to manually close Excel. (Keep in mind, however, that if you didn’t set the Visible property to True you wouldn’t be able to do this. You’d either have to stop the excel.exe process, or you’d have to run this next block of code.) In this case, we’re not going to require you to manually close Excel, we’re going to let the script close it for us.
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Remove-Variable objExcel
Remove-Variable wb
Remove-Variable item
Yes, this looks pretty complicated. And if you dig way down into the details of how it all works, it actually is pretty complicated. But we’re not going to do that. Why not? Because we don’t actually need to. (No, really, we’re not just being lazy this time.) Instead, we’re going to give you the high-level look at all this, then tell you to just be sure to stick this at the end of your script and you’ll be fine. And you will.
So let’s start with the first line:
$objExcel.Quit()
This line is pretty simple. We call the Quit method of the Excel object to shut down Excel. You might think at this point that we should be done: we shut down Excel, what else do we need to do? Well, there is one more thing: we have to clean up after ourselves.
Now, we could go into a lot of details about managed code and unmanaged code, handles and events and the inner workings of the .NET Framework. But as we said, there’s no need to do that here. All we’re doing in the rest of this script is getting rid of some things that are still hanging around using up memory on our computer. As it turns out, shutting down Excel doesn’t actually get rid of the object we created or even stop the Excel.exe process that we started on the computer. As far as our computer is concerned, the Excel process is still running. In order to get rid of it completely, we need to do three things: release the Excel objects we created, collect the garbage, and wait for the finalizers to finish.
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
The first three lines release our workbook object ($wb), our worksheet object ($item), and our Excel object ($objExcel). By “releasing” the objects we’re telling.NET, which keeps track of all the objects we’re using, that we’re not using them anymore.
Note. The ReleaseComObject method returns an integer value indicating the success or failure of the method call. When the method runs successfully the value returned is 0. In this script we call ReleaseComObject three times, once for each Excel object, meaning we get three zeroes returned, which happen to get displayed to the command window. We decided we didn’t need to see three zeroes every time we ran the script, so we piped the output of ReleaseComObject to the Out-Null cmdlet, which suppresses the display. You can leave out the call to Out-Null if you want to see the three zeroes in your window after your script runs.
After the objects are released we call the Collect method. This method does what’s called garbage collection. Garbage collection refers to freeing up memory that’s no longer in use. Normally Windows and .NET are very good at cleaning up memory, but sometimes they need a little help. In this case we need to call Collect to tell .NET to free up the memory from the objects we just told it we’re not using anymore.
Finally, we call the WaitForPendingFinalizers method. This is another part of garbage collection. Basically it just makes our script sit and wait and not do anything else until .NET has finished cleaning up the memory.
The very last thing we do in our script is get rid of the variables we were using to refer to our Excel objects:
Remove-Variable objExcel
Remove-Variable wb
Remove-Variable item
You don’t have to do this part, it won’t cause any problems with performance or memory if you don’t. Calling Remove-Variable just deletes the variable and is good scripting practice; it makes sure you don’t inadvertently try to use those variables again now that we’ve gotten rid of the objects associated with them.
That’s probably more than you wanted to know about .NET and garbage collection. But like we said at the beginning, the only thing you really need to know is that anytime you use a script like this one to open Excel from within Windows PowerShell, you need to include all that cleanup stuff after you close it if you don’t want extra processes running silently in the background on your computer.
Note. We wrote an article a few years ago where all we needed to include at the end of a script like this were these two lines:
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
That worked at the time. Of course, back then we were running Windows PowerShell 1.0 and Excel 2003. We’re now on Windows PowerShell 2.0 and Excel 2010, and that doesn’t work anymore. Now you need the whole thing:
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
A little more coding, but just copy and paste every time you need it; copying six lines isn’t much more trouble than copying two lines.
And, finally, that’s it. There are a lot of other things you can do with Excel from a PowerShell script, like formatting and sorting and so on, but in this case we are too lazy – er, busy – to get into that today.