The Surly Admin

HTML Reporting – A Simple Cell Color Changer

As a follow-up to the ConvertTo-AdvHTML function, I have also written a much simpler version that simply changes the color of a cell.  The advantage to this script over the bigger ConvertTo-AdvHTML function is it works with current ConvertTo-HTML, or even HTML pages from other sources.  It also allows you the ability to set the criteria for the the color change.  Best of all it’s simple to use.

Why Another Script?

While I was still developing the ConvertTo-AdvHTML script a discussion came up at Spiceworks on how to change a single cell if it’s value rose above a certain number.  Now, I already had the ConvertTo-AdvHTML function written, but it’s a big script and does require some in depth knowledge of PowerShell and how to manipulate objects.  And to be honest, I wasn’t ready to release the script and I didn’t think the OP would be up to making the kinds of changes that the script would require.  So how to make this happen?  RegEx, of course.  Now, I’ll be the first to admit that I haven’t been the biggest fan of RegEx as I think it’s syntax is ridiculously complex.  But that said, it has some amazing features that are difficult to emulate!  One of them is the ability to locate and isolate text inside a string, and to make that information available to you as a programmer/scripter.  And in the simple example at Spiceworks I was able to isolate the information the OP wanted and make the report work–now, he still had problems and I never did find out what happened (as often happens in the forums) but at least I knew the concept worked.

Making an Advanced Function

The trick to any advanced function is generalizing it so it will work for anyone.  And with this script that was a little tricky.  I needed to isolate the column/cell that I would be working on, and while I could just make the user specify which column number to use (offset by 1, of course) that didn’t seem too user friendly.   I’d need to do it by name (working off of the table header row).  I also needed to be able to specify the color to change the cell into, and I needed to have some kind of criteria that would trigger the change.  This turned out to be the tricky part, but while helping another person over at PowerShell.com I was turned onto the HTMLTable.ps1 script by Cookie.Monster.  I didn’t end up using any of his code, but it did inspire me on how to set the criteria for the cell change.

Here’s the Param section of the code:

Really making an effort to use the common parameter names here, as you can see.  Property and Color are what column I’m going to be working off of, and what color I’ll be changing to.  Nothing too fancy here.  I’ll be using Property later to go through the table header row and attempting to locate the proper column, and get the column number at that point. You might notice that the Mandatory and ValueFromPipeline are a little different than I’ve used in the past.  Recently Shay Levi released a script to convert VHD files to VHDX and I noticed he was using this notation, instead of Mandatory=$true, which I think is a great way to do it.  You never stop learning new things about PowerShell!

Filter is the interesting piece here.  At this point it’s just a string, but I’ll be working with it first thing in the script.  Since this is a pipeline-able (new word?) script, I’ll be needing the Begin/Process/End scriptblocks, and that’s where I’ll be doing the filter validation.

This is the Begin block, and where I’ll make the Filter parameter into a scriptblock.  I wanted to make the Filter format the same as what you would do in the filter parameter of Get-ADUser (or any of the Exchange PowerShell cmdlet’s) so using a -Filter “property -eq 35” was what I was going for.  The problem is “property” isn’t a proper PowerShell variable, and if you cast that string into a scriptblock it would fail.  So the first line checks that the property name is in the string (first level validation), if not it’ll just fail out right there.  If it is there, I want to replace it with a PowerShell variable.  Later on in the script I use $Value to hold the value of the $Property column so that’s what the next line does.  Now the $Filter string has been changed to “$Value -eq 35” so I attempt to cast that into a scriptblock using the .Net Create method.  Wrapping it in a Try/Catch will capture any error that’s thrown (usually because the scriptblock is malformed) and exit the script.  If it survives the conversion I’ll now have a variable that is a scriptblock that I can use later on.  The trick is any return of $true would trigger a cell color change, and a $false return would mean no change is to be made.

Now on to the Process scriptblock:

Here is where I attempt to locate $Property in the header row.  The first thing is to detect if the string even contains the TH tag.  If it does I use RegEx to isolate all of the text in-between the TH tags.  This turned out to be a little trickier than originally expected because it’s always possible that TH tag might have some additional HTML arguments inside it.  How to detect–and ignore–those?  I believe I came up with the proper syntax here:

“<th” is just that exact text, as is.  The ” ?” means one space while the question mark modifies that to say zero or more of them.  So the Regular Expression will be perfectly happy with 1 space, or none at all.  Next is a character class [a-z\-:;”=] which means any one of these characters:  a-z, – (since a dash is part of a character class syntax I have to escape it with a backslash), colon, semi-colon, double quote and the equal sign.  After that is * which means zero or more of the preceding character class.  Essentially any character I might encounter in a HTML argument, or none at all.  Next comes a closing bracket (or greater than symbol), then another odd notation:  (.*?) which translate to capture any data no matter what it is.  This is the cool feature of RegEx and PowerShell fully supports it (mostly), and that’s the ability to isolate text and “name” it.  In PowerShell that means if you use Select-String with the AllMatches switch every instance of your expression will be saved into a array element in the Matches property of your search.

What I mean by that is:

In this line I do a Select-String with the AllMatches switch and store everything in $Search.  The $Search.Matches propertywill contain an array of objects that include every instance of our Regular Expression text capture, which happens to be the name of every column in the table header row!  I use the same expression to do the same thing in the table detail (TD) rows!  After that, I have that information captured–and in one line, try doing that with typical string manipulation!–it’s a simple matter of looping through the elements of our Matches array and look for our $Property value.  You may have noticed that I don’t actually capture that data, but instead use $Index to increment a number?  This number corresponds with the column number!  Since the Matches property has an array that exactly matches up to the column count all I really need is the column number to do my searches later on in the table detail rows.

Speaking of:

The table detail/data processing is very similar to the table header processing, very similar Regular Expression.  I use IndexOf to determine if I should even do anything on this line (a lot of HTML lines have nothing to do with the table), then use RegEx expression to isolate all of the columns.  Since I know my column number now I can jump straight to the $Search.Matches element I need.  Lines 3-6 are taking that value and first trying to make a number out of it, and if that fails it just make a string out of it.  Then I use Invoke-Command on the $Filter scriptblock and if a $true is returned I use simple string replacement to modify the TD tag with a style argument with the designated color change.  The last line there simply writes the string back out to the pipeline after it’s done.  Notice this is done regardless if the TH or TD processing is successful.

And that’s it.  Here is some test data and the resulting report:

Another sample using Get-Process:

You can find the source code on Spiceworks, as always!

Set-CellColor

Also now hosting the most up to date code on Github:

Set-CellColor on Github

March 3, 2014 - Posted by Martin9700 | PowerShell - HTML Reporting | HTML Reports, Set-CellColor