The Surly Admin

Father, husband, IT Pro, cancer survivor

How to Create HTML Reports

Find yourself creating reports in Powershell and wished they looked a little more presentable than a CSV file?  Send regular reports to your manager and want it to look professional?  Here are some technique’s I’ve developed that produce some pretty sharp reports in HTML using standard Powershell cmdlets (plus one function to pretty up your tables).

ConvertTo-HTML Limitations

By itself, the ConvertTo-HTML cmdlet is a good tool for taking our custom Objects and turning them into simple HTML tables.  I talked in more detail about objects in this post, Building PSObject Performance.  Assuming you’ve built a PSObject full of wonderful data, how do you output it so it looks nice?  The first step is ConvertTo-HTML, here’s an example:

$MyObject | ConvertTo-HTML | Out-File C:\MyReport.HTML

And here’s an example of the output:

example1Not a bad report, but from an aesthetics standpoint it leaves a lot to be desired.  One problem, of course, is the fields are all in the wrong order.  We can solve that by using the Select-Object cmdlet but how do we make our page look a little better?  The thing to remember is that this table is HTML, that means if you could get a custom style sheet (CSS) in there we could go a long way towards prettying this up.  As it turns out, ConvertTo-HTML allows us to do this with the -Head parameter.  Using that parameter we can insert whatever we want into the Header node of our output.  Here’s an example of putting some CSS into a here-string.

$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
"@

$MyObject | Select 'Folder Name',Owner,'Created On','Last Updated',Size | ConvertTo-HTML -Head $Header

And here’s the output:

example2Now, that looks a lot better.  In fact, I’d say for most reports this would be pretty good and you could walk away.  But I like to do a little more.  Now, I’ll be the first to admit my HTML and CSS foo is pretty low, so while I’ll be showing you some techniques for making this report look better someone with mad CSS skills could take it much further.  What I can show you is how to apply those mad skills to your Powershell reporting repertory.

One thing any good report needs is a header that shows you what the report is all about, and if we have it, you may want to add some information at the bottom summing up.  I’m also going to add a <title> tag to the $Header string to change the heading in your browser.  We accomplish this by using the -PreContent parameter, which adds your data before the table and the -PostContent parameter, which adds data after the table.

$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
<title>
Title of my Report
</title>
"@
$Pre = "Heading before the report"
$Post = "Footer after the report"

$MyObject | Select 'Folder Name',Owner,'Created On','Last Updated',Size | ConvertTo-HTML -Head $Header -PreContent $Pre -PostContent $Post

And our example would now look like this:

example3And, since the $Pre and $Post variables are simple strings you can insert any information into them that you want.  So this report is looking pretty darn good, if you ask me.  But one thing I’ve always liked, and maybe this harkens back to my days as a Computer Operator on IBM Mainframes (who remembers DOS/VSE?), but I like reports that have alternating row colors.  Especially on a report with a lot of rows in it, and as you can see from our example if we change that directory to a large folder tree we’re going to have a ton of lines!

All we need to do is tell ConvertTo-HTML to us specific CSS class names on its rows and that should take care of it, right?  And that’s the problem.  ConvertTo-HTML doesn’t have anything like that in it.  I solved this problem on my DFS Monitor Report by simply constructing the HTML myself, but honestly that’s a pain and I’d rather avoid that for simple reporting.

The first solution is to use the Set-AlternatingCSSClasses function developed by Don Jones, a Microsoft MVP that can be found over at Powershell.org.  Don wrote an interesting e-book called Creating HTML Reports in Powershell that goes into a lot of detail, covering a lot of the ground I’ve written about here so I highly recommend reading it.

But I had a problem with the function he created.   It works well for what he did, but when using it you have to use the -Fragment parameter on ConvertTo-HTML.  This parameter leaves out all of the and tags and just creates the HTML table from your objects.  That means if you want all of those tags in your final report you have to manually put them together.  Now, in fairness, that’s not super difficult as you can create multiple here-strings and just put them together.

But it’s not easy, is it?  I want things to be simple for anyone to plug into their script and produce a great report.  So I wrote my own function and called it Set-AlternatingRows.  This accepts input from the pipeline and does a simple string replacement of the tags it finds.  You do have to provide your own CSS and tell the function what they are but in your CSS you can customize the colors to anything you want–you can even customize the class names to anything you want.  Here’s what it looks like:

