PowerShell Excel Functions Updated

In PSExpectDataLib.ps1, we’ve created a small number of Excel-related functions that hopefully make it easier to use Excel for storing test data. What this does for you is increase the "test script to test case" ratio, ideally lowering the cost of testing (as would any other data-driven testing technique). We chose Excel in part because of its ubiquity but also because any end-users just might be able to help fill it out, especially for functional tests of custom-developed applications.

The primary goal in providing this library is to write tests that are shielded from the Excel COM API (object model). In other words, when Excel 2007 comes out and the COM API is different or there is a better .NET-oriented way of accessing Excel, then we can re-write DataLib.ps1 and all of the test scripts we’ve built that rely on Excel will still function. It also minimizes how much of Excel the test author really needs to know.

The secondary goal was to provide a set of functions that could be used with any culture setting.  There were problems running the Excel COM API from .NET if the culture was not ‘en-US’.  This  has been resolved in the library using reflection since you can pass in the culture as a parameter to the method invocation.  This had a ripple effect in that the Range property behaved differently than it did calling it directly.  All the tests pass on the two machines that I have access to (one with ‘en-US’ the other with ‘en-CA’).

With the current release of the source code, the library provides net new functions ‘open-workbook’, ‘get-worksheet’, and ‘get-range’.  The ‘collect’ function has been replaced by ‘collect-range’.  The usage of these functions is demonstrated in srcTestCollectFromExcel.ps1, samplesTestGetWeather.ps1, and samplesTestGetProcessFromExcel.ps1.  Here is a brief example from one of those scripts:

# get the data $workbook = open-workbook $excel $WorkbookFullPath $worksheet = get-worksheet $workbook $WorksheetName $range = get-range $worksheet $RangeName $rangeAsList = collect-range $range $FieldNames

The only messy part is in cleaning up the references to the COM Runtime Callable Wrappers (RCW). All the scripts above contain the code for releasing the COM objects that are used, but because the Range COM API call returns a System.Object[] of COM objects, we have also provided a function called ‘release-range’ that loops through this array and releases each of the COM objects.


$excel.Quit() $a = release-range $range $range = $null $a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) $a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) $a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

Feedback is welcome on this version of the source, especially if you are not a ‘en-US’ user of PowerShell and Excel.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s