excel-testng: driving TestNG tests through MS Excel

Over last few days, I have been working on a small project in my spare time. It's called excel-testng and it provides a way to drive TestNG tests through MS Excel. The code repository is here: http://code.google.com/p/excel-testng/ and the jars can be downloaded from here. I also put together a small automation project using Selenium WebDriver to demonstrate its use here: https://github.com/randomsync/excel-testng-demo.

Introduction

During functional testing projects, after we create tests for a certain application, we need to review them with the rest of the team (developers, analysts, project managers etc). We may use a test management tool to document the test cases and then export them into an easily distributable format like MS Excel or PDF. Sometimes, we may also create the tests in Excel directly specifying the test name, description, parameters and other data in the spreadsheets. And then finally, we may automate a part (or all) of the tests.
So after the tests are automated and when executing the tests, we need to specify which tests to run and the test data (parameters). In Quality Center, it involves creating a test set (kind of like a test suite) and then adding tests to it. If they are automated in QTP which integrates with QC, they can be executed by running the test set. But we have started using Selenium WebDriver for its cross browser capabilities and that means we need to specify the tests in a format that can drive the Selenium tests. We use TestNG as the framework for test execution, assertions and reporting.

Extending TestNG

TestNG is a great framework for test execution and its input can be in form of an XML file that specifies which tests to run, where to find the test classes/methods, test parameters and a whole lot of other features that gives you a fine grained control over each test execution. However for our UI functional tests, we wanted to be able to specify the tests and test executions in an easily distributable format (like MS Excel) as mentioned above. The good thing is that TestNG provides the capability to extend it so that its input can be created and executed programmatically. So this way, the test specification in Excel files can be parsed and driven through TestNG. And this is exactly what excel-testng does. It externalizes all Excel parsing and TestNG XmlSuite creation so that the focus can be in creating the test classes and methods. Once that is done and Excel specifications created, all that needs to be done is to provide a main method that does this:

   1: ExcelTestNGRunner runner = new ExcelTestNGRunner("input.xls"); // this can be a single file

2: // or a directory, in which case all spreadsheets in that directory are parsed

3: runner.run(); // run the tests

With ExcelTestNGRunner class, you just specify the input location of the Excel file(s) that constitute the test specifications and then call the run() method, which parses the excel file using default parser (an instance of ExcelSuiteParser) into XmlSuites, creates a TestNG object if not already created and then runs them. After that TestNG takes care of test execution and reporting.

Excel Test Specification

ExcelTestNGRunner parses each worksheet in the Excel file(s) into a separate suite using the included parser (ExcelSuiteParser). Each suite can have suite level parameters specified in the worksheet and the test specifications that specify which tests will be run, their name, description, parameters and the test classes. Here's what a test suite in Excel looks like (a demo spreadsheet can also be downloaded from here and can be used as a starting point):

Specifying TestNG tests in Excel File

The top few rows in the worksheet provide the suite information. ExcelSuiteParser looks for the string "Suite Name" and retrieves the name of the suite from next cell in the same row. Similarly, it looks for string "Suite Parameters" and retrieves suite parameters from the next cell. "Suite Configuration" is not currently used. You can customize the location of these values by providing your own map to the parser. See "Customizing Input" for more details.

To retrieve the tests that will be executed, it looks for the 1st row containing "Id" in the 1st column. This will be the header row, below which each row is a separate test. The header row and tests must have columns specifying Id, Test Name, Test Parameters and Test Configuration (which specifies the classes containing the Test methods). If "Id" is left blank, the test will not be added to the suite. Finally, it parses each row under the header row into a TestNG XmlTest and adds it to the suite. The test specifications are provided as:

• Test Name: generated by concatenating Id & Test Name
• Test Parameters: retrieved from "Test Parameters" column and need to be provided in valid properties (<key>=<value> etc.) format. You can also specify functions as parameter values and then add the logic to parse and evaluate the functions in your test classes (maybe a Base Test class)
• Test Classes: specified under "Test Configuration" column as classes property. Currently, you can only specify a single test class of which, all @Test annotated methods will be executed as a part of this test execution. I'm going to add the ability to select the test methods in later releases.

