Defining test steps and object properties in an Excel Data Source for Selenium

Recently, one of the readers of this blog sent me an email asking whether it was possible to define not only input and validation parameters in an external data source (such as an Excel file), but also the test steps to be taken and the object properties on which these steps need to be performed by Selenium WebDriver. As it happens, I have been playing around with this idea for a while in the past, but until now I’ve never gotten round to writing a blog post on it.

The Excel Data Source
As an example, we are going to perform a search query on Google and validate the number of results we get from this search query. This scripts consists of the following steps:

  1. Open a new browser instance
  2. Navigate to the Google homepage
  3. Type the search query in the text box
  4. Click on the search button
  5. Validate the contents of the web element displaying the number of search results against our expected value
  6. Close the browser instance

For each step, we are going to define (if applicable):

  • A keyword identifying the action to be taken
  • A parameter value that is used in the action (note that for example a type action takes a parameter, a click action does not)
  • The attribute type that uniquely identifies the object on which the action is performed
  • The corresponding attribute value

For our example test script, the Excel Data Source to be used could look something like this:
Excel Data Source
Reading the Data Source
Similar to the concept explained in a previous post, first we need to read the values from the Excel sheet in order to execute the test steps defined. We need to be a little extra careful here as some cells might be empty and the methods we use do not particularly like reading values from cells that do not exist.

