Selenium Data Driven | Python | xlrd | .XLSX

XLRD

xlrd is a library that is used to extract data from an excel file using Python Binding. It helps you to do data scrapping from an Excel spreadsheet.


Setup xlrd with Python 

1| Open cmd prompt
2| Locate the folder C:\Python27\Scripts in cmd and enter
pip install xlrd

Excel

Create an Excel sheet that resembles this:

from selenium import webdriver
import unittest, time
import xlrd

class moduleName(unittest.TestCase):
    def setUp(self):
        self.driver = webdriver.Firefox()
        self.driver.implicitly_wait(30)
        self.base_url = "https://www.google.co.in"
        self.verificationErrors = []
        self.accept_next_alert = True
        
    def test_Login(self):
        driver=self.driver
        driver.get(self.base_url +"/")
        driver.maximize_window()
        wb=xlrd.open_workbook('C://sams.xlsx')
        sheetname = wb.sheet_names() 
        #Read for XLSX Sheet names
        sh1 = wb.sheet_by_index(0) 
                        
        i=0
        while (i<2):
            rownum=(i)
            rows = sh1.row_values(rownum)
            element = driver.find_element_by_id("gbqfq")
            driver.find_element_by_id("gbqfq").send_keys(rows[0])        
            element.submit()
            time.sleep(3)
            print "The keyword [" + rows[0] + "] is searched"
            driver.back()
            time.sleep(3)
            i=i+1
    
    def tearDown(self):
        self.driver.quit()

if __name__ == "__main__":
    unittest.main()

Test Console looks the same as below after Execution

NOTE| Use Python 2.7 instead of Python 3; xlrd doesn't support Python 3

Get user input at run-time to select Test ENV | Scanner | Selenium

In Java, Scanner is used to get the user input at run-time and act accordingly. Here, we make use of this to select the specific environment that we have to run. Please find the test class below..

SCANNER | Selenium


import java.util.Scanner;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public class classname {
private WebDriver driver;
private String baseUrl;
private int a, i;

@BeforeTest
public void setUp() throws Exception {

int a;
Scanner in = new Scanner(System.in);
driver = new FirefoxDriver();

System.out.println("Please choose the Environment: \n");
System.out.println("1. DEV Environment");
System.out.println("2. QA Environment");
System.out.println("3. PROD Environment");

a = in.nextInt();

if (a == 1) {
System.out.println("You have selected 'DEV Environment' ...\n");
driver.get("http://dev-xyz.com/");
}
if (a == 2) {
System.out.println("You have selected 'QA Environment' ...\n");
driver.get("http://qa-xyz.com/");
}
if (a == 3) {
System.out
.println("You have selected 'PROD Cloud Environment' ...\n");
driver.get("http://prod-xyz.com/");
}

in.close();

driver.manage().timeouts().implicitlyWait(3, TimeUnit.SECONDS);
}

@Test
public void Test01() throws Exception {
// your code here...
}

@AfterTest
public void tearDown() throws Exception {
driver.quit();
}
}

Selenium Data Driven | Text File

The Text file lets you store the keywords for data driven tests. Here, the Text file acts as a data-source.

1| Open Notepad
2| Enter keywords as shown in the figure.
3| Make sure that both the keywords are separated with a space.
4| Save them as .txt [data.txt]

