Selenium WebDriver – How To Create TestNG Suite XML From Spreadsheet

Overview:

If we use testNG framework, it could be difficult to maintain the testNG suite xml for a huge project where we have thousands of test cases. For such projects, it is convenient to maintain the tests in a spreadsheet & create the testNG suite xml from the spreadsheet.

In this article, let’s see how we could create the testNG suite XML file at run time!

Sample Spreadsheet:

Lets assume, I keep all my test cases in a spreadsheet as shown here.

• Description column is used to provide high level description of the test. It is used as the value of the name tag in testNG suite xml.
• There is an Active indicator to run only Active (marked as ‘Y’) test cases. We might not want to run all.
• There is a Module column to group the tests. For ex: All tests related to Order module should be executed.
• ClassName column shows the list of classes (comma separated) to be executed as part of the test.
• Data column (you could add more coulmns for data) is used to pass the data as the parameter to the test in the testNG suite XML.

I would want to query the above spreadsheet to get the specific tests. For example, If I need to execute all the active and order module tests, I would be using SQL as shown here.

Select * from TestCase where Active = 'Y' and Module = 'Order';

 Fillo:

To query the spreadsheet, I have been using Fillo for the past few years. It is just a Java API to query xls, xlsx files. The advantage of using Fillo here is, it treats your spreadsheet as a database and each sheet as a table of  the database.

Check here for the maven dependency and some sample code to use the library.

Suite Object Implementation:

First lets create a class diagram for the testNG suite xml. We could expect the testNG suite xml in the below format

<suite name="suite-name">
   <test name="test-name">
      <parameter name="param-name" value="param-value" />
      <classes>
         <class name="com.testautomationguru.test.Test1" />
     <class name="com.testautomationguru.test.Test2" />
      </classes>
   </test>
</suite>

So, our class hierarchy will be as shown here.

suite (name)
	test (name)
		parameter (name, value)
		classes
			class (name)

For the above model, test, class, parameter could be Lists.

I add the below mvn dependencies in my java project.

<dependency>
    <groupId>com.fasterxml.jackson.dataformat</groupId>
    <artifactId>jackson-dataformat-xml</artifactId>
    <version>2.9.0.pr4</version>
</dependency>
<dependency>
    <groupId>com.codoid.products</groupId>
    <artifactId>fillo</artifactId>
    <version>1.15</version>
</dependency>

I create a Suite.java to represent the above class hierarchy – I would create an instance of this class by reading the spreadsheet, so that I could export this as a testNG xml.

@JacksonXmlRootElement(localName = "suite")
public class Suite {

    @JacksonXmlProperty(isAttribute = true)
    private String name;

    @JacksonXmlProperty(localName = "test")
    @JacksonXmlElementWrapper(useWrapping = false)
    private List < Test > tests;

    public Suite(String name) {
        this.name = name;
        this.tests = new ArrayList < Suite.Test > ();
    }

    public void addTest(String testname, String paramName, String paramValue, String className) {
        Test test = new Test(testname);
        test.addParam(paramName, paramValue);
        Pattern.compile(",").splitAsStream(className).forEach(test::addClass);
        this.tests.add(test);
    }

    class Test {

        @JacksonXmlProperty(isAttribute = true)
        private String name;

        @JacksonXmlProperty(localName = "parameter")
        private Parameter param;

        @JacksonXmlProperty(localName = "classes")
        private Classes klasses;

        public Test(String name) {
            this.name = name;
            klasses = new Classes();
        }

        public void addParam(String name, String value) {
            param = new Parameter(name, value);
        }

        public void addClass(String name) {
            klasses.assClasses(name);
        }

    }

    class Parameter {
        @JacksonXmlProperty(isAttribute = true)
        private String name;

        @JacksonXmlProperty(isAttribute = true)
        private String value;

        public Parameter(String name, String value) {
            this.name = name;
            this.value = value;
        }

    }

    class Classes {

        @JacksonXmlProperty(localName = "class")
        @JacksonXmlElementWrapper(useWrapping = false)
        private List < Class > classes;

        public Classes() {
            this.classes = new ArrayList < Suite.Class > ();
        }

        public void assClasses(String name) {
            this.classes.add(new Class(name));
        }
    }

    class Class {

        @JacksonXmlProperty(isAttribute = true)
        private String name;

        Class(String name) {
            this.name = name;
        }

    }

}

XLS Reader:

I create a sample XLS reader as shown here using Fillo. I query the spreadsheet and then create the xml.

public class XLSReader {

    private final Fillo fillo;
    private final String filePath;

    private Connection connection;

    public XLSReader(String filePath) {
        fillo = new Fillo();
        this.filePath = filePath;
    }

    public void getTests(String query) {
        try {
            connection = fillo.getConnection(this.filePath);
            Recordset recordset = connection.executeQuery(query);
            this.createSuite(recordset);
        } catch (FilloException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    private void createSuite(Recordset recordset) {
        XmlMapper xmlMapper = new XmlMapper();
        Suite suite = new Suite("TesTautomationGuru");
        try {
            while (recordset.next()) {

                String testName = recordset.getField("TestCaseDescription");
                String className = recordset.getField("ClassName");
                String param = "Data";
                String paramValue = recordset.getField("Data");

                suite.addTest(testName, param, paramValue, className);
            }
            xmlMapper.writeValue(new File("c:/testng-suite.xml"), suite);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            recordset.close();
        }
    }

}

TestNG Suite XML:

Simply executing specific query will fetch corresponding tests and create a testng xml at run time as shown here.

XLSReader suite = new XLSReader("tests.xls");
suite.getTests("select * from TestCase where module='Order'");

testng xml output:

<suite name="TesTautomationGuru">
   <test name="Testcase Description 1">
      <parameter name="Data" value="data1" />
      <classes>
         <class name="com.testautomationguru.test.Test1" />
         <class name="com.testautomationguru.test.Test2" />
      </classes>
   </test>
   <test name="Testcase Description 2">
      <parameter name="Data" value="data2" />
      <classes>
         <class name="com.testautomationguru.test.Test3" />
         <class name="com.testautomationguru.test.Test4" />
      </classes>
   </test>
   <test name="Testcase Description 3">
      <parameter name="Data" value="data3" />
      <classes>
         <class name="com.testautomationguru.test.Test5" />
      </classes>
   </test>
   <test name="Testcase Description 4">
      <parameter name="Data" value="data4" />
      <classes>
         <class name="com.testautomationguru.test.Test6" />
      </classes>
   </test>
   <test name="Testcase Description 5">
      <parameter name="Data" value="data5" />
      <classes>
         <class name="com.testautomationguru.test.Test7" />
      </classes>
   </test>
   <test name="Testcase Description 6">
      <parameter name="Data" value="data6" />
      <classes>
         <class name="com.testautomationguru.test.Test8" />
      </classes>
   </test>
   <test name="Testcase Description 7">
      <parameter name="Data" value="data7" />
      <classes>
         <class name="com.testautomationguru.test.Test9" />
      </classes>
   </test>
</suite>

Summary:

Above approach for creating testNG suite xml at run time saves a lot of time in maintaining the suite file for big projects where we could have thousands of test cases.  We maintain all the tests in a spreadsheet and simply tag the module names to logically group the tests. So that I could use SQL to filter specific tests I need to execute.

Happy Testing & Subscribe 🙂