Writing to Excel

Background

About a year ago I started writing things to Excel with PowerShell, based on the information I found on an MSDN blog – Create Excel file with PowerShell

I wasn’t quite satisfied, though. I wanted some more formatting (specifically: change the top row behavior). It was somewhat frustrating to not be able to get everything the way I wanted, thus I decided to start finding out how to do this. It started early evening and late night (or early morning) I was finally satisfied with all the things I could do with Excel. I decided to share the script and struggles with you. I couldn’t find a place which had all these customizations together with creating the Excel file, thus this might help you save a lot of time when trying to reach the same goal.

Goal

My goal is to freeze the top row, set the text at least to bold (depending on difficulty in setting the font properties) and set an autofilter on the top row. And of course to fill the Excel workbook with data. I also want to refit the column width based on its text.

In real life I use it to document things (who likes documenting and why document things if you can have the computer doing it for you?), in this Example, I’ll use it to write the output of the Get-Process command to get a list of processes, process IDs, UserNames and Sessions (in the example, the CPU object is also documented)

Writing the script

With the Get-Process command including the IncludeUserName parameter in my script, I need to run this as administrator. There are several ways to achieve this, I found these two options on the net: Start script as administrator & Check if user is administrator (nearly last comment from MOW)

Combining those two gets met to these lines of code (check if the user started the script as administrator & check if the user can be an administrator. If the script wasn’t started as admin and the security settings allow it, it’ll start the script as admin, otherwise it’ll give an error that you need to be an admin to be able to run this script):

$IsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
$CanBeAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]"$env:USERNAME").IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

If(!($IsAdmin))
{
   If($CanBeAdmin)
   {
       $Arguments = "& '" + $MyInvocation.MyCommand.Definition + "'"
       Start-Process powershell -Verb runAs -ArgumentList $Arguments
   }
   Else
   {
       Write-Error "You have to be an administrator to run this script" -ErrorAction Stop
   }
   Break
}

These lines go in the top of the script. The script breaks if the user isn’t an admin.

When you live in a non US country (which are more people that actually live in the US, but ok), your CurrentCulture settings will probably have you run into problems when trying to use the Excel com object. The following Error will probably appear on many of your Excel com object calls:

Exception calling "<name>" with "<int>" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At line:<int> char:<int>
+ <PSObject>.<call> <<<< ()
   + CategoryInfo         : NotSpecified: (:) [], MethodInvocationException
   + FullyQualifiedErrorId : ComMethodTargetInvocation

Thus we’ll have to add this line of code to set the CurrentCulture to US:

[System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US"

Now we can start with the Excel code. In my example I delete the Excel file if it exists, before I start a new one. This makes the code obsolete to Open & Save instead of Create & SaveAs, but I left it in there as a reference. Then came the struggle of setting the top row defaults.

The bold part was pretty easy and straightforward:

$Range = $sheet.UsedRange
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True

Locking the top rows was somewhat harder, but I stumbled upon it when searching for setting the auto filter. Thanks, rwskas on Experts-Exchange

$sheet.Application.ActiveWindow.SplitColumn = 0
$sheet.Application.ActiveWindow.SplitRow = 1
$sheet.Application.ActiveWindow.FreezePanes = $true

With the top rows locked all that’s left is setting the autofilter. Which proved to be pretty hard information to find. Microsoft has information about the Range.AutoFilter Method, but it still left me with questions on how to implement this in PowerShell. I had this C# example from Stack Overflow. Then I found a nearly correct way to use it on powershell.org. Thanks, Tim Pringle. I still needed to know how get the ‘missing type’ and the Excel.XlAutoFilterOperator.xlAnd (from the C# example), which was (probably) the 1 in the PowerShell example. When searching for the XLAutoFilterOperators I stumbled upon a TechNet post with an answer by Boe Prox (Thank you!), there’s the missing type, now I can try it. And … It works! The 1 seems to be xlAnd (the default value) as we thought. This gives us the following code:

$Range.AutoFilter(1, $MissingType, 1, $MissingType, $MissingType) | Out-Null

All that’s left now is refitting the column width based on its text (pretty straightforward)

$Range.EntireColumn.AutoFit() | Out-Null

Then I added an extra line of code, so my example file won’t contain my user name.

If($Process.UserName -eq "$env:USERDOMAIN\$env:USERNAME") { $Process.UserName = "PowershellAdministrator" }

In the end, I quit Excel, release its com object and remove the Excel variable as used in the script.

$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | out-null
Remove-Variable -Name Excel -Scope Global -EA 0

And that’s it. Now you’re automatically writing to Excel. In the end the code isn’t that hard to understand, but to get there can be a pain sometimes. So I hope this’ll help someone trying to reach the same goal(s) as I did.

The script and Excel example file

The Excel example file can be found here, the PowerShell script here.

Happy scripting.