package packagename;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class classname {
WebDriver driver;
private String baseUrl;

@BeforeClass
public void BeforeClass() {

driver = new FirefoxDriver();
driver.manage().window().maximize();
baseUrl = "http://www.google.co.in";
}

@Test
public void Test01() {
Execute();
}

public void Execute() {

BufferedReader in = null;
InputStreamReader inputStream = null;
try {

inputStream = new InputStreamReader(new FileInputStream("C:"
+ File.separator + "data.txt"));
in = new BufferedReader(inputStream);
String line = null;
String actualvalue = "";
String expectedvalue = "";
while ((line = in.readLine()) != null) {
String[] data = line.split(" ");
if (data.length >= 1) {
actualvalue = data[0];
expectedvalue = data[1];
System.out.println("Actual : " + actualvalue);
System.out.println("Expected : " + expectedvalue);
// for (int i = 0; i < 10; i++) { // same action multiple
// times
// Your CODE here
driver.get(baseUrl + "/");
driver.findElement(By.id("gbqfq")).sendKeys(actualvalue);
driver.findElement(By.id("gbqfq")).sendKeys(Keys.RETURN);
boolean b = driver.getPageSource().contains(expectedvalue);
Assert.assertTrue(b);
// }
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}

@AfterClass
public void AfterClass() {
driver.quit();
}
}

Datadriven + JXL API | TestNG | Method 3

JXL API

Download Jxl.jar

This is the continuation of one of the previous posts from me [ Link ].  In this method, we are using @dataProvider annotation on TestNG to fetch keywords from Excel and passing the arguments to Test class. I would always prefer this Method. Please follow the below example test class..

Method 3 

TYPE 1 - With Excel

@DataProvider | TestNG

package packagename;

import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class classname{
WebDriver driver;
private String baseUrl;

@DataProvider(name = "Test")
public Object[][] createPayId() throws Exception {
Object[][] retObjArr = getTableArray("C:\\data.xls", "Sheet1", "test1");
return (retObjArr);
}

@BeforeClass
public void BeforeClass() {
driver = new FirefoxDriver();
driver.manage().window().maximize();
baseUrl = "http://www.google.co.in";

}

@Test(dataProvider = "Test", description = "Testing ")
public void Test01(String column1, String column2, String column3,
String column4) throws Exception {

driver.get(baseUrl + "/");
driver.findElement(By.id("gbqfq")).sendKeys(column1);
driver.findElement(By.id("gbqfq")).sendKeys(Keys.RETURN);
}

public String[][] getTableArray(String xlFilePath, String sheetName,
String tableName) throws Exception {
String[][] tabArray = null;

Workbook workbook = Workbook.getWorkbook(new File(xlFilePath));
Sheet sheet = workbook.getSheet(sheetName);
int startRow, startCol, endRow, endCol, ci, cj;
Cell tableStart = sheet.findCell(tableName);
startRow = tableStart.getRow();
startCol = tableStart.getColumn();

Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1,
100, 64000, false);

endRow = tableEnd.getRow();
endCol = tableEnd.getColumn();
System.out.println("startRow=" + startRow + ", endRow=" + endRow + ", "
+ "startCol=" + startCol + ", endCol=" + endCol);
tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
ci = 0;

for (int i = startRow + 1; i < endRow; i++, ci++) {
cj = 0;
for (int j = startCol + 1; j < endCol; j++, cj++) {
tabArray[ci][cj] = sheet.getCell(j, i).getContents();
}
}
return (tabArray);
}

@AfterClass
public void AfterClass() {
driver.quit();
}

}


Create an Excel sheet that resembles this:

Test Console looks the same as below after Execution

TYPE 2 - Without Excel

@DataProvider | TestNG

package packagename;

import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class classname {
WebDriver driver;
private String baseUrl;

@DataProvider(name = "Test")
public String[][] y() {

return new String[][] { { "prashanth sams", "prashanth sams" },
{ "yeah", "prashanth sams" },
{ "seleniumworks", "prashanth sams" } };
}

@BeforeClass
public void BeforeClass() {
driver = new FirefoxDriver();
driver.manage().window().maximize();
baseUrl = "http://www.google.co.in";

}

@Test(dataProvider = "Test")
public void Google(String actual, String expected) throws Exception {

driver.get(baseUrl + "/");
driver.findElement(By.id("gbqfq")).sendKeys(actual);
driver.findElement(By.id("gbqfq")).sendKeys(Keys.RETURN);

boolean b = driver.getPageSource().contains(expected);
Assert.assertTrue(b);

}

@AfterClass
public void AfterClass() {
driver.quit();
}

}

Test Console looks the same as below after Execution

E-mail Selenium Test Reports

E-mailing selenium Test Reports become even easier for you to notify your clients.

Create a class Lib

1| Create a class library in your package.
2| Just copy & paste the below code;  Do few edits that are highlighted in ORANGE


package packagename;

import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

public class SendMailSSL {

public static void execute(String reportFileName) throws Exception {

final String username = "yourusername@gmail.com";
final String password = "yourpassword";

Properties props = new Properties();
props.put("mail.smtp.starttls.enable", "true");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.host", "smtp.gmail.com");
props.put("mail.smtp.port", "587");

Session session = Session.getInstance(props,
new javax.mail.Authenticator() {
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username, password);
}
});

try {

Message message = new MimeMessage(session);
message.setFrom(new InternetAddress("yourusername@gmail.com"));
message.setRecipients(Message.RecipientType.TO,
InternetAddress.parse("yourrecepientusername@gmail.com"));
message.setSubject("Reports Availalbe!");
message.setText("Dear Mail Crawler,"
+ "\n\n No spam to my email, please!");

MimeBodyPart messageBodyPart = new MimeBodyPart();

Multipart multipart = new MimeMultipart();

messageBodyPart = new MimeBodyPart();
String file = "C:\\Users\\prashanth_sams\\workspace\\jxl\\test-output\\";
String fileName = reportFileName;
DataSource source = new FileDataSource(file + fileName);
messageBodyPart.setDataHandler(new DataHandler(source));
messageBodyPart.setFileName(fileName);
multipart.addBodyPart(messageBodyPart);

message.setContent(multipart);
System.out.println("Sending");
Transport.send(message);
System.out.println("Done");
} catch (MessagingException e) {
throw new RuntimeException(e);
}
}
}


