In this example we will see how to pass the data to Dataproviders by reading the data from excel sheet. DataProvider helps to send multiple sets of data to a test method. But here we need to make sure that the array returned by the dataprovider should match with the test method parameters.
We will write a simple program in which we will validate login screen by taking multiple usernames and passwords. The annotated method must return object[][] where each object[] can be assigned to the test method one as username and the other parameter as password.
Step 1: First create a method to read excel data and return string array.
Step 2: Create before class and after class methods which helps in getting the browser and closing them when done.
Step 3: Create a data provider which actually gets the values by reading the excel.
Step 4: Create a Test which takes two parameters username and password.
Step 5: Add dataprovider name for @Test method to receive data from dataprovider.
package com.pack;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.testng.Assert;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcelDataProvider {
public WebDriver driver;
public WebDriverWait wait;
String appURL = "https://www.linkedin.com/";
//Locators
private By byEmail = By.id("session_key-login");
private By byPassword = By.id("session_password-login");
private By bySubmit = By.id("signin");
private By byError = By.id("global-alert-queue");
@BeforeClass
public void testSetup() {
driver=new FirefoxDriver();
driver.manage().window().maximize();
wait = new WebDriverWait(driver, 5);
}
@Test(dataProvider="empLogin")
public void VerifyInvalidLogin(String userName, String password) {
driver.navigate().to(appURL);
driver.findElement(byEmail).sendKeys(userName);
driver.findElement(byPassword).sendKeys(password);
//wait for element to be visible and perform click
wait.until(ExpectedConditions.visibilityOfElementLocated(bySubmit));
driver.findElement(bySubmit).click();
//Check for error message
wait.until(ExpectedConditions.presenceOfElementLocated(byError));
String actualErrorDisplayed = driver.findElement(byError).getText();
String requiredErrorMessage = "Please correct the marked field(s) below.";
Assert.assertEquals(requiredErrorMessage, actualErrorDisplayed);
}
@DataProvider(name="empLogin")
public Object[][] loginData() {
Object[][] arrayObject = getExcelData("D:/sampledoc.xls","Sheet1");
return arrayObject;
}
/**
* @param File Name
* @param Sheet Name
* @return
*/
public String[][] getExcelData(String fileName, String sheetName) {
String[][] arrayExcelData = null;
try {
FileInputStream fs = new FileInputStream(fileName);
Workbook wb = Workbook.getWorkbook(fs);
Sheet sh = wb.getSheet(sheetName);
int totalNoOfCols = sh.getColumns();
int totalNoOfRows = sh.getRows();
arrayExcelData = new String[totalNoOfRows-1][totalNoOfCols];
for (int i= 1 ; i < totalNoOfRows; i++) {
for (int j=0; j < totalNoOfCols; j++) {
arrayExcelData[i-1][j] = sh.getCell(j, i).getContents();
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
return arrayExcelData;
}
@Test
public void tearDown() {
driver.quit();
}
}
After clicking on login button, we are using WebdriverWaits to Check for error message and validate.
The output should look like below:
[TestNG] Running:
C:\Users\easy\AppData\Local\Temp\testng-eclipse-583753747\testng-customsuite.xml
PASSED: VerifyInvalidLogin("testuser1", "testpassword1")
PASSED: VerifyInvalidLogin("testuser2", "testpassword2")
PASSED: VerifyInvalidLogin("testuser3", "testpassword3")
PASSED: VerifyInvalidLogin("testuser4", "testpassword4")
PASSED: VerifyInvalidLogin("testuser5", "testpassword5")
===============================================
Default test
Tests run: 5, Failures: 0, Skips: 0
===============================================
===============================================
Default suite
Total tests run: 5, Failures: 0, Skips: 0
===============================================
- ‹ Execute selenium webdriver testng.xml using Maven
- Element is not clickable at point SeleniumWebdriverException ›
Comments
Query
I have tried executing above script with some changes, but what it does, it only show below result
=====
PASSED: tearDown
SKIPPED: VerifyInvalidLogin
=====
I want to ask what goes wrong in this. I also make a escel sheet with relevant test data, with proper columns.
Please help!
TearDown method should have
TearDown method should have @AfterMEthod Annotation not @Test Annotation
need above code for Dropdown of username and password
in above script, the data is read and send to the username and password field. assume there are drop-down of Username and Password, so how we can handle that and assign the value from excel sheet to the dropdown menu.
How can i do it??
tried using
var newValue = 24;
document.getElementById(‘projectID’).value = newValue;
but of no use. Please suggest any alternate method.
Query
Hi,
I need to create a data provider which actually gets the values by reading the excel.My Excel has 18 Rows, so should I create object array with 18 square brackets[][][][][][].....[]18?
Answer
You Could use a collection Frame work to do so, LinkedHashMap, ArrayList.....these can simplify the task creating an array with 18 square brackets is notcorrect
data provider method should be generic .
@DataProvider(name="empLogin")
public Object[][] loginData() {
Object[][] arrayObject = getExcelData("D:/sampledoc.xls","Sheet1");
return arrayObject;
}
in above example i want to pass file location and sheet name from external java i tried several way please help me to find some solution.
Format for Excel sheet
Please Can anyone tell me how the excel sheet structure/format should be...
I want to get data 100s of data from excel
hi..
There is only two record but I have to fetch 100+ data from a row of excel so for this i would not pass the 100+ variables in
@test annotation. and also I have multiple rows.
I have tried executing above
I have tried executing above script, but it only show below result
=====
PASSED: tearDown
SKIPPED: VerifyInvalidLogin
=====
Please Help me
query
We can handle the excel sheet data in three ways.
1. Using Apache POI + Java code
2. Data Providers of TestnNG
3. Using Apache POI + Java code + Collections (Hashmap)
Which one is light weight and less memory consumption thereby improves performancce ?
Add new comment