PowerShell Excel Functions – Release Candidate

The technique for using Excel as the source of the data to support data-driven testing continues to evolve, but this time I think I’ve come to the place where I want to be for Release 0.3 of PSExpect (http://www.codeplex.com/psexpect).

In 0.3, there is a new function in DataLib.ps1 called ‘start-test’ that takes a workbook name, a worksheet name, a range name, and a set of field names as input, and runs the tests that are described in that range.  It does this by calling a function that has the same name as the range – much like FIT calls classes/methods based on the contents of the input table.

So here’s the process to follow to use start-test.

  1. Describe your tests in Excel.  You should include both inputs and expected outputs in this table.  There is no need to differentiate the inputs from expected outputs like in FIT (in FIT, you specify expected outputs by including parentheses since they represent method calls).  The column titles are meaningless in PSExpect, so you can use titles that make the worksheet easier to read and understand as needed.
  2. Define a named range (Insert->Name->Define…) that EXCLUDES the column titles but includes all the rows.  This is why the column titles are meaningless. Unfortunately, a hyphen is not a valid character to use in a range name in Excel so your test fixture can’t have a name that looks like most PoSh functions and cmdlets.
  3. Write a test fixture (function) in PoSh that has exactly the same name as the name you gave the range in Step 2.  The parameters should match the columns in the table by position (because we build a string in PoSh for execution using invoke-expression).  This function must include an appropriate Assert* function call that determines whether or not the test case passes.
  4. Call start-test and provide the workbook name, the worksheet name, the range name, and an array of fieldnames as parameters.
  5. Run the script – your results will be on the console and in the log file.

Using ‘start-test’ is demonstrated in a performance test for the weather web service I’ve used in previous samples, samplesTest-GetWeatherPerf.ps1.   The Excel table and the test script are listed below.







Success in Celsius



Conditions for Huntsville, AL


Success in Fahrenheit



Conditions for Huntsville, AL


Success – Bad Units



Conditions for Huntsville, AL


Success – Default Units



Conditions for Huntsville, AL


Failure – No Zip



City not found


Failure – Invalid Zip



City not found
# Exercises the target of the test - the get-weather web service # and then verifies the results against the expected values that are also on the # worksheet (in this simple case, the $title parameter) function TestGetWeather() { param( [string]$TestCase, ` [string]$Remark, ` [string]$Zip, ` [string]$Units, ` [string]$Title) if ($Units -eq "$null") { $Units = $null } if ($Zip -eq "$null") { $Zip = $null } [string]$urlbase="http://xml.weather.yahoo.com/forecastrss" [string]$url=$urlbase + "?p="+$Zip+"&u="+$Units write-host Connecting to $url # create .NET Webclient object and call the web service $webclient = new-object "System.Net.WebClient" $targetBlock = {[xml]$weather=$webclient.DownloadString($url)} # measure the results and verify $targetBlock | AssertFaster -MaximumTime 300 -Label $TestCase } # run the function library that contains the PowerShell Testing Functions # the functions are defined as global so you don't need to use dot sourcing if (!(Test-Path variable:_XLLIB)) { ..srcDataLib.ps1 } if (!(Test-Path variable:_TESTLIB)) { ..srcTestLib.ps1 } # run the test by calling DataLibstart-test function $FieldNames = ("TestCase","Remark","Zip","Units","Title") start-test ((get-location).ToString() + "TestGetWeather.xls") "Sheet1" ` "TestGetWeather" $FieldNames

The only thing that might change in future releases is a way of eliminating the need to pass in an array of field names to start-test.  This is a left-over from the way that I’ve implemented the functions start-test depends on (such as select-row).  In those underlying functions, I wanted to ensure that the tester retained control over how the fields were named in order to avoid them having to ALT-TAB between the test data and the routine in order to get the field names right.  This has the additional benefit of NOT restricting column titles on the worksheet, so you can keep them readable and understandable – they aren’t property or method names on a test fixture or class.

Notes for those familiar with FIT

  • PSExpect doesn’t use the column titles in the worksheet, FIT uses them as property names and method calls.
  • PSExpect relies on the order of parameters, FIT does not.
  • PSExpect uses the name you give the range in Excel as the name of the test fixture to call, FIT uses a table title cell.
  • PSExpect requires the test fixture function include the pass/fail decision with a call to an Assert* function, FIT does textual comparisons.
  • PSExpect generates colourized output to the console and plain text in a log file, FIT returns the input table marked up to show pass/fail.

I’ve almost got 0.3 out the door – all the tests of the functions are still passing but I want to include an example of keyword-driven testing, and that’s taking some time to get to.


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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s