Archive for the 'software testing' Category

Exporting XML From Excel Tables – 3: Self-checking Structure

In the previous post, we were looking at how to export a hierarchical structure as XML, from a set of Excel tables, where the rows are hierarchically numbered (1, 1.2, 1.2.3, etc). The approach in our VBA is to build a hierarchical object model from our worksheet tables, and then ask the root of the model for its XML string. Both the building and the string generation proceed recursively down the structure. Note that this approach works for any hierarchical data – in our case it is software test definitions.

Having built a hierarchical object structure from our worksheet data, there are a number of other things we might want to do:

  • check the test definitions for structural validity
  • generate output in other formats, such as plain text (really plain, not XML)

I’ll talk a bit more about the first of these, here.

The basic idea is that of a class invariant – a property (which can be a conjunction of simpler properties) that always applies to any instance of the class. This idea is, of course, part of Bertrand Meyer’s Design By Contract approach.

Writing good invariants is hard, particularly for objects that are essentially reactive in their behavior. However, in our case here, we have a static hierarchical object structure, and there are some fairly obvious relationships between a node in our hierarchy and its child nodes:

  • there is a least one child node
  • the ID of the current node is a prefix of each child node ID (for example, 2.3 –> 2.3.1)
  • the number of the table rows representing the current node = the sum of the rows for the child nodes + 1 row for the current node

These properties help to ensure that the test data worksheets are ‘well formed’ – with no extra or missing rows, and with proper hierarchical nesting. You can see an example of the last property in this screen shot:

worksheet

  • test condition 101 is represented by rows 3:9 = 7 rows
  • test scenario 101.1 is represented by rows 4:6 = 3 rows
  • test scenario 101.2 is represented by rows 7:9 = 3 rows
  • so 7 = 3 + 3 + 1 row for the test condition itself

The Eiffel language has full support for invariants and other assertions, and it would be the job of the class constructor to establish the invariant. With VBA, we have to take a less rigorous approach, and have the invariant as a normal Boolean-returning function, which should return True any time after the Build procedure has been called. In Eiffel, failure of an invariant or other assertion raises an exception; in VBA, we’ll just put up an error MsgBox.

