Run selenium test using excel sheet as test data source

In one of our previous post, we have seen how to read write excel sheet using java POI.

Now we will see the practical usage of excel sheet as test data source for selenium tests.

Excel sheet is considered as best way to store multiple test data or larger test data because of it’s row, column and sheet structure.

Let’s consider below site as our AUT registration form screen, where we need to enter multiple set of test data and fill the fields.

https://qavbox.github.io/demo/signup/

Our excel sheet looks some what like below – [for demo purpose, we are using couple of fields only], 2 sets of data are presented as 2 rows in the excel sheet, we need to enter each set of data one by one

Now we will use the method getCellData(String columnName, int rownum) to fetch the excel data and use it on application screen

Let’s see code implementation

Required maven dependencies in pom.xml – Refer pom.xml

• Selenium
• Apache POI
• TestNG
• WebDriverManager

Steps –

• Launch the application
• Based on the no. of rows we have data in excel sheet “Registration”, let’s loop
• Each loop, we will fetch data and enter into application

package selExcel;

import io.github.bonigarcia.wdm.WebDriverManager;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.Select;
import org.testng.annotations.Test;

import java.io.IOException;

public class SelTestData {

    WebDriver driver;

    @Test
    public void Test1() throws InterruptedException, IOException {
        //System.setProperty("webdriver.chrome.driver", "/Users/skpatro/sel/chromedriver");
        WebDriverManager.chromedriver().setup();
        driver = new ChromeDriver();

        driver.get("https://qavbox.github.io/demo/signup/");

        ExcelReader excelReader = new ExcelReader();
        excelReader.setExcelFile("./testdata.xlsx", "Registration");

        //Identify the fields
        WebElement fullName = driver.findElement(By.id("username"));
        WebElement email = driver.findElement(By.id("email"));
        WebElement telephone  = driver.findElement(By.id("tel"));
        WebElement gender = driver.findElement(By.name("sgender"));
        Select s = new Select(gender);

        for(int i=1; i<excelReader.getRows();i++){
            fullName.clear();
            fullName.sendKeys(excelReader.getCellData("fullname", i));

            email.clear();
            email.sendKeys(excelReader.getCellData("email", i));

            telephone.clear();
            telephone.sendKeys(excelReader.getCellData("telephone", i));
            s.selectByVisibleText(excelReader.getCellData("gender", i));

            //Do save operation or submit as your application supports to save data
            Thread.sleep(3000);

        }


        Thread.sleep(1000);
        driver.quit();
    }
}

Explanation

1st, we have identified all the elements on which we need to perform operation, example – email , telephone etc…

Then in for loop, we have 1st fetched no. of rows excel sheet has data, based on the loop

Note – getRows() will return the int including the header, so we have started the loop from 1

in side the for loop, we performed the element actions like entering text & selecting value based on the test data.

After all the test data entered, you can either save or submit based on the application feature to save the values into database for future validation [intentionally left this as we are using dummy application]

Hope this above code implementation provides a basic idea on how to use the test data in selenium tests.