public static void main (String args[]) {

		String action = "";
		String value = "";
		String attribute = "";
		String attrval = "";
		
		try {
			// Open the Excel file for reading
			FileInputStream fis = new FileInputStream("C:\\Tools\\testscript.xls");
			// Open it for writing too
			FileOutputStream fos = new FileOutputStream("C:\\Tools\\testscript.xls");
			// Access the required test data sheet
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			HSSFSheet sheet = wb.getSheet("steps");
			// Loop through all rows in the sheet
			// Start at row 1 as row 0 is our header row
			for(int count = 1;count<=sheet.getLastRowNum();count++){
				HSSFRow row = sheet.getRow(count);
				System.out.println("Running test step " + row.getCell(0).toString());

				// Run the test step for the current test data row
				if(!(row.getCell(1) == null || row.getCell(1).equals(Cell.CELL_TYPE_BLANK))) {
					action = row.getCell(1).toString();
				} else {
					action = "";
				}

				if(!(row.getCell(2) == null || row.getCell(2).equals(Cell.CELL_TYPE_BLANK))) {
					value = row.getCell(2).toString();
				} else {
					value = "";
				}

				if(!(row.getCell(3) == null || row.getCell(3).equals(Cell.CELL_TYPE_BLANK))) {
					attribute = row.getCell(3).toString();
				} else {
					attribute = "";
				}

				if(!(row.getCell(4) == null || row.getCell(4).equals(Cell.CELL_TYPE_BLANK))) {
					attrval = row.getCell(4).toString();
				} else {
					attrval = "";
				}

				System.out.println("Test action: " + action);
				System.out.println("Parameter value: " + value);
				System.out.println("Attribute: " + attribute);
				System.out.println("Attribute value: " + attrval);
				
				String result = runTestStep(action,value,attribute,attrval);
				
				// Write the result back to the Excel sheet
				row.createCell(5).setCellValue(result);
				
			}
			
			// Save the Excel sheet and close the file streams
			wb.write(fos);
			fis.close();
			fos.close();
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
}

We do not read the value from the Result column, but rather we are going to execute the test steps using the values from the other columns, determine the result and write this back to the Excel sheet. In this way, we have a rudimentary logging function built in directly into our framework. Neat, right?

Defining and executing test steps
Next, we need to implement the generic runTestStep method we use to execute the test steps we have defined in our Data Source. This can be done pretty straightforward by looking at the current Action keyword and then executing the necessary steps for that keyword.

public static String runTestStep(String action, String value, String attribute, String attrval) throws Exception {

		switch(action.toLowerCase()) {
		case "openbrowser":
			switch(value.toLowerCase()) {
			case "firefox":
				driver = new FirefoxDriver();
				driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
				return "OK";
			case "htmlunit":
				driver = new HtmlUnitDriver();
				driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
				return "OK";
			default:
				return "NOK";
			}
		case "navigate":
			driver.get(value);
			return "OK";
		case "type":
			try {
				WebElement element = findMyElement(attribute,attrval);
				element.sendKeys(value);
				return "OK";
			} catch (Exception e) {
				System.out.println(e.toString());
				return "NOK";
			}
		case "click":
			try {
				WebElement element = findMyElement(attribute,attrval);
				element.click();
				return "OK";
			} catch (Exception e) {
				System.out.println(e.toString());
				return "NOK";
			}
		case "validate":
			try {
				WebElement element = findMyElement(attribute,attrval);
				if (element.getText().equals(value)) {
					return "OK";
				} else {
					System.out.println("Actual value: " + element.getText() + ", expected value: " + value);
					return "NOK";
				}
			} catch (Exception e) {
				System.out.println(e.toString());
			}
		case "closebrowser":
			driver.quit();
			return "OK";
		default:
			throw new Exception("Unknown keyword " + action);
		}
}

This setup makes it very easy to add other keywords (e.g., for selecting a value from a dropdown list), both for standardized browser actions as well as for custom keywords. Every step returns a result (here either OK or NOK) to indicate whether the action has been executed successfully. This is written back to the Excel sheet in our main method, so we can see whether our test steps have been executed correctly.

Finding the required object
I’ve also used a helper method findMyElement that, given an attribute and an attribute value, returns a WebElement that corresponds to these values. Its implementation is quite rudimentary, but it should be easy for you to extend it and make it more fail safe. I’m lazy sometimes. I haven’t even implemented all types of selectors that Selenium can handle!

public static WebElement findMyElement(String attribute, String attrval) throws Exception {

		switch(attribute.toLowerCase()) {
		case "id":
			return driver.findElement(By.id(attrval));
		case "name":
			return driver.findElement(By.name(attrval));
		case "xpath":
			return driver.findElement(By.xpath(attrval));
		case "css-select":
			return driver.findElement(By.cssSelector(attrval));
		default:
			throw new Exception("Unknown selector type " + attribute);
		}
}

Now, when I run the test, my code nicely reads all rows from Excel, determines what test action needs to be performed, executes it and reports the result back to Excel. After running the test, the Excel sheet looks like this:
Excel Data Source including test results
The only test step that fails does so because it Google doesn’t always return the same number of test results in the same amount of time, obviously. Nevertheless, it illustrates the added value of writing back the result of every step to the Excel sheet. Of course, you can very easily extend this to include a useful error message and even a link to a screenshot.

The Eclipse project containing the code I’ve used to create this example can be downloaded here.

Data driven testing in Selenium Webdriver using Excel

Most commercial automated software tools on the market support some sort of data driven testing, which allows you to automatically run a test case multiple times with different input and validation values. As Selenium Webdriver is more an automated testing framework than a ready-to-use tool, you will have to put in some effort to support data driven testing in your automated tests. In this article, I will show you one way of implementing data driven testing in Selenium. There are lots of different approaches possible, and I am aware that the solution presented here can possible be enhanced further enhanced and extended as well, but it will set you off in the right direction when you want to implement data driven testing in your own tests.

The test data source
Before we dive into the implementation in Selenium, let’s first look at the test data source we are going to use to store our input and validation values. As it is widely used in the testing world for test script and test data administration, I usually prefer to use Microsoft Excel as the format for storing my parameters. An additional advantage of using Excel is that you can easily outsource the test data administration to someone other than yourself, someone who might have better knowledge of the test cases that need to be run and the parameters required to execute them.

In the example presented here, I have used a very simple Excel sheet containing a single input parameter (SearchString – a Google search string) and a single validation parameter (PageTitle – the page title displayed by the browser after the search has been executed). Yes, we are performing a pretty trivial test case, but it is sufficient to demonstrate the principle behind the solution presented here.

data_driven_test_data_source

Reading data from the test data source
Next, we need a way to open this Excel sheet and read data from it within our Selenium test script. For this purpose, I use the Apache POI library, which allows you to read, create and edit Microsoft Office-documents using Java. The library, as well as its JavaDoc, can be found at http://poi.apache.org. The classes and methods we are going to use to read data from our Excel sheet are located in the org.apache.poi.hssf.usermodel package.

A simple main method, where we loop through all rows in the Excel sheet containing test data and call the actual test method using the current test data, could look like this:

public static void main (String args[]) {

	try {
		// Open the Excel file
		FileInputStream fis = new FileInputStream("Z:\\Documents\\Bas\\blog\\datasources\\testdata.xls");
		// Access the required test data sheet
		HSSFWorkbook wb = new HSSFWorkbook(fis);
		HSSFSheet sheet = wb.getSheet("testdata");
		// Loop through all rows in the sheet
		// Start at row 1 as row 0 is our header row
		for(int count = 1;count<=sheet.getLastRowNum();count++){
			HSSFRow row = sheet.getRow(count);
			System.out.println("Running test case " + row.getCell(0).toString());
			// Run the test for the current test data row
			runTest(row.getCell(1).toString(),row.getCell(2).toString());
		}
		fis.close();
	} catch (IOException e) {
		System.out.println("Test data file not found");
	}	
}

Simple, yet effective. Of course, many enhancements or improvements can be made to this example, such as:

  • The path to the test data file can be passed as an argument to the main method, or the user can be allowed to select a test data sheet, for example using JFileChooser
  • The fact that the first row contains column headers can be made optional, for example by passing a Boolean argument to our method
  • Instead of using column indexes as we have done here (in the getCell method, we could use column headers and have our code determine the correct column index for a given column at runtime

Executing our sample test
The actual test we are going to execute is located in the runTest method, which is called from the main method for every test data row in our sheet. It starts a browser driver, in this case a HtmlUnitDriver, executes a Google query using the query from the Excel sheet and checks the page title to see whether it matches the expected value. The code is pretty straightforward:

public static void runTest(String strSearchString, String strPageTitle) {
		
		// Start a browser driver and navigate to Google
		WebDriver driver = new HtmlUnitDriver();
        driver.get("http://www.google.com");

        // Enter the search string and send it
        WebElement element = driver.findElement(By.name("q"));
        element.sendKeys(strSearchString);
        element.submit();
        
        // Check the title of the page
        if (driver.getTitle().equals(strPageTitle)) {
        	System.out.println("Page title is " + strPageTitle + ", as expected");
        } else {
        	System.out.println("Expected page title was " + strPageTitle + ", but was " + driver.getTitle() + " instead");
        }
        
        //Close the browser
        driver.quit();
}

When we run this test, we can see in our stdout that the test script is executed three times, once for each row in the test data sheet:
data_driven_console_outputAdding or changing test cases is now as easy as editing the Excel sheet associated with our test. As long as nothing is changed in the location or the order of the columns, no maintenance is required in our Selenium script.