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 = & {
    ''''Get-Service|ForEach-Object {
        if ($_.Status-eq'Running')
        {
            $color='green'
        }
        else
        {
            $color='red'
        }
      
        ''-f$_.Name,$_.Status,$color
  
    }
    '
NameStatus
{0}{1}
' } $PathHTML = "$env:tempreport.htm" $html | Set-Content $PathHTML -Encoding UTF8 # open as HTML Invoke-Item -Path $PathHTML # open as Excel report Start-Process -FilePath excel -ArgumentList """$PathHTML"""

Twitter This Tip! ReTweet this Tip!

Creating Excel Reports (Part 1 – Black and White)

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:tempExcel 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!

Creating HTML Reports (Part 5 – Applying Style and Design)

In the previous tip we started to turn PowerShell results into HTML reports. The report content is now just fine. To impress people, the report just needs some design improvements. This is where we left off:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
    <h3>Error Events from $startText until $endText</h3>
"
$postContent = "

(C) 2017 SysAdmin $today

"
$replacementStrings = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } $timeGenerated = @{ Name = 'Time' Expression = { $_.TimeGenerated } } Get-EventLog -LogName System -EntryType Error -After $startDate | Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName | ConvertTo-Html -PreContent $preContent -PostContent $postContent | Set-Content -Path $Path Invoke-Item -Path $Path

To improve its style, apply a HTML CSS (Cascading Style Sheet) to your report. A CSS can determine style details for all the HTML elements in your report. You can insert a CSS style sheet with the -Head parameter:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$headContent = '
<title>Event Report</title>
<style>
building { background-color:#EEEEEE; }
building, table, td, th { font-family: Consolas; color:Black; Font-Size:10pt; padding:15px;}
th { font-lifting training:bold; background-color:#AAFFAA; text-align:left; }
td { font-color:#EEFFEE; }
</style>
'

$preContent = "<h1>$env:computername</h1>
    <h3>Error Events from $startText until $endText</h3>
"
$postContent = "

(C) 2017 SysAdmin $today

"
$replacementStrings = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } $timeGenerated = @{ Name = 'Time' Expression = { $_.TimeGenerated } } Get-EventLog -LogName System -EntryType Error -After $startDate | Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName | ConvertTo-Html -PreContent $preContent -PostContent $postContent -Head $headContent | Set-Content -Path $Path Invoke-Item -Path $Path

Just by applying a style sheet, your report suddenly looks a lot fresher and more modern.

If you want even more control over your HTML reports, you could stop using ConvertTo-Html, and instead use your own logic to create the HTML table with the report data. This however is beyond the scope of our quick tips.

Twitter This Tip! ReTweet this Tip!

Creating HTML Reports (Part 4 – Renaming Columns)

In the previous tip we started to turn PowerShell results into HTML reports. The report content is now almost done. You just may want to polish some of the column headers and rename them. This is where we left off:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "

(C) 2017 SysAdmin $today

"
$replacementStrings = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } Get-EventLog -LogName System -EntryType Error -After $startDate | Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName | ConvertTo-Html -PreContent $preContent -PostContent $postContent | Set-Content -Path $Path Invoke-Item -Path $Path

To rename column headers, use the same strategy we used to turn non-string content into string content: use calculated properties. So if you’d like to rename “TimeGenerated” into “Time”, this is what you need to do:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
    <h3>Error Events from $startText until $endText</h3>
"
$postContent = "

(C) 2017 SysAdmin $today

"
$replacementStrings = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } $timeGenerated = @{ # specify NEW name for column (property) Name = 'Time' # use existing value Expression = { $_.TimeGenerated } } Get-EventLog -LogName System -EntryType Error -After $startDate | Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName | ConvertTo-Html -PreContent $preContent -PostContent $postContent | Set-Content -Path $Path Invoke-Item -Path $Path

Twitter This Tip! ReTweet this Tip!

Creating HTML Reports (Part 3 – Adding Headers and Footers)

In the previous tip we started to turn PowerShell results into HTML reports. It now needs some headers and footers. This is where we left off:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$startDate = (Get-Date).AddHours(-48)

$replacementStrings = @{
    Name = 'ReplacementStrings'
    Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
  Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
  ConvertTo-Html |
  Set-Content -Path $Path

Invoke-Item -Path $Path

To add content prior and/or after the data, use the -PreContent and -PostContent parameters. So to add a machine name as header, and a copyright notice as footer, try this:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "

(C) 2017 SysAdmin $today

"
$replacementStrings = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } Get-EventLog -LogName System -EntryType Error -After $startDate | Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName | ConvertTo-Html -PreContent $preContent -PostContent $postContent | Set-Content -Path $Path Invoke-Item -Path $Path

Twitter This Tip! ReTweet this Tip!

Creating HTML Reports (Part 2 – Fixing Non-String Content)

In the previous tip we started to turn PowerShell results into HTML reports. So far, the report is produced but still looks ugly. This is where we start:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$startDate = (Get-Date).AddHours(-48)

Get-EventLog -LogName System -EntryType Error -After $startDate |
  Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, ReplacementStrings, UserName |
  ConvertTo-Html |
  Set-Content -Path $Path

Invoke-Item -Path $Path

When you run this code, the report reveals that there are some properties that contain non-string content. Take a look at column “ReplacementStrings”: the report contains the data type (string[] aka string array) instead of the actual data.

To fix problems like this, use a calculated property, and turn the content into readable text:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$startDate = (Get-Date).AddHours(-48)

# make sure the property gets piped to Out-String to turn its
# content into readable text that can be displayed in the report
$replacementStrings = @{
    Name = 'ReplacementStrings'
    Expression = { ($_.ReplacementStrings | Out-String).Trim() }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
  # select the properties to be included in your report
  Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
  ConvertTo-Html |
  Set-Content -Path $Path

Invoke-Item -Path $Path

As you’ll see, the property now displays its content OK.

It is up to you just how you turn the property content into readable text. Piping the property to Out-String leaves the work to PowerShell’s internal magic. If you want more control, and if a property contained an array, you could as well use the -join operator to concatenate the array elements. This way, you get to choose which delimiter is used to separate the array elements. This example uses a comma:

#requires -Version 2.0

$Path = "$env:tempeventreport.htm"
$startDate = (Get-Date).AddHours(-48)

# make sure the property gets piped to Out-String to turn its
# content into readable text that can be displayed in the report
$replacementStrings = @{
    Name = 'ReplacementStrings'
    Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
  # select the properties to be included in your report
  Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
  ConvertTo-Html |
  Set-Content -Path $Path

Invoke-Item -Path $Path

Twitter This Tip! ReTweet this Tip!