Creating Excel Reports (Part 2 – Colorful)

When you load CSV data into Excel, you cannot specify formatting, fonts or colors. This is different when you load HTML data into Excel. Here is an example that illustrates how easy it can be to create a formatted and colorful Excel report, provided the report has a table design:

#requires -Version 2.0
$html = & {
    '<table>'
    '<tr><th>Name</th><th>Status</th></tr>'
    Get-Service |
    ForEach-Object {
        if ($_.Status -eq 'Running')
        {
            $color = 'green'
        }
        else
        {
            $color = 'red'
        }
      
        '<tr><td>{0}</td><td bgcolor="{2}">{1}</td></tr>' -f $_.Name, $_.Status, $color
  
    }
    '</table>'
}

$PathHTML = "$env:temp\report.htm"
$html | Set-Content $PathHTML -Encoding UTF8
# open as HTML
Invoke-Item -Path $PathHTML
# open as Excel report 
Start-Process -FilePath excel -ArgumentList """$PathHTML"""

ReTweet this Tip!