top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-
Search

Data Driven Testing In Cucumber Using Excel


Data Driven framework is used to drive test cases and suites from an external data feed. The data feed can be data sheets like xls, xlsx, and csv files. A Data Driven Framework in Selenium is a technique of separating the “data set” from the actual “test case” (code).



Importance of Data Driven Testing:


Data driven testing helps keeping data separate from test scripts and the same test scripts can be executed for different combinations of input test data and test results can be generated efficiently.


Advantages of Data Driven Testing:

  • Multiple sets of data can be tested with ease during regression testing

  • Confidential data need not be hard coded

  • Can be reused in different tests

  • Maintenance and management of test scripts is easy

  • Time saving, efficient and reduce costs

  • Same test cases can be executed several times which helps to reduce test case and scripts

Framework for Cucumber BDD using Data Driven Testing:



Framework view at Eclipse Level


Performing Data Driven Testing with Example:


Step 1: Create an excel file with test data and have it saved at project level under the folder named Data Files*


Test Data in Excel:



Step 2: Add required dependencies to the pom.xml file


Dependencies for Data Driven Testing:


Handling .xls Files using Apache POI

Handling .xlsx Files using Apache POI



Note: Please make sure versions of both the dependencies are the same.


Step 3: Create feature file with gherkins for the scenarios which needs to be tested


Feature File (Gherkin):


Scenario Outline: Validating Login process for User with valid data

Given User is on login page

When User clicks on Login button with entering valid Username “<SheetName>” and Password <Rownumber>

Then User should land on home page

Examples:

| SheetName | Rownumber |

| Test Data | 0 |


Scenario Outline: Validating Login process for User with invalid data

Given User is on login page

When User clicks on Login button with entering invalid Username “<SheetName>” and Password <Rownumber>

Then User should not land on home page

Examples:

| SheetName | Rownumber |

| Test Data | 1 |




Step 4: Create PageObjects Class.


Page Object Class:


import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.support.PageFactory;


public class Login {

WebDriver driver;

public Login (WebDriver driver) {

this.driver=driver;

PageFactory.initElements(driver, this);

}

By user_name = By.id("username");

By pass_word= By.id("password");

By login = By.xpath("//button[@type='submit']");

public void setuser (String Username) {

driver.findElement(user_name).sendKeys(Username);

}

public void setpassword (String Password) {

driver.findElement(pass_word).sendKeys(Password);

}

public void clickloginbtn (){

driver.findElement(login).click();

}

}


Step 5: Create StepDefinition File and add all the unimplemented steps.


StepDefinition File:


import java.io.IOException;

import org.testng.Assert;

import com.LMS.base.BaseClass;

import com.LMS.pageObjects.Login;

import com.LMS.utilities.ReadConfig;

import io.cucumber.java.After;

import io.cucumber.java.Before;

import io.cucumber.java.en.Given;

import io.cucumber.java.en.Then;

import io.cucumber.java.en.When;


public class LoginStepdefinition extends BaseClass{

Webdriver driver;

Login login=new Login(driver);


@Given("User is on login page")

public void user_is_on_login_page() throws IOException, InterruptedException {

login.driver.get(“URL”);

login.logger.info("Application launched");

}


@When("User clicks on Login button with entering valid {string} and {string}")

public void user_clicks_on_login_button_with_entering_valid_and(String Username, String Password) throws InterruptedException {

ExcelReader excelReader = new ExcelReader();

List<Map<String,String>> LoginData=

excelReader.getData(ReadConfig.EXCEL, sheetName);

String Username= LoginData.get(rowNumber).get("Username");

String Password= LoginData.get(rowNumber).get("Password");


login.setuser(Username);

login.setpassword(Password);

login.clickloginbtn();

}


@Then("User should land on home page")

public void user_should_land_on_home_page() {

if (driver.getCurrentUrl().equals("URL"))

{

Assert.assertTrue(true);

logger.info("User logged successfully");

}

else

}

Assert.assertTrue(false);

logger.info("login unsuccessful");

}

}



@When("User clicks on Login button with entering Invalid {string} and {string}")

public void user_clicks_on_login_button_with_entering_Invalid_and(String Username, String Password) throws InterruptedException {

ExcelReader excelReader = new ExcelReader();

List<Map<String,String>> LoginData=

excelReader.getData(ReadConfig.EXCEL, sheetName);

String Username= LoginData.get(rowNumber).get("Username");

String Password= LoginData.get(rowNumber).get("Password");


login.setuser(Username);

login.setpassword(Password);

login.clickloginbtn();

}


@Then("User should not land on home page")

public void user_should_not_land_on_home_page() {

if (driver.getCurrentUrl().equals("URL"))

{

Assert.assertTrue(true);

logger.info("User logged successfully");

}

else

}

Assert.assertTrue(false);

logger.info("login unsuccessful");

}

}

}


Step 6: Create Excel Reader file


Excel Reader File:


We create multiple methods which are required for extracting data from excel sheets and keep them in Excel Reader. This Reader can be used to read the data from the file.


import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;


import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

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

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

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

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

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

import org.apache.poi.ss.util.NumberToTextConverter;


