Saturday, August 11, 2012

PowerShell: Use VBScript to Color Table Rows

I decided to take a break from the XML solutions for HTML management of multi-table reports in PowerShell.  This week I will demonstrate an extremely simple method that most Admin scripters can master because it uses the old standby “VBScript” to do the color tagging in the browser.

We use the same method to generate a table and create the HTML:

Param(
$reportpath
}
Get-WmiObject win32_logicaldisk |
Select-Object @{N='Server';E={$_.__SERVER}},
Deviceid,
@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html | Out-File $reportpath
&$reportpath

Same report.  Same HTML.

We can colorize the rows very easily by injecting some VBScript into the output file.

$head=@"
<script language="
vbscript">
Sub window_onload()
Set tbl1 = document.getElementsByTagName("
table")(0)
For Each r In tbl1.Rows
Set cell = r.lastChild
if IsNumeric(cell.innerText) Then
If cell.innerText < $threshold Then
cell.style.color="
red"
Else
cell.style.color="
green"
End If
Else
If LCase(cell.tagName) <> "
th" Then
cell.innerText="
n/a"
End If
End If
Next
End Sub
</script>
"
@

The above is just a “here-string”  wrapped around a block of VBScript.  You can put the script into the output file manually to see it work.  I built and tested the script in an HTA in about 5 minutes.

The VBScript is just a “window_onload” event that grabs first the table on the page and goes through each row in a for loop.

Set tbl1 = document.getElementsByTagName("table")(0)
For Each r In tbl1.Rows
Set cell = r.lastChild
If IsNumeric(cell.innerText) Then
If cell.innerText < 10 Then
cell.style.color="red"
Else
cell.style.color="green"
End If
Else
If LCase(cell.tagName) <> "th" Then
cell.innerText="n/a"
End If
End If
Next

Note how we skip the header cells and how I test for the existence of a numeric value.  For each row I get the last child of the row which I know is the column with the numbers. All columns are tested against a value that tells us when to color the column red.  We could also color the whole row by using cell.parent.style=”red”.  We might even set the background color.

Here is a copy of the test file: Copy and run it to see that it works.

<html>
<head>
<script language="vbscript">
   1:  
   2:     Sub window_onload()
   3:         Set tbl1 = document.getElementsByTagName("table")(0)
   4:         For Each r In tbl1.Rows
   5:             Set cell = r.lastChild 
   6:             if IsNumeric(cell.innerText) Then
   7:                 If cell.innerText < 10 Then
   8:                     cell.style.color="red"
   9:                 Else
  10:                     cell.style.color="green"
  11:                 End If
  12:             Else
  13:                 If LCase(cell.tagName) <> "th" Then
  14:                     cell.innerText="n/a"
  15:                 End If
  16:             End If
  17:         Next
  18:     End Sub
</script>
</head>
<body>
<table>
<colgroup>
<col/>
<col/>
<col/>
</colgroup>
<tr><th>Server</th><th>Deviceid</th><th>PercentFree</th></tr>
<tr><td>OMEGA2</td><td>A:</td><td></td></tr>
<tr><td>OMEGA2</td><td>C:</td><td>2</td></tr>
<tr><td>OMEGA2</td><td>D:</td><td></td></tr>
<tr><td>OMEGA2</td><td>E:</td><td>87</td></tr>
<tr><td>OMEGA2</td><td>F:</td><td>89</td></tr>
<tr><td>OMEGA2</td><td>X:</td><td>60</td></tr>
</table>
</body>
</html>

Once I had this working I just copied the script tag to my PowerShell editor and wrapped it in a here-string.  I also added a variable in place of the hard coded match variable.

Combining all the pieces we get this very simple PowerShell script.

<#
Create-DrivespaceReport.ps1
#>
Param(
$threshold = 50,
$reportpath='\\omega2\test\DriveSpace.htm'
)

$head=@"
<style type="
text/css">
body{
background-color: antiquewhite;
}
h4{
font-style: italic;
}
table{
border-collapse:collapse;
background-color: LightGoldenRodYellow;
}
table td{
border: 1px solid black;
font-weight: thinner;
}
table th{

border: 1px solid black;
font-weight: bolder;
color: #00008B;
background-color: gold;
}
</style>
<script language="
vbscript">
Sub window_onload()
Set tbl1 = document.getElementsByTagName("
table")(0)
For Each r In tbl1.Rows
Set cell = r.lastChild
if IsNumeric(cell.innerText) Then
If cell.innerText < $threshold Then
cell.style.color="
red"
Else
cell.style.color="
green"
End If
Else
If LCase(cell.tagName) <> "
th" Then
cell.innerText="
n/a"
End If
End If
Next
End Sub
</script>
"
@
$precontent="<h4>Disk Usage Report for $([datetime]::today.ToLongDateString())</h4>"
# generate the table
gwmi win32_logicaldisk |
Select-Object @{N='Server';E={$_.__SERVER}},
Deviceid,
@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} |
ConvertTo-Html -head $head -PreContent $precontent | Out-File $reportpath

# page has been copied to a trusted share so the script will execute
&$reportpath


For the final version I threw in a simple style sheet along with the script to give the page some color.  Click the link below to see the final sample.
 
Here is a link to download it: Simple Dynamically Colored Table Script

Here is a link to a sample of the report: Sample Report

Other articles in the series on managing HTML reports from PowerShell.

PowerShell: Dynamically Color PosH Generated HTML:Part 1
PowerShell: Dynamically Color PosH Generated HTML:Part 2
PowerShell: Dynamically Color PosH Generated HTML:Part 3

No comments:

Post a Comment