Passing data to DataProvider from Excel sheet

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
===============================================
Test Frameworks: 

Comments

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 @AfterMEthod Annotation not @Test Annotation

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.

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?

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

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

Please Can anyone tell me how the excel sheet structure/format should be...

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 script, but it only show below result
=====
PASSED: tearDown
SKIPPED: VerifyInvalidLogin
=====

Please Help me

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

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.