.Net 3.0, PowerShell and Scott Hanselman

Scott posted Back to Basics: var != Dim showing how to use Microsoft Office Interop to change a value in the CustomDocumentProperties of a Word document.

My Approach

Use PowerShell and System.IO.Packaging in Windowbase.dll from .Net 3.0.

Advantages

• Microsoft Office does not need to be installed
• Works with Word, Excel and PowerPoint
• It is not limited to just custom properties 
• Only a text editor is needed to write the glue

I wrote the PowerShell Package dll which facilitates the transition between PowerShell and the Packaging namespace.

Process

Office 2007 files are zip files. Take an Excel file, rename it to .zip and unzip it. Check out the file system directory structure and contents. It is all Xml. The System.IO.Packaging namespace let’s you work with compressed file contents, package parts, relationships and the data stream.

The DLL and PowerShell scripts abstract this further. The Support.ps1 script contains functions and filters which wrap the C# DLL so the methods play better in PowerShell’s pipeline.

The Update-Properties.ps1 script pipes through the relationships, relationship types, target uri’s and retrieves the document object model. The nested for each statements extract all the custom properties, sets them and finally saves and closes the document.

Summary

This code forms the basis for next steps in modifying the data in Office documents.  Recently I joined the open source project PowerTools for Open XML and found a script written by Dave Glover which sparked this approach.

Dave demonstrated how to generate Office Open Xml documents on the fly by merging a Word Template with an Xml file of Customers into customer invoices.

His script generates 80 documents/second. After making it more PowerShell like I generated 140 documents/second. Refactoring can improve this another 30%.

In subsequent posts I will improve this code and demonstrate other ways to interact with Office Open Xml.

The Code

param($file=".\Template.docx")

. .\Support

(Open-Package $file).Relationships |
  Where {$_.RelationshipType -match 'custom-properties'} |
    ForEach {$_.TargetUri} |
      Get-Dom |
        ForEach { $_.Properties.property } |
          ForEach {$_.lpwstr="TestChange"}

Save-Package
Close-Package

Download

The Standalone.zip contains test documents, PowerShell scripts and the compiled DLL. The other zip contains the VS 2008 solution as well.