Checking for a TCP Listener Connection

I’ve been spending some time re-writing my PoshChat project to replace various things that I feel could have been written better as well as trying to knock out some of the issues that I had in the beginning but just never got around to fixing. One of those items was how I was using the System.Net.Sockets.TcpListener and setting it up to listen for new connections and then handling those connections (usually in another runspace) so I still have the room to handle other new connections.

The ‘Old” Way

The big issue here is that I was using the AcceptTCPClient() method, which presents a blocking call on the console. This means that I cannot do anything else at all, which really wasn’t an issue for me because I had all of this operating in its own runspace. The code snippet below shows the setting up of the runspace that will be handling the listener as well as when connections happen.

$SharedData = [HashTable]::Synchronized(@{})
$Listener = [HashTable]::Synchronized(@{})

#Initial runspace creation to set up server listener 
$NewRunspace = [RunSpaceFactory]::CreateRunspace()
$NewRunspace.Open()
$NewRunspace.SessionStateProxy.setVariable("sharedData", $sharedData)
$NewRunspace.SessionStateProxy.setVariable("Listener", $Listener)
$NewPowerShell = [PowerShell]::Create()
$NewPowerShell.Runspace = $NewRunspace
$sb = {
     $Listener['listener'] = [System.Net.Sockets.TcpListener]15600
     $Listener['listener'].Start()
    while($true) {
    [byte[]]$byte = New-Object byte[] 1024
    $client = $Listener['listener'].AcceptTcpClient()
    If ($client -ne $Null) {
        $stream = $client.GetStream()
        # ... #
    } Else {
        #Connection to server closed
        Break
    }
    }#End While
}
$Handle = $NewPowerShell.AddScript($sb).BeginInvoke()

You can see where I begin using AcceptTCPClient() which presents the blocking call. What happens is that at this point I have no way to break out of this call. Even if I were to change the $True to $False in my While() {} statement, it wouldn’t matter because of the blocking call. Obviously, this presents an issue if I wanted to halt my chat server which would mean completely halting the PowerShell process to effectively stop the listener.

The “New” Way using Pending()

The solution that I have found involves using the Pending() method of the listener object. By still using a loop and instead calling the Pending() method which will return $True for a connection that is attempting to be made to the listener and $False if no connections are being attempted, we can keep from blocking the runspace and allow us to inject a variable update to halt the loop if we wanted to shut down the server. Let’s give it a shot and see what happens.

First we need to start out listener:

#Create the listener and kick it off
$Listener = [System.Net.Sockets.TcpListener]15600 
$Listener.Start()

image

Ok, now if we check for pending connections, we will get back a False, meaning that there are no connections currently being attempted.

$Listener.Pending()

image

Since that is the case, I would simply start a sleep cycle for a second and check again. But now let’s assume that a connection was attempted (using the code below) and we can check again to see what happens.

#Connect to server
$Server = 'Boe-PC'
$Endpoint = new-object System.Net.IPEndpoint ([ipaddress]::any,$SourcePort)
$Client = [Net.Sockets.TCPClient]$endpoint  
$Client.Connect($Server,15600)

Now we can check for pending connection attempts.

image

Looks like we have a connection being made, so we had better act on it by calling the AcceptTCPClient() method. We also can open up a stream during this process as well.

$Client = $Listener.AcceptTcpClient() 
$ClientConnections = $Client  
$ClientStream = $Client.GetStream()         
Write-Verbose ("[$(Get-Date)] New Connection from {0} <{1}>!" -f 
$Client.Client.RemoteEndPoint.Address, $Client.Client.RemoteEndPoint.Port) –Verbos

e

image

Perfect! Now we can begin doing whatever should be done with this new connection. In this case, I am not going to do anything with it, but in PoshChat, this would get handed off to another runspace to handle the client connection and stream for messaging and the listener would be back watching for new connections.

By watching for Pending() connections, we avoid being blocked by the AcceptTCPClient() method and can then easily close our listener up without completely trashing our current PowerShell console.

Hopefully this approach helps you out if you happen to be working with client connections to a listener and provides a way to more easily handle tracking those new connections to the listener without completely taking away your ability to shut down the listener.

Posted in powershell | Tagged , , , | 1 Comment

A Look at Filtering Performance in PowerShell

