PowerShell Excel Functions Updated – Again

Note: You will need the Jan 22 version of the PSExpect source code to use the functionality described in this entry.

Continuing along the lines of the previous blog entry on the revised set of functions for accessing Excel from PowerShell, there is another new function that I’d like to introduce called ‘select-row’.  Good thing I’ve only made beta releases of the library so far (http://www.codeplex.com/psexpect) because this is evolving as I learn more about PowerShell and how we might want to use it to write test scripts.

The ‘select-row’ function consolidates all the Excel functionality into a single function so that the tester writing a test script relying on this data doesn’t have to know anything about calling COM objects from PowerShell.  Nor does the tester have to know anything about the Excel COM object model.

To use select-row, you have to know that Excel uses workbooks, workbooks contain worksheets, and that on a worksheet, a range of cells can be named.  But you don’t have to know the Excel API or that you have to release the COM objects when you’re done with them.

The following is the re-written script for testing the ‘get-process’ cmdlet using Excel to store the test data.

# specify the range location including workbook, worksheet, range $theFolder = get-location $WorkbookName = $theFolder.ToString() + "TestGetProcessFromExcel.xls" # if this worksheet name doesn't exist in the workbook, the error is ugly # and Excel is left orphaned and running in the background $WorksheetName = "Sheet1" $RangeName = "TestGetProcessFixture" # specify how to reference the row contents # this isn't read from the worksheet so that the tester has control over how # the columns are referenced in the rest of the test script $FieldNames = "TestCase","Remark","ProcessName","ShouldExist" # retrieve the contents of the range $rangeAsList = ` select-row $WorkbookName $WorksheetName $RangeName $FieldNames # make sure it's not null AssertNotNull $rangeAsList "GPSXL-1" # for each row, foreach ($item in $rangeAsList) { # exercise the target of the test (function doesn't exist in this code listing) $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 } RaiseAssertions

Much simpler than before.  I’ve used the verb ‘select’ in the sense that the function returns an array of objects (in this case, hashtables) and I’ve used the ‘row’ noun since that is what it being represented by each hashtable.  All of the COM object-release-stuff from the last blog entry is inside the select-row function so that you don’t have to worry about it.

I’ve left the other functions in place because this function just calls them in order to work, but also for those testers that are using many workbooks and don’t want to be starting/stopping Excel many times in their test script.


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