Creating Colorized Excel Output

by Aug 26, 2013

PowerShell can send data to Excel using CSV files easily. Here's a short script creating a list of running services and opening it in Excel (provided you have Excel installed of course):

$Path = "$env:temp\tempfile.csv"
Get-Service | Export-Csv -Path $Path -NoTypeInformation -Encoding UTF8 -UseCulture
Invoke-Item -Path $Path 

This is fast and reliable, but due to the CSV format, only data can be imported, and you have no way of formatting and colorizing the data.

Fortunately, modern Excel versions can also import HTML files, as Josh Miller discovered on powershellmagazine.com (http://www.powershellmagazine.com/2013/06/17/pstip-use-excel-to-view-html-output/). To do that, you have to call Excel directly and submit the path to the HTML file as an argument. Here's a sample:

$Path = "$env:temp\tempfile.html"
Get-Service | ConvertTo-HTML | Set-Content -Path $Path -Encoding UTF8

$Excel = @(Resolve-Path "C:\Program Files*\Microsoft Office\Office*\EXCEL.EXE")[0].Path
& $Excel $Path 

This produces formatted Excel data: in contrast to the previous example, Excel now uses a different font. And if you want more formatting, you now can change the HTML data – for example, add HTML color tags – before you import it into Excel.

Twitter This Tip! ReTweet this Tip!