Thursday, February 23, 2012

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.

11 comments:

  1. Great work!! If you could add a demo excel workbook as template for download that would be great.

    ReplyDelete
    Replies
    1. A demo excel file is available at: https://github.com/randomsync/excel-testng-demo/blob/master/test-input/GoogleSearchTests.xlsx

      Delete
  2. I'm having some problems getting the demo to work. When attempting to run from the TestRunner Class I get the following:

    java.lang.NoClassDefFoundError: net/randomsync/googlesearch/TestRunner
    Caused by: java.lang.ClassNotFoundException: net.randomsync.googlesearch.TestRunner
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    Exception in thread "main"

    Pretty sure I'm missing something pretty basic.

    ReplyDelete
    Replies
    1. Did you specify the excel-testng-demo.jar file in classpath, using -cp:
      "java -cp .\excel-testng-demo.jar;.\lib* net.randomsync.googlesearch.TestRunner test-input 2"

      Delete
    2. Have added excel-testng-demo.jar into the classpath.. Still i get the same error
      java.lang.NoClassDefFoundError: net/randomsync/googlesearch/TestRunner

      Note: Executed through jar file

      Delete
  3. SKIPPED: testGoogleSearch
    org.testng.TestNGException:
    Parameter 'query' is required by @Test on method testGoogleSearch but has not been marked @Optional or defined
    in C:\Users\dell\AppData\Local\Temp\testng-eclipse-1954394509\testng-customsuite.xml
    at org.testng.internal.Parameters.createParameters(Parameters.java:155)
    at org.testng.internal.Parameters.createParameters(Parameters.java:355)
    at org.testng.internal.Parameters.handleParameters(Parameters.java:447)
    at org.testng.internal.Invoker.handleParameters(Invoker.java:1389)
    at org.testng.internal.Invoker.createParameters(Invoker.java:1081)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1186)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:51)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:85)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1197)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1122)
    at org.testng.TestNG.run(TestNG.java:1030)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)

    ReplyDelete
    Replies
    1. Seems like you're running excel-testng-demo project and the query parameter is missing in your input excel spreadsheet. Add "query=testng" in Test Parameters column as specified here and retry:
      http://3.bp.blogspot.com/-aQJizh9H60w/T1E5AWBTUkI/AAAAAAAAABk/CcZIhp4itMw/s1600/GoogleSearchTests-Excel.jpg

      Delete
  4. I executed Search.java but unfortunately I got the following error:
    [TestNG] Running:
    C:\Users\DELL\AppData\Local\Temp\testng-eclipse-1069165699\testng-customsuite.xml

    SKIPPED: testGoogleSearch
    org.testng.TestNGException:
    Parameter 'query' is required by @Test on method testGoogleSearch but has not been marked @Optional or defined
    in C:\Users\DELL\AppData\Local\Temp\testng-eclipse-1069165699\testng-customsuite.xml
    at org.testng.internal.Parameters.createParameters(Parameters.java:155)
    at org.testng.internal.Parameters.createParameters(Parameters.java:355)
    at org.testng.internal.Parameters.handleParameters(Parameters.java:447)
    at org.testng.internal.Invoker.handleParameters(Invoker.java:1383)
    at org.testng.internal.Invoker.createParameters(Invoker.java:1075)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1180)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1197)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1122)
    at org.testng.TestNG.run(TestNG.java:1030)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)


    ===============================================
    Default test
    Tests run: 1, Failures: 0, Skips: 1
    ===============================================


    ===============================================
    Default suite
    Total tests run: 1, Failures: 1, Skips: 0
    ===============================================

    [TestNG] Time taken by org.testng.reporters.SuiteHTMLReporter@8a0d5d: 32 ms
    [TestNG] Time taken by org.testng.reporters.jq.Main@1e4457d: 36 ms
    [TestNG] Time taken by org.testng.reporters.XMLReporter@10b4b2f: 12 ms
    [TestNG] Time taken by [FailedReporter passed=0 failed=0 skipped=0]: 6 ms
    [TestNG] Time taken by org.testng.reporters.EmailableReporter@18a7efd: 4 ms
    [TestNG] Time taken by org.testng.reporters.JUnitReportReporter@cdedfd: 7 ms

    ReplyDelete
  5. Exception in thread "main" org.testng.TestNGException:
    Two suites cannot have the same name:
    at org.testng.TestNG.checkSuiteNamesInternal(TestNG.java:1019)
    at org.testng.TestNG.checkSuiteNames(TestNG.java:1012)
    at org.testng.TestNG.sanityCheck(TestNG.java:985)
    at org.testng.TestNG.run(TestNG.java:1037)
    at net.randomsync.testng.excel.ExcelTestNGRunner.run(ExcelTestNGRunner.java:98)
    at Runner.MyRunner1.getXmlSuites(MyRunner1.java:44)
    at net.randomsync.testng.excel.ExcelTestNGRunner.run(ExcelTestNGRunner.java:83)
    at Runner.MyRunner.main(MyRunner.java:20)
    I am facing this error while driving tests from excel.

    ReplyDelete
  6. Hey
    You have done very good work. I am curious to know following things if possible to do with your library
    a) Is there any way if we can define the test parametersin separate columns rather than in single column
    b) is it possible to write the result of that test case in the same XLS file where my test are.

    Thanks in Advance

    ReplyDelete
  7. Hey
    Thanks for an wonderful project.
    I have almost build a Maven based TestNG framework, i read TestData from Data Sheet separately.

    Now all i want is i should use an excel to specify all the Suite and testclass details and it should execute from that excel and not from the TestNg.xml.

    Help me in doing this , i have couple of doubts
    1 Where will i find maven dependencies for excel-testng jar
    2 Should i delete the TestNG.jar from my project and the suite files as well?

    Thanks

    ReplyDelete