Skip to toolbar

TestNG – 16 || Access data from Excel sheet using DataProvider.

We can use an Excel sheet as a source of input. We can access data from excel sheet using DataProvider annotation and pass arguments through @Test methods. Mainly this approach is used in Data Driven Framework.

 

Access data from Excel sheet using DataProvider
                                                                                          Data Driven Framework

 

Data Driven Framework: A data-driven framework is used to run the same script for a huge amount of test data from any source like properties files, excel files etc. On the other hand, a Key Driven framework is based on keywords which are used to control the execution of the script without modifying the actual code. A tester has to just set the keywords which he/she wants to execute during a review cycle. Nowadays, the hybrid framework is used which is the combination of Keyword Driven and Data Driven Framework. 

Please refer Simple example of Key Driven Framework using the excel sheet.

DataProvider

A data provider is defined with @DataProvider annotation. This annotation contains only one attribute its name. If the name is not supplied, the data provider’s name automatically defaults to the method’s name. A data provider returns an array of objects as mentioned below:
The return type of methods using “@DataProvider(name=”testdata”)” should be Object[Row][Col].
Row – defines the number of rows in excel files
        – It also defines the total number of times test get repeat and it treats it as individual test case in TestNg
Col – defines number of columns in excel file
       – defines total number of parameters in the test data

@DataProvider(name=“excelData”)
public Object[ ][ ] readExcel()
{
           Object[ ][ ] obj=new Object[2][2];
           return obj;
}

@Test(dataProvider=“excelData”)
Public void testMethod(String arg1, String arg2)
{
   System.out.println(arg1);
   System.out.println(arg2);
}

Selenium Code:

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.Assert;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class TestCase 
{
	public static WebDriver driver;
	public static String targetURL="https://gmail.com";
	
	@BeforeClass
	public void launchURL()
	{
		System.setProperty("webdriver.chrome.driver", "exe\\chromedriver.exe");
		driver=new ChromeDriver();
		driver.get(targetURL);
	}
	
	@DataProvider(name="excelData")
	public Object[][] readExcel() throws IOException
	{
		File file=new File("data\\testData.xlsx");
		FileInputStream fis=new FileInputStream(file);
		XSSFWorkbook workbook= new XSSFWorkbook(fis);
		XSSFSheet sheet=workbook.getSheet("Sheet1");
		int totalRows=sheet.getLastRowNum();
		int totalColums=sheet.getRow(0).getPhysicalNumberOfCells();
		
		// Read data from excel and store the same in the Object Array.
		Object obj[][]=new Object[totalRows][totalColums];
		for(int i=0;i<totalRows;i++)
		{
			obj[i][0]=sheet.getRow(i+1).getCell(0).toString();
			obj[i][1]=sheet.getRow(i+1).getCell(1).toString();
		}
		
		return obj;
	}
	
	@Test(dataProvider="excelData")
	public void validateUser(String userName,String password) throws InterruptedException
	{
		By userNameXpath=By.xpath(".//*[@id='identifierId']");
		By nextBtnXpath=By.xpath(".//*[@id='identifierNext']");
		By passwordXpath=By.xpath(".//*[@id='password']//input[contains(@name,'password')]");
		By passwordNextBtnXpath=By.xpath(".//*[@id='passwordNext']");
		By searchXpath=By.xpath("//input[contains(@aria-label,'Search')]");
		
		wait(userNameXpath);
		driver.findElement(userNameXpath).click();
		driver.findElement(userNameXpath).clear();
		driver.findElement(userNameXpath).sendKeys(userName);
		driver.findElement(nextBtnXpath).click();
		
		wait(passwordXpath);
		driver.findElement(passwordXpath).click();
		driver.findElement(passwordXpath).sendKeys(password);
		driver.findElement(passwordNextBtnXpath).click();
		
		try
		{
			wait(searchXpath);
			
			String windowTitle=driver.getTitle();
			if(windowTitle.contains(userName))
			{
				Assert.assertTrue(true);
				logout();
				switchAccount();
			}
			else
			{
				Assert.assertTrue(false);
			}
		}
		catch(Exception e)
		{
			switchAccount();
			Assert.assertTrue(false);
		}
	}
	
	public void wait(By locator)
	{
		WebDriverWait wait=new WebDriverWait(driver, 10);
		wait.until(ExpectedConditions.visibilityOfElementLocated(locator));
	}
	
	
	public void logout() throws InterruptedException
	{
		By myProfileXpath=By.xpath("//*[contains(@href,'SignOutOptions')]");
		By logoutBtnXpath=By.xpath("//*[contains(@href,'Logout')]");
		
		
		driver.findElement(myProfileXpath).click();
		wait(logoutBtnXpath);
		driver.findElement(logoutBtnXpath).click();
		
		
	}
	
	public void switchAccount()
	{
		By switchAccountXpath=By.xpath("//div[contains(@aria-label,'Switch account')]");
		By useOtherAccountXpath=By.xpath("//*[@id='identifierLink']");
		
		wait(switchAccountXpath);
		driver.findElement(switchAccountXpath).click();
		wait(useOtherAccountXpath);
		driver.findElement(useOtherAccountXpath).click();
		
	}

}

TestNG:

Computer Basics:

Java Basics:

Java File Handling:

OOPs Concept:

Java Question And Answer:

Java Programs:

Selenium Tutorials:

JIRA:

Agile:


8753total visits,9visits today

Leave a Reply

Your email address will not be published. Required fields are marked *

Site Statistics

  • Users online: 0 
  • Visitors today : 11
  • Page views today : 21
  • Total visitors : 48,397
  • Total page view: 66,589

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.