While working on a script for work, I ran into a situation where I needed to some filtering on a bunch of data and the thought struck me as to what was the quickest approach to performing a filter of data. We know that Where-Object is the official filtering cmdlet with PowerShell and that it gets the job done without much issue. With PowerShell V4, we also got the .Where() method which is built for Desired State Configuration, but has uses outside of that in terms of performance vs. using Where-Object, but at the expense of having all of the data stored in memory prior to performing the filter whereas Where-Object takes input from the pipeline and processes each item to see what matches the filter.

Edit: I should know better than to write late at night, but thanks to Dave for catching that I didn’t have my TestFunction configured to match our Filter. As mentioned, while the pipeline is an amazing piece of PowerShell, it can be expensive with performance.

Where Do We Begin?

I wanted to just do a simple filter just to see how the performance would be. So with that I will use $%2 which will evaluate each number ($) to $True on odd numbers and $False on even numbers. This will allow me to look to only filter only for odd numbers.

 
1..4|ForEach {
    If ($_%2){
        "{0}: Odd" -f $_
    }Else{
        "{0}: Even" -f $_
    }
}

image

As I said, its very simple with no complexity at all, but it is all I need for testing.

Now that we have that taken care of, the next step is to look at as many possible ways to filter data that I can think of. Now this is an exhaustive list of filtering possibilities, but it has enough ways to show where performance is great down to where it is lacking as we deal with more data.

Next up is looking at those possible filtering techniques which will range from using a cmdlet to a method to some other techniques that you may not have seen before.

The eleven filtering methods that I will be testing are as follows:

  • ForEach() {}
  • | ForEach {}
  • | Where-Object {}
  • .Where({})
  • PowerShell Filter (see below for source code)
  • PowerShell Filter with Parameter (see below for source code)
  • .{Process {}}
  • Using [Predicate[Object]] (see below for source code)
  • .ForEach({})
  • PowerShell Function using parameter (see below for source code)
  • PowerShell Function with Pipeline (see below for source code)

Source Code for Custom Methods

PowerShell Filter

Dating back to V1, this was the original way to send data via the pipeline to a custom command. It is still very much useful with V5 to provide a quick way to filter out data.

 
Filter TestFilter {   
    If ($_%2){$_}
}

PowerShell Filter with Parameter

Because I wanted to include a parameter that lets you set a predicate vs. hard coding one.

 
Filter TestFilter_Predicate {
    Param ($Predicate)   
    If (&$Predicate){$_}
}

Predicate Object

I use this with some of my UIs to quickly filter data; figured it would make for a good filter method here as well.

 
$t = [System.Windows.Data.CollectionViewSource]::GetDefaultView($List)
$t.Filter = [Predicate[Object]]{
    Try {
        $args[0] % 2
    } Catch {$True}
}

PowerShell Function

The PowerShell functions we know and love that supports the pipeline. I’ll test both the pipeline approach as well as using the –InputObject parameter.

Function TestFunction {
    [cmdletbinding()]
    Param(
        [parameter(ValueFromPipeline)]
        $InputObject
    )
    Process {
        If ($_%2){$_}
    }
}
Function TestFunction_param {
    [cmdletbinding()]
    Param(
        $InputObject
    )
    ForEach ($item in $InputObject){
        If ($item%2){$item}
    }
}

Of course, the data returned varies based on how many resources are being consumed on your computer.

Let’s see the data!

I am going to be posting the source code that I will be using to provide the results below so you can take it and use it for your own testing.

I am going to look at running the tests against the following collection of count of numbers: 10,100,1000,10000,100000 and pull only the odd numbers from that list. What we have below are the results of the tests with each count grouped together and sorted from fastest to slowest. The fastest has green font while the slowest one has red font.

Filter1 Filter2

Here we can see that the winners are split between using ForEach(){} and the TestFunction using a parameter while using Where-Object and a Function taking pipeline input (the updated function still wasn’t the fastest approach, but it is no longer in the top two slowest after taking out the unneeded pipeline within the function) turn out to be the slowest approaches (although applying a parameter to our Filter definitely slows it down as we start adding more data). The Filter performed admirably as well as using .{Process{}} to do the filtering. Some of these approaches, such as top two winners, require that you have enough memory to support keeping all of the data prior to performing the filter. If you want just a little bit slower performance (and I do mean a little), you can rely on the pipeline and save memory by using a Filter or looking at .{Process{}} instead.

