Parameterization/Data Driven Testing Of Selenium Webdriver Test Using Excel

Data parameterization in selenium or data driven test is must required thing of any software automation testing tool. If you can not perform data driven testing in any software automation tool then it is biggest drawback of that tool. Till now, Selenium webdriver software automation testing tool has not any built in structure or method to parameterize your test. If you know, we can perform data driven testing using user extension in selenium IDE software testing tool. You can view practical example of selenium IDE parameterization test on THIS ARTICLE POST. To data parameterization in selenium webdriver test, We need support of Java Excel API. First of all, we need to configure eclipse. Configuration steps are as bellow.

Another way of data driven testing In selenium webdriver software testing tool Is using TestNG @DataProvider Annotation. VIEW STEPS OF DATA DRIVEN TESTING USING @DataProvider.

Step 1 : Download latest jxl.jar file for selenium parameterized test

Current latest version of jexcelapi is 2_6_12. It may change in future so download link can also change.
  • Click on THIS LINK to go to jexcelapi_2_6_12 page.
  • Click on jexcelapi_2_6_12.zip link as shown in bellow image. It will start downloading zip folder.
  • On completion of zip folder download, extract that zip folder. You will find jxl.jar file inside it.

Step 2 : Add jxl.jar in your project folder as a external jar

  • Right click on your project folder in eclipse and go to Build Path -> Configure Build path -> Libraries tab -> Click on Add External JARs button and select jxl.jar.
For detailed description with image on how to add any external jar in your project folder, You can follow step 2 of THIS POST .

Step 3 : Download data file for data parameterization in selenium

I have prepared example data file for parameterization. CLICK HERE to download data file and save in your D: drive.

Step 4 : Import header files for selenium parameterization

All Bellow given header files are required for this example. Please include them in your header file list if not included.
import java.io.FileInputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.openqa.selenium.JavascriptExecutor;

Step 5 : Run bellow given example in your eclipse for data driven in selenium

Bellow given example will read data from D:\\MyDataSheet.xls file and then type that data in First Name and Last Name text box one by one.

Last Name text box will be disabled initially so I have used javascript executor to enable it. You can view practical example with description for the same on THIS LINK.

Copy bellow given @Test method part of data driven testing example and replace it with the @Test method part of example given on THIS PAGE(Note : @Test method is marked with pink color in that linked page).

@Test
  public void test () throws BiffException, IOException, InterruptedException 
  {
  //Open MyDataSheet.xls file from given location. 
  FileInputStream fileinput = new FileInputStream("D:\\MyDataSheet.xls");
  //Access first data sheet. getSheet(0) describes first sheet.
  Workbook wbk = Workbook.getWorkbook(fileinput);
  Sheet sheet = wbk.getSheet(0);
  
  //Read data from the first data sheet of xls file and store it in array.
  String TestData[][] = new String[sheet.getRows()][sheet.getColumns()];
  
  //To enable Last Name text box.
  JavascriptExecutor javascript = (JavascriptExecutor) driver;
  String toenable = "document.getElementsByName('lname')[0].removeAttribute('disabled');";
  javascript.executeScript(toenable);
    
  //Type data in first name and last name text box from array.
  for(int i=0;i<sheet.getRows();i++)
  {
   for (int j=0;j<sheet.getColumns();j++)
   {
    TestData[i][j] = sheet.getCell(j,i).getContents();
    if(j%2==0)
    {
     driver.findElement(By.xpath("//input[@name='fname']")).sendKeys(TestData[i][j]);
    }
    else
    {
     driver.findElement(By.xpath("//input[@name='lname']")).sendKeys(TestData[i][j]);
    }
   }
   Thread.sleep(1000);
   driver.findElement(By.xpath("//input[@name='fname']")).clear();
   driver.findElement(By.xpath("//input[@name='lname']")).clear();
  }
  Thread.sleep(1000);
  
  }

10 comments:

  1. I want to read data from a html table and store it in a file how can i do that

    ReplyDelete
  2. where did you get this xpath from By.xpath("//input[@name='fname']"?

    ReplyDelete
  3. Error coming in this line JavascriptExecutor javascript = (JavascriptExecutor) driver;
    asking to create local variable driver/parameter driver/field driver/contant driver/change to javascript..

    Could you please help ?

    ReplyDelete
  4. can u please help me how to download data file its not getting downloaded

    ReplyDelete
    Replies
    1. refer this post http://software-testing-tutorials-automation.blogspot.in/2014/05/how-to-download-different-files-using.html

      Delete
  5. What will happen to the file after using and reading it?
    Shouldn't it be closed?

    ReplyDelete
  6. There are instance where the number of columns acquired is not correct.
    For example when all the columns have a format but no data.
    This will result to an infinite loop(almost)

    ReplyDelete