Creating Colorized Excel Output (Part 2)

In a previous tip, we illustrated how you can feed HTML data to Excel in order to create formatted Excel sheets. Today, we'll show you an easy way to create color-coded data. Instead of having PowerShell create HTML (via ConvertTo-Html), you can create the HTML output yourself. This way, it is much easier to add the formatting tags you want.

This example creates an Excel sheet with all services, highlighting running services in green, and displaying stopped services in red:

# write HTML intro code:
$begin = 
{
    '<table>'
    '<tr>'
    '<th>DisplayName</th><th>Status</th><th>Required</th><th>Dependent</th>'
    '</tr>'
}

# this is executed for each data object:
$process = 
{
    if ($_.Status -eq 'Running')
    {
        $style = '<td style="color:green">'
    }
    else
    {
        $style = '<td style="color:red">'
    }
    
    '<tr>'
    '{0}{1}</td><td>{2}</td><td>{3}</td><td>{4}</td>' -f $style, $_.DisplayName, $_.Status, ($_.RequiredServices -join ','), ($_.DependentServices -join ',')
    '</tr>'
}

# finish HTML fragment:
$end = 
{
    '</table>'
}

$Path = "$env:temp\tempfile.html"

# get all services and create custom HTML report:
Get-Service | 
  ForEach-Object -Begin $begin -Process $process -End $end |
  Set-Content -Path $Path -Encoding UTF8

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

The result is a beautifully colorized Excel worksheet, and by looking at the code, you'll see that creating your HTML data manually provides a lot of additional opportunities to add HTML formatting such as cell alignment or font selection, before the HTML is created and fed into Excel.

ReTweet this Tip!