Of course, a graph can show just how these approaches scale out over the course of adding more data to each set.

FilterGraph

Now let’s break this out to see how each of these handles more data so you can get a better idea as to what is going on.

FilterGraph1 FilterGraph2 FilterGraph3 FilterGraph4 FilterGraph5 FilterGraph6 FilterGraph7 FilterGraph8 FilterGraph9 FilterGraph10 FilterGraph11

Only a few graphs, right?

In the end, what we have seen is that while Where-Object is the most well known filtering approach in PowerShell, if you are really looking to squeeze each and every possible millisecond from your commands, you might want to look at some alternative approaches to filtering your data, such as a Filter if you don’t want to exhaust memory. Some approaches like building out a predicate is probably just silly, but I wanted to use everything that I could think of in my tests, which also doesn’t really do anything all that complex at all.

Unless there is a pressing need to work with a ton of data, I will still rely mostly on Where-Object to accomplish what I need to do. Because it is simple and gets the job done without much thought involved (unless your filtering queries are complex, of course).

The source code for my testing is available below. Give it a shot and let me know how the results look for you. Speaking of which, if you think I missed something or have other recommendations, feel free to let me know or post up your results here!

Source Code

 
Filter TestFilter {   
    If ($_%2){$_}
}
Function TestFunction {
    [cmdletbinding()]
    Param(
        [parameter(ValueFromPipeline)]
        $InputObject
    )
    Process {
        If ($_%2){$_}
    }
}
Function TestFunction_param {
    [cmdletbinding()]
    Param(
        $InputObject
    )
    ForEach ($item in $InputObject){
        If ($item%2){$item}
    }
}
Filter TestFilter_Predicate {
    Param ($Predicate)   
    If (&$Predicate){$_}
}
 
