The Surly Admin

Father, husband, IT Pro, cancer survivor

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:

Set-CellColor1Another sample using Get-Process:

Set-CellColor2

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

Set-CellColor

About these ads

March 3, 2014 - Posted by | PowerShell - HTML Reporting | ,

9 Comments »

  1. […] 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. […]

    Pingback by How to Create HTML Reports « The Surly Admin | March 3, 2014 | Reply

  2. […] 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. […]

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

  3. Hello Martin, thanks for you script, great job ! I have experimented with a bit, I wanted to report à ‘strange’ behavior of the script :
    Under some situations, cells which do not belong to filtered columns are being coloured as well. And it does not affect all cells of the column. It’s a bit as if the colour was leaking to nearby cells to give you an idea. I can show you an example if needed. I wondered if someone else had reported this. thanks, best regards.

    Comment by Olivier | September 1, 2014 | Reply

    • Hi Olivier, yes, I believe there is a bug in the code which basically colors in all cells with the same condition. So let’s say you have 4 columns and two of them have “True” in columns 3 and 4. If you search for “True” in column 4 column 3 will also get highlighted. Unfortunately, I have not worked out a fix for this and at this point I can only point you towards the ConvertTo-AdvHTML function or Don Jones’ excellent function for the same thing here.

      Comment by Martin9700 | September 1, 2014 | Reply

  4. Martin, Thank you for the quick answer. Indeed same symtoms. I will check the link provided, thanks again for your contributions.

    Comment by Olivier | September 1, 2014 | Reply

  5. Martin: I just wanted to point out that you have included a -Fragment parameter to the function which is passed to ConvertTo-Html if present. The fragment parameter set does not work with the -Head parameter so if -Fragment is present you will receive the error “Parameter set cannot be resolved”.

    Probably not a huge deal since using a fragment with your function seems counter to the spirit of the function itself. Maybe just remove that parameter?

    Anyway, great work on this!

    Comment by mattmcnabb | September 3, 2014 | Reply

    • Thanks Matt, I’ll look it over!

      Comment by Martin9700 | September 3, 2014 | Reply

  6. […] and allows you to manipulate them directly.  This is good because there’s a current bug in Set-CellColor script that if two cells in a row have the same data, and you’re trying to color the cell […]

    Pingback by SpiceWorld 2014 Just Around The Corner « The Surly Admin | September 12, 2014 | Reply

  7. I found how to filter according to datetimes. It does require the filter to have the get-date cmdlet, but if you add

    if($filter -like ‘*get-date*’){
    $value = $search.matches[$index].groups[1].value -as [datetime]}
    Else{
    $Value = $search.matches[$index.groups[1].value -as [double]
    }
    you have to replace the $Value = $search.matches[$index.groups[1].value -as [double] line with that and it will start filtering datetimes. this is in the process section.

    Comment by Nicholas Bostwick | October 20, 2014 | 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

Follow

Get every new post delivered to your Inbox.

Join 234 other followers

%d bloggers like this: