Data driven testing - Get value from excel in selenium

Data Driven Testing in Selenium

We require to perform Data driven testing many times while running selenium scripts.Below is the best method to fetch data from excel sheet and write in another excel sheet.

We can perform data driven using excel file with help of Apache POI library.

Java Code for Data driven Testing :

 

package com.seleniumblog.selenium.test;

import java.io.*;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDriven{

 public static void main(String[] args) {

        WebDriver driver = new FirefoxDriver();

        driver.get("http://www.google.com");

        driver.manage().window().maximize(); 
      
        WebElement searchbox = driver.findElement(By.name("q"));

 try {
   
  FileInputStream file = new FileInputStream(new File("C:\\data.xlsx"));
  XSSFWorkbook workbook = new XSSFWorkbook(file);

  XSSFSheet sheet = workbook.getSheetAt(0);

for (int i=1; i <= sheet.getLastRowNum(); i++){

        String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

        searchbox.sendKeys(keyword);

        searchbox.submit();      
 
        driver.manage().timeouts().implicitlyWait(1000, TimeUnit.MILLISECONDS);

}

  workbook.close();
  file.close();

 } catch (FileNotFoundException fe) {
  fe.printStackTrace();
 }
 }
}
 

So now lets see how the above code works:

Below code is the required packages for JAVA IO to make integration with excel file and Timeunit.

   
import java.io.*;
import java.util.concurrent.TimeUnit;

Below code is the required packages for Apache POI library.

   
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Below code is the required packages for Selenium.

   
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
Following code is to initialize the Firefox driver.

WebDriver driver = new FirefoxDriver();

Below code is to open the hello selenium blog in browser. 

driver.get("http://www.google.com");

You can also use the Below code is to open the hello selenium blog in browser.

driver.navigate().to("http://www.google.com");

Below code is to maximize the Firefox Driver instance.
   
driver.manage().window().maximize();

Below code is to store WebElement into a variable.
   
WebElement searchbox = driver.findElement(By.name("md"));

Below code is to locate the path of excel file.

FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx"));

Below code is to initialize the excel file as a workbook.
   
XSSFWorkbook workbook = new XSSFWorkbook(file);

Below code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.
   
XSSFSheet sheet = workbook.getSheetAt(0);

Below code is to run the loop till it found cell value of last row.
   
for (int i=1; i <= sheet.getLastRowNum(); i++)

Below code is to get the keyword value from the worksheet.

String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

Below code is to type the keyword into search textbox.

input.sendKeys(keyword);

Below code is to press RETURN within textbox.
   
input.submit();

Below code is wait for 10 seconds.
   
driver.manage().timeouts().implicitlyWait(100, TimeUnit.MILLISECONDS);

You can also use the Below code is to close the excel file.
   
workbook.close();
file.close();

For more data driven testing , follow this web page.

 

 

• Tweet
• Share
• Share
• Share
• Share