$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
.odd  { background-color:#ffffff; }
.even { background-color:#dddddd; }
</style>
<title>
Title of my Report
</title>
"@
$Pre = "Heading before the report"
$Post = "Footer after the report"

$MyObject | Select 'Folder Name',Owner,'Created On','Last Updated',Size | ConvertTo-HTML -Head $Header -PreContent $Pre -PostContent $Post | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd

Since Set-AlternatingRows accepts information from the pipeline, you don’t have to specify anything for Set-AlternatingRows, but we do have to tell it which classes in the CSS are for our even rows and what class to use for the odd rows.  That’s where the -CSSEvenClass and CSSOddClass come in.  Here’s are final report:

example4And that’s it, a final report.  With more HTML and CSS combinations there’s a lot you could do to make things even better but this will give you the basis to work from.  Next post I will actually break down how the function works and how to create functions that accept pipeline information.

Update 8/14/2014: And just to make things even more interesting, I recently ran across a simple one line addition to the CSS that will create the alternating rows with no additional effort!  One line!!  Both psyched because it’s cool, and a little sad at seeing the death of such a useful little reporting function.  Here’s the CSS magic:

That’s it.  Another fun CSS to add to that is a hover where the row will highlight with another color when you pass the mouse over:

 

Source code to Set-AlternatingRows

Update 2/27/2014: If you liked reading about this, you might want to take a look at my new advanced function ConvertTo-AdvHTML.  It allows for far greater control over your HTML table!

Update 3/3/2014: So want to change the color of a cell, but don’t want to go through the hassle of ConvertTo-AdvHTML?  Here’s another advanced function that might be up your alley:  Set-CellColor.

Advertisements

January 21, 2013 - Posted by | PowerShell - HTML Reporting | , , ,

7 Comments »

  1. […] on Spiceworks if you want to look at it.  We’ve got 3 of our steps done now.  I reuse some HTML techniques and add that to the script then simply use the Get-VM | Get-Snapshot code from […]

    Pingback by Report All Snapshots in Your VMware Environment « The Surly Admin | March 18, 2013 | Reply

  2. […] snippets of code from other scripts I’ve talked about here.  The final product will be an HTML report, pulling data from Active Directory, Exchange (2007 or 2010) and Spiceworks.  We’re also […]

    Pingback by Getting User Information « The Surly Admin | April 1, 2013 | Reply

  3. […] script had it’s beginnings with the Set-AlternatingRows function I wrote some time back, but that function was very limited.  Later came a new version of […]

    Pingback by ConvertTo-AdvHTML – New Advanced Function for HTML Reporting « The Surly Admin | February 27, 2014 | Reply

  4. […] Owen – ConvertTo-HTML and CSS (Hey, nth-child works in SharePoint!) The Surly Admin – How to Create HTML Reports Campaign Monitor’s CSS vs Mail Client Comp…Example CSS someone threw onto […]

    Pingback by Making PowerShell Emails Pretty | Brian Bunke | February 18, 2015 | Reply

  5. […] How to Create HTML Reports Genopro – genealogy software – genopro, Genopro easy genealogy software create family trees genograms.. […]

    Pingback by How To Create Html | Bau Kelek Woy!!! | March 12, 2016 | Reply

  6. […] How to Create HTML Reports […]

    Pingback by How To Create Table In Html | Bau Kelek Woy!!! | March 12, 2016 | Reply

  7. Hello

    I am trying to get “Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd” with an HTML email report using New-Object -Property @{ found at https://thesurlyadmin.com/2013/01/14/building-psobject-performance/ to generate most of the report. Any ideas?

    $Header = @”

    TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
    TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    .odd { background-color:#ffffff; }
    .even { background-color:#dddddd; }
    “@

    $Body = $Result | ConvertTo-HTML -Head $Header -PreContent “Active Directory Inactive AD Users” | Set-AlternateRows -CSSEvenClass even -CSSOddClass odd | Out-String

    Error = Set-AlternateRows : The term ‘Set-AlternateRows’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name,
    or if a path was included, verify that the path is correct and try again.

    Thank you

    Comment by Alan | February 11, 2017 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: