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.

50 thoughts on “Defining test steps and object properties in an Excel Data Source for Selenium

  1. Excellent article providing a fresh approach to data driven automated testing.
    I am working with Visual Studio 2010 and C# so had to adapt the code – happy to share my sample code with you should you wish to provide both Java and .Net examples.
    One question I have is around this article vs your previous article on Excel driven testing.
    Would this approach replace or complement the previous example ?
    e.g. if you have a web app whereby you can create users. As part of my testing I require to create 10 users with First Name, LastName, Email etc.
    Do I simply put each of this values in a step ? Or do I have seperate xls tab with test cases (TC1.01, TC1.02…) providing all the required info; for this scenario there would be 10 rows of data. Happy to further expand on this example should you require more clarification 🙂

    • Hi David,

      first of all thank you for the nice words!

      I’d say pick whatever approach you like, I think the selection of a specific approach depends on the situation. For your test case, I don’t think I’d specify all object properties in Excel, only the test data to be provided. Identification and handling of the objects can be done in the code, for example using the Page Object Pattern (I’ve written about that as well). Just write a test that loops through the Excel file with your user data and call the correct Page Objects.

      Hope that makes sense.

    • Hi Tester,

      in my case it’s done using the line

      row.createCell(5).setCellValue(result);

      where result is the value to be written to the newly created Excel cell. There are lots of code snippets to be found on how to write to Excel from Java using the Apache POI library, I think a quick Google search should help you out. If not, ask me again and please send your relevant code samples to bas@ontestautomation.com.

      Good luck!

    • Are you referring to exporting the console output to xls? In .Net of Java ?
      This is what I used (c#):
      // save console output to output.xls
      string filePath = @”c:\temp\” + DateTime.Now.ToString(“yyyyMMMdd_HHmm”).ToUpper() + “”;
      Directory.CreateDirectory(filePath);
      FileStream fsConsole = new FileStream(filePath + @”\output.xls”, FileMode.Create);
      var streamwriter = new StreamWriter(fsConsole);
      streamwriter.AutoFlush = true;
      Console.SetOut(streamwriter);
      Console.SetError(streamwriter);

  2. David ,

    I am trying to find all A tag links from page and then want to put those all text[link name] to excel. I am getting all link names proper , Excel is also generating , But coming blank.

    Thanks for reply. My code is given below :

    public static void main(String[] args) throws IOException {
    // TODO Auto-generated method stub

    WebDriver driver = new FirefoxDriver();

    driver.get(“http://www.digit.in”);
    java.util.List links = driver.findElements(By.tagName(“a”));
    System.out.println(links.size());

    Workbook wb = new HSSFWorkbook();
    FileOutputStream fs = new FileOutputStream(“D:\\data.xls”);

    Sheet sh = wb.createSheet(“sheet1”);
    Row row = sh.createRow(10);
    //Cell cell = row.createCell(0);

    for (int i=0;i<=links.size()-1;i=i+1)

    {

    System.out.println(links.get(i).getText());
    String l = links.get(i).getText();
    row.createCell(50).setCellValue(l);

    }

    wb.write(fs);
    fs.close();

    }

    • Hi Tester,

      I fixed your code for you:

      public static void main(String[] args) throws IOException {

      WebDriver driver = new FirefoxDriver();

      driver.get(“http://www.digit.in”);
      List links = driver.findElements(By.tagName(“a”));
      System.out.println(links.size());

      HSSFWorkbook wb = new HSSFWorkbook();
      FileOutputStream fs = new FileOutputStream(“C:\\Temp\\test.xls”);

      HSSFSheet sh = wb.createSheet(“testsheet”);

      int rowCount = 0;

      for (int i=0;i<=links.size()-1;i++) { String linkText = links.get(i).getText(); if(!linkText.equals("")) { HSSFRow row = sh.createRow(rowCount); System.out.println(linkText); row.createCell(0).setCellValue(linkText); rowCount++; } } wb.write(fs); fs.close(); driver.quit(); } The main problem with your code was that you didn't create a new row for every link, so it just keeps overwriting the same cell over and over again.

  3. Hi ,

    I am writing console data to excel and then reading data from excel and then compare it. It works fine. But based on comparison I want to write “TRUE” & “FALSE” in excel , this thing is not working.

    My code is given below :

    WebDriver driver = new FirefoxDriver();

    driver.get(“http://en.wikipedia.org/wiki/Software_testing”);
    String title = driver.getTitle();
    System.out.println(title);

    FileOutputStream webdata = new FileOutputStream (“D:\\Book.xls”);
    Workbook wb = new HSSFWorkbook();
    Sheet sh = wb.createSheet(“sheet1”);
    Row row = sh.createRow(0);
    row.createCell(1).setCellValue(title);
    wb.write(webdata);
    webdata.close();

    int count=0;
    FileInputStream input = new FileInputStream(“D:\\Book.xls”);
    Row Row = sh.getRow(count);
    String data = row.getCell(1).toString();
    System.out.println(data);
    input.close();

    if(title==data)

    {
    Row row1 = sh.createRow(10);
    row1.createCell(10).setCellValue(“TRUE”);
    wb.write(webdata);
    //webdata.close();
    }
    else
    {
    System.out.println(“False”);

    }
    driver.close();

    }

    }

    • I’ve tried to get my head around what you are trying to do here, but I can’t work it out.

      It looks to me like you want to compare something that you’ve written to Excel to a value you’re reading from the exact same field, and then writing to another cell whether the two values match..

      Either I don’t get it (which is very likely) or you are trying to compare an apple with itself, which is kind of superfluous..

      Could you please clarify your problem?

      • Bas ,

        Its ok. I have redefine program. Please check below code :

        driver.get(“https://www.google.co.in/?gfe_rd=cr&ei=VQiAVOeCFavM8gf59IHACg&gws_rd=ssl#q=software+testing”);
        java.util.List links = driver.findElements(By.tagName(“h3”));
        int sizecount = links.size();
        System.out.println(sizecount);

        FileInputStream input = new FileInputStream(“D:\\sel.xls”);
        int count=0;

        HSSFWorkbook wb = new HSSFWorkbook(input);
        HSSFSheet sh = wb.getSheet(“sheet1”);
        HSSFRow row = sh.getRow(count);
        //String data = row.getCell(0).toString();
        //System.out.println(data);

        int inc = 1;
        int count1= sh.getPhysicalNumberOfRows();

        for(int i=1;i<=links.size()-1;i++)
        {

        String linkdata = links.get(i).getText();
        if(!linkdata.equals("")){
        HSSFRow row1 = sh.createRow(count1);
        row1.createCell(inc).setCellValue(linkdata);
        count1++;
        }
        }

        FileOutputStream webdata = new FileOutputStream ("D:\\sel.xls");
        wb.write(webdata);
        wb.close();

        Note : What I am doing in above program , I am getting titles from Google and put in to Excel. Everything works. But it prints titles after last filed ROW in excel. I want to print data from 2nd col & 1st Row [Because in my excel 1st col has already data filled]. When I try to write data in 2nd col and 1st row of excel , it erases data of 1st col. then print my new data. So I want solution that it prints new data but should not erase data which is already in my excel.

        • Hi tester,

          the issue with your code is that when you call createRow(), it creates a new row at the given index, thereby removing anything that was already in that row. Better is to do getRow() to get the current row, then check whether the row already exists, and if not, create a new row. The code below should work (it did on my machine):

          WebDriver driver = new FirefoxDriver();

          driver.get(“https://www.google.co.in/?gfe_rd=cr&ei=VQiAVOeCFavM8gf59IHACg&gws_rd=ssl#q=software+testing”);
          List links = driver.findElements(By.tagName(“h3”));
          int sizecount = links.size();
          System.out.println(sizecount);

          FileInputStream input = new FileInputStream(“C:\\Bas\\test.xls”);

          HSSFWorkbook wb = new HSSFWorkbook(input);
          HSSFSheet sh = wb.getSheet(“sheet1”);

          int inc = 1;

          // start from the top, this should work now
          int count1 = 0;

          System.out.println(“Row count: ” + Integer.toString(sh.getLastRowNum()));

          for(int i=1;i<=links.size()-1;i++) { String linkdata = links.get(i).getText(); if(!linkdata.equals("")){ // get current row HSSFRow row1 = sh.getRow(count1); // create row if it doesn't exist yet if (row1.equals(null)) { row1 = sh.createRow(count1); } row1.createCell(inc).setCellValue(linkdata); count1++; } } FileOutputStream webdata = new FileOutputStream ("C:\\Bas\\test.xls"); wb.write(webdata); webdata.close();

          • Bas,

            You have always awesome solution. Thanks for quick help. It works. I am having just 2 question :

            1 – Why we are fetching row count? like Row count:14385

            2 – as per your given updated code mainly which condition says my web driver that not to erase excel data of 1st col. and start to write from 2nd col.

          • 1 – I don’t know, it was in the original code so I left it there, you don’t need it so you might as well remove it..

            2 – This is done by using getRow() initially instead of createRow() within the for-loop. If the object returned by getRow() equals NULL it means that the row does not yet exist in the sheet, and only then it is created. Using getRow() preserves all data that is already in the row, so your values in column 1 are not removed now.

            Glad to hear it works on your side too!

  4. Bas , I again need your help in below program :

    I am storing excel values in 1st array and console output values in 2nd array. Then I am comparing excel value with console output one by one . **But it always print “FALSE” even if value matches true.**

    My latest code is given below :

    WebDriver driver = new FirefoxDriver();

    driver.get(“https://www.google.co.in/?gfe_rd=cr&ei=1YaGVMutHcXN8gf39ID4Aw&gws_rd=ssl#q=what+is+software+testing”);
    java.util.List links = driver.findElements(By.tagName(“h3”));
    int sizecount = links.size();
    System.out.println(sizecount);

    //READING DATA FROM EXCEL FROM 1ST COLUMN
    FileInputStream input = new FileInputStream(“D:\\sel.xls”);
    int count=0;

    HSSFWorkbook wb = new HSSFWorkbook(input);
    HSSFSheet sh = wb.getSheet(“sheet1”);

    String exceldata[] = new String[20];
    for (int i=0;i<=sh.getLastRowNum();i++)

    {
    HSSFRow row = sh.getRow(i);
    exceldata[i]= row.getCell(count).toString();
    System.out.println(exceldata[i]);
    }

    String linkdata[] = new String[20];
    for(int j=1;j<=links.size()-1;j++)

    {

    linkdata[j] = links.get(j).getText();
    System.out.println(linkdata[j]);
    }

    for(int k=0;k<links.size()-1;k++)

    {

    if(exceldata==linkdata)

    {
    System.out.println("TRUE");

    }
    else
    {

    System.out.println("FALSE");

    }
    }
    driver.close();

    }

    }

    • It looks like you’re comparing the arrays as a whole rather than the individual field.. What happens if you try

      if(exceldata[k].equals(linkdata[k])

      instead of

      if(exceldata==linkdata)

      ?

  5. Hi Bas ,
    Nice to meet you with this email . first of all thanks for all of your sincere effort to make this blog happen and for all of your
    follow ups. I have tried to execute the script as it is which I have downloaded from your blog for the blog post ” Defining test
    steps and object properties in an Excel Data Source for Selenium “ . the issue I am facing is , once after the execution starts
    the eclipse shows the error message “java.io.IOException: Unable to read entire header; 0 bytes
    read; expected 512 bytes”. Then if I look in to the excel sheet , the data’s were deleted from the excel and the sheet Is
    empty . but I am able to use the same excel apache poi library for other normal read and write operations successfully. Please
    help me to fix this.
    Note : I have commented the code part for “ HTML unit driver” in switch case block.
    I am using the following configuration on my test environment :
    Eclipse : Version: Juno Service Release 1
    Selenium webdriver java client :2.44.0
    Apache poi (to handle MS Excel): poi-bin-3.10.1-20140818

    • Hi Suresh,

      could you send your edited version of the code to bas@ontestautomation.com? And also the Excel file you’re using. I’ll see what happens. Might take a day or two though, but I’ll try to take a look as soon as possible.

      Thanks for the kind words!

      Bas

  6. Hi Bas

    This is intelligent stuff on selenium with excel sheet. But unfortunately am not able to read and write the data in the same excel sheet.

    Below is the error

    java.io.IOException: Unable to read entire header; 0 bytes read; expected 512 bytes

    after getting this error the excel file is corrupted and not opening.. it has 0 bytes(no data)

    Pls help me on this..

  7. Also pls let me know how to expand your code for more text fields. I mean I have search scenario which has around 40 text fields. I need to provide random data on any of the field and fetch the result.Thanks

    • Hi Syed,

      if your test scenario is that large I think you’re better off with a slightly different approach, especially when they’re all on the same page I think using a Page Object would work better for that.

      • Bas,

        I can use page factory for this. But i need to deal this with DDF, reading from excel and writing the result on the same sheet. Can we expand the earlier google search approach for this?

  8. Actually Bas the thing i am confused with is whether it is data driven framework or keyword driven framework or hybrid framework,these methods should be used in automated testing rather than automating a website since we have to give so many inputs to get an output for that particular website right?

    • This is both, so you could call it hybrid. I hate to use the word framework for every piece of code so I won’t use that..

      What’s the difference between automated testing and automating a website in your question?

    • That’s a tricky one. It depends on the way file uploads are handled in your application. If it uses standard File dialogs, you can’t use Selenium as it doesn’t work on those dialogs. If custom file dialogs are used, you can do it just like you would interact with every other web element, using click and sendkeys.

  9. hi sir i am new to the selenium . i am trying to execute the same code with my excel sheet file location path but i ma getting error like org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file

    how to reduce this

  10. Hi,
    Thank you for your tutorial, it helped me create a keyword driven test
    I have a little issue, in the web application that i test i have a button that i have to click. The problem is that the same locator is used for a lot of buttons. So i want to specify my xpath by “contains” and tell to the script to take the value from the Excel Sheet and place it in contains. this value i changed it from test to another
    I was succesful in selenium IDE but not in selenium webdriver
    i used:
    xpath=//a[@href=’url/${number}’]/img[@class=’icon_base icon_edit’]

    Thanks for your help

    • Hey,

      do you use Java? Either way, you could do something like this:

      string number = “”; // assign the right value to the variable here
      string xpath = “//a[@href=’url/” + number + “‘]/img[@class=’icon_base icon_edit’]”;
      WebElement element = driver.findElement(By.Xpath(xpath));

      • Thanks for your answer,
        I use java and junit
        the code for Xpath is something like this:

        private By getObject(Properties p,String objectName,String objectType) throws Exception{

        //Find by xpath if(objectType.equalsIgnoreCase(“XPATH”)){
        return By.xpath(p.getProperty(objectName));
        }
        In Excel i just have ti indicate the object Type is XPATH and it’s triggerd
        All my locators are in a seperate file Object.properties

  11. Sorry
    it didn’t work
    the value stored by the script i don’t know how to put it in the variable

    Sorry i’m a biginner in the java programming

    Can i email you my code if ti’s possible

    Thanks

  12. Hi bob, I need a suggestion, I want to include my test case in the same excel as a first column and enable as the second column, for test case enablement then only the test case will run, So how i can implement this in excel

    • If you really want to do this in Excel (I wouldn’t recommend it) then it’s just a matter of an if statement around your test executor so that it only runs when the value in the Excel column is ‘yes’, ‘X’, ‘true’ or whatever.

Leave a Reply

Your email address will not be published. Required fields are marked *