Simple DDT(Data driven testing) | Read user's detail from excel & login to application multiple times

Hello Dear Friends!!!

I hope you are doing well!!!

So guys in today's article we are going to see below points.

1. Data driven testing using excel

2. Simple ways and methods to read data from excel file

3. Read data from excel and Automate web application

4. Use of @DataProvider annotation in TestNG framework

Problem Statement :

Read multiple user's credentials from excel file and login to application one by one.

In last session we have created one project i.e. TestNGDemo, so  that project only will take for the today's demo.

To watch last session open mentioned link : TestNG Demo

And to watch today's session on YouTube open mentioned link : DDT Session

Solution :

For the demo will create two classes and using one input file as mentioned below :

1. LoginDemo.java 

2. ExcelReadingMethods.java

3. Input file LoginData.xlsx

1. LoginDemo.java  //This class is for writing the Test Cases logic

package com.techhelpline.testCases;

import java.io.IOException;

import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;

import org.openqa.selenium.NoAlertPresentException;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

import org.openqa.selenium.chrome.ChromeOptions;

import org.testng.Assert;

import org.testng.annotations.AfterClass;

import org.testng.annotations.BeforeClass;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

import com.techhelpline.utilities.ExcelReadingMethods;

import io.github.bonigarcia.wdm.WebDriverManager;

public class LoginDemo {

// In this class we have to create 4 sub methods

// 1. Test case method

// 2. BeforeClass

// 3. AfterClass

// 4. to get data from input file

// so let's create one by one

WebDriver driver;

ChromeOptions options;

String appURL = "https://demo.guru99.com/v4/";

String inputFilename = "./" + "src\\test\\java\\com\\techhelpline\\testData\\LoginData.xlsx";

String sheetname = "Sheet1";

@Test(dataProvider = "userdata")

public void toLogin(String username, String password) throws InterruptedException {

System.out.println("In Login Method!!!!");

System.out.println("username is: " + username);

System.out.println("password is: " + password);

driver.findElement(By.name("uid")).sendKeys(username);

driver.findElement(By.name("password")).sendKeys(password);

driver.findElement(By.name("btnLogin")).click();

Thread.sleep(2000);

System.out.println("Alert flag is: " + alertExist());

if (alertExist() == true) {

System.out.println("Alert is present!!!");

driver.switchTo().alert().accept();

Thread.sleep(4000);

System.out.println("Alert is closed!!!");

Assert.assertTrue(true);

} else {

System.out.println("In else");

Thread.sleep(3000);

driver.findElement(By.xpath("/html/body/div[3]/div/ul/li[15]/a")).click();

Thread.sleep(4000);

driver.switchTo().alert().accept();

Thread.sleep(3000);

System.out.println("Alert is closed!!!!");

Assert.assertTrue(true);

}

}

public boolean alertExist() {

boolean alertFlag = false;

try {

driver.switchTo().alert();

alertFlag = true;

} catch (NoAlertPresentException e) {

e.printStackTrace();

}

return alertFlag;

}

@BeforeClass

public void setup() {

System.out.println("In setup method!!!!");

WebDriverManager.chromedriver().setup();

options = new ChromeOptions();

options.addArguments("--ignore-certificate-errors");

driver = new ChromeDriver(options);

driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);

driver.get(appURL);

driver.manage().window().maximize();

}

@AfterClass

public void tearDown() {

System.out.println("In tear down method");

if (driver != null) {

driver.close();

driver.quit();

}

}

@DataProvider(name = "userdata")

public String[][] getData() throws IOException {

System.out.println("In get data method!!!!");

int rowcount = ExcelReadingMethods.getRowCount(inputFilename, sheetname);

System.out.println("Rowcount is: " + rowcount);

int cellcount = ExcelReadingMethods.getCellData(inputFilename, sheetname, 0);

System.out.println("Cell count is: " + cellcount);

String loginData[][] = new String[rowcount][cellcount];

for (int row = 1; row <= rowcount; row++) {

for (int cell = 0; cell < cellcount; cell++) {

loginData[row - 1][cell] = ExcelReadingMethods.getCellData(inputFilename, sheetname, row, cell);

}

}

return loginData;

}

}

2. ExcelReadingMethods.java  // This class is for writing the excel reading methods

package com.techhelpline.utilities;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.ss.usermodel.DataFormatter;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReadingMethods {

// So in this class will create all methods as mentioned below

// 1. get row count of excel

// 2. get cell count

// 3. get cell data

// so let's create one by one

public static FileInputStream fileInputStream;

public static FileOutputStream fileOutputStream;

public static XSSFWorkbook workbook;

public static XSSFSheet sheet;

public static XSSFRow row;

public static XSSFCell cell;

public static int getRowCount(String filename, String sheetName) throws IOException {

fileInputStream = new FileInputStream(filename);

workbook = new XSSFWorkbook(fileInputStream);

sheet = workbook.getSheet(sheetName);

int rowscount = sheet.getLastRowNum();

workbook.close();

fileInputStream.close();

return rowscount;

}

public static int getCellData(String filename, String sheetName, int rowNum) throws IOException {

fileInputStream = new FileInputStream(filename);

workbook = new XSSFWorkbook(fileInputStream);

sheet = workbook.getSheet(sheetName);

row = sheet.getRow(rowNum);

int cellCount = row.getLastCellNum();

workbook.close();

fileInputStream.close();

return cellCount;

}

public static String getCellData(String filename, String sheetName, int rowNum, int colNum) throws IOException {

fileInputStream = new FileInputStream(filename);

workbook = new XSSFWorkbook(fileInputStream);

sheet = workbook.getSheet(sheetName);

row = sheet.getRow(rowNum);

cell = row.getCell(colNum);

String data;

try {

DataFormatter dataFormatter = new DataFormatter();

String cellData = dataFormatter.formatCellValue(cell);

return cellData;

} catch (Exception e) {

data = "";

}

workbook.close();

fileInputStream.close();

return data;

}

}

Input File Data:

So guys we have successfully completed the demo and I hope you guys 

understand.

If any suggestion please do comment in chat box and please subscribe our channel.

Thanks....

:)