Creating Colored Excel Reports

When you open a CSV file in Excel, you get a very fast data import, but the result is "black and white"; CSV data has no way of colorizing cells.

As an alternative, you can wrap your data in HTML, and then feed the HTML to Excel. This produces colored Excel reports and allows you to even specify fonts and font sizes.

The following example produces a status report in Excel. Running services are displayed in green, and stopped services surface in red:

#requires -Version 1

# 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; font-family:courier">'
    }
    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

# find Excel, and feed HTML report into Excel
$Excel = Resolve-Path "C:\Program Files*\Microsoft Office\Office*\EXCEL.EXE" | 
  Select-Object -First 1 -ExpandProperty Path

& $Excel $Path

ReTweet this Tip!