Here’s the invariant function for a test condition (level #2 in the hierarchy):

Function Invariant() As Boolean
    Invariant = _
            tcond_tid.IsCondition And _
            tcond_scenarios.Count > 0 And _
            tcond_scenarios(1).ScenNum = "1" And _
            ChildrenPartitionRows
    If Not Invariant Then MsgBox "Problem with Condition " & Me.Tid

    Dim ts As TScen
    For Each ts In tcond_scenarios
        Invariant = Invariant And IsChild(ts.Tid, Me.Tid)
        If Not Invariant Then MsgBox ts.Tid & " is not a child number of " & Me.Tid
        Invariant = Invariant And ts.Invariant
    Next
End Function

The first part checks:

  • the Condition’s ID is at the right level for a Condition
  • there is at least one child Scenario, and that the numbering starts from 1
  • that the child Scenarios cover all of the Condition’s rows (minus 1)

The second part iterates over the child Scenarios:

  • checking that the child IDs extend the Condition’s ID (there’s an IsChild relationship between the two IDs)
  • recursively evaluating the invariants for the child Scenarios (and thence recursively down the whole structure)

The ChildrenPartitionRows function is a simple iteration over the child Scenarios:

Function ChildrenPartitionRows() As Boolean
        'Do the rows of my children + 1 = my rows?
    Dim childrenrows As Integer
    Dim ts As TScen
    For Each ts In tcond_scens
        childrenrows = childrenrows + ts.Rowcount
    Next
    ChildrenPartitionRows = (childrenrows = tcond_tblrowcount - 1)

End Function

It might seem that we’re making extra work in writing these invariant and helper functions, but it’s a useful exercise in understanding how our worksheet tables represent a hierarchical structure, and what rules this implies for the worksheet/table layout. Further, we can make the checking directly accessible to a user via a Command Button: the handler simply builds the structure and calls the top-level invariant (on the Test Definition):

Private Sub CheckCommand_Click()
        'Build the TDefn, then evaluate its Invariant (and recursively down the structure)

    Dim td As TDefn
    Set td = New TDefn
    td.Build
    If td.Invariant Then
        MsgBox "Check Complete"
    Else
        MsgBox "Some problems were found"
    End If
End Sub

Note that we’re not being very informative about the precise location of an invariant failure. However, it would be simple enough to record an invariant failure on the object concerned, and then interrogate the structure subsequently to find out the ID of that object.

Finally, as an example of ‘plain text’ output (as opposed to XML), we can have objects provide a simple text representation of themselves – say just ID and Description, with vbTab and vbCrLf. This can be displayed in a TextBox (with property MultiLine = True), to give us a simple Table of Contents. Done within the handler above, this would be:

Worksheets("Front").TocText.value = td.Contents

For a simple one-level ToC, the Test Definition would simply iterate over the child Conditions, concatenating their individual Contents strings.

Exporting XML From Excel Tables – 2: Object Model

In the previous post, we were looking at how to export a hierarchical structure as XML, from a set of Excel tables, where the rows are hierarchically numbered (1, 1.2, 1.2.3, etc). The approach in our VBA is to build a hierarchical object model from our worksheet tables, and then ask the root of the model for its XML string. Both the building and the string generation proceed recursively down the structure. Note that this approach works for any hierarchical data – in our case it is software test definitions.

There are six levels in our hierarchy. #1 is represented by an entire workbook; #2 to #5 are represented by subsets of (contiguous) table rows; #6 is represented by a single cell in a table row (plus its associated header information). There is a VBA Class Module for levels #1-5.

Level #6 corresponds to a test parameter – a single cell, plus its associated column header. The parameters don’t have their own class, as they are really just a couple of values (data, header). The level #5 class holds this information as arrays (corresponding to the relevant data row and the header row).

The multi-level numerical ID is complex enough to warrant a VBA Class Module of its own. This allows us to do things like asking an ID what level it’s at, or asking for a new ID for its Nth child.

Each of the five classes representing a test level has attributes (Private) to record:

  • the chunk of worksheet to which an instance maps: the table (ListObject), the row within the table, and the row count (i.e. the size of the table chunk)
  • its ID (TestID object)
  • data that applies to that level, such as a description, category and mode
  • a Collection of child objects, representing the next level down

Each of the five classes has a Build method (Sub), which is called by its parent object (or the user interface, for the top level). The Build method takes as parameters:

  • its parent’s ID
  • the chunk of worksheet to which an instance maps: table (ListObject), row within the table, and row count (i.e. the size of the table chunk)

The Build method:

  • stores its parameters
  • gets and stores its table-row information
  • creates a new ID for itself (derived from its parent’s ID)
  • calls a method to BuildChildren

The BuildChildren method scans through the table chunk, finding the sub-chunks corresponding to each of its children. For each sub-chunk, it creates a child object of the appropriate class, and asks the child to Build itself, given the appropriate parameters (see above). Determining which level a particular row represents is just a case of seeing which of its first four cells have numbers, and which blanks. There are utility functions for doing this.

A couple of observations regarding the ‘object oriented-ness’ of this. Firstly, in a fully OO language, the Build method would probably be the constructor for an object. The is preferable because it removes the possibility of having ‘not yet built’ objects. However, in VBA, Sub Class_Initialize can not take parameters, so we need a separate method called immediately after the object creation (Set … New …). Secondly, in a fully OO language, one would be tempted to to abstract some of the structure and processing into an abstract class – say, TestNode. However, our structure is not arbitrarily deep – we’re not going to have 20 levels. Also, the top and bottom levels are special cases – actually, only levels #2 and #3 are substantially the same.

Having built an object model, generating the XML is then very easy. Each of the five classes representing a test level has an AsXml function. Here’s the top-level one, on Test Definition:

Function AsXml() As String
        'Element <tdefn>

    AsXml = "<tdefn id='" & tdefn_id & "'>" & vbCrLf & _
                DescAsXml() & _
                ConditionsAsXml() & _
            "</tdefn>"

End Function

Function ConditionsAsXml() As String
        'Element <tcond>*

    ConditionsAsXml = ""
    Dim tc As tcond
    For Each tc In tdefn_conds
        ConditionsAsXml = ConditionsAsXml & tc.AsXml & vbCrLf
    Next

End Function

The second function shows the recursion of AsXml to the child level (here, Test Condition). At other levels, there’s a little extra complexity to allow for optionally outputting secondary information, such as Descriptions.

The top-level XML String is then written out to a file via a TextStream (MS Scripting library). This is wrapped in a function XmlOut.

There is a simple ‘user interface’ worksheet, with a Command Button:

Private Sub ExportCommand_Click()
        'Build the TDefn and write its AsXml string to file

    Dim td As TDefn
    Set td = New TDefn
    td.Build
    XmlOut td.AsXml

The same kind of recursion down our hierarchical object structure can be used to make the structure self-checking (up to a point). I’ll talk about this in the next post.

Back, Briefly: Exporting XML From Excel Tables

It’s been a while since I posted anything to this VBA blog, since my main field of work is XML based documentation (see previous posts). However, I recently did an Excel VBA micro-project, which I think is worth sharing. I’ll describe the problem and outline the solution in this post, and add some details in a couple of further posts.

The overall aim is to provide a way of creating and editing software test definitions, while also making those definitions accessible to a test harness for actually executing the tests. (The solution I’m going to describe is, with appropriate modifications, more widely applicable than just for test definitions).

Since the test definitions fall naturally into a tabular form, Excel is an obvious front-end tool. The problem is how to make the data accessible to a testing harness. One way is to use Apache POI, a Java API for accessing Microsoft Office files – and our developer was keen to use this. However, there are benefits to having a plain text – that is, XML – representation of the data:

  • It can be stored (alongside the Excel workbook) in a version-control repository, and different versions differenced, using a plain text or (better) XML-aware diff tool
  • A body of test definitions can be analyzed and reported on using XQuery
  • Any scripting language with an XML library can access the test data

So, the requirement is for a VBA export facility that takes the test definitions in a workbook, and exports it as a single XML file.

Now, if the test definitions were simply a list of tests, it would be very simple – we might be able to use Excel’s built-in XML facilities. However, our test definitions are quite deeply hierarchical:

  1. Test Definition (for a system component or capability)
  2. Test Condition (a functional area to be tested)
  3. Test Scenario (a particular test configuration)
  4. Test (an atomic test)
  5. Test Step (one or more steps within a Test – typically only one)
  6. Test Parameter (property, value and purpose – in, out, environment)

#1 is represented by an entire workbook; #2 to #5 are represented by subsets of (contiguous) table rows; #6 is represented by a single cell in a table row (plus its associated header information).

#2 to #5 have hierarchical, numeric IDs, spread over the first 4 columns of a table. Where (typically) a Test has only a single Step, they are represented by a single row (that is, the Step is implicit). For example:

worksheet

  • 101 is a Condition,represented by rows 3:9
  • 101.2 is a Scenario,represented by rows 7:9
  • 101.2.1 is a Test, represented by row 8

(each Test here has a single implicit Step). The columns on the right-hand half of the table represent the Parameters. The greying-out of ID numbers is just conditional formatting.

The XML looks much like any XML:

XML

The point to note is that we have a six-level hierarchical structure, with each node in the hierarchy mapping on to a chunk of Excel table.

Now, the solution, in outline. We could pass through the tables and rows, spitting out XML strings as we go. However, this approach is rather limited, as there are a number of other things we might want to do:

  • check the test definitions for structural validity
  • generate output in other formats, such as plain text (really plain, not XML)

As earlier posts have illustrated, I’m keen on building object models that ‘sit behind’ the worksheets, and provide the application functionality. This seems to be a classic application for this. Each item in our test definition hierarchy is represented by an object (of the appropriate class), and is built from a chunk of worksheet table.

We start by creating a Test Definition object, and asking it to build itself. It goes through the worksheets and tables, finding the chunks that correspond to Conditions (using the ID level). It creates Test Condition objects, tells them to build themselves from the appropriate chunks, and adds them into a Collection.

Each Test Condition object goes through its chunk, finding the sub-chunks that correspond to Scenarios (using the ID level). It creates Test Scenario objects, tells them to build themselves from the appropriate sub-chunks, and adds them into a Collection. And so on, down the levels…

Having built the object hierarchy, we can do various things with it. Primarily, we want an XML string, which we can write out to a file. We simply ask the top-level Test Definition object for its XML string. This asks each of its Test Conditions for their XML strings, concatenates them successively, and wraps the result in its own Definition-level XML element (<tdefn> – see above).

Each Test Condition asks its Test Scenarios for their XML strings, concatenates them successively, and wraps the result in its own Condition-level XML element (<tcond> – see above). And so on, down the levels… A few other elements, such as descriptions, get mixed in, as we go.

Note that the object hierarchy is built anew, each time we invoke an operation – there’s no persistence other than through the table rows. This might seem wasteful, but there’s really no performance issue.

In the next post, I’ll say a bit more about the actual code involved, and I’ll say something about the self-checking functionality in a third post.


September 2014
M T W T F S S
« Dec    
1234567
891011121314
15161718192021
22232425262728
2930  

Follow

Get every new post delivered to your Inbox.