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:

• 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.