The most simple way of creating Excel reports requires just a couple of lines of PowerShell code: dump the results to a CSV file, then submit it as an argument to Excel:

#requires -Version 2.0

$timestamp = Get-Date -Format 'yyyy-MM-dd HH-mm-ss'
$Path = "$env:temp\Excel Report $timestamp.csv"

Get-Service |
  Export-Csv -Path $Path -Encoding UTF8 -UseCulture -NoTypeInformation

Start-Process -FilePath excel -ArgumentList """$Path"""

There are a couple of things to watch out for:

  • Excel locks a file while it is open. So make sure you add a time stamp or some other unique identifier to the file name. Else, you run into errors when you run your script multiple times without closing the previously opened document
  • When dumping data to CSV, make sure you use UTF8 encoding to preserve special characters
  • Also, make sure the CSV file and Excel use the same delimiter. Simply use -UseCulture to use the delimiter set in the registry
  • When starting Excel, make sure you place the path into additional quotes. Else, if your path name contains whitespace, Excel would not find it

Twitter This Tip! ReTweet this Tip!

Anonymous