How to read data from excel file using Selenium WebDriver

Before we dive into the implementation in selenium, first we have look at the data source we are going to use. As usual I would prefer excel is best option to read and write data using selenium WebDriver. Many of the folk and depend on the requirement use sql server or any database but that little different way to implementation of reading and writting data from both.

There are lots of different approaches possible, and I am aware that the solution presented here can possible be enhanced further and extended as well.

Above is the data source from where data is being used for data driven using Selenium WebDriver.

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.usermodelpackage.

Code:

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * Created by Gaurav on 5/26/2014.
 */
public class demoReadFromExcel {


        public static void main(String[] args) {
            try {
                FileInputStream file = new FileInputStream(new File("D:\\data1.xls"));
                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet sheet1 = workbook.getSheet("sheet1");
                String heading = sheet1.getRow(0).getCell(0).getStringCellValue();
                String searchText1 = sheet1.getRow(1).getCell(0).getStringCellValue();
                String searchText2 = sheet1.getRow(2).getCell(0).getStringCellValue();
                String searchText3 = sheet1.getRow(3).getCell(0).getStringCellValue();

                String heading1 = sheet1.getRow(0).getCell(1).getStringCellValue();
                String searchText4 = sheet1.getRow(1).getCell(1).getStringCellValue();
                String searchText5 = sheet1.getRow(2).getCell(1).getStringCellValue();
                String searchText6 = sheet1.getRow(3).getCell(1).getStringCellValue();


                String heading2 = sheet1.getRow(0).getCell(2).getStringCellValue();
                String searchText7 = sheet1.getRow(1).getCell(2).getStringCellValue();
                String searchText8 = sheet1.getRow(2).getCell(2).getStringCellValue();
                String searchText9 = sheet1.getRow(3).getCell(2).getStringCellValue();


                System.out.println("Heading is:" +heading+ " " +heading1+ " " +heading2);
                System.out.println("Search Text 1 is:" +searchText1+ " " +searchText4+ " " +searchText7);
                System.out.println("Search Text 2 is:" +searchText2+ " " +searchText5+ " " +searchText8);
                System.out.println("Search Text 3 is:" +searchText3+ " " +searchText6+ " " +searchText9);

                file.close();
            }
            catch (FileNotFoundException fnfe) {
                fnfe.printStackTrace();
            } catch (IOException ioe) {
                ioe.printStackTrace();
            }
        }
    }

Implicit Wait Selenium Webdriver

How to use implicit wait in selenium webdriver and why 

 

As you knows sometimes, some elements takes some time to appear on page when browser is loading the page. In this case, sometime your webdriver test will fail if you have not applied Implicit wait in your test case. If implicit wait is applied in your test case then webdriver will wait for specified amount of time if targeted element not appears on page. As you know, we can Set default timeout which is same as implicit wait in webdriver.

If you write implicit wait statement in you webdriver script then it will be applied automatically to all elements of your test case. I am suggesting you to use Implicit wait in your all test script of software web application with 10 to 15 seconds. In webdriver, Implicit wait statement is as bellow.

How To Write Implicit Wait In WebDriver

driver.manage().timeouts().implicitlyWait(15, TimeUnit.SECONDS); 

Above statement will tell webdriver to wait for 15 seconds if targeted element not found/not appears on page. Le we look at simple exemple to understand implicit wait better.

 

public class demoImplicitWait {

  FirefoxDriver driver;

@Before
public void setUp()
{

  driver = new FirefoxDriver();
  driver.get("http://jsbin.com/usidix/1");
} 

 @Test
       public void shoudImplicitWait(){
       driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
       driver.findElement(By.cssSelector("input[type=\"button\"]")).click();
       String text = driver.switchTo().alert().getText();
       driver.switchTo().alert().accept();
       System.out.println(text);
    }
}

   

How to use explicit wait in selenium webdriver and why

 

Explicit waits are intelligent waits that are confined to a particular web element. Using explicit waits you are basically telling WebDriver at the max it is to wait for X units of time before it gives up.

 

In explicit wait you can write custom code for a particular element to wait for particular time of period before executing next steps in your test. This provide you better option than implicit wait. Webdriver provide “WebDriverWait”, “ExpectedCondition” classes to implement this.

 

