Creating Excel Reports (Part 3 – Individually Accessing Workbook)

Sometimes you may want to create irregularly shaped reports, that is reports in a non-table design.

For this, PowerShell lets you connect to the Excel object model. This way, you can access individual cells, read and write their content, and even format them. This gives you maximum flexibility. However, the drawback is that it requires a lot of code since you have to manually set up every single workbook cell. Also, accessing COM objects via .NET is relatively slow.

Here is the code that gets you started. It shows you how to connect to Excel, access individual cells, and even apply formatting:

#requires -Version 2.0
Add-Type -AssemblyName System.Drawing

# accessing excel via COM
$excel = New-Object -ComObject Excel.Application
# make it visible (for debugging only, can be set to $false later in production)
$excel.Visible = $true

# add workbook
$workbook = $excel.Workbooks.Add()

# access workbook cells
$workbook.ActiveSheet.Cells.Item(1,1) = 'Hey!'

# formatting cell
$workbook.ActiveSheet.Cells.Item(1,1).Font.Size = 20

$r = 200
$g = 100
$b = 255
[System.Drawing.ColorTranslator]::ToOle([System.Drawing.Color]::FromArgb(255,$r,$g,$b))
$workbook.ActiveSheet.Cells.Item(1,1).Font.Color = $r + ($g * 256) + ($b * 256 * 256)

# saving workbook to file
$Path = "$env:tempexcel.xlsx"
$workbook.SaveAs($Path)

Twitter This Tip! ReTweet this Tip!

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!