Selenium Automation Framework P8 : Read and Write Data From Excel File

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

package com.selenium.test; import org.openqa.selenium.WebDriver; import org.openqa.selenium.firefox.FirefoxDriver; import org.testng.annotations.Test; import org.testng.annotations.BeforeTest; import org.testng.annotations.AfterTest; import com.selenium.pageObject.LoginPage; import com.selenium.userAction.SignIn; import com.selenium.util.ExcelUtil; public class loginFacebookByExcelData { private WebDriver driver; private static String username; private static String password; @BeforeTest public void beforeTest() throws Exception { driver = new FirefoxDriver(); //set file excel is my excel file in folder Driver , you can set the other path to your excel file ExcelUtil.setExcelFile(ExcelUtil.PathExcelInProject(), "test"); //get the data from file excel username = ExcelUtil.getCellData(2, 1); password = ExcelUtil.getCellData(2, 2); } @Test public void loginByExcel() { //Call loading page of login page facebook , take a look in post [Page Object Model] of my blog LoginPage.loadPage(driver); //Call action SignIn , take a look in post [Modular Driven Framework] of my blogs SignIn.Execute(driver, username, password); //write data to excel String result = "success" ExcelUtil.setCellData(result,3,3); } @AfterTest public void afterTest() { driver.quit(); } }

Difference between Webdriver get() and Webdriver navigate()

driver.get("URL")
The first thing you’ll want to do with WebDriver is navigate to a page. The normal way to do this is by calling get:

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

Webdriver will wait until the page has fully loaded before returning the control to test or script. If there many ajax calls in the current page which webdriver is loading then webdriver may not know when it has loaded completely. If you need to make sure such pages are fully loaded then you can use waits.

driver.navigate().to("URL")

Earlier, we covered navigating to a page using the get command (driver.get("http://www.example.com")) As you have seen, webdriver has a number of smaller, task-focused interfaces, and navigation is a useful task. Because loading a page is such a fundamental requirement, the method to do this lives on the main Webdriver interface, but it’s simply a synonym to:

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

navigate().to() and get() do exactly the same thing. One's just a lot easier to type than the other!

The navigate interface also has the ability to move backwards and forwards in your browser’s history:

driver.navigate().refresh();
driver.navigate().forward();
driver.navigate().back();

We can also use Actions class of WebDriver to perform page refresh

Actions actions = new Actions(driver);
actions.keyDown(Keys.CONTROL).sendKeys(Keys.F5).perform();

How to Read Excel file using Java

Normally, to read a data in excel, first we should have access to workbook, sheet which we want to read as workbook contains multiple sheets and if you want to read a particular cell we need location of a Cell.

In this article, we will discuss how to access workbook, sheet and a Cell using Jxl library. You can also consider Apache Poi Library to perform read and write operations with excel sheets.

As we know JXL doesn't support Excel 2007 ".xlsx" file format. It only supports the old BIFF (binary) ".xls" format. Where as Apache POI supports both Excel 2003 - xls and Excel 2007 - xlsx file formats.

To start with gaining access to Workbook, we should always remember the below command:

String FilePath = "d://filepath.xls";
FileInputStream fs = new FileInputStream(FilePath);
Workbook wb = Workbook.getWorkbook(fs);

Or You can also directly send the file as below

Workbook wb = Workbook.getWorkbook(new File("samplefile.xls"));

Now to get the access to the particular sheet, we should use the below command:

Sheet sh = wb.getSheet(0); // this is to get the access to Sheet1. 

If you want to get the access to sheet2, you should specify as below:

Sheet sh = wb.getSheet(1);

You can also get the sheet access by sheet name, you should specify as below:

Sheet sh = wb.getSheet("sheet1");

Now we will get the content in particular location.

String CellGetContent = sh.getCell(0,0).getContents();
System.out.println(CellGetContent);

We can also write it as :

System.out.println(sh.getCell(0,0).getContents());

There is an other style to get the cell contents as below:

Cell Row0Col0 = sheet.getCell(0,0);
Cell Row1Col1 = sheet.getCell(1,1);
String FirstRowFirstColumn = Row0Col0.getContents();
String SecondRowSecondColumn = Row1Col1.getcontents();

The below is the input sheet for the example program:

Please find the below code in which we will read a data from excel sheet and print using for loop

package com.pack;

import java.io.FileInputStream;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcelFile {
 
 public void readExcel() throws BiffException, IOException {
  String FilePath = "D:\\sampledoc.xls";
  FileInputStream fs = new FileInputStream(FilePath);
  Workbook wb = Workbook.getWorkbook(fs);

  // TO get the access to the sheet
  Sheet sh = wb.getSheet("Sheet1");

  // To get the number of rows present in sheet
  int totalNoOfRows = sh.getRows();

  // To get the number of columns present in sheet
  int totalNoOfCols = sh.getColumns();

  for (int row = 0; row < totalNoOfRows; row++) {

   for (int col = 0; col < totalNoOfCols; col++) {
    System.out.print(sh.getCell(col, row).getContents() + "\t");
   }
   System.out.println();
  }
 }

 public static void main(String args[]) throws BiffException, IOException {
  ReadExcelFile DT = new ReadExcelFile();
  DT.readExcel();
 }
}

The output of the below program is:

Username password
testuser1 testpassword1
testuser2 testpassword2
testuser3 testpassword3
testuser4 testpassword4

Hope the above code works. Please let me know if you face any problems. Thank you.

Find out broken links on website using selenium webdriver and HTTP Client

Earlier we have seen working with finding broken images, now here we will see finding invalid URLs. Here a valid URL will always have a status with 200. We have different HTTP status codes which are used for different purposes. You can check Wiki page for more information on HTTP Status Codes

Here 2xx class of status codes indicates that the action request by client was received and processed successfully without any issues.

And 4xx class of status code is mainly intended for cases in which the client seems to have erred.

And 5xx class of status codes are intended for cases in which the server seems to have erred.

The following are the list of different HTTP status codes.

By just seeing the Links in the UI, we may not be able to confirm if that link is working or not until we click and verify it.

To achieve this, we can use HTTPClient library to check status codes of the URLs on a page. You need to download and add it to the build path.

If request was NOT processed correctly, then the HTTP status codes may return any of the above listed codes but not a 200 status code. We can easily say whether the link is broken or not with status codes.

Now let us jump into the example, First we will try to find all anchor tags on the page by using Webdriver. By using the below syntax:

List<WebElement> anchorTagsList = driver.findElements(By.tagName("a"));

We need to iterate through each link and verify request response Status codes and it should be 200 if not, we will increment invalid links count

Let us look into the example :

package com.linked;

import java.util.List;

import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.HttpClientBuilder;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class FindBrokenLinksExample {
 
 private WebDriver driver;
 private int invalidLinksCount;

 @BeforeClass
 public void setUp() {

  driver = new FirefoxDriver();
  driver.get("http://google.com");
 }

 @Test
 public void validateInvalidLinks() {

  try {
   invalidLinksCount = 0;
   List<WebElement> anchorTagsList = driver.findElements(By
     .tagName("a"));
   System.out.println("Total no. of links are "
     + anchorTagsList.size());
   for (WebElement anchorTagElement : anchorTagsList) {
    if (anchorTagElement != null) {
     String url = anchorTagElement.getAttribute("href");
     if (url != null && !url.contains("javascript")) {
      verifyURLStatus(url);
     } else {
      invalidLinksCount++;
     }
    }
   }

   System.out.println("Total no. of invalid links are "
     + invalidLinksCount);

  } catch (Exception e) {
   e.printStackTrace();
   System.out.println(e.getMessage());
  }
 }

 @AfterClass
 public void tearDown() {
  if (driver != null)
   driver.quit();
 }

 public void verifyURLStatus(String URL) {

  HttpClient client = HttpClientBuilder.create().build();
  HttpGet request = new HttpGet(URL);
  try {
   HttpResponse response = client.execute(request);
   // verifying response code and The HttpStatus should be 200 if not,
   // increment invalid link count
   ////We can also check for 404 status code like response.getStatusLine().getStatusCode() == 404
   if (response.getStatusLine().getStatusCode() != 200)
    invalidLinksCount++;
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
}

How to verify tooltip text with selenium webdriver using java

When user mouse hovers an any item (Button/link/field etc), without clicking it, and a tool tip may appear with information about the item being hovered. And Some times it may require us to check for the tooltip text.

There are different ways in showing tool tip to the user. We will look into two such different examples, one is with simple HTML and other example with Jquery ToolTip.

Example#1:

Let us take selenium official website to verify the tooltip in the first case.

Here when we mouse hover on the header, it has anchor tag with title attribute which is displayed as tooltip. Below is the screen shot:

In this case it is very simple to get tooltip text by using selenium getAttribute() method.

Syntax:

WebElement element = driver.findElement(By.cssSelector(".header"));
String toolTipText = element.getAttribute("title");

We will follow the below steps for Case#1:

1. Open browser
2. Identify the element
3. Get tool tip text by attribute
4. compare Actual with Expected tool tip text

Example#2:

Now let us take JQuery example for tool tip.

Here when user mouse hover on the text field, it will display the tool tip. But when you observer the HTML, it doesn't have any title attribute. When ever user mouse hover, it will generate a div tag in which tool tip text resides. Check the below screen shot.

So here getAttribute() will not work. To get the tool tip text here, we need to take the help of Selenium actions class.

Syntax:

Actions action = new Actions(driver);
WebElement element = driver.findElement(By.id("boxElement"));
actions.moveToElement(element).build().perform();

We will follow the below steps for Case#2:

1. Open browser
2. Switch to Frame
3. Identify the element
4. Mouse Hover on the text field using Actions class
5. Get tool tip text by attribute
6. compare Actual with Expected tool tip text

Let us now create an example for above cases and see how they work.
1. Create a class with name 'ToolTipExample.java'
2. Create two test methods 'toolTipCase1' and 'toolTipCase2'
3. Will have Before and After class for driver setup and teardown.

package com.tooltip;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.interactions.Actions;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class ToolTipExample {

 String seleniumURL = "http://docs.seleniumhq.org";
 String jQueryURL = "https://jqueryui.com/tooltip/";
 public WebDriver driver;

 @BeforeClass
 public void setUp() {
  driver = new FirefoxDriver();
  driver.manage().window().maximize();
 }

 @Test
 public void toolTipCase1() {
  driver.navigate().to(seleniumURL);

  WebElement element = driver.findElement(By.cssSelector("#header>h1 a"));
  // Get tooltip text
  String toolTipText = element.getAttribute("title");
  System.out.println("Tool tip text present :- " + toolTipText);

  // Compare toll tip text
  Assert.assertEquals("Return to Selenium home page", toolTipText);
 }

 @Test
 public void toolTipCase2() {
  driver.navigate().to(jQueryURL);

  // As there is frame, we have to navigate to frame
  WebDriverWait wait = new WebDriverWait(driver, 5);
  wait.until(ExpectedConditions.frameToBeAvailableAndSwitchToIt(By.cssSelector(".demo-frame")));

  // Text box field, where we mouse hover
  WebElement element = driver.findElement(By.id("age"));

  // Use action class to mouse hover on Text box field
  Actions action = new Actions(driver);
  action.moveToElement(element).build().perform();
  WebElement toolTipElement = driver.findElement(By.cssSelector(".ui-tooltip"));

  // To get the tool tip text and assert
  String toolTipText = toolTipElement.getText();
  Assert.assertEquals("We ask for your age only for statistical purposes.", toolTipText);

 }

 @AfterClass
 public void tearDown() {
  if (driver != null) {
   driver.quit();
  }
 }
}

Webdriver Select with Multiple Attribute

WebDriver’s support classes called “Select”, which provides useful methods for interacting with select options. User can perform operations on a select dropdown and also de-select operation using the below methods.

User can also get the text of the values in the dropdown. Also can get the option which are selected by the user. To use these options, the Select Tag should have "multiple" attribute.

Method Name: getOptions

Syntax: select.getOptions ();
Returns: List
Purpose: Returns all the option elements displayed in this select tag (dropdown list)

Example:HTML Sample Code

<html>
<head>
<title>Multi select Drop Down List Box</title>
</head>
<body>
<p>What all devices do you listen to music on?</p>
<select name="Mobdevices" multiple="multiple"><option value="0" selected> Please select</option>
<option value="1">iPhone</option>
<option value="2">Nokia</option>
<option value="3">Samsung</option>
<option value="4">HTC</option>
<option value="5">BlackBerry</option>
</select>
</body>
</html>

Example Webdriver Code:

//To get all the options present in the dropdown
List<WebElement> allOptions = se.getOptions();
for (WebElement webElement : allOptions)
{
System.out.println(webElement.getText());
}

Method Name: getAllSelectedOptions

Syntax: select.getAllSelectedOptions();
Returns: List
Purpose: It will return all the option elements that are selected in the select tag.

Example Webdriver Code

WebElement element=driver.findElement(By.name("Mobdevices"));
Select se=new Select(element);
se.selectByVisibleText("Nokia");
se.selectByVisibleText("HTC");
//To get all the options that are selected in the dropdown.
List<WebElement> allSelectedOptions = se.getAllSelectedOptions();
for (WebElement webElement : allSelectedOptions)
{
System.out.println("You have selected ::"+ webElement.getText());
}

Method Name: getFirstSelectedOption

Syntax: Select.getFirstSelectedOption();
Returns: WebElement
Purpose:  It will return the first selected option in this select tag (or the currently selected option in a normal select)

Example Webdriver Code:

WebElement element=driver.findElement(By.name("Mobdevices"));
Select se=new Select(element);
se.selectByVisibleText("Nokia");
se.selectByVisibleText("HTC");
//To get the first selected option in the dropdown
WebElement firstOption = se.getFirstSelectedOption();
System.out.println("The First selected option is::" +firstOption.getText());

Method Name: isMultiple

Syntax: select.isMultiple ();
Returns: Boolean
Purpose: To check whether the Select element supports selecting multiple options. This will be done by checking the value of the "multiple" attributes in Select tag.

Example:

if(se.isMultiple())
{
System.out.println("Select tag allows multiple selection"); 
}
else
{
System.out.println("Select does not allow multiple selections");
}

The below example Webdriver code show all the above examples together

package com.first.example;
import java.util.Iterator;
import java.util.List;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.Select;
import org.testng.annotations.Test;

public class selectExamples {
 WebDriver driver;
 @Test
 public void selectSamples() throws InterruptedException
 {
  driver = new FirefoxDriver();
  driver.get("C:\\Users\\DEV\\Desktop\\html-and-css-code-samples\\Final Code\\chapter-07\\dropdown-select.html");
  WebElement element=driver.findElement(By.name("Mobdevices"));
  Select se=new Select(element);
  
  se.selectByVisibleText("Nokia");
  se.selectByVisibleText("HTC");
  
  //To get all the options present in the dropdown
  List<WebElement> allOptions = se.getOptions();
  for (WebElement webElement : allOptions)
  {
   System.out.println(webElement.getText());
  }
  
  //To get all the options that are selected in the dropdown.
  List<WebElement> allSelectedOptions = se.getAllSelectedOptions();
  for (WebElement webElement : allSelectedOptions)
  {
   System.out.println("You have selected::"+ webElement.getText());
  }
   
  //To get the first selected option in the dropdown
  WebElement firstOption = se.getFirstSelectedOption();
  System.out.println("The First selected option is::" +firstOption.getText());
  
  if(se.isMultiple())
  {
  System.out.println("Select tag allows multiple selection"); 
  }
 }
}

Please click the below link for Select options 'Part-1"
Select Options Part -I

Webdriver SELECT Methods to work with Dropdowns

WebDriver’s support classes called “Select”, which provides useful methods for interacting with select options. User can perform operations on a select dropdown and also de-select operation using the below methods.

Method Name: selectByIndex 

Syntax: select.selectByIndex(Index);
Purpose:  To Select the option based on the index given by the user.
There is an attribute called "values" which will have the index values.

The below is the sample html code using index
Example
HTML Code

<html>
<head>
<title>Select Example by Index value</title>
</head>
<body>
<select name="Mobiles"><option value="0" selected> Please select</option>
<option value="1">iPhone</option>
<option value="2">Nokia</option>
<option value="3">Samsung</option>
<option value="4">HTC</option>
<option value="5">BlackBerry</option>
</select>
</body>
</html>

Webdriver code for Selecting a Value using select.selectByValue(Value);

public class selectByIndexExample {
 WebDriver driver;
 @Test
 public void selectSamples()
 {
  driver = new FirefoxDriver();
  driver.get("C:\\Users\\DEV\\Desktop\\html-and-css-code-samples\\Final Code\\chapter-07\\dropdown-select.html");
  WebElement element=driver.findElement(By.name("Mobiles"));
  Select se=new Select(element);
  se.selectByIndex(1);
 }
}

Method Name: selectByValue 

Syntax: select.selectByValue(Value);
Purpose:  To Select the options that have a value matching with the given argument by the user.

Example:
HTML Code:

<html>
<head>
<title>Select Example by Value</title>
</head>
<body>
<p>Which mobile device do you like most?</p>
<select name="Mobiles"><option selectd> Please select</option>
<option value="iphone">iPhone</option>
<option value="nokia">Nokia</option>
<option value="samsung">Samsung</option>
<option value="htc">HTC</option>
<option value="blackberry">BlackBerry</option>
</select>
</body>
</html>

Webdriver code for Selecting a Value using select.selectByValue(Value);

public class selectExamples {
 WebDriver driver;
 @Test
 public void selectSamples()
 {
  driver = new FirefoxDriver();
  driver.get("C:\\Users\\DEV\\Desktop\\html-and-css-code-samples\\Final Code\\chapter-07\\dropdown-select.html");
  WebElement element=driver.findElement(By.name("Mobiles"));
  Select se=new Select(element);
  se.selectByValue("nokia");
 }
}

Method Name: selectByVisibleText 

Syntax: select.selectByVisibleText(Text);
Purpose: To Select all options that display text matching the given argument. It will not look for any index or value, it will try to match the VisibleText (which will display in dropdown)

Example: Any of the above html code can be taken as example.

Webdriver example code to select the value by Visible text.

public class selectExamples {
 WebDriver driver;
 @Test
 public void selectSamples()
 {
  driver = new FirefoxDriver();
  driver.get("C:\\Users\\DEV\\Desktop\\html-and-css-code-samples\\Final Code\\chapter-07\\dropdown-select.html");
  WebElement element=driver.findElement(By.name("Mobiles"));
  Select se=new Select(element);
  se.selectByVisibleText("HTC");
 }
}

Method Name: deselectByIndex  

Syntax: select.deselectByIndex(Index);
Purpose: To Deselect the option at the given index. The user has to provide the value of index.
Please check for the below example.

Method Name: deselectByValue  

Syntax: select.deselectByValue(Value);
Purpose: To Deselect all options that have a value matching the given argument.
Please check for the below example.

Method Name: deselectByVisibleText  

Syntax: select.deselectByVisibleText(Text);
Purpose: To Deselect all options that display text matching the given argument.
Please check for the below example.

Method Name: deselectAll

Syntax: select.deselectAll();
Purpose: To Clear all selected entries. This works only when the SELECT supports multiple selections. It throws NotImplemented eError if the "SELECT" does not support multiple selections. In select it mandatory to have an attribute multiple="multiple"
Please check for the below example.

Example::
HTML Code for Multi Select :

<html>
<head>
<title>Multi select Drop Down List Box</title>
</head>
<body>
<p>What all devices do you listen to music on?</p>
<select name="Mobdevices" multiple="multiple"><option value="0" selectd> Please select</option>
<option value="1">iPhone</option>
<option value="2">Nokia</option>
<option value="3">Samsung</option>
<option value="4">HTC</option>
<option value="5">BlackBerry</option>
</select>
</body>
</html>

Webdriver code to show all the above deselect methods.

public class selectExamples {
 WebDriver driver;
 @Test
 public void selectSamples() throws InterruptedException
 {
  driver = new FirefoxDriver();
  driver.get("C:\\Users\\DEV\\Desktop\\html-and-css-code-samples\\Final Code\\chapter-07\\dropdown-select.html");
  WebElement element=driver.findElement(By.name("Mobdevices"));
  Select se=new Select(element);
  //Here we will take multi select dropdown to show you the difference
  se.selectByVisibleText("HTC");
  se.selectByValue("nokia");
  
  //From the above two commands, in the dropdown two values will be selected. 
  //Now we will try to deselect any of the One
  //Im using thread to see the difference when selecting and selecting
  Thread.sleep(3000);
  se.deselectByValue("nokia");

  //You can deselect the value by specifying the index, value and VisibleText
  
  //It will work if you the index is already selected
  se.deselectByIndex(1);
  
  //It will deselect if the visible text HTC is in selected mode
  se.deselectByVisibleText("HTC");
  
  //It will de-select all the values which are selected
  se.deselectAll();
 }
}

Please click the below link for Select options 'Part-2"
Select Options Part -2