Test Class

Add a small snippet [SendMailSSL.execute("report.html");] after your test execution.


@AfterTest
public void tearDown() throws Exception {
driver.quit();
Thread.sleep(3000);
SendMailSSL.execute("emailable-report.html");
}

Datadriven + JXL API | TestNG | Method 2

JXL API
Download Jxl.jar

This is the continuation of one of the previous posts from me [ Link ].  In this method, you don't need to create any reusable library and found easy compared to all the other methods. Please follow the below example test class..

Method 2

package packagename;

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

import jxl.Sheet;
import jxl.Workbook;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public class className {
WebDriver driver;
Sheet s;

@BeforeTest
public void setUp() {
driver = new FirefoxDriver();
driver.get("http://www.quikr.com/bye");
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

@Test
public void Quikrpass() throws Exception {
FileInputStream fi = new FileInputStream("C:\\Data.xls");
Workbook w = Workbook.getWorkbook(fi);
s = w.getSheet(0);
for (int row = 1; row <= s.getRows() - 1; row++) {
String input = s.getCell(0, row).getContents();
String output = s.getCell(1, row).getContents();

driver.findElement(By.className("search_field")).clear();
driver.findElement(By.className("search_field")).sendKeys(input);
driver.findElement(By.className("blue_search_button_f4")).click();

Assert.assertEquals(
driver.findElement(
By.xpath("//div[@id='welcome']/div[3]/span[2]"))
.getText(), output);
driver.navigate().back();
}
}

@Test
public void Quikrfail() throws Exception {
FileInputStream fi = new FileInputStream("C:\\Data.xls");
Workbook w = Workbook.getWorkbook(fi);
s = w.getSheet(0);
for (int row = 1; row <= s.getRows() - 1; row++) {
String input2 = s.getCell(2, row).getContents();
String output2 = s.getCell(3, row).getContents();

driver.findElement(By.className("search_field")).clear();
driver.findElement(By.className("search_field")).sendKeys(input2);
driver.findElement(By.className("blue_search_button_f4")).click();

Assert.assertEquals(
driver.findElement(
By.xpath("//div[@id='welcome']/div[3]/span[2]"))
.getText(), output2);
driver.navigate().back();
}
}

@AfterTest
public void tearDown() {
driver.quit();
}
}


Create an Excel sheet that resembles this:

Test Console looks the same as below after Execution

EventFiringWebDriver | AbstractWebDriverEventListener

Every tests should undergo WebDriverEventListener for analyzing tests and for better reporting. This post is very Generic and anyone can easily understand the concepts.

Note|
1| There are more to do and customize with the WebDriver EventListeners. Explained below is the most important functions under WebDriver EventListeners.
2| In the given example, make sure all the sections are commented out except one which you are working on. eg., if you working on 'onException' then comment out the other sections

EventFiringWebDriver supports registering of a WebDriverEventListener. Create a test class as shown below.

import org.openqa.selenium.support.events.EventFiringWebDriver;

public class className {
private WebDriver driver;

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

@Test
public void Test01() throws Exception {

EventFiringWebDriver event = new EventFiringWebDriver(driver);
WebDriverListerners eventListener = new WebDriverListerners();
event.register(eventListener);

                // beforeNavigateTo & afterNavigateTo
                event.get("http://www.google.com");

                // beforeNavigateBack & afterNavigateBack
                event.get("http://www.google.com");
                event.get("http://www.bing.com");
event.navigate().back();

                // beforeNavigateForward & afterNavigateForward
                event.get("http://www.google.com");
event.get("http://www.bing.com");
event.navigate().back();
event.navigate().forward();                   

                // onException
                event.get("http://.........");
event.findElement(By.id("Wrong Value"));

                // beforeFindBy & afterFindBy
                event.get("http://.........");
event.findElement(By.id("Value"));

                // beforeClickOn & afterClickOn
                event.get("http://.........");
event.findElement(By.id("Value")).click();

                // beforeChangeValueOf & afterChangeValueOf
                event.get("http://.........");
event.findElement(By.id("Value")).clear();
                event.findElement(By.id("Value")).sendKeys("Seleniumworks");
       }
}

AbstractWebDriverEventListener

Use AbstractWebDriverEventListener as a Base class for implementing WebDriverEventListener.


package packagename;

import java.io.File;

import org.openqa.selenium.By;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.StaleElementReferenceException;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.OutputType;
import org.openqa.selenium.TakesScreenshot;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.support.events.AbstractWebDriverEventListener;

public class WebDriverListerners extends AbstractWebDriverEventListener {
private By lastFindBy;
private WebElement lastElement;
private String originalValue;

/*
 *  URL NAVIGATION | NAVIGATE() & GET()
 */
// Prints the URL before Navigating to specific URL "get("http://www.google.com");"
@Override
public void beforeNavigateTo(String url, WebDriver driver) {
System.out.println("Before Navigating To : " + url + ", my url was: "
+ driver.getCurrentUrl());
}

// Prints the current URL after Navigating to specific URL "get("http://www.google.com");"
@Override
public void afterNavigateTo(String url, WebDriver driver) {
System.out.println("After Navigating To: " + url + ", my url is: "
+ driver.getCurrentUrl());
}

// Prints the URL before Navigating back "navigate().back()"
@Override
public void beforeNavigateBack(WebDriver driver) {
System.out.println("Before Navigating Back. I was at "
+ driver.getCurrentUrl());
}

// Prints the current URL after Navigating back "navigate().back()"
@Override
public void afterNavigateBack(WebDriver driver) {
System.out.println("After Navigating Back. I'm at "
+ driver.getCurrentUrl());
}

// Prints the URL before Navigating forward "navigate().forward()"
@Override
public void beforeNavigateForward(WebDriver driver) {
System.out.println("Before Navigating Forward. I was at "
+ driver.getCurrentUrl());
}

// Prints the current URL after Navigating forward "navigate().forward()"
@Override
public void afterNavigateForward(WebDriver driver) {
System.out.println("After Navigating Forward. I'm at "
+ driver.getCurrentUrl());
}


/*
 * ON EXCEPTION | SCREENSHOT, THROWING ERROR
 */
// Takes screenshot on any Exception thrown during test execution
@Override
public void onException(Throwable throwable, WebDriver webdriver) {
System.out.println("Caught Exception");
File scrFile = ((TakesScreenshot) webdriver)
.getScreenshotAs(OutputType.FILE);
try {
org.apache.commons.io.FileUtils.copyFile(scrFile, new File(
"C:\\Testfailure.jpeg"));
} catch (Exception e) {
System.out.println("Unable to Save");
}
}


/*
 * FINDING ELEMENTS | FINDELEMENT() & FINDELEMENTS()
 */
// Called before finding Element(s)
@Override
public void beforeFindBy(By by, WebElement element, WebDriver driver) {
lastFindBy = by;
System.out.println("Trying to find: '" + lastFindBy + "'.");
System.out.println("Trying to find: " + by.toString()); // This is optional and an alternate way
       }

// Called after finding Element(s)
@Override
public void afterFindBy(By by, WebElement element, WebDriver driver) {
lastFindBy = by;
System.out.println("Found: '" + lastFindBy + "'.");
System.out.println("Found: " + by.toString() + "'."); // This is optional and an alternate way
}


/*
 * CLICK | CLICK()
 */
// Called before clicking an Element
@Override
public void beforeClickOn(WebElement element, WebDriver driver) {
System.out.println("Trying to click: '" + element + "'");
// Highlight Elements before clicking
for (int i = 0; i < 1; i++) {
JavascriptExecutor js = (JavascriptExecutor) driver;
js.executeScript(
"arguments[0].setAttribute('style', arguments[1]);",
element, "color: black; border: 3px solid black;");
}
}

// Called after clicking an Element
@Override
public void afterClickOn(WebElement element, WebDriver driver) {
System.out.println("Clicked Element with: '" + element + "'");
}

/*
 * CHANGING VALUES | CLEAR() & SENDKEYS()
 */
// Before Changing values
@Override
public void beforeChangeValueOf(WebElement element, WebDriver driver) {
lastElement = element;
originalValue = element.getText();

// What if the element is not visible anymore?
if (originalValue.isEmpty()) {
originalValue = element.getAttribute("value");
}
}

// After Changing values
@Override
public void afterChangeValueOf(WebElement element, WebDriver driver) {
lastElement = element;
String changedValue = "";
try {
changedValue = element.getText();
} catch (StaleElementReferenceException e) {
System.out
.println("Could not log change of element, because of a stale"
+ " element reference exception.");
return;
}
// What if the element is not visible anymore?
if (changedValue.isEmpty()) {
changedValue = element.getAttribute("value");
}

System.out.println("Changing value in element found " + lastElement
+ " from '" + originalValue + "' to '" + changedValue + "'");
}


/*
 * SCRIPT - this section will be modified ASAP
 */
// Called before RemoteWebDriver.executeScript(java.lang.String, java.lang.Object[])
@Override
public void beforeScript(String script, WebDriver driver) {
// TODO Auto-generated method stub
       }

// Called before RemoteWebDriver.executeScript(java.lang.String, java.lang.Object[])
  @Override
        public void afterScript(String script, WebDriver driver) {
// TODO Auto-generated method stub
       }



OUTPUT |URL NAVIGATION | NAVIGATE() & GET()|
beforeNavigateTo & afterNavigateTo
Before Navigating To : http://www.google.com, my url was: about:blank
After Navigating To: http://www.google.com, my url is: https://www.google.co.in/?gfe_rd=cr&ei=qsb8Ur6bMqHM8gett4DoAQ

beforeNavigateBack & afterNavigateBack
Before Navigating Back. I was at http://www.bing.com/

After Navigating Back. I'm at https://www.google.co.in/?gfe_rd=cr&ei=Nsj8UqKwEoLV8gfnzYDwBQ

beforeNavigateForward & afterNavigateForward
Before Navigating Forward. I was at https://www.google.co.in/?gfe_rd=ctrl&ei=bMn8UonqD8PM8ge66YHICA&gws_rd=cr

After Navigating Forward. I'm at http://www.bing.com/

OUTPUT |ON EXCEPTION | SCREENSHOT, THROWING ERROR|

Check specific folder for screenshot on test failure due to Exception

OUTPUT |FINDING ELEMENTS | FINDELEMENT() & FINDELEMENTS()|

Trying to find: [Element]
Found: '[Element]'.

OUTPUT |CLICK | CLICK()|

Trying to click: '[Element]'
Clicked Element with: '[Element]'

OUTPUT |CHANGING VALUES | CLEAR() & SENDKEYS()|

Changing value in element found [Element] from 'old text' to ''
Changing value in element found [Element] from '' to 'Seleniumworks'