by Lawrence Hodson

In this article I have a go at creating an Open XML Document using Microsoft’s excellent PowerShell command-line environment. To do so I use PowerTools for Open XML, recently updated on CodePlex (http://www.codeplex.com/PowerTools).
As you’ll see it makes an otherwise difficult task really quite easy.

The scenario

To demonstrate this I’ve come up with what I think might be a reasonably real-world scenario. If not, hopefully it’s enough to get the concept across.

The scenario I chose is one of an SLA (service level agreement) type report. Imagine you’re a company providing an agreed level of service on servers for various clients.  It’s quite likely you’d want to send your client details on how well you are fulfilling the SLA.

Here are the basic requirements for this report:

  • Create a PowerShell script that can be run for a specified client and computer name, and produces a single Word document.
  • The beginning of the output document will be tailored for the particular client.
  • The middle will be built up from 1 or more SLA metrics, using WMI information.
    • For simplicity’s sake I’m going to use simple WMI metrics for Processor Time % and Disk Time %
    • Depending on these metrics the appropriate Pass/Fail content will be output.
  • The end will be standard for all clients.

Getting Started

Go ahead and download the attached sample files.

The sample consists of the following:

  • Sample.ps1
    this is the PowerShell script
  • sampleDocs folder
    • sampleBeginning-client-A.docx
      sample beginning document for Client “A”.
    • sampleBeginning-client-B.docx
      sample beginning document for Client “B”.
    • sampleBeginning-client-C.docx
      sample beginning document for Client “C”.
    • sampleMiddle.docx
      sample document for the various SLA metrics.
    • sampleEnd.docx
      sample document for the end.
  • pssnapin folder
    contains the compiled PowerTools for Open XML binaries.

While I have included the PowerTools binaries, but you could also download the PowerTools for Open XML source files from http://www.codeplex.com/PowerTools

Binaries are also available here:

 You will also need the following software installed:

NOTE:
PowerShell v2.0 is used because it has some new features for working with WMI, however most of the sample would work fine with PowerShell v1.0.

You can also use the Open XML SDK v1.0 as the PowerTools for Open XML works with both v1.0 and 2.0.

Sample Documents Explained

1.  sampleBeginning-client-A.docx, sampleBeginning-client-B.docx & sampleBeginning-client-C.docx

These documents are all very similar except for being individually tailored for each client A, B & C.

2. sampleMiddle.docx

While the other documents are all static content, the middle document contains multiple Headings that will be picked out selectively by the PowerShell script. It also contains places where actual SLA values will be injected using Open XML’s CustomXml feature. More on this later.

3. sampleEnd.docx

 

Preparing for CustomXML values in the Middle document

As I mentioned earlier, the sampleMiddle.docx document is a little more than meets the eye.

Since only parts of it will be used selectively, we need a way of delimiting those parts.  I have simply made the individual parts begin with a “Heading1” styled text. The PowerShell script will be able to use this to selectively pull out the content required. 

Each metric has a Pass & Fail version, because I’d rather do fancier formatting in the document, than in PowerShell.

You’ll also see that each metric has a value, which the PowerShell script will need to inject.  I could have done this by formatting and injecting some Word ML using PowerShell, but instead I chose to use the CustomXml feature.

It nicely separates the View from Data. Meaning that someone non technical could pretty-up the document without too much fear of “breaking” the dynamic nature of the document.

Firstly I created the document and got things looking about right. Then I just needed to add the custom fields. Using the Developer ribbon of Word you can add Controls.  However, I found this doesn’t actually bind the values to any kind of custom Xml.  It was really easy once learned of the Content Control Toolkit.

 

At this point, go and take a look at Andrew Coats’ blog post that I found very helpful.

http://blogs.msdn.com/acoat/archive/2007/03/01/linking-word-2007-content-controls-to-custom-xml.aspx

 

The end result looks something like this in Word.

The next thing I did is take a look at the packaged Open XML for the document. This is as easy as renaming it with a .ZIP extension and extracting the files.

In the CustomXml folder I took a look at the Xml files and found that the Item2.xml file represented my CustomXml content. 

- <root>

   <ProcessorTime>0</ProcessorTime>

   <DiskTime>0</DiskTime>

   </root>

With this information I will be able to get PowerShell to get the Xml, tweak the values, and set it back into the document. More on this later.

 

The Powershell Script

The PowerShell script is quite straight forward.

·         Get any command-line parameters passed when the script is executed

·         Load the Open XML PowerTools snapin. (this provides the “cmdlets” we’ll use to create the output document).

·         Create references to the static documents that will make up the output document (sampleBeginning.docx & sampleEnd.docx).

·         Selectively get references to middle document content based on WMI metrics.

·         Merge the documents together, and create the output document.

·         Update the CustomXml (Item2.xml) with the WMI metrics

 

Now would be a good time to take a look at the sample.ps1 script.

 

Getting the DocumentSources from the Beginning and End

 

The PowerTools for Open XML provide a “DocumentSource” object.  We can use it to get a reference to an entire document, or part of a document.

The following line, simply gets the entire document named in the $docBeginningPath variable.

$docBeginning = New-Object -TypeName OpenXml.PowerTools.DocumentSource -ArgumentList $docBeginningPath

The same technique is done for the Ending document.

 

Getting the DocumentSources selectively for the Middle

 

The Middle document needs a different technique. We only want to get specific parts of the document.  To do this we will still use the DocumentSource object, but will specify a start Element Number and Length for the desired piece of content.

To identify the Element Number and Length we can use the Select-OpenXmlString cmdlet as follows.

$snippetHeadings = Select-OpenXmlString -Path $docMiddlePath -Style Heading1

This gets all the “Heading1” styled elements from the document.

We could alternatively use it to get elements with a particular text in the content. I chose not to since I am going to need to work out the Length of the content also. Instead we search the Headings for the particular text.  This gives us an Element Number representing the start of the content.  Then we can find the next Heading and subtract One to get the end. The difference is the Length.

You can see this logic in the GetDocSnippet function below. It looks for the specified heading text, determines the start & length, then returns the DocumentSource for just that snippet of content.

 

function GetDocSnippet($headings, $docPath, $snippetName)

{

       $elementStart = 0

       $elementEnd = 0;

 

       [bool]$found = 0;   

       $headingIndex = 0

       foreach ($heading in $headings)

       {

              if ($found -eq 0)

              {

                     #searching for start

                     if ($heading.Content -eq $snippetName)

                     {

                           #found start

                           $found = 1

                           $elementStart = $heading.ElementNumber

                           $elementEnd = $elementStart

                     }

              }

              else

              {

                     #start found, so searching for end. Will use the next heading

                     if ($elementEnd -eq $elementStart)

                     {

                           #found end

                           $elementEnd = $heading.ElementNumber

                     }

              }

      

       }

 

       if ($found -eq 1)

       {

              $elementStart ++

              if ($elementStart -lt $elementEnd)

              {

                     $length = $elementEnd - $elementStart

                     $doc = New-Object -TypeName OpenXml.PowerTools.DocumentSource -ArgumentList $docPath, $elementStart, $length

              }

              else

              {

                     #no end was found, so was probably the last item. use from the start to the rest of the doc

                     $doc = New-Object -TypeName OpenXml.PowerTools.DocumentSource -ArgumentList $docPath, $elementStart

              }

             

              return $doc

       }

       else

       {

              return

       }

}

 

Getting the metrics from WMI

 

PowerShell provides a Get-WmiObject cmdlet for accessing WMI.  The following is how I get the Processor Time metric.

$totalProcessorTimeMetric = Get-WmiObject -class "Win32_PerfFormattedData_PerfOS_Processor" -ComputerName $computerName | select Name,PercentProcessorTime | where {$_.Name -eq "_Total"}

$totalProcessorTime = $totalProcessorTimeMetric.PercentProcessorTime

 

Then based on the value, the GetDocSnippet function is called to get either the PASS or FAIL snippet. (Note: these names should match the Heading1 styled text in the sampleMiddle.docx document.

 

if ($totalProcessorTime -ge $processorTimeThreshold)

{

       $docMiddle = GetDocSnippet $snippetHeadings $docMiddlePath 'ProcessorTime-FAIL'

}

else

{

       $docMiddle = GetDocSnippet $snippetHeadings $docMiddlePath 'ProcessorTime-PASS'

}

 

Merging it all together

 

Now that all the appropriate documents and parts have been identified we can merge them together into a single output document.

First, the DocumentSources are added into an array.

$docs = @()

$docs += $docBeginning

$docs += $docMiddle

$docs += $docMiddle2

$docs += $docEnding

 

Then the actual merge is done using the array.

 

Merge-OpenXmlDocument -OutputPath $docOutputPath -Sources $docs

 

Injecting the CustomXML values

 

We now have an output document comprising of all the right pieces. However, we still need to inject the WMI values into the content.

Since I chose to use the CustomXml approach this is really quite simple.

First we get the existing CustomXml out of the document. Earlier we found it was the part named item2.xml.

 

$customXml = Get-OpenXmlCustomXmlData -Path $docOutputPath -Part item2.xml

 

Next we set the values we got out of WMI.

 

$customXml.Root.Element("ProcessorTime").Value = [string]$totalProcessorTime + ' %'

$customXml.Root.Element("DiskTime").Value = [string]$totalDiskTime + ' %'

 

And lastly we set it back into the document as follows.

 

Set-OpenXmlCustomXmlData -Path $docOutputPath -Part $customXml -PartName item2.xml -SuppressBackups

 

And that’s all there is to it!

 

Runnning the Sample

 

To run the sample start PowerShell,

Before you can run the sample script you will need to give PowerShell permissions to run scripts. At the prompt enter...

Set-ExecutionPolicy Unrestricted

Then enter

<Path>\sample.ps1 <clientId> <computerName>

Where <clientId> is A,B or C. (these should match the suffixes on the sampleBeginning-client-*.docx files)

and <computerName> (optional) is a computer name or IP addresss, localhost or 127.0.0.1. (if blank it defaults to 127.0.0.1)

e.g.

.\sample.ps1 A localhost

All going well you should end up with an output.docx file in the current directory looking like this...

 

 

Conclusion

Thanks to the PowerTools for Open XML  we have a simple, but powerful way of building up a document using PowerShell.  

PowerShell is very powerful in its own right, but I do believe it would have been a fairly painful to achieve this result without the PowerTools for Open XML.

For my sample I was unsuccessful at querying WMI for a remote computer. I believe this was probably related to my infrastructure environment. It works fine for the local computer (i.e. localhost, 127.0.0.1)