Customizing Input

If your test cases are specified in Excel but in different format, there are 2 levels of customizations you can do with ExcelTestNGRunner on how to parse the input spreadsheet(s):

1. Custom Parser Map (currently not implemented): You can use the in-built parser but specify your own parser map, which tells the parser where it can find the suite and test data
2. Custom Parser: You can create your own parser by implementing IExcelFileParser interface. You need to parse the spreadsheet file and return a list of TestNG XmlSuites.

ExcelTestNGRunner also provides helper methods to customize the TestNG object it uses to execute tests. For example, you can specify any custom listeners using addTestNGListener() method. If you need to have more control, you can create your own TestNG object and then pass it to ExcelTestNGRunner. Please see javadocs for more details.

Putting it together

You can see the project at https://github.com/randomsync/excel-testng-demo for a complete working demo of excel-testng to parse the input test specifications in Excel. It uses Selenium WebDriver to automate the testing of basic Google search functionality.

Update (3/12/2012): I'm now using google code to host this project because of the provision to host the downloadable jars, javadocs and wiki easily. I'll keep it synced with github but you can find the project documentation and downloads there.

A Generic Function to enhance BPT components

About 2 years ago, we created a set of QTP function libraries to drive the automation effort using Quality Center's Business Process Testing (BPT) functionality. What those libraries provide are a set of generic functions from clicking on a UI object to verifying an object's property, which then can be used to create both generic BPT components that can be used on any application and specific components that target a particular application or a screen within an application. We have used those libraries and components to successfully automate our application testing without much modification and maintenance.

Recently, we were using the same set of components to create/update the test cases for a new application and I took this opportunity to enhance the libraries by adding a few functions. One of them is a generic Eval function that provides some more flexibility in creating components. As we know, VBScript has its own Eval function that evaluates the provided expression and returns the result. So to be able to use that functionality from within the components, I created a new function that returns the result of that expression or the string itself if it is not an expression. Here's the function listing:

' Function EvalFunction
' ------------------
' Evals the function call specified in the parameter and returns the result
' Parameter: expr - Any VBScript expression
'@Description Evals the function call specified in the parameter and returns the result
'@Documentation Evals the function call specified in the parameter and returns the result
Public Function EvalFunction(ByVal expr)
  Dim res
  On Error Resume Next
  If (expr <> "") Then
    res = Eval(expr)
    If (Err <> 0)Then
      res = -1
      Err.Clear
    Elseif res = "" Then
      res = expr
    End If
  End If
  EvalFunction = res
  On Error Goto 0
End Function

The function itself is quite simple but it provides a lot of added functionality. I can use this function in my BPT components to evaluate any expression during run-time. And in addition, I also create a generic component that calls this function and returns the result. When I need to create a test case that needs a run-time value, I can use this component within my test cases to have that functionality.

Using the EvalFunction function in components:

For a part of our application's functionality, we had a component ("AddIP") that adds an IP to a user. Since there were 4 text fields for each of the octets, we had 4 component input parameters for each octet. In the picture below of AddIP component, each octet is a different text field (“IP1”…”IP4”) requiring a different input parameter (BrowserWebEditSet is another generic function to enter value in a WebEdit object)

But while revisiting the testing, I felt that its much easier to provide the IP parameter as a whole when you're creating a large test case where multiple IPs need to be added. So instead of having to create a new function, I just used the EvalFunction to create another component ("AddIPv2") that takes the full IP address and splits it into each octet and then enters the values as required in different text boxes. I use the VBScript “split” function to split the input parameter (the IP address) into each octet like this: "Split(""" & Parameter("IP") & """,""."",-1,vbTextCompare)(0)" which returns the 1st octet. As in the picture of the component below, I call the function 4 times for each octet which returns the result in a local parameter which I can then use to set for each text field.