public class ExcelReader {


public List<Map<String, String>> getData(String excelFilePath, String sheetName)

throws InvalidFormatException, IOException {

Sheet sheet = getSheetByName(excelFilePath, sheetName);

return readSheet(sheet);

}


public List<Map<String, String>> getData(String excelFilePath, int sheetNumber)

throws InvalidFormatException, IOException {

Sheet sheet = getSheetByIndex(excelFilePath, sheetNumber);

return readSheet(sheet);

}


private Sheet getSheetByName(String excelFilePath, String sheetName) throws IOException, InvalidFormatException {

Sheet sheet = getWorkBook(excelFilePath).getSheet(sheetName);

return sheet;

}


private Sheet getSheetByIndex(String excelFilePath, int sheetNumber) throws IOException, InvalidFormatException {

Sheet sheet = getWorkBook(excelFilePath).getSheetAt(sheetNumber);

return sheet;

}


private Workbook getWorkBook(String excelFilePath) throws IOException, InvalidFormatException {

return WorkbookFactory.create(new File(excelFilePath));

}


private List<Map<String, String>> readSheet(Sheet sheet) {

Row row;

int totalRow = sheet.getPhysicalNumberOfRows();

List<Map<String, String>> excelRows = new ArrayList<Map<String, String>>();

int headerRowNumber = getHeaderRowNumber(sheet);

if (headerRowNumber != -1) {

int totalColumn = sheet.getRow(headerRowNumber).getLastCellNum();

int setCurrentRow = 1;

for (int currentRow = setCurrentRow; currentRow <= totalRow; currentRow++) {

row = getRow(sheet, sheet.getFirstRowNum() + currentRow);

LinkedHashMap<String, String> columnMapdata = new LinkedHashMap<String, String>();

for (int currentColumn = 0; currentColumn < totalColumn; currentColumn++) {

columnMapdata.putAll(getCellValue(sheet, row, currentColumn));

}

excelRows.add(columnMapdata);

}

}

return excelRows;

}


private int getHeaderRowNumber(Sheet sheet) {

Row row;

int totalRow = sheet.getLastRowNum();

for (int currentRow = 0; currentRow <= totalRow + 1; currentRow++) {

row = getRow(sheet, currentRow);

if (row != null) {

int totalColumn = row.getLastCellNum();

for (int currentColumn = 0; currentColumn < totalColumn; currentColumn++) {

Cell cell;

cell = row.getCell(currentColumn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

if (cell.getCellType() == CellType.STRING) {

return row.getRowNum();


} else if (cell.getCellType() == CellType.NUMERIC) {

return row.getRowNum();


} else if (cell.getCellType() == CellType.BOOLEAN) {

return row.getRowNum();

} else if (cell.getCellType() == CellType.ERROR) {

return row.getRowNum();

}

}

}

}

return (-1);

}


private Row getRow(Sheet sheet, int rowNumber) {

return sheet.getRow(rowNumber);

}


private LinkedHashMap<String, String> getCellValue(Sheet sheet, Row row, int currentColumn) {

LinkedHashMap<String, String> columnMapdata = new LinkedHashMap<String, String>();

Cell cell;

if (row == null) {

if (sheet.getRow(sheet.getFirstRowNum()).getCell(currentColumn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(currentColumn)

.getStringCellValue();

columnMapdata.put(columnHeaderName, "");

}

} else {

cell = row.getCell(currentColumn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

if (cell.getCellType() == CellType.STRING) {

if (sheet.getRow(sheet.getFirstRowNum())

.getCell(cell.getColumnIndex(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())

.getStringCellValue();

columnMapdata.put(columnHeaderName, cell.getStringCellValue());

}

} else if (cell.getCellType() == CellType.NUMERIC) {

if (sheet.getRow(sheet.getFirstRowNum())

.getCell(cell.getColumnIndex(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())

.getStringCellValue();

columnMapdata.put(columnHeaderName, NumberToTextConverter.toText(cell.getNumericCellValue()));

}

} else if (cell.getCellType() == CellType.BLANK) {

if (sheet.getRow(sheet.getFirstRowNum())

.getCell(cell.getColumnIndex(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())

.getStringCellValue();

columnMapdata.put(columnHeaderName, "");

}

} else if (cell.getCellType() == CellType.BOOLEAN) {

if (sheet.getRow(sheet.getFirstRowNum())

.getCell(cell.getColumnIndex(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())

.getStringCellValue();

columnMapdata.put(columnHeaderName, Boolean.toString(cell.getBooleanCellValue()));

}

} else if (cell.getCellType() == CellType.ERROR) {

if (sheet.getRow(sheet.getFirstRowNum())

.getCell(cell.getColumnIndex(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

.getCellType() != CellType.BLANK) {

String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())

.getStringCellValue();

columnMapdata.put(columnHeaderName, Byte.toString(cell.getErrorCellValue()));

}

}

}

return columnMapdata;

}


}



2,274 views1 comment

1 комментарий

Оценка: 0 из 5 звезд.
Еще нет оценок
Добавить рейтинг
Shilpa Mudagal
Shilpa Mudagal
13 июл. 2023 г.
Оценка: 4 из 5 звезд.

Very detailed , was helpful

Лайк
bottom of page