This above classes provide set of predefined conditions to wait for the particular element to load. Following are few conditions mostly used in the Expected condition class.

• alertIsPresent() : Alert is present

• elementSelectionStateToBe: an element state is selection.

• elementToBeClickable: an element is present and clickable.

• elementToBeSelected: element is selected

• frameToBeAvailableAndSwitchToIt: frame is available and frame selected.

• invisibilityOfElementLocated: an element is invisible

• presenceOfAllElementsLocatedBy: present element located by.

• textToBePresentInElement: text present on particular an element

• textToBePresentInElementValue: and element value present for a particular element.

• visibilityOf: an element visible.

• titleContains: title contains

public class demoExplicitWait {

  public WebDriver driver;

  String baseUrl;

 

 

    @Test

  public void testUntitled() throws Exception {

    driver = new FirefoxDriver();
    driver.get("http://www.wikipedia.org/");

   

    //explicit wait for search field

    WebDriverWait wait = new WebDriverWait(driver, 10);

    wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("searchInput")));

    driver.findElement(By.id("searchInput")).clear();     

    driver.findElement(By.id("searchInput")).sendKeys("India");

    driver.findElement(By.className("formBtn")).click();

 

  }

  @AfterMethod

  public void tearDown() throws Exception {

    driver.quit();   

  } 

} 
_____________________________________________

Selenium WebDriver Simple Examples

Selenium WebDriver With Simple Examples

Below are the examples of switching frames, getting text of an element of alert box and print it out on console, input data if field is displayed and enabled which helps for the selenium beginners.

Examples written in the order explained above.

  public class demoSwitchFrames {

    FirefoxDriver driver;

    @Before
    public void setUp()

    {
        driver = new FirefoxDriver();
                driver.get("http://selenium.googlecode.com/svn/trunk/docs/api/java/index.html");

    }

//    @Test
//    public void shouldSwitchToNewFrame()
//
//    {
//        driver.switchTo().frame("classFrame");
//        driver.findElement(By.linkText("Deprecated")).click();
//        driver.close();
//
//    }

    @Test
    public void shouldSwitchToNewFrame()

    {
        driver.switchTo().frame("packageListFrame");
        driver.findElement(By.linkText("org.openqa.selenium")).click();
        driver.findElement(By.linkText("org.openqa.selenium.remote.html5")).click();

        driver.close();

    }

}

If the application under test contains frames, then we need to switch the WebDriver control to that frame before interacting with the element inside the frame.

There are three ways to switch the frames...

First method is using frame name:
driver.switchTo().frame("frameName");

Second is using frame index (Zero based):
driver.switchTo().frame(1);

And to come back to default frame:
driver.switchTo().defaultContent();

So in this way we can play around the different frames and can apply click event to select particular option inside the frames. 

The best way to use is by iframeID
//get the number of frames available
System.out.println("The number of iFrames are " webdriver.findElements(By.tagName("iframe")).size());
//if only 1 frame available, then get(0)
webdriver.switchTo().frame(webdriver.findElements(By.tagName("iframe")).get(0));

//if more than 1 frame, get the size and move the frame of your choice 

 
____________________________________________________________________________  

 public class demoGettingTextOnAlertBox {

    FirefoxDriver driver;

    @Before
    public void setUp()

    {
        driver = new FirefoxDriver();
        driver.get("http://jsbin.com/usidix/1");

    }

    @Test
    public void TextonAlert() throws InterruptedException {


//        css=input[type="button"]

        driver.findElement(By.cssSelector("input[type=\"button\"]")).click();
        String text = driver.switchTo().alert().getText();
        try {
            Thread.sleep(3000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        driver.switchTo().alert().accept();

        System.out.println(text);
    }

}
________________________________________________________________________________

  public class demoInputDataDisplayEnabled {

    FirefoxDriver driver;

    @Before
    public void setUp()

    {
        driver = new FirefoxDriver();
        driver.get("http://newtours.demoaut.com/");

    }
    @Test
    public void displayEnabled ()
    {
    WebElement username = driver.findElement(By.name("userName"));
        if(username.isDisplayed() && username.isEnabled());

        {
            username.sendKeys("Softedge100");

        }
    }
} 

________________________________________________________________________________