[decimal[]]$Count = 1E1,1E2,1E3,1E4,1E5,1E6
$Data = ForEach ($Item in $Count) {
    $List = 1..$Item
    Write-Verbose "Testing for Count: $($Item)" -Verbose
    $Seconds = (Measure-Command {$List|Where{$_%2}}).TotalSeconds
    [pscustomobject]@{
        Type = 'Where-Object Filter <Pipeline>'
        IsPipeline = $True
        Time_seconds =$Seconds
        Count = $Item
    }
    $Seconds = (Measure-Command {$List.Where({$_%2})}).TotalSeconds
    [pscustomobject]@{
        Type = '.Where() Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
 
    $Seconds = (Measure-Command {$List|ForEach{If($_%2){$_}}}).TotalSeconds
    [pscustomobject]@{
        Type = 'ForEach Filter <Pipeline>'
        IsPipeline = $True
        Time_seconds =$Seconds
        Count = $Item
    }
    $Seconds = (Measure-Command {$List.ForEach({If($_%2){$_}})}).TotalSeconds
    [pscustomobject]@{
        Type = '.ForEach Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
    $Seconds = (Measure-Command {ForEach ($Item in $List){If($Item%2){$Item}}}).TotalSeconds
    [pscustomobject]@{
        Type = 'ForEach Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
 
    $Seconds = (Measure-Command {$list | .{process{If($_%2){$_}}}}).TotalSeconds
    [pscustomobject]@{
        Type = '.{Process{}} Filter <pipeline>'
        IsPipeline = $True
        Time_seconds =$Seconds
        Count = $Item
    }
 
    $Seconds = (Measure-Command {$List|TestFunction}).TotalSeconds
    [pscustomobject]@{
        Type = 'TestFunction Filter <Pipeline>'
        IsPipeline = $True
        Time_seconds =$Seconds
        Count = $Item
    }

    $Seconds = (Measure-Command {TestFunction_param $List}).TotalSeconds
    [pscustomobject]@{
        Type = 'TestFunction Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
 
    $Seconds = (Measure-Command {$List|TestFilter}).TotalSeconds
    [pscustomobject]@{
        Type = 'TestFilter Filter <pipeline>'
        IsPipeline = $True
        Time_seconds =$Seconds
        Count = $Item
    }
    $Seconds = (Measure-Command {$List|TestFilter_Predicate -Predicate {$_%2}}).TotalSeconds
    [pscustomobject]@{
        Type = 'TestFilter_Predicate Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
    $Seconds = (Measure-Command {
        $t = [System.Windows.Data.CollectionViewSource]::GetDefaultView($List)
        $t.Filter = [Predicate[Object]]{
            Try {
                $args[0] % 2
            } Catch {$True}
        }
    }).TotalSeconds
    [pscustomobject]@{
        Type = 'Predicate Filter'
        IsPipeline = $False
        Time_seconds =$Seconds
        Count = $Item
    }
}
#Send data to CSVs
Remove-Variable List,Count
$data|group count | ForEach {
   
    $temp = ((($_.Group|sort Time_Seconds |ft -auto|out-string) -split '\n')|?{$_ -match '\w|-'})
    For ($i=0;$i -lt $temp.count;$i++) {
        If ($i -eq 2) {
            Write-Host $temp[$i] -fore Green
        } ElseIf ($i -eq ($Temp.Count-1)) {
            Write-Host $temp[$i] -fore Red
        } Else {
            Write-Host $temp[$i]
        }
    }
    Write-Host "`n--------------------------------------------------------`n"
}
Posted in powershell | Tagged , , | 3 Comments

Dealing with Variables in a WinForm Event Handler: An Alternative to Script Scope

I saw a question  with an answer a while back showing how to work around an issue that had previously worked up until PowerShell V3 involving variables and how they work in an event handler in a WinForm or WPF UI. The suggested answer was to create the variable with the Script variable scope so that way the data in the variable would be available in the event handler and its scope.

An example of this can be shown below, first with the code that the variable would not work with because it is out of the scope of the event handler.

 
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$window = New-Object System.Windows.Forms.Form
$window.Width = 1000
$window.Height = 1000
 
$windowTextBox = New-Object System.Windows.Forms.TextBox
$windowTextBox.Location = New-Object System.Drawing.Size(10,10)
$windowTextBox.Size = New-Object System.Drawing.Size(500,500)
 
$windowButtonOK = New-Object System.Windows.Forms.Button
$windowButtonOK.Location = New-Object System.Drawing.Size(10,510)
$windowButtonOK.Size = New-Object System.Drawing.Size(50,50)
$windowButtonOK.Text = "OK"
$windowButtonOK.Add_Click({
    $text  = $windowTextBox.Text
    $window.Dispose()
})
 
$window.Controls.Add($windowTextBox)
$window.Controls.Add($windowButtonOK)
 
[void]$window.ShowDialog()
 
Write-Host -ForegroundColor Yellow "Test: $($Text)"

What happens here is that even though we set the variable ($Text) in the event handler, we cannot access it outside of the event handler at all.

image

As you can see below, the actual text inputted doesn’t make it outside of the event handler.

image

The solution to was to change the scope of the variable to Script: (not Global which would be beyond what is needed) to allow for the variable to be accessible to commands run in the script. As shown with the code below, we need to ensure that each time the variable is used, that we specify it as Script: to ensure it is read properly, otherwise the variable will just be treated like it was in the previous example.

 
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$window = New-Object System.Windows.Forms.Form
$window.Width = 1000
$window.Height = 1000
$ThreadID = [System.Threading.Thread]::CurrentThread.ManagedThreadId 
Write-Host -ForegroundColor Green "ThreadID: $($ThreadID) - ProcessID: $PID)" 
$windowTextBox = New-Object System.Windows.Forms.TextBox
$windowTextBox.Location = New-Object System.Drawing.Size(10,10)
$windowTextBox.Size = New-Object System.Drawing.Size(500,500)
 
$windowButtonOK = New-Object System.Windows.Forms.Button
$windowButtonOK.Location = New-Object System.Drawing.Size(10,510)
$windowButtonOK.Size = New-Object System.Drawing.Size(50,50)
$windowButtonOK.Text = "OK"
$windowButtonOK.Add_Click({
    $ThreadID = [System.Threading.Thread]::CurrentThread.ManagedThreadId 
    Write-Host -ForegroundColor Green "[EventHandler] ThreadID: $($ThreadID) - ProcessID: $PID)" 
    $Script:text  = $windowTextBox.Text
    $window.Dispose()
})
 
$window.Controls.Add($windowTextBox)
$window.Controls.Add($windowButtonOK)
 
[void]$window.ShowDialog()
 
Write-Host -ForegroundColor Yellow "Test: $($Script:text)"

image

SNAGHTML1686c8

This time we have our variable available outside of the event handler scope and now shows up when we display the information. Definitely something to remember when you are working with UIs in PowerShell V3 and above so you are not left wondering why specific events are not working properly.

In case you are wondering if the event handler occurs in a different process id and/or thread, it doesn’t.

image

I started with this because it works. But it isn’t the only approach to dealing with this issue by adjusting the variable scope. My approach to this is done using synchronized collections, or what I commonly use with runspaces, the synchronized hash table.

 
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$hash = [hashtable]::Synchronized(@{}) 
$hash.text = ""
$window = New-Object System.Windows.Forms.Form
$window.Width = 1000
$window.Height = 1000
 
$windowTextBox = New-Object System.Windows.Forms.TextBox
$windowTextBox.Location = New-Object System.Drawing.Size(10,10)
$windowTextBox.Size = New-Object System.Drawing.Size(500,500)
 
$windowButtonOK = New-Object System.Windows.Forms.Button
$windowButtonOK.Location = New-Object System.Drawing.Size(10,510)
$windowButtonOK.Size = New-Object System.Drawing.Size(50,50)
$windowButtonOK.Text = "OK"
$windowButtonOK.Add_Click({
    $hash.text = $windowTextBox.Text
    $window.Dispose()
})
 
$window.Controls.Add($windowTextBox)
$window.Controls.Add($windowButtonOK)
 
[void]$window.ShowDialog()
 
Write-Host -ForegroundColor Yellow "Test: $($hash.text)"

image

Works like a charm without having to mess with variable scopes. But it does require that you initialize the hash table and make sure it is synchronized. This is a nice approach also if you have multiple variables that you may need to track across other event handlers rather than dealing with the variable scope.

Now, this doesn’t actually just apply to working with the UIs in PowerShell. This can also be applied to other event handlers such as those that have action script blocks using the Register-*Event cmdlets as well as shown in the demo below.

 
$synchash = [hashtable]::Synchronized(@{})
$synchash.test = 'test1'
$ThreadID = [System.Threading.Thread]::CurrentThread.ManagedThreadId 
Write-Host -ForegroundColor Green "ThreadID: $($ThreadID) - ProcessID: $PID)" 
$Job = start-job -ScriptBlock {start-sleep -seconds 2}
Register-ObjectEvent -InputObject $Job -EventName StateChanged -Action {
    $ThreadID = [System.Threading.Thread]::CurrentThread.ManagedThreadId 
    Write-Host -ForegroundColor Green "[EventHandler] ThreadID: $($ThreadID) - ProcessID: $PID)" 
    Write-Host "Data: $($synchash.test)" -ForegroundColor Yellow -BackgroundColor Black
}

image

Using the same approach worked again to display the data from within the Event handler script block. And as you can see again, the process id and thread id is the same as what is outside of the handler. We could also write to the hash table as well from within the handler and the data would be available for us to use in the current script.

So with that, we have an alternative to messing with the scope of variables if needed if you would rather not have to deal with it. This doesn’t mean that you have to stop using scopes such as Script, it just means that like most things in PowerShell, there are always alternatives to performing a single action. It does have a little bit more work in creating and initializing the synchronized collection, but it gives you something that will work perfectly within the scope of event handlers as well as offering a way to transport multiple variables within a single collection.

Posted in powershell | Tagged , , , | 4 Comments

Trend Reporting using PowerShell and SQL on MCPMag.com

I have a new series of articles that will be focusing on trend reporting using PowerShell and SQL. Be sure to check them out and let me know what you think!

PowerShell Trend Reporting, Part 1: Data Gathering
PowerShell Trend Reporting, Part 2: Send and Receive SQL Server Data
PowerShell Trend Reporting, Part 3: Excel and Word Reports
Posted in powershell | Tagged , , , , | 4 Comments

Creating a Table in Word Using PowerShell

In this article, I will be showing you how to build a table in Word using PowerShell.

First we will go ahead and make the initial object creation and open up Word.

 
$Word = New-Object -ComObject Word.Application
$Word.Visible = $True
$Document = $Word.Documents.Add()
$Selection = $Word.Selection

Now that we have done that, we can start looking at building out a table to use in our document. The first example will look more like a typical table that has the headers on the first row and the rest of the rows will consist of the actual data. Of course, the first thing that I need first is some actual data that I can put into a table.

 
$Volume = Get-WmiObject Win32_Volume

I think listing out all of the volumes on my system should suffice. Now that I have this information, I can now start building out my table. One of the values that I want to document has a specific lookup that I need to quickly build using a hash table.

 
$DriveType = @{
    0x0 = 'Unknown'
    0x1 = 'No Root Directory'
    0x2 = 'Removable Disk'
    0x3 = 'Local Disk'
    0x4 = 'Network Drive'
    0x5 = 'Compact Disk'
    0x6 = 'RAM Disk'
}

I want to ensure that I am at the very end of my current spot in the document so I can put the table after whatever I may have written. I also need to make sure that the object is treated like a collection, otherwise I won’t be able to specify the index, in this case –1 for the last item in the collection.

 
$Range = @($Selection.Paragraphs)[-1].Range

The $Range will be used soon in the construction and placement of the table. Using the $Selection, I will create a new able by using the Tables.Add() method and then saving the output of the method which is the table object that I can then use to begin processing and adding data into the table.

Looking at the parameters really doesn’t help much.

 
$Selection.Tables.add

image

This is one of those cases where a trip to MSDN will provide more useful information to determine what we can actually use.

image

Now that is much more useful to us! we can see that we need to specify the Range that was found as well as some parameters for the number of Rows and Columns as well as the behavior of the table and how the items will fit. We don’t actually have to specify the Behaviors, but in this case I will be adding those as well.

As it shows, the rows and columns are pretty much a no-brainer as we can look at how many properties we are using and that will be the Column count and as for the Rows, we will take the number of objects returned and then add 1 to the count which will account for the header row. In this case, I am going to have 6 columns and 5 rows in my table.

image

Now for my Table and AutoFit behavior, I need to figure out exactly what is available to choose from. Again, I can find this inform on MSDN, but I think this is a great exercise in PowerShell’s ability to easily explore the environment to locate what we are looking for.

 
$AutoFit = [Microsoft.Office.Interop.Word.WdDefaultTableBehavior].Assembly.GetTypes() | 
Where {$_.Name -match 'autofit'}
$AutoFit | Select FullName

$DefaultTable = [Microsoft.Office.Interop.Word.WdDefaultTableBehavior].Assembly.GetTypes() | 
Where {$_.Name -match 'defaulttable'}
$DefaultTable | Select FullName

image

Perfect! Now to see what values we have to choose from.

 
[Enum]::GetNames($AutoFit)
[Enum]::GetNames($DefaultTable)

image

For my table, I am going with wdAutoFitContent and wdWord9TableBehavior when I build out my table.

 
$Table = $Selection.Tables.add(
    $Selection.Range,($Volume.Count + 1),6,
    [Microsoft.Office.Interop.Word.WdDefaultTableBehavior]::wdWord9TableBehavior,
    [Microsoft.Office.Interop.Word.WdAutoFitBehavior]::wdAutoFitContent
)

image

I am next going to pick out the table style that will be used for this specific table. Because I want a focus on the distinction between the header and the data, I am going with Medium Shading 1 – Accent 1 which means that my table goes from this…

image

…to this:

 
$Table.Style = &amp;quot;Medium Shading 1 - Accent 1&amp;quot; 

image

Ok, right now this really doesn’t look all that impressive, but it will look nicer once we have finished with the data. And speaking of data, lets start adding the header to this now.

Think of the table as a multi-dimensional array and that you have to use coordinates to properly place the data. In this case we are using the $Table.Cell(column,row) property where we have to specify the coordinates within the parenthesis to say where the data going and if we are going to add some custom formatting to that particular cell. Once each column has been completed we simply move onto the next column and perform the same operation until completed and ready for the next row.

 
## Header
$Table.cell(1,1).range.Bold=1
$Table.cell(1,1).range.text = &amp;quot;Drive&amp;quot;
$Table.cell(1,2).range.Bold=1
$Table.cell(1,2).range.text = &amp;quot;DriveType&amp;quot;
$Table.cell(1,3).range.Bold=1
$Table.cell(1,3).range.text = &amp;quot;Label&amp;quot;
$Table.cell(1,4).range.Bold=1
$Table.cell(1,4).range.text = &amp;quot;FileSystem&amp;quot;
$Table.cell(1,5).range.Bold=1
$Table.cell(1,5).range.text = &amp;quot;FreeSpaceGB&amp;quot;
$Table.cell(1,6).range.Bold=1
$Table.cell(1,6).range.text = &amp;quot;CapacityGB&amp;quot;

image

Starting to look like something a little more useful now. The last piece of this is to add the data. Given that I really don’t know just how much data might be available at the time of this, I am relying on a For() loop to handle this for me.

 
## Values
For ($i=0; $i -lt ($Volume.Count); $i++) {
    $Table.cell(($i+2),1).range.Bold = 0
    $Table.cell(($i+2),1).range.text = $Volume[$i].Name
    $Table.cell(($i+2),2).range.Bold = 0
    $Table.cell(($i+2),2).range.text = $DriveType[[int]$Volume[$i].DriveType]
    $Table.cell(($i+2),3).range.Bold = 0
    $Table.cell(($i+2),3).range.text = $Volume[$i].Label
    $Table.cell(($i+2),4).range.Bold = 0
    $Table.cell(($i+2),4).range.text = $Volume[$i].FileSystem
    $Table.cell(($i+2),5).range.Bold = 0
    $Table.cell(($i+2),5).range.text = [math]::Round($Volume[$i].FreeSpace/1GB,2)
    $Table.cell(($i+2),6).range.Bold = 0
    $Table.cell(($i+2),6).range.text = [math]::Round($Volume[$i].Capacity/1GB,2)
}

Same approach as my header but not only do it increment the columns on each pass, I also have to make sure to increment the rows as well to ensure that the data is accurate. The end result is something like this:

image

Lastly, I want to make sure I am at the end of this and outside of my table so I can continue adding data or writing if necessary without writing to the table. I also want to add a space after the table for formatting purposes.

 
$Word.Selection.Start= $Document.Content.End
$Selection.TypeParagraph()

If you wanted more of a list type of format, that can also be accomplished using a similar approach. This time we will keep the number of columns to two and use as many rows as we need.

 
$BIOS = Get-WmiObject Win32_Bios

$Range = @($Selection.Paragraphs)[-1].Range
$Table = $Selection.Tables.add(
    $Selection.Range,5,2,
    [Microsoft.Office.Interop.Word.WdDefaultTableBehavior]::wdWord9TableBehavior,
    [Microsoft.Office.Interop.Word.WdAutoFitBehavior]::wdAutoFitContent
)

Another different is that I don’t want to focus on the first row, so a different style will be used.

 
$Table.Style = &amp;quot;Light Shading - Accent 1&amp;quot;

And lastly, we start building out the list table.

 
## Header
$Table.cell(1,1).range.Bold=1
$Table.cell(1,1).range.text = &amp;quot;Manufacturer&amp;quot;
$Table.cell(2,1).range.Bold=1
$Table.cell(2,1).range.text = &amp;quot;Name&amp;quot;
$Table.cell(3,1).range.Bold=1
$Table.cell(3,1).range.text = &amp;quot;Version&amp;quot;
$Table.cell(4,1).range.Bold=1
$Table.cell(4,1).range.text = &amp;quot;Serial Number&amp;quot;
$Table.cell(5,1).range.Bold=1
$Table.cell(5,1).range.text = &amp;quot;BIOS Version&amp;quot;
    
## Data
$Table.cell(1,2).range.Bold = 0
$Table.cell(1,2).range.text = $BIOS.Manufacturer
$Table.cell(2,2).range.Bold = 0
$Table.cell(2,2).range.text = $BIOS.Name
$Table.cell(3,2).range.Bold = 0
$Table.cell(3,2).range.text = $BIOS.Version
$Table.cell(4,2).range.Bold = 0
$Table.cell(4,2).range.text = $BIOS.SerialNumber
$Table.cell(5,2).range.Bold = 0
$Table.cell(5,2).range.text = $BIOS.SMBIOSBIOSVersion

$Word.Selection.Start= $Document.Content.End
$Selection.TypeParagraph()

The end result is something that looks like this:

image

Of course, this can easily start adding to your line count if you plan on having several tables that you want to add in your document and writing code isn’t about who has the most rows. Smile

I wrote a small function that takes an object and can build out a Table or List fairly easily and all it requires is the $Object to be added to the table and the $Selection object. Note that this is one that I use on my personal projects but can be altered to meet your needs. Another thing is that you will need to tailor your object to only have specific properties, otherwise your table/list will be overrun with data.

An example of it in use is here:

 
$Volume = @(Get-WmiObject Win32_Volume  | ForEach {
    [pscustomobject]@{
        Drive = $_.Name
        DriveType = $DriveType[[int]$_.DriveType]
        Label = $_.label
        FileSystem = $_.FileSystem
        &amp;quot;FreeSpace(GB)&amp;quot; = &amp;quot;{0:N2}&amp;quot; -f ($_.FreeSpace /1GB)
        &amp;quot;Capacity(GB)&amp;quot; = &amp;quot;{0:N2}&amp;quot; -f ($_.Capacity/1GB)
    }
})

New-WordTable -Object $Volume -Columns 6 -Rows ($Volume.Count+1) –AsTable

And here:

 
$BIOS = @(Get-WmiObject Win32_Bios | ForEach {
    [pscustomobject] @{
        Manufacturer = $_.Manufacturer
        Name = $_.Name
        Version = $_.Version
        SerialNumber = $_.SerialNumber
        BIOSVersion = $_.SMBIOSBIOSVersion
    }
})

New-WordTable -Object $BIOS -Columns 2 -Rows ($BIOS.PSObject.Properties | Measure-Object).Count -AsList

Source Code

 
Function New-WordTable {
    [cmdletbinding(
        DefaultParameterSetName='Table'
    )]
    Param (
        [parameter()]
        [object]$WordObject,
        [parameter()]
        [object]$Object,
        [parameter()]
        [int]$Columns,
        [parameter()]
        [int]$Rows,
        [parameter(ParameterSetName='Table')]
        [switch]$AsTable,
        [parameter(ParameterSetName='List')]
        [switch]$AsList,
        [parameter()]
        [string]$TableStyle,
        [parameter()]
        [Microsoft.Office.Interop.Word.WdDefaultTableBehavior]$TableBehavior = 'wdWord9TableBehavior',
        [parameter()]
        [Microsoft.Office.Interop.Word.WdAutoFitBehavior]$AutoFitBehavior = 'wdAutoFitContent'
    )
    #Specifying 0 index ensures we get accurate data from a single object
    $Properties = $Object[0].psobject.properties.name
    $Range = @($WordObject.Paragraphs)[-1].Range
    $Table = $WordObject.Tables.add(
    $WordObject.Range,$Rows,$Columns,$TableBehavior, $AutoFitBehavior)

    Switch ($PSCmdlet.ParameterSetName) {
        'Table' {
            If (-NOT $PSBoundParameters.ContainsKey('TableStyle')) {
                $Table.Style = "Medium Shading 1 - Accent 1"
            }
            $c = 1
            $r = 1
            #Build header
            $Properties | ForEach {
                Write-Verbose "Adding $($_)"
                $Table.cell($r,$c).range.Bold=1
                $Table.cell($r,$c).range.text = $_
                $c++
            }  
            $c = 1    
            #Add Data
            For ($i=0; $i -lt (($Object | Measure-Object).Count); $i++) {
                $Properties | ForEach {
                    $Table.cell(($i+2),$c).range.Bold=0
                    $Table.cell(($i+2),$c).range.text = $Object[$i].$_
                    $c++
                }
                $c = 1 
            }                 
        }
        'List' {
            If (-NOT $PSBoundParameters.ContainsKey('TableStyle')) {
                $Table.Style = "Light Shading - Accent 1"
            }
            $c = 1
            $r = 1
            $Properties | ForEach {
                $Table.cell($r,$c).range.Bold=1
                $Table.cell($r,$c).range.text = $_
                $c++
                $Table.cell($r,$c).range.Bold=0
                $Table.cell($r,$c).range.text = $Object.$_
                $c--
                $r++
            }
        }
    }
}
Posted in powershell | Tagged , , , | 8 Comments