Creating Excel Reports (Part 1 – Black and White)

by Aug 16, 2017

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!