Caution: This doesn't work if you're default culture is not 'en-US'.  I'm working on a version that works with any default culture for the next release of PSExpect.

Let's face it - testing is expensive. Based on a survey that was part of my Masters work a couple of years ago, teams generally spend around 30% of their budget on testing at all levels (from low-levels like unit testing up to high levels like acceptance testing). The real cost comes in two areas: test script creation and execution.

Test automation is a partial solution to this since even though the script might take longer to write and require a more expensive resource to do the work, it can be run by anyone and as often as required. At least the 'execution' area is therefore less expensive.

Another way to reduce the cost of testing is to increase the 'test cases to test script' ratio, that is, write fewer (and leaner) test scripts that can be used to run a great many more test cases each. Consider the humble column fixture in the FIT testing framework (http://fit.c2.com). This is a classic example of a test script (a table defines one test script) that can run many test cases (each row in the table represents a test case). Consider the example below:

In that trivial example, the first column defines the test script as follows:

1. Assign the variable aNumber to the contents of the aNumber cell
2. Assign the varable bNumber to the contents of the bNumber cell
3. Invoke Add() and return the actual result
4. Compare the contents of the Add() cell (the expected result) to the actual result
5. Colour the background of the Add() cell green if actual equals expected, red otherwise.

Adding to this table is the way you increase the number of test cases - you could literally add tens or hundreds of test cases without modifying your script. FIT is great that way. Now, the PowerShell twist. We can do the same thing with PowerShell using Excel - that is, we can increase the ratio of test cases to test scripts by importing test data from Excel.

The Test Data

Let's consider testing the PowerShell cmdlet 'get-process'. In this context, we will create one or more test cases for get-process in Excel and then run them using the same PowerShell script block.

Here's a text-based version of the worksheet TestGetProcessFromExcel.xls:

The first column is a label for the test case - not really required, but a good practice with FIT as well as with any data-driven testing framework. Labeling your test cases makes debugging easier. The 'Remark' column is also optional and again good practice - you should explain your test case for the next tester. The ProcessName column then identifies the parameter that we will send to get-process, while the ShouldExist column represents an expected result. This isn't a complete test of get-process, but it's a start and is sufficient for this demonstration.

The Test Script

Let's build this inside out. First, write a script block that exercises the target of the test - in other words, a script block that we want to run for each of the rows in our worksheet data area.

function TestGetProcessFixture($ProcessName)
{
    if ($ProcessName -eq $null) {return "FALSE"}

    $result = "TRUE"
    $proc = get-process $ProcessName
    if ($proc -eq $null)
    {
        $result = "FALSE"
    }
    return $result
} 

The function is trivial enough that I don't think it needs explanation other than to point out that this fixture has two purposes - the first being to run the target of the test, and second being to translate the result into the vernacular of the input test data. In this case, it is returning a string that has values of "FALSE" or "TRUE". We're not using real Boolean variables here on purpose - keeping things as strings and avoiding data marshalling issues is generally the easiest thing to do. For now.

Driving the Test

To operate this script many times, we can write a PowerShell script that drives the rows from the worksheet through the test script above. This means we have to start Excel, grab the data, and then loop through it, each time calling the TestGetProcessFixture function listed above.

# start Excel - PowerShell works with COM objects
$excel = New-Object -comobject Excel.Application 

# get the data - standard Excel object model stuff
$workbook = $excel.Workbooks.Open(
  $WorkbookFullPath)
$worksheet = $workbook.Worksheets.Item(
  $WorksheetName)
$range = $worksheet.Range($RangeName) 

This gives us three COM objects that reference the Excel object model. You would do the same in any scripting language that runs Excel from its COM interfaces. Now comes some personal preferences...

First, I prefer ordered lists. Then when I loop through an ordered list in a test run, I know what order they are going to come in. In the PsExpect.DataLib.ps1 script, you'll find a Collect function that translates an Excel range object into an ordered list - each item in the list representing a row in the worksheet. I used hashtables since you can use the keys with the dot notation to retrieve specific items in the hashtable.

$FieldNames = "TestCase","Remark","ProcessName",
  "ShouldExist"
$rangeAsList = Collect $range $FieldNames 

You can find the implementation of the Collect function in the script file DataLib.ps1 that you can download as part of PSExpect, http://www.codeplex.com/psexpect. The result of the Collect function call above allows me to refer to the data like this:

$rangeAsList[0].TestCase # first row, first field
$rangeAsList[1].ShouldExist # 2nd row, last field 

So the loop for each row looks like this:

# for each row in the test cases range,
foreach ($item in $rangeAsList)
{ 

    # exercise the target of the test
    $proc = TestGetProcessFixture
      $item.ProcessName 

    # check the actual results against the 
    # expected results on the worksheet 
    # and use the label from the worksheet's 
    # first column 
    AssertEqual $proc $item.ShouldExist
      $item.TestCase
} 

Note: The AssertEqual function comes from PSExpect, the function library for PowerShell available at http://www.codeplex.com/psexpect. 

There is one final trick required - reliably shutting down Excel. The problem is that the managed code Runtime Callable Wrappers (RCW) for COM objects maintain a reference counter - and if that reference counter stays above zero, the process won't stop even if you tell it to. Even after your script finishes. The trick is to explicitly decrement the reference counter for each COM object that you have either created or referenced in the script.

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

This solution is referenced in the following Knowledge Base article from Microsoft: http://support.microsoft.com/kb/317109. The complete solution as described above is available in samples\TestGetProcessFromExcel.ps1 downloaded with PSExpect, http://www.codeplex.com/psexpect. The output of running the entire script is as follows:

There are 3 test cases.
Get-Process : Cannot find a process with the name 'xyz'. Verify the process name and call the cmdlet again.
At C:\wirk\ps1testlib\Ps1TestLib\samples\TestGetProcessFromExcel.ps1:78 char:24
+ $proc = get-process <<<< $ProcessName
12/18/2006 10:55:34 PM,SHOULDPASS,PASSED,GPXL-1
12/18/2006 10:55:34 PM,SHOULDPASS,PASSED,TC1
12/18/2006 10:55:34 PM,SHOULDPASS,PASSED,TC2
12/18/2006 10:55:34 PM,SHOULDPASS,PASSED,TC3 

The cmdlet error is to be expected - you can suppress those errors at runtime if you prefer. The next four lines are the results of the Assert* statements that are in the test script.  The fields following the timestamp represent the developer/tester's intent for that specific test case, the actual result, and the label that uniquely identifies the test condition in the test script.  This is the standard log entry (and console output) when using the PSExpect PowerShell Scripts for Testing.

Summary

Data-driven testing is a partial solution to improving test script maintainability and lowering the cost of adding test cases. As illustrated above, we can write a single test script and a driver script in PowerShell to support data-driven testing using Excel. Combined with the PowerShell Scripts for Testing library, this makes for a simple but effective mechanism for testing any target that can be called from PowerShell.