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

How to Read Data From Excel Sheet in BDD cucumber Framework

Hello Everyone,This is My first Blog . I would like to share my knowledge on how to perform DD Testing on Cucumber Framework. This blog gives you insight on how to read input data from Excel sheet using Excel Reader class. We implemented this method for "DSAlgo project" testing.


What is DataDriven Testing?


Data-driven testing is a Test Automation Framework where the data that ‘drives’ the testing is not hard-coded but taken from a table external to the source code and used by the test scripts during execution.


Example: Excel sheets, database tables, arrays, and so on. Excel files and are presented as a table. This lets you modify them easily. If you need to add more data, you simply modify the file either in any text editor or in Microsoft Excel (in case of hard-coded values, you should modify both data and code).

This is our sample Excel sheet we used in DSAlgo project for sign in page.


ExcelSheet:

Login
.xlsx
Download XLSX • 7KB

Sample data:

username

password

expectedmessage

Numpysdet84

user

Please check your password

username

sdet84batch

​Please check your user id

Numpysdet84

sdet84batch

You are logged in

Numpysdet86

sdet86batch

Invalid Username and Password

Save your file on ExcelData Folder under src/test/resources













POM file :

We need to add the following dependencies in .pom file inorder to read data from Excel sheet.


<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>5.2.3</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>5.2.3</version>

</dependency


ExcelReader Java Class:

We keep all utility files under Utility Pacakge.

















ExcelReader
.txt
Download TXT • 2KB

Feature File:

Let's see how we used excel data for the sign in feature file.


@TS_signin_03

Scenario Outline: User on login page and login with invalid and valid inputs from Excel "<Sheetname>" and <RowNumber>

Given The user is on signin page

When The user enter sheet "<Sheetname>" and <RowNumber>

Then click login button


Examples:

| Sheetname | RowNumber |

| Sheet1 | 0 |

| Sheet1 | 1 |

| Sheet1 | 2 |

| Sheet1 | 3 |


StepDefintion Class:


The corresponding step Defintion class for the Sign in feature file,

Given:


// User is on Sigin page


@Given("The user is on signin page")

public void the_user_is_on_signin_page() {


Loggerload.info("User is on Sigin Page");

sign.login_page();

}


WHEN:


// User read both invalid and valid data from excel


@When("The user enter sheet {string} and {int}")

public void the_user_enter_sheet_and(String sheetname, Integer rownumber)

throws InvalidFormatException, IOException {


ExcelReader reader = new ExcelReader();


List<Map<String, String>> testdata = reader.getData(Excelpath, sheetname);

username = testdata.get(rownumber).get("username");

password = testdata.get(rownumber).get("password");

message = testdata.get(rownumber).get("expectedmessage");


Loggerload.info("User Enter username as \" " + username + " \"and Password as \" " + password + "\" ");

if (username != null || password != null)

sign.doLogin(username, password);


}


Using above method, we need to retrive data from Excel sheet and pass it to the "Signin- Page" class to verify the the given inputs.


Signin Page Class :


@FindBy (xpath="//*[@id='id_username']")static WebElement user;

@FindBy (xpath="//*[@id='id_password']")static WebElement pwd;


public Boolean doLogin(String username, String password) {


user.clear();

user.sendKeys(username);

pwd.clear();

pwd.sendKeys(password);


// To check empty fields , we need to check "required" field is on an attribute

if (username.isBlank()) {

JavascriptExecutor js_user = (JavascriptExecutor) driver;

isRequired = (Boolean) js_user.executeScript("return arguments[0].required;", user);

return isRequired;

} else if (password.isBlank()) {

JavascriptExecutor js_password = (JavascriptExecutor) driver;

isRequired = (Boolean) js_password.executeScript("return arguments[0].required;", pwd);

return isRequired;


}

return isRequired;

}


Here, we pass the values from excel sheet to username and password fields. We assert the value in "Then " step as follows,


THEN :


// User get an error message when login with invalid data and login successfully for valid data


@Then("click login button")

public void click_login_button() {


Loggerload.info("User clicks on login button");

Loggerload.info("Expected Message - Excel Sheet : " + message);

String msg = sign.click_login();

Loggerload.info("Actual Message : " + msg);

assertEquals(msg, message);


}

In this way we can read n number of data from External file. This how we can achieve DataDriven testing in Cucumber Framework.


Thank you for reading and give your valuable comments to improvise.

225 views0 comments

Recent Posts

See All
bottom of page