So using this enhanced component, the user has to provide only the whole IP address “xxx.xxx.xxx.xxx” instead of each octet in a separate parameter.

Using the EvalFunction component in BPT tests:

The EvalFunction component is a one operation component that calls the EvalFunction and returns the result in a component output parameter. This component can be used in any test to evaluate any expression and use the result in a subsequent component.

For example, a lot of our test cases require creating a new user. Instead of specifying a fixed value for the user ID or having the tester enter a value in a run-time parameter before every run of the test, the user ID can be generated using a timestamp which guarantees its uniqueness. This is done by using the EvalFunction component. In the BPT test case below, the EvalFunction component is used with input parameter a string prefix concatenated with the current timestamp (DateFormatter is another function that returns the current date/time in appropriate format). It returns the result in a component parameter “result” which is then later used in AddUser component to create a user with that user ID. 

This way, this test can be run any number of times without requiring any modification.

Epilogue:

The function that I showed in this post is a part of the automation libraries that we created. Over time, as we use them for testing multiple applications, we have enhanced the libraries even more. These libraries, along with the BPT functionality provided by Quality Center, have helped us create reusable components that require very less maintenance. And these components are being used by testers to create and run automated test cases for different applications and different functionalities.

QTP Automation: A Generic Function to perform mouse click

For all the automation testing I’ve been doing in recent months, I’ve built a set of function libraries in QTP that contain functions doing a lot of different things from clicking on a button, validating properties of different web objects to parsing data in excel files. One of these libraries contains all the WebBrowser related functions and one of those is BrowserButtonClick. It takes the title of the browser and name/index of the button as input and does the obvious – clicks on the button within the specified browser. It does all that through descriptive programming, not needing to have any objects in the GUI repository and gives me the advantage of reusing this library framework in different projects/applications. This has allowed me to automate functional tests of different applications very quickly.

But this post is not about that function or about the framework. I’ll write about that later - how it all fits together to provide an automation framework that can speed up functional automation. In this post, I want to share another QTP VBScript function that I wrote to perform a mouse click on any web object. This function forms the basis of descriptive programming that I’ve used in my libraries and provides greater flexibility instead of tying it down to a specific object type.

Like I described, the BrowserButtonClick takes the name of the button as parameter. This works fine as long as the button object has the name property defined (which most of them do) and I know it when creating the tests. For one of the application page however, there were 2 buttons with no name specified but with “html id” property. This led me to come up with another function that will take the property names (“html id” or “name” etc) and property values as input parameters, search for a button object with specified properties and perform the action (left button click) on that. But thinking about it a little more, since the class of the object (“micclass”) is also another property of the object, it doesn’t have to be restricted to a “WebButton”. So the end result was this generic function that takes the list of appropriately delimited properties and values, finds the object and performs a left-click on the object. The input parameter is in the format “propName1=propValue1;propName2=propValue2…”. For example, “micclass=WebButton;html id=Search”. This way, it can be used to perform a click on any object and similar functions can be used to perform any action on any GUI object.

Here’s the function.

Part 1: Function Definition

' Function BrowserObjectClick
' ------------------
' Perform a mouse click on an object within the specified Browser
' Parameter: browserTitle - Title of the browser
' Parameter: props - a list of name-value pairs of properties and their values, semi-colon delimted. ("propName1=propValue1;propName2=propValue2…”)
' Parameter: objIndex - the index (0-based) of the object that needs to be clicked (if multiple objects match)
'@Description Perform a mouse click on the object that matches specified properties within the specified Browser. Returns 0 is successful, -1 if no object is found or the object is disabled
'@Documentation Perform a mouse click on object that matches <props> properties within <browserTitle> browser
Public Function BrowserObjectClick(ByVal browserTitle, ByVal props, ByVal objIndex)

As I already explained above, the function performs a mouse click on any object that matches the specified properties within the specified browser. The browser title needs to be provided as input parameter and so do the properties, which are name-value pairs delimited by semi-colon. For example, to find a WebButton object with name “Search”, the input will be “micclass=WebButton;name=Search”. For a link with outertext property “Log Off, the input will be “micclass=Link;outertext=Log Off”. If multiple objects match the identification properties, objIndex (0-based) will specify which one of those objects is clicked.

Part 2: Getting the browser objects

    On Error Resume Next
    If (props <> "") Then
        indx = CInt(objIndex)
        If (Err <> 0 Or indx < 0)Then
            indx = 0
            Err.Clear
        End If

        'Get the browser objects
        Set desc = Description.Create()
        desc("micclass").Value = "Browser"
        Set bObjs = Desktop.ChildObjects(desc)
    
        For i = 0 To bObjs.Count -1
            If Instr(1, bObjs(i).GetROProperty("title"), browserTitle, 1) > 0 Then

First we make sure that the properties are not blank in the input parameters. If they are blank, there’s not much we can do so we just exit out of the function. Next thing to do is to make sure the objIndex is an integer >=0. In case it isn’t, we take the default value of 0, which means that the first object matching the properties will be clicked. Then we get all existing browser objects and iterate through them until we find the one with matching title. If none are found, we’ll exit out with appropriate return values. If multiple browsers are found, we’ll use the 1st one that matches the title. In the applications I’ve automated so far, I haven’t come across a case where multiple browsers match the title…but if needed (in case the application pops up multiple windows, for example), the function can be easily modified to handle that.

Part 3: Getting the Object and performing the Action

                desc("micclass").Value = "Page"
                Set pObjs = bObjs(i).ChildObjects(desc)
                'Make sure you got one
                If (pObjs.Count > 0) Then
                    'load the properties in an array
                    Dim propArray : propArray = Split(props,";",-1,vbTextCompare)
                    Dim prop
                    Dim propsDict    'As Scripting.Dictionary
                    Set propsDict = CreateObject("Scripting.Dictionary")
                    For Each prop in propArray
                        propsDict.Add Split(prop,"=",-1,vbTextCompare)(0), Split(prop,"=",-1,vbTextCompare)(1)
                    Next
                    
                    'Get the Object
                    Dim j, pKeys : pKeys = propsDict.Keys
                    For j = 0 to propsDict.Count-1
                        desc(pKeys(j)).Value = propsDict.Item(pKeys(j))
                    Next
                    Set propsDict = Nothing
                    Set objs = pObjs(0).ChildObjects(desc)

So once we find the browser with specified title, we get its “Page” child objects since all other objects are child objects of the page object. We split the properties that we got as input by the semi-colon “;” and load it in an array. At this point, the array is populated with the name value pairs of the properties delimted by an equals sign “=”. I’m not doing much error handling here because I trust the input provided will be appropriately delimited.

Next, we split each of the array values by an equals sign “=” and load it in a “Scripting.Dictionary” object as key-value pairs, where the key is the name of the property. We create a Description object, load all the properties in it and find all matching child objects.

Part 4: Performing action on the object

                    If (objs.Count > indx) Then
                        'Execute the event
                        If (objs(indx).GetROProperty("disabled")) Then
                            BrowserObjectClick = -1
                        Else
                            objs(indx).Click
                            BrowserObjectClick = 0
                        End If
                        'Free up the objects
                        Set desc = Nothing
                        Set bObjs = Nothing
                        Set pObjs = Nothing
                        Set objs = Nothing
                        Exit Function
                    End If

Now that we have a collection of all objects that match the properties, we’ll make sure that the index is not more than the number of objects returned, that it is not disabled and then perform the click action. If it is disabled, we’ll return a –1 so that caller can handle it appropriately. If not, we perform the mouse click by calling the click method of the object and return a 0. Finally, we do some cleanup and exit function.

PostScript

This function provides a generic method to implement a mouse-click on any object using descriptive programming and is particularly useful where the standard properties of an object (name) are not available. It can be further extended to implement any action, not just clicks and provide other functionality as well.

If you have any questions or suggestions to improve or simplify the function, let me know.

QuickTest Pro: Handling XMLs using XMLDOM object

This is how diverse my writings are going to be. Testing tools, that’s it. I could write about my adventures in photography but that wouldn’t be very interesting until I put some more time and money into it. Even though I’ve been using my 50mm f/1.8 lens with some interesting results, I still feel that there’s lots more I need to learn before I can write about it …maybe someday soon. But this one is about a different, though as interesting topic - handling XML objects in QTP.

Background:

I’m currently involved in test automation for IDM application. If you are familiar with Sun’s IDM product, you know that all objects are stored in database as XML and these objects can be viewed/checked out/edited through the debug interface. Now the way we have customized it is that managers (users with Manager role) or end users are allowed to submit requests for creating users or modifying users’ attributes. The requests are submitted through the user interface and the workflows take care of taking appropriate actions in the background. There is very little information displayed on the web page once the request is submitted that can be used to validate if the action actually succeeded or not. The only way to validate that the action was successful is to either login through admin interface and check the task results or through debug pages, pull up the user’s XML object and verify the changes have gone through.

As I was creating the BPT components and test cases to handle different test scenarios, it was increasingly obvious to me that the most foolproof way to validate the results of the test would be to create a component that validates the user’s XML. I wasn’t much familiar with handling XML objects in VBScript so I left it for later. And later is now (or was yesterday). So I spent yesterday going through the XML Document Object Model (DOM) and Microsoft’s implementation of it and creating a VBScript function to validate an XML’s attribute value. It worked out fine with a little initial struggle.

Please keep in mind that what I’m providing below is in no way a complete solution of using XMLDOM to do whatever you want to do. All I needed to do was to retrieve an attribute value from an XML using the XPath that is provided as input parameter and to compare it with the expected value. What you want do with XMLDOM may be different and based on your application’s and automation needs. The reason I’m posting this is that if you’re using XMLDOM with QTP/VBScript for the first time, you can avoid the initial struggle that I went through and get the job done faster.

For Microsoft DOM reference (this is where I got most of the information I needed), visit this: http://msdn.microsoft.com/en-us/library/ms764730(VS.85).aspx. The XPath language is described at: http://www.w3.org/TR/xpath

Solution:

1. The first step is to create a parser object and load the XML. The XML can be from a file or from a string. Here I’m using the loadXML() method to load it from the innertext property of a WebElement object that I retrieved earlier. To use a file, use the load() method instead.

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = false
xmlDoc.loadXML(objs(0).GetROProperty("innertext"))

You set the async property to false so that it doesn’t move on before the document is completely loaded. The reason I’m using CreateObject(“Microsoft.XMLDOM”) instead of XMLUtil.CreateXML() which is provided in QTP OM Reference is that when I used that, it gave me an error specifying that it couldn’t find the DTD file referenced in the XML. It seems that it needs the DTD if it’s mentioned in XML to be able to load it. I had the DTD and when I uploaded it to my local component folder, it worked fine. But since I could be running the test on a remote host, I didn’t want to have to upload that DTD to all my host machines. And I didn’t look into any other way. But if you decided to use the XMLData object provided by QTP, the objects and method names are different even though the overall steps will be the same. For example, to load the document, you’ll use:

Set xmlDoc = XMLUtil.CreateXML()
xmlDoc.load(objs(0).GetROProperty("innertext"))

2. Once you have the XML object, you need to get to the desired element. I kept it simple and asked for the XPath to the desired element as an input parameter. Once I have that, I used the selectNodes() method to get all the nodes in the desired XPath.

Set objNodes = xmlDoc.selectNodes(nodeXPath)

This gives me a collection of all the nodes (specifically, the IXMLDOMNodeList object) that match the specified parameter ‘nodeXPath’. With QTP XMLData, you can use:

Set objNodes = xmlDoc.ChildElementsByPath(nodeXPath)

If nodeXPath is invalid or doesn’t match any elements, the length of the collection will be 0.

3. So now, I need to check the length of the collection and get to the actual element that I want. Since I didn’t need to care if multiple elements match the XPath, I just took the first element in the collection. The item property returns a single node (IXMLDOMNode) from the collection specified by the index, in this case 0.

If objNodes.length > 0 Then
Set objNode = objNodes.item(0)

4. Now that I have the actual element, I need to get the value of specified attribute. The getAttribute(name) method returns the value of the ‘name’ attribute. The attribute name and its expected value are passed as input parameters:

If StrComp(attrValue, objNode.getAttribute(attrName), vbTextCompare) = 0 Then
'expected value matches actual value

attrValue is the input parameter containing the expected value and attrName is the attribute name. If it matches, I write a Pass in the results and if not, it’s a fail. Once done, I clean up the objects.

Set xmlDoc = Nothing
Set objNodes = Nothing
Set objNode = Nothing

That’s pretty much it. Once we start using the component actively, I may come up with some more ideas to improve it. I’ll write about it if they provide me enough of a challenge.

----------------------------------------------------------------------------------

Sample XML:

<User name="abcdefg" creator="Configurator" email="a.a@x.com" disabled="false" locked="false">
<Roles>
<ObjectRef name="Employee" isWeak="false"/>
</Roles>
<Attribute name="employmenttype" type="string" value="Employee" syntax="string"/>
<Attribute name="firstname" type="string" value="a" syntax="string"/>
<Attribute name="fullname" type="string" value="a, a" syntax="string"/>
<Attribute name="ismanager" type="string" value="true" syntax="string"/>
<Attribute name="lastname" type="string" value="a" syntax="string"/>
<Attribute name="middlename" type="string" value="M" syntax="string"/>
<Attribute name="phone" type="string" value="9999999999" syntax="string"/>
<AdminRoles>
<ObjectRef type="AdminRole" name="Manager" isWeak="false"/>
</AdminRoles>
</User>

For example, the XPath to get to <Attribute> with name=employmenttype is: User/Attribute[@name='employmenttype’]

Business Case for Testing Tools & Automation

This post had been lying in my drafts for a long time. For some reason, I decided not to publish it. But it's still relevant...except that I have submitted the business case since then and it's lying at somebody at Finance's desk or email Inbox for review/approvals.

----------------------------------------------------------------------------------

I'm currently working on a business case to invest in testing tools and functional test automation. The case will be sent to upper management and finance for approval and once they do, we're going to start working on building an automation framework. I foresee a lot of hurdles and am not keeping my hopes high based on the tightening of budget and how the business case is coming out to be.

One of the things that I have to add in the business case is financial analysis which includes an estimate on return of investment. And it has been a very "enlightening" process. Based on the current estimates on the capital and labor costs, it'll take us 2.9 years to get a return on investment. And of course, it's based on a lot of assumptions and I'm kind of skeptic when claiming that number for ROI.

You see, one of things that are never in short supply at my workplace is the list outstanding tasks. And with that comes extreme work pressure and very little time to invest in exploring new technologies and coming up with new ideas. I'm quite sure that designing the automation framework will be a time consuming and learning process and I will have to be devoted at least half-time (if not more) in this effort. And I'm quite sure that when we actually start working on that effort, the estimates that I put in for labor in the business case will start looking like best case scenario.

The brighter side is that I have now under my belt the experience of creating a business case. And once/if approved, we'll be undertaking the effort of functionally automating the test cases for our applications. We're also planning to expand our QA service by offering the testing tools and/or automation to other teams.

My colleague and I had been talking about the design of the framework since we first conceptualized the idea of automation. We had this whole idea in our minds on how to make it reusable, to maximize customizability with least rework. He has experience in building these kind of frameworks and I have had a brief encounter at my last job. I recently looked up online and found that it is now one of the established practices, one of the buzzwords surrounding test automation process - Keyword Driven Test Automation Frameworks. It must have been a long time since both of us were in automation business because what I read matched what we had been planning to do. Seems like a lot of people have written their experiences with building automation frameworks with this idea. Seems like we'll be able to use some of the knowledge to our benefit.