HOW TO PERFORM DATA DRIVEN USING EXCEL FILE (.XLS) IN SELENIUM WEBDRIVER ?

We can perform data driven using excel file with help of Apache POI library.
Here is the very basic program to perform data driven from excel file using Apache POI library.




Create a testdata.xls file in the C drive with following details:
Search Text
hello selenium
abhishek yadav qa
Use below code to perform data driven from excel file:

Java Source code:

package com.helloselenium.selenium.test;

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

import org.apache.poi.hssf.usermodel.*;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDrivenUsingExcelFile{

 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:\\testdata.xls")); 
  HSSFWorkbook workbook = new HSSFWorkbook(file);

  HSSFSheet 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(10000, TimeUnit.MILLISECONDS);

}

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

 } catch (FileNotFoundException fnfe) {
  fnfe.printStackTrace();
 } catch (IOException ioe) {
  ioe.printStackTrace();
 }
 }
}
Detailed explanation for the above program is as follows:


Following code is the required packages for JAVA IO to make integration with excel file and Timeunit.
import java.io.*;
import java.util.concurrent.TimeUnit;
Following code is the required packages for Apache POI library.
import org.apache.poi.hssf.usermodel.*;
Following 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();
Following code is to open the hello selenium blog in browser.
driver.get("http://www.google.com");


You can also use the following code is to open the hello selenium blog in browser.
driver.navigate().to("http://www.google.com");
Following code is to maximize the Firefox Driver instance.
driver.manage().window().maximize();
Following code is to store WebElement into a variable.
WebElement searchbox = driver.findElement(By.name("q"));
Following code is to locate the path of excel file.
FileInputStream file = new FileInputStream(new File("C:\\testdata.xls"));
Following code is to initialize the excel file as a workbook.
HSSFWorkbook workbook = new HSSFWorkbook(file);
Following code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.
HSSFSheet sheet = workbook.getSheetAt(0);
Following code is to run the loop till it found cell value of last row.
for (int i=1; i <= sheet.getLastRowNum(); i++){
Following code is to get the keyword value from the worksheet.
String keyword = sheet.getRow(i).getCell(0).getStringCellValue();
Following code is to type the keyword into search textbox.
searchbox.sendKeys(keyword);
Following code is to press RETURN within textbox.
searchbox.submit();
Following code is wait for 10 seconds.
driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);
You can also use the following code is to close the excel file.
workbook.close();
file.close();


Post a Comment

15 Comments

  1. Really helpfull .Good line to line explanation

    ReplyDelete
  2. Got error in "searchbox.sendKeys(keyword);". How to solve this issue?

    ReplyDelete
    Replies
    1. What is the error/exception? Please share the details.

      Delete
  3. Got error in sendKeys command

    ReplyDelete
    Replies
    1. What is the error/exception? Please share the details.

      Delete
  4. Hi,
    I am getting below error for the code. Please help to resolve

    log4j:WARN No appenders could be found for logger (org.apache.http.client.protocol.RequestAddCookies).
    log4j:WARN Please initialize the log4j system properly.
    log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.

    Thanks,
    Mayur

    ReplyDelete
    Replies
    1. These are not errors. These are only log4j warnings and this coming becuase you may using log4j in your project for logging.

      Delete
  5. public String readDataFromExcel(int rowcount,int columncount,String filepath,String Sheetname )
    {
    String data=null;
    try
    {
    FileInputStream input= new FileInputStream(filepath);
    XSSFWorkbook wb=new XSSFWorkbook(input);
    XSSFSheet sh=wb.getSheet(Sheetname);
    XSSFRow row=sh.getRow(rowcount);
    row.getCell(columncount).toString();
    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    return data;

    }
    public void writeDataFromExcel(int rowcount,int columncount,String filepath,String Sheetname,String value)
    {
    try
    {
    FileInputStream input=new FileInputStream(filepath);
    XSSFWorkbook wb=new XSSFWorkbook(input);
    XSSFSheet sh=wb.getSheet(Sheetname);
    XSSFRow row=sh.getRow(rowcount);
    FileOutputStream webdata=new FileOutputStream(filepath);
    row.createCell(columncount).setCellValue(value);
    wb.write(webdata);

    }
    catch(Exception e)
    {

    }
    }

    ReplyDelete
    Replies
    1. Thanks Stephen for posting a framework level method to read and write data from excel.

      Delete
  6. I am getting these Errors:
    log4j:WARN No appenders could be found for logger (org.apache.http.client.protocol.RequestAddCookies).
    log4j:WARN Please initialize the log4j system properly.
    log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
    java.io.FileNotFoundException: C:\mydata\Selenium Tests Data\workspace\ExcelJava\src\testData\Test-Data.xlsx (The system cannot find the file specified)
    at java.io.FileInputStream.open(Native Method)
    at java.io.FileInputStream.(Unknown Source)
    at appModules.DataDrivenUsingExcelFile.main(DataDrivenUsingExcelFile.java:26)

    KIndly help to resolve these.

    ReplyDelete
    Replies
    1. It seems file is not available at "C:\mydata\Selenium Tests Data\workspace\ExcelJava\src\testData\Test-Data.xlsx". If file is available there please try to do below steps to resolve your problem:
      1. Use filepath as in Java format - "C:\\mydata\\Selenium Tests Data\\workspace\\ExcelJava\\src\\testData\\Test-Data.xlsx"
      2. You are using .XLSX file for your test data, so please refer to this blog article to drive data from .XLSX file - "http://www.helloselenium.com/2014/09/how-to-perform-data-driven-using-excel.html"

      Delete
  7. Give example for both read from excel file and result write in excel file..

    ReplyDelete


What would you like to add in my list? I look forward to reading your comments below.