Data Driven Testing In Selenium | Data-Driven Testing Examples

Data Driven Framework in Selenium

By Neha Vaidya, Community Contributor – February 8, 2023

As code is added, the number of tests must be increased to ensure that the new code works smoothly. This situation can easily become burdensome. Eventually, testers may give up, reducing testing and opening the path to defective code. With data-driven tests, it is possible to avoid such a scenario.

This article covers why data-driven tests are important. It will discuss steps on how to data-drive your tests, as well as some dos and don’ts.

Why Data Driven Tests important?

There are two main benefits to this:

• They reduce the cost of adding new tests and changing them when your business rules change. This is done by creating parameters for different scenarios, using data sets that the same code can run against.
• They help to identify what data is most important for tested behavior. By separating first-class scenario data into parameters, it becomes clear what matters most to the test. This makes it easy to remember how something works when developers need to change it.

What is Data Driven Testing Framework in Selenium?

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). Since the test case is separated from the data set, one can easily modify the test case of a particular functionality without making changes to the code.

For example, if one has to modify the code for login functionality, they can modify just the login functionality instead of having to modify any other feature dependent on the same code.

One can easily increase the number of test parameters by adding more username and password fields to the excel file (or other sources). Know more about different types of test driven development with the help of this article on TDD vs BDD.

Now let’s understand how to create a test case using a Data Driven Framework.

Data Driven Testing Example

This example will demonstrate how to read the data from excel files and perform data driven testing using Selenium. WebDriver does not directly support data reading of excel files. Therefore, one needs to use a plugin such as Apache POI for reading/writing on any Microsoft office document.

• To download Apache POI Jar files click here. Download the zip file or tar file as per requirement and place them along with the set of Selenium JARs and configure your build path.

Now let’s understand how to write the first test case. An excel file to read the data from the sheet. The user has entered different combinations of username and password in the sheet.

The task here is to enter all the combinations of username and passwords into the login field in order to test the functionality. Let’s see how to do that.

Here, the target is to enter all these combinations of username and password into the Browserstack Sign in page as shown below.

Let’s write a code snippet to read the data files.

Step 1: Go to the Eclipse IDE and create a project. Add all the dependencies for TestNG, Selenium and Apache POI.

Step 2: Create a class file to write the functionality.

import org.openqa.selenium.By; import org.testng.Assert; import org.testng.annotations.AfterMethod; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class ExcelExample{ @Test(dataProvider=”testdata”) public void demoClass(String username, String password) throws InterruptedException { System.setProperty(“webdriver.chrome.driver”, “Path of Chrome Driver”); Webdriver driver = new ChromeDriver(); driver.get(”

In the above code, there is a “TestDataExample() method” in which the user has created an object instance of another class named “ReadExcelFile”. The user has mentioned the path to the excel file. The user has further defined a for loop to retrieve the text from the excel workbook. But to fetch the data from the excel file, one needs to write a class file for the same.

Try Running Selenium Tests on Cloud for Free

import java.io.File; import java.io.FileInputStream; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadExcelFile{ XSSFWorkbook work_book; XSSFSheet sheet; public ReadExcelFile(String excelfilePath) { try { File s = new File(excelfilePath); FileInputStream stream = new FileInputStream(s); work_book = new XSSFWorkbook(stream); } catch(Exception e) { System.out.println(e.getMessage()); } } public String getData(int sheetnumber, int row, int column){ sheet = work_book.getSheetAt(sheetnumber); String data = sheet.getRow(row).getCell(column).getStringCellValue(); return data; } public int getRowCount(int sheetIndex){ int row = work_book.getSheetAt(sheetIndex).getLastRowNum(); row = row + 1; return row; }

In the code above, the user has used Apache POI libraries to fetch the data from the excel file. Next, it will point to the data present in the excel file and then enter the relevant username and password to the sign in page.

Note: The same thing can be done using a Data provider in TestNG. But to fetch the data from the Excel sheet, the user needs Apache POI jar files.

Note: Please enter one valid credential to test.

Advantages of Data Driven Testing Framework

1. Allows testing of the application with multiple sets of data values during regression testing
2. Separates the test case data from the executable test script
3. Allows reusing of Actions and Functions in different tests
4. Generates test data automatically. This is helpful when large volumes of random test data are necessary
5. Results in the creation of extensive code that is flexible and easy to maintain
6. Lets developers and testers separate the logic of their test cases/scripts from the test data
7. Allows execution of test cases several times which helps to reduce test cases and scripts
8. It does not let changes in test scripts affect the test data.

By incorporating data-driven testing using Selenium, testers can refine their test cases for more efficient execution. This shortens timelines, makes their lives easier and results in more thoroughly tested and better quality software.

Data Driven Testing Framework

Bài đăng này đã không được cập nhật trong 5 năm

Chào các bạn hôm nay mình sẽ giới thiệu đến các bạn một Framwork quen thuộc khi làm việc với SeleniumWebdriver : Data Driven Framwork. Do mình vừa tìm hiểu vừa viết bài này nên có gì sai sót mong các bạn hãy comment để góp ý thêm cho mình
Nội dung của bài gồm

1. Data Driven Testing là gì
2. Tại sao lại sử dụng Data Driven Testing
3. Như nào để tạo một Data Driven Automation Framework
4. Ưu điểm của Data- Driven testing
5. Nhược điểm của Data – Driven testing
6. Một vài ví dụ về Data Driven testing

Step 2: Configure Environment Variable

After installing Java, you should specify the Java path to the system. To do so:Open System -> Advanced System Properties -> Advanced -> Environment Variables.

For ‘user variables for User’, enter:

• C:Program FilesJavajdk-14.0.2

For the System Variables path, enter the bin folder path:

• C:Program FilesJavajdk-14.0.2bin

Now, Java is ready for use.

How to create Data Driven Framework in Selenium WebDriver using Apache POI

In this section of this Selenium Java tutorial, we look at how to perform read, write, and update operations on Excel files that are available in the .xls and .xlsx formats. Here are the prerequisites for implementing the Data Driven Framework in Selenium:

• IDE – IntelliJ IDEA (or Eclipse IDE)
• Framework – TestNG
• MS Excel – .xls and .xlsx files that would contain the data for realizing data driven testing

For demonstration, we create a Data Driven Project with Maven with the following structure:

The following class files are created under the package named ‘DataDrivenFramework’:

• test_DataDrivenFramework.java
• test_LT_DataDrivenFramework.java

The tests demonstrating Data Driven Framework in Selenium WebDriver are run on cloud-based online Selenium Grid by LambdaTest. To get started with LambdaTest, we create a profile on LambdaTest and note the user name & access key available on the LambdaTest profile page. Then, the Desired Capabilities for the browser & OS combination under test is generated using the LambdaTest Capabilities Generator, and the respective tests are run on LambdaTest’s Selenium 4 Grid.

Shown below is pom.xml (that contains the required dependencies) and testng.xml (that contains the test classes to be run):

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd”> 4.0.0 org.ddf DataDrivenFramework 1.0-SNAPSHOT org.testng testng 6.9.10 test org.slf4j slf4j-nop 1.7.28 test org.seleniumhq.selenium selenium-java 4.0.0-alpha-7 org.seleniumhq.selenium selenium-remote-driver 4.0.0-alpha-7 org.seleniumhq.selenium selenium-chrome-driver 4.0.0-alpha-7 junit junit 4.12 test org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2 test install maven-compiler-plugin 3.0 1.8 1.8 org.apache.maven.plugins maven-compiler-plugin 11 11

10 11 12

Now, let’s look at the different ways in which Data Driven Framework in Selenium is used along with Apache POI for running automated browser testing scenarios.

Read and Write data from & to Excel sheets (.xls) in Selenium using Apache POI

For performing CRUD operations on MS Excel sheets in the .xlsx format, we use the XSSF implementation provided by the Apache POI library.

Test Scenario

1. (a) 🡪 Search for ‘LambdaTest’ on Bing.com
2. (b) 🡪 Search for ‘LambdaTest Blog’ on Bing.com
3. Click on the First test result
4. Assert if the current page title does not match with the expected page title
5. Append the corresponding test result in the cell next to the test case

Here is the content of the MS Excel File (i.e., Test_1.xls) that contains details of the test scenario:

Implementation

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129

package com.DataDrivenFramework; import java.io.*; import java.net.MalformedURLException; import java.net.URL; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openqa.selenium.*; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.remote.DesiredCapabilities; import org.openqa.selenium.remote.RemoteWebDriver; import org.testng.Assert; import org.testng.annotations.AfterClass; import org.testng.annotations.BeforeTest; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class test_DataDrivenFramework WebDriver driver; XSSFWorkbook workbook; Sheet sheet; Cell cell; String username = “user-name”; String access_key = “access-key”; @BeforeTest public void init() throws InterruptedException, MalformedURLException DesiredCapabilities capabilities = new DesiredCapabilities(); capabilities.setCapability(“build”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“name”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“platformName”, “OS X Yosemite”); capabilities.setCapability(“browserName”, “MicrosoftEdge”); capabilities.setCapability(“browserVersion”,”81.0″); capabilities.setCapability(“tunnel”,false); capabilities.setCapability(“network”,true); capabilities.setCapability(“console”,true); capabilities.setCapability(“visual”,true); driver = new RemoteWebDriver(new URL(“http://” + username + “:” + access_key + “@hub.lambdatest.com/wd/hub”), capabilities); System.out.println(“Started session”); @Test (description = “Testing data input from XLS (i.e. HSSF)”, priority = 1, enabled = true) public void Test_ddf_hssf_input() throws IOException, InterruptedException HSSFWorkbook workbook; String test_url = null; WebElement lt_link = null; String exp_title = null; /* Import Excel Sheet */ File src=new File(“C:\\Folder\\Test_1.xls”); /* Load the file */ FileInputStream fis = new FileInputStream(src); /* Load the workbook */ workbook = new HSSFWorkbook(fis); /* Load the sheet in the workbook */ /* Index = 0 –> Tab – 1 */ sheet = workbook.getSheetAt(0); for(int counter = 1; counter <= sheet.getLastRowNum(); counter++) /* Row – 0 –> Contains the site details and search term */ /* Hence, we skip that row */ cell = sheet.getRow(counter).getCell(0); /* Cell [1,0] contains the test URL */ if (cell.getCellType() == CellType.STRING) test_url = cell.getStringCellValue(); driver.get(test_url); /* Cell [1,1] –> Search Term */ cell = sheet.getRow(counter).getCell(1); if (cell.getCellType() == CellType.STRING) String search_string = cell.getStringCellValue(); /* Let’s perform the search operation */ try /* Enter the search term in the Google Search Box */ WebElement search_box = driver.findElement(By.xpath(“//input[@id=’sb_form_q’]”)); search_box.sendKeys(search_string + Keys.ENTER); Thread.sleep(3000); if (search_string.equalsIgnoreCase(“LambdaTest”)) { lt_link = driver.findElement(By.xpath(“//a[.=’Most Powerful Cross Browser Testing Tool Online | LambdaTest’]”)); exp_title = “Most Powerful Cross Browser Testing Tool Online | LambdaTest”; } else if (search_string.equalsIgnoreCase(“LambdaTest Blog”)) { lt_link = driver.findElement(By.xpath(“//a[.=’LambdaTest | A Cross Browser Testing Blog’]”)); exp_title = “LambdaTest | A Cross Browser Testing Blog”; if (lt_link!= null) lt_link.click(); Thread.sleep(3000); String curr_window_title = driver.getTitle(); Assert.assertEquals(curr_window_title, exp_title); /* Write the result in the excel sheet */ /* Write Data in the Result Column */ FileOutputStream fos = new FileOutputStream(src); String message = “Passed”; sheet.getRow(counter).createCell(2).setCellValue(message); workbook.write(fos); fos.close(); } catch (Exception e) { System.out.println(e.getMessage()); @AfterClass public void tearDown() if (driver != null) driver.quit();

Code Walkthrough

1. Import the packages that contain the methods and interfaces for performing operations on .xls file

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

2. The method implemented under the @BeforeTest annotation sets the desired browser capabilities. A RemoteWebDriver instance is created with the desired browser capabilities, with the Selenium Grid URL set to the cloud-based Selenium Grid on LambdaTest [ @hub.lambdatest.com/wd/hub ].

10 11 12

@BeforeTest public void init() throws InterruptedException, MalformedURLException DesiredCapabilities capabilities = new DesiredCapabilities(); capabilities.setCapability(“build”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“name”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“platformName”, “OS X Yosemite”); capabilities.setCapability(“browserName”, “MicrosoftEdge”); capabilities.setCapability(“browserVersion”,”81.0″); capabilities.setCapability(“tunnel”,false); driver = new RemoteWebDriver(new URL(“http://” + username + “:” + access_key + “@hub.lambdatest.com/wd/hub”), capabilities);

3. Create a new workbook of type HSSFWorkbook. It will be further used when accessing the Sheet and reading/writing at appropriate cells in the Sheet.

public void Test_ddf_hssf_input() throws IOException, InterruptedException HSSFWorkbook workbook;

4. Create a Workbook object by referring to the FileInputStream object that points to the location where the Excel File (.xls) is located on the host machine.

File src=new File(“C:\\Folder\\Test_1.xls”); FileInputStream fis = new FileInputStream(src);

5. Load the Excel Workbook using the FileInputStream object obtained from Step (4).

workbook = new HSSFWorkbook(fis);

6. Using the Workbook object, we access the Sheet at index ‘0’ using the getSheetAt method.

sheet = workbook.getSheetAt(0);

7. Row zero contains the Header (or title) in the sheet. Hence, that row is ignored. Cell (0,0) 🡪 Test URL, Cell (0, 1) 🡪 Search Term, and Cell (0,2) 🡪 Test Result. A for loop starting from 1 to the total number of rows (i.e., 2 in this case) obtained using getLastRowNum method of the Sheet class is run for executing the test scenarios 1(a) and 1(b).

for(int counter = 1; counter <= sheet.getLastRowNum(); counter++)

8. The getRow method returns the logical row number [i.e. 1 for test scenario -1(a) and 2 for test scenario – 1(b)]. Once we have the row number, the getCell(cell-number) method of the HSSFRow object is used for accessing the specific cell.

Shown below are the contents of the cells on Row – 1:

• Cell (1,0 ) 🡪https://www.bing.com
• Cell (1,1) 🡪 LambdaTest
• Cell (1,2) 🡪 Result (Test Status updated post test execution)

cell = sheet.getRow(counter).getCell(0);

9. Cell (1,0) contains the URL under test. Using the Cell obtained from step (8), the getStringCellValue method gets the value of the cell as a String. Next, we navigate to the test URL.

if (cell.getCellType() == CellType.STRING) test_url = cell.getStringCellValue(); driver.get(test_url);

10. The required search terms are located in the following Cells:

• Cell (1,1) 🡪 LambdaTest
• Cell (2,1) 🡪 LambdaTest Blog

The getRow method returns the logical row number. For example, row number 1 represents Test Scenario – 1(a) and row number 2 represents Test Scenario – 1(b). Cell number remains unchanged. Hence it is hard coded to ‘1’. Finally, the search string is retrieved from the Cell using the getStringCellValue() method.

cell = sheet.getRow(counter).getCell(1); String search_string = cell.getStringCellValue();

11. Now that we have the search string from the previous step, it is entered in the search box, and an Enter key is pressed to initiate the search operation. For test scenario 1(a), the search term is ‘LambdaTest,’ and for scenario 1(b), the search term is ‘LambdaTest Blog.’

Though the search terms for both tests are different, the overall execution path remains unchanged.

WebElement search_box = driver.findElement(By.xpath(“//input[@id=’sb_form_q’]”)); search_box.sendKeys(search_string + Keys.ENTER);

12. Depending on the search term, the link that appears as the first result also changes. A case insensitive string comparison is done with the search term and based on the comparison result, and the top result link is located using the XPath property of the WebElement.

Also Read – XPath In Selenium With Examples

Test Scenario 1(a)

lt_link = driver.findElement(By.xpath(“//a[.=’Most Powerful Cross Browser Testing Tool Online | LambdaTest’]”)); exp_title = “Most Powerful Cross Browser Testing Tool Online | LambdaTest”;

Test Scenario 1(b)

lt_link = driver.findElement(By.xpath(“//a[.=’LambdaTest | A Cross Browser Testing Blog’]”)); exp_title = “LambdaTest | A Cross Browser Testing Blog”;

13. A click operation is performed on the WebElement that points to the first test result on the Bing Search page.

if (lt_link!= null) lt_link.click();

14. Assert is thrown if the Page Title does not match with the expected page title.

String curr_window_title = driver.getTitle(); Assert.assertEquals(curr_window_title, exp_title);

15. We use the Java FileOutputStream class, an output stream used for writing data in the file. The Excel Workbook, which has the test data (i.e., Path_To_File\File.xls), is opened to write the test result.

• Test Scenario 1(a) – Test result to be written to Cell (1,2)
• Test Scenario 1(b) – Test result to be written to Cell (2,2)

Using the required row number, we create a new Cell using the createCell method. In our case, the Cell number where the test result is to be written is 2. Then, in the Cell (inline with the corresponding test scenario), we write the test result (in String format) in the current cell using the setCellValue method offered by the HSSFCell class.

FileOutputStream fos = new FileOutputStream(src); String message = “Passed”; sheet.getRow(counter).createCell(2).setCellValue(message);

16. We now write to the OutputStream (i.e., fos) and then close the output stream using the close method offered by the Output Stream.

workbook.write(fos); fos.close();

Execution

As seen in the snapshot of the Excel Workbook that contained the test inputs, the test scenarios were executed successfully, and the test result was written in the respective cells in the Sheet.

Read and Write data from & to Excel sheets (.xlsx) in Selenium using Apache POI

For performing CRUD operations on MS Excel sheets in the .xlsx format, we use the XSSF implementation provided by the Apache POI library.

Test Scenario

1. (a) & 1(b) 🡪 Test URL – https://lambdatest.github.io/sample-todo-app/
2. Add a new item in the LambdaTest ToDo app
   • Test Scenario 1(a) – Testing on LambdaTest
   • Test Scenario 1(b) – Hello World on LambdaTest
3. Assert if the new item is not added successfully to the list
4. Write the test result in the input Excel file

Here is the content of the MS Excel File (i.e., Test_2.xlsx) that contains details of the test scenario:

Important

When using Apache POI (for .xls files) and POI OOXML (for .xlsx files) in the same class, it is important to have poi and poi-ooxml point to the same version in pom.xml. This is because Apache Maven POI dependencies for poi & poi-ooxml pointing to different versions lead to the error java.lang.IncompatibleClassChangeError.

In pom.xml, the dependencies for POI and POI-OOXML point to the same versions (i.e., 4.1.2).

Implementation

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124

package com.DataDrivenFramework; import java.io.*; import java.net.MalformedURLException; import java.net.URL; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openqa.selenium.*; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.remote.DesiredCapabilities; import org.openqa.selenium.remote.RemoteWebDriver; import org.testng.Assert; import org.testng.annotations.AfterClass; import org.testng.annotations.BeforeTest; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class test_DataDrivenFramework WebDriver driver; XSSFWorkbook workbook; Sheet sheet; Cell cell; String username = “user-name”; String access_key = “access-key”; @BeforeTest public void init() throws InterruptedException, MalformedURLException DesiredCapabilities capabilities = new DesiredCapabilities(); capabilities.setCapability(“build”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“name”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“platformName”, “OS X Yosemite”); capabilities.setCapability(“browserName”, “MicrosoftEdge”); capabilities.setCapability(“browserVersion”,”81.0″); capabilities.setCapability(“tunnel”,false); capabilities.setCapability(“network”,true); capabilities.setCapability(“console”,true); capabilities.setCapability(“visual”,true); driver = new RemoteWebDriver(new URL(“http://” + username + “:” + access_key + “@hub.lambdatest.com/wd/hub”), capabilities); System.out.println(“Started session”); @Test (description = “Testing data input from XLSX (i.e. XSSF)”, priority = 2, enabled = true) public void Test_ddf_xssf_input() throws IOException, InterruptedException XSSFWorkbook workbook; /* Import the Excel Sheet */ File src=new File(“C:\\Folder\\Test_2.xlsx”); /* Load the file */ FileInputStream fis = new FileInputStream(src); /* Load the workbook */ workbook = new XSSFWorkbook(fis); /* The sheet is in Tab-0 */ sheet = workbook.getSheetAt(0); for(int counter = 1; counter <= sheet.getLastRowNum(); counter++) /* Skip Row – 0 */ cell = sheet.getRow(counter).getCell(0); /* Cell [1,0] contains the test URL */ if (cell.getCellType() == CellType.STRING) String test_url = cell.getStringCellValue(); driver.get(test_url); /* Cell [1,1] –> Search Term */ cell = sheet.getRow(counter).getCell(1); if (cell.getCellType() == CellType.STRING) String new_item = cell.getStringCellValue(); try /* Let’s mark done first two items in the list. */ driver.findElement(By.name(“li1”)).click(); driver.findElement(By.name(“li2”)).click(); /* Get the item to be added from the sheet */ /* Let’s add an item in the list. */ driver.findElement(By.id(“sampletodotext”)).sendKeys(new_item); driver.findElement(By.id(“addbutton”)).click(); /* Let’s check that the item we added is added in the list. */ String enteredText = driver.findElement(By.xpath(“/html/body/div/div/div/ul/li[6]/span”)).getText(); if (enteredText.equals(new_item)) System.out.println(“Demonstration is complete”); String status = “passed”; /* Write the result in the excel sheet */ /* Write Data in the Result Column */ FileOutputStream fos = new FileOutputStream(src); String message = “Passed”; sheet.getRow(counter).createCell(2).setCellValue(message); workbook.write(fos); fos.close(); catch (Exception e) System.out.println(e.getMessage()); @AfterClass public void tearDown() if (driver != null) driver.quit();

Code Walkthrough

1. Create an object of XSSFWorkbook. This object will be further used when accessing the Sheet and reading/writing at appropriate cells in the Sheet.

XSSFWorkbook workbook;

2. Create a Workbook object by referring to the FileInputStream object that points to the location where the Excel File (.xlsx) is located on the host machine.

File src=new File(“C:\\Folder\\Test_2.xlsx”); FileInputStream fis = new FileInputStream(src);

3. Load the Excel Workbook using the FileInputStream object obtained from Step (2).

workbook = new XSSFWorkbook(fis);

4. Using the Workbook object, we access the Sheet at index ‘0’ using the getSheetAt method.

sheet = workbook.getSheetAt(0);

5. Like the earlier test, which demonstrated the usage of the HSSF Workbook, row (0) also contains the title of the fields (i.e., Test URL, Item to Add, and Result). Hence, we exclude Row (0) from the test execution.

A for loop is run from rows 1 to sheet.getLastRowNum() [which equates to ‘2’ in our case] so that the two test scenarios in the sheet can be executed one after the other.

for(int counter = 1; counter <= sheet.getLastRowNum(); counter++)

6. The getRow method returns the logical row number [i.e. 1 for test scenario -1(a) and 2 for test scenario – 1(b)]. Now that we have the row number, the getCell(cell-number) method of the XSSFRow is used for accessing the specific cell.

Shown below are the contents of the cells on Row – 1:

• Cell (1,0 ) 🡪 https://lambdatest.github.io/sample-todo-app/
• Cell (1,1) 🡪 Testing on LambdaTest
• Cell (1,2) 🡪 Result (Test Status updated post-test execution)

Cell (1,0) contains the URL under test. The getStringCellValue method gets the value of the cell as a String. Next, we navigate to the test URL.

cell = sheet.getRow(counter).getCell(0); /* Cell [1,0] contains the test URL */ if (cell.getCellType() == CellType.STRING) String test_url = cell.getStringCellValue(); driver.get(test_url);

7. New items to be added are located in the following Cells:

• Cell (1, 1) 🡪 Testing on LambdaTest
• Cell (2,1) 🡪 Hello World on LambdaTest

Row number 1 represents Test Scenario – 1(a) and row number 2 represents Test Scenario – 1(b). Cell number remains unchanged; hence it is hard coded to ‘1’. The new items to be added to the ToDo list are retrieved from the Cell using the getStringCellValue() method.

cell = sheet.getRow(counter).getCell(1); if (cell.getCellType() == CellType.STRING) String new_item = cell.getStringCellValue();

8. First two items in the ToDo list are marked as ‘Done’ by locating the respective WebElement using the Name property and then performing the click() operation on that WebElement.

driver.findElement(By.name(“li1”)).click(); driver.findElement(By.name(“li2”)).click();

Also Read – How To Use Name Locator In Selenium Automation Scripts?

9. New entry to the ToDo list is added by locating the WebElement using the ID property and using the sendKeys method for entering the new entry in the text box.

The Add Button on the page is located using the ID property, and click operation is performed on the button for adding the new entry to the ToDo list.

driver.findElement(By.id(“sampletodotext”)).sendKeys(new_item); driver.findElement(By.id(“addbutton”)).click();

Also Read – Making The Move With ID Locator In Selenium WebDriver

10. The content of the newly added item is fetched using the getText() method in Selenium. If the newly added item equals the expected item, the test is considered as ‘Passed.’

String enteredText = driver.findElement(By.xpath(“/html/body/div/div/div/ul/li[6]/span”)).getText(); if (enteredText.equals(new_item)) System.out.println(“Demonstration is complete”); String status = “passed”;

11. The Java FileOutputStream class is used for writing the data in the file. Then, the Excel Workbook with the test data (i.e., Path_To_File\File.xlsx) is opened to write the test result.

• Test Scenario 1(a) – Test result to be written to Cell (1,2)
• Test Scenario 1(b) – Test result to be written to Cell (2,2)

The test result for the respective tests is written in the corresponding Cells as a String Value. The setCellValue method writes the test result in the Cell.

FileOutputStream fos = new FileOutputStream(src); String message = “Passed”; sheet.getRow(counter).createCell(2).setCellValue(message);

12. We now write to the OutputStream (i.e. fos). Post the write operation, the close method is invoked for closing the OutputStream.

workbook.write(fos); fos.close();

Execution

As seen in the Excel Workbook snapshot containing the test inputs, test scenarios are executed successfully, and test results are written in the correct Cells in the Sheet.

Data Driven Framework in Selenium using TestNG DataProvider with Excel

DataProvider in TestNG is popularly used with MS Excel, where the data to be supplied in the DataProvider is read from an Excel sheet. This approach is primarily useful when the test has to be performed against a massive amount of test data. You can refer to DataProviders in TestNG for a quick recap on the topic.

Test Scenario

1. (a) 🡪 Search for ‘LambdaTest’ on Google
2. (b) 🡪 Search for ‘LambdaTest Blog’ on Google
3. Click on the First test result
4. Assert if the current page title does not match with the expected page title
5. Append the corresponding test result in the cell next to the test case

To get started, we create a test data set in an Excel Sheet. The first column contains the Search Term and the second column contains the Expected Page Title after the search is executed.

Implementation

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140

package com.DataDrivenFramework; import java.io.*; import java.net.MalformedURLException; import java.net.URL; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openqa.selenium.*; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.remote.DesiredCapabilities; import org.openqa.selenium.remote.RemoteWebDriver; import org.testng.Assert; import org.testng.annotations.AfterClass; import org.testng.annotations.BeforeTest; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class test_DataDrivenFramework WebDriver driver; XSSFWorkbook workbook; Sheet sheet; Cell cell; String username = “user-name”; String access_key = “access-key”; @BeforeTest public void init() throws InterruptedException, MalformedURLException DesiredCapabilities capabilities = new DesiredCapabilities(); capabilities.setCapability(“build”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“name”, “[Java] Data Driven Framework in Selenium WebDriver”); capabilities.setCapability(“platformName”, “OS X Yosemite”); capabilities.setCapability(“browserName”, “MicrosoftEdge”); capabilities.setCapability(“browserVersion”,”81.0″); capabilities.setCapability(“tunnel”,false); capabilities.setCapability(“network”,true); capabilities.setCapability(“console”,true); capabilities.setCapability(“visual”,true); driver = new RemoteWebDriver(new URL(“http://” + username + “:” + access_key + “@hub.lambdatest.com/wd/hub”), capabilities); System.out.println(“Started session”); @DataProvider(name=”dataset”) public Object[][] getExcelData() Object[][] arrObj = getExData(“C:\\Folder\\Test_4.xlsx”,”TestData”); return arrObj; public String[][] getExData(String fileName, String sheetName) String[][] data = null; try FileInputStream fis = new FileInputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheet(sheetName); XSSFRow row = sheet.getRow(0); int noOfRows = sheet.getPhysicalNumberOfRows(); int noOfCols = row.getLastCellNum(); Cell cell; data = new String[noOfRows – 1][noOfCols]; for(int outer_cnt = 1; outer_cnt < noOfRows; outer_cnt++) for(int inner_cnt = 0; inner_cnt < noOfCols; inner_cnt++) row = sheet.getRow(outer_cnt); cell= row.getCell(inner_cnt); data[outer_cnt – 1][inner_cnt] = cell.getStringCellValue(); System.out.println(data[outer_cnt – 1][inner_cnt]); catch (Exception e) System.out.println(“The exception is: ” +e.getMessage()); return data; @Test(dataProvider =”dataset”, description = “Data Driven framework using Data Provider”, priority = 3, enabled = true) public void Test_ddf_dataprovider_excel(String search_string, String expected_title) throws IOException, InterruptedException String test_url = “https://www.google.com”; WebElement lt_link = null; driver.manage().window().maximize(); driver.get(test_url); Thread.sleep(3000); try /* Enter the search term in the Google Search Box */ WebElement search_box = driver.findElement(By.xpath(“//input[@name=’q’]”)); search_box.sendKeys(search_string); search_box.submit(); Thread.sleep(3000); /* Click on the first result which will open up the LambdaTest homepage */ if (search_string.equalsIgnoreCase(“LambdaTest”)) lt_link = driver.findElement(By.xpath(“//span[.=’LambdaTest: Most Powerful Cross Browser Testing Tool Online’]”)); else if (search_string.equalsIgnoreCase(“LambdaTest Blog”)) lt_link = driver.findElement(By.xpath(“//span[.=’A Cross Browser Testing Blog – LambdaTest’]”)); lt_link.click(); Thread.sleep(3000); String curr_window_title = driver.getTitle(); Assert.assertEquals(curr_window_title, expected_title); catch (Exception e) System.out.println(e.getMessage()); @AfterClass public void tearDown() if (driver != null) driver.quit();

Code WalkThrough

• getExData Method

1. An object of Workbook is created by referring to the FileInputStream object that points to the location where the Excel File (.xlsx) is located on the host machine.

The object of type XSSFWorkbook will be further used for accessing the Sheet and the various Cells in the Sheet.

FileInputStream fis = new FileInputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(fis);

2. We get the Sheet using the getSheet method that takes the Sheetname as the input argument. In our case, the test data is available in the sheet with the name ‘TestData’; hence, the same is passed to the getSheet method.

Once inside the Sheet, we read the first using the getRow method, which returns an object of type XSSFRow.

XSSFSheet sheet = workbook.getSheet(sheetName); XSSFRow row = sheet.getRow(0);

3. We get the total number of rows and columns (or Cells) using the getPhysicalNumberOfRows method in XSSFSheet class and getLastCellNum method in XFFSRow class.

int noOfRows = sheet.getPhysicalNumberOfRows(); int noOfCols = row.getLastCellNum();

In our case, the number of Rows & Columns are 3 & 2, respectively.

4. We create a new 2D String array of [2] [2] dimensions

data = new String[noOfRows – 1][noOfCols];

5. In a nested for loop (outer-count 🡪 1.. no of rows, inner-count 🡪 0.. no of columns), we fetch the current row using the getRow method. The getCell method returns the current column.

Now that we have the row and column, the data in that particular cell is read using the getStringCellValue() method provided by the Cell class. Since the Row ‘0’ contains the title (i.e., Keyword, Expected Title), we start reading from the first row.

• First for loop run
• Second for loop run

row = 1, cell = 0, data [0] [0] = LambdaTest (i.e. Cell [1] [0])row = 1, cell = 1, data [0] [1] = Most Powerful Cross Browser Testing Tool Online | LambdaTest (i.e. Cell [1] [1])

row = 2, cell = 0, data [1] [0] = LambdaTest Blog (i.e. Cell [2] [0])row = 2, cell = 1, data [1] [1] = LambdaTest | A Cross Browser Testing Blog (i.e. Cell [2] [1])

10	for(int outer_cnt = 1; outer_cnt < noOfRows; outer_cnt++) for(int inner_cnt = 0; inner_cnt < noOfCols; inner_cnt++) row = sheet.getRow(outer_cnt); cell= row.getCell(inner_cnt); data[outer_cnt – 1][inner_cnt] = cell.getStringCellValue(); System.out.println(data[outer_cnt – 1][inner_cnt]);

6. Return the 2D array (i.e., data[][]) that we populated with the values from the Sheet [in Step (5)]


return data;


• getExcelData

In this method, we create a DataProvider method that uses the getExData method that returns a 2D object created by reading the required Cell values from the Excel Sheet.

@DataProvider(name=”dataset”) public Object[][] getExcelData() Object[][] arrObj = getExData(“C:\\Folder\\Test_4.xlsx”,”TestData”); return arrObj;

• Test_ddf_dataprovider_excel

1. This test method uses the ‘dataset’ DataProvider that returns a 2D object containing the search string and expected page title.

@Test(dataProvider =”dataset”, description = “Data Driven framework using Data Provider”, priority = 3, enabled = true) public void Test_ddf_dataprovider_excel(String search_string, String expected_title) throws IOException, InterruptedException

2. Once we navigate to the test URL (i.e., Google Homepage), enter the search term in the search box using the sendKeys method in Selenium. The search box is located using the findElement method in Selenium which uses the XPath property for locating the search box Web Element.

WebElement search_box = driver.findElement(By.xpath(“//input[@name=’q’]”)); search_box.sendKeys(search_string);

3. Trigger the search using the submit() method.

search_box.submit();

4. Perform a case-insensitive comparison of the Search String (i.e. LambdaTest/LambdaTest Blog) and accordingly locate the first link using the findElement method with the WebElement’s XPath property.

if (search_string.equalsIgnoreCase(“LambdaTest”)) lt_link = driver.findElement(By.xpath(“//span[.=’LambdaTest: Most Powerful Cross Browser Testing Tool Online’]”)); else if (search_string.equalsIgnoreCase(“LambdaTest Blog”)) lt_link = driver.findElement(By.xpath(“//span[.=’A Cross Browser Testing Blog – LambdaTest’]”));

5. Now that the link is located click on the link to navigate to the resultant page.


lt_link.click();


6. Get the current page title using the getTitle() method of Selenium WebDriver. Assert if the current page title does not match the expected title.

String curr_window_title = driver.getTitle(); Assert.assertEquals(curr_window_title, expected_title);

Execution

Below is the execution snapshot from the IntelliJ IDE, which indicates that the data from the external data set (i.e., Excel Sheet) was read successfully. As a result, the DataProvider in TestNG was able to use that data for performing the search operation on Google.

Conclusion

Data-driven testing is an amazing approach to improve your efficiency in highly repetitive test scenarios. You can either build a data-driven testing framework from scratch using Selenium and libraries, which is highly customizable for specific scenarios, but once those scripts are broken, maintaining them is quite a daunting task. Or, you can go with an automated testing tool that comes with a prebuilt data-driven testing framework that you can use right away.

TestComplete samples (both built-in and additional) are located in the

\Public\Public Documents\TestComplete 15 Samples folder.

Some file managers display the Public Documents folder as Documents.

This topic provides you with general information on Data-Driven testing:

About Data-Driven Testing

Automated tests play back a recorded (or programmed) sequence of user actions that cover a certain area of the tested application. To get larger coverage, you can perform tests with different input data. Suppose, for example, you recorded actions that input data into an application’s form. The recorded test contains only those values that you entered during the recording and, most likely, these values do not cause errors in the application, but other data may cause them. So, you have to run your test with different set of input data to ensure that the application works as expected for various input values. This testing approach is called data-driven testing.

Of course, you can record many tests with various input data, but the best solution is to separate data from test commands. Data-driven tests will read data from data storages, for example, from a file or database, rather than use hard-coded values. Such a separation makes tests logically simpler. A test containing several sets of hard-coded values can be rather difficult to modify and maintain. For instance, if you need more input data, you will have to modify the test commands.

Data Storages

For data-driven tests in TestComplete, you can use the following types of data storages:

Note:

Normally, the storages contain both input data and verifying values. This approach let you easily specify verification data for each portion of test data.

To prepare and access your test data, see Preparing Data for Data-Driven Testing.

To organize your test data files, see Organizing Data Storages.

Common Tasks

Typically, a data-driven test performs the following operations in a loop:

1. Retrieves the portion of test data from a storage.

2. Enters the data in an application form and simulates other actions.

3. Verifies results.

4. Continues testing with the next set of input data.

TestComplete includes special scripting functions and objects that simplify performing these steps. These objects, for instance, let you easily extract data from CSV (comma-separated values) files, database tables and Excel worksheets. For more information, see the following topics:

Samples

TestComplete also includes a sample project that illustrates data-driven testing:

Data:

\Common\Data-Driven Testing\TestBook.xlsx

TestComplete project suites:

\Common\Data-Driven Testing\Scripts

Note:

If you do not have the sample, download the TestComplete Samples installation package from the support.smartbear.com/testcomplete/downloads/samples page of our website and run it.

How Do I Start?

Follow the step-by-step instructions to learn how to create data-driven tests step by step:

You can explore the following script example to learn how to use an Excel file as a data source for your test and how to extract data from the Excel file:

Using the DDTDriver Object to Retrieve Data From Excel Files

To learn more about data-driven testing, you can also read the following article:

http://vig.pearsoned.com/samplechapter/0131838806.pdf

Note:

To view this file you must have Adobe Acrobat Reader installed.

Remarks

When iterating through data sets, you can use the

Log.AppendFolder

,

Log.PopLogFolder

, and

Log.PushLogFolder

methods, or the

aqTestCase.Begin

and

aqTestCase.End

methods to isolates operations performed for each data set. The

aqTestCase.Begin

and

aqTestCase.End

methods will also add each iteration to the Summary report as a separate test case.

See Also

Data-Driven TestingUsing Keyword Tests for Data-Driven TestingUsing Scripts for Data-Driven TestingOrganizing Data StoragesUsing Excel Files as Data StoragesUsing CSV Files as Data StoragesUsing Database Table as Data Storages

Cedric Beust created the popular test automation framework TestNG in 2003 using the Java programming language. It is similar to JUnit and NUnit but solves some of their shortcomings such as poor configuration control, static programming models, lack of dependency testing, etc. It is designed to cover a wide range of testing projects including functional, unit, and integrated system testing. TestNG offers powerful features that not only enable testers to perform complex testing tasks but also make functionalities easy to use. While TestNG is mostly popular for its annotations, TestNG DataProviders is another handy feature that makes data-driven testing easy and efficient. With data parameterization, Data listeners, DataProviders, HTML reports, logs, test case grouping, and prioritizing and parallel testing abilities, TestNG has quickly become a favorite for most testers.

How To Do Data-Driven Testing With Katalon

Katalon Platform is a comprehensive test automation platform that allows you to easily create, manage, execute, and generate reports for test cases across web, desktop, mobile, and APIs. Katalon comes with a data-driven testing framework already built in, so all you have to do is create an account, and download Katalon Studio, which is a productive IDE to generate automated tests without having to write a single line of code.

Download Katalon & Start Testing Within Minutes

Once you’ve downloaded Katalon Studio, launch it. You should see the Katalon Studio interface as below. You can then click the “Create new Test Case” button.

You can craft test cases from the wide variety of keywords we offer (which are essentially code snippets to perform specific actions). Here are just a few examples of them: Navigate to URL, Open Browser, Refresh, Right Click, Scroll, Send Keys, etc.

Read more: Create test cases with Katalon Studio

Here we have created a test case called “Find a place”, which includes the following:

1. Open browser (Chrome).
2. Navigate to the URL. In this case we chose Airbnb.
3. Click on Search.
4. Enter text for a city.
5. Click Search.

This test case is put under the DDT with MS Excel Files test suite.

Let’s prepare a data file in Excel with two columns: “City” and “Expected Result”. The values Atlanta, New York, and Tokyo will be dynamically added to the script as we execute.

After that, in the Data File section let’s create new test data called excelfile_100cities. Once done, you should arrive at the test data interface, and you can click Browse to import the Excel file we’ve just created into Katalon Studio.

Once imported, we go back to the DDT With MS Excel Files test suite. Click Add and choose the newly imported Excel to bring it to your test suite. After that, click Map All, and Katalon automatically maps all of the columns in your Excel file to the corresponding variable in your test script.

Finally, click Run, and you’re good to go!

As the test gets executed, you should see that it automatically opens the browser, navigates to Airbnb, searches for the exact location we specify in our Excel file, and checks for the available spaces there.

What’s even better is that once the execution is done, the test results are automatically imported into Katalon TestOps for management and reporting purposes. For more information, you can check out Data-driven testing with Katalon Studio.

You can even watch a detailed guide on how to do data-driven testing at Katalon Academy.

You can see how the process of doing data-driven testing is much more straightforward and simpler than going with the traditional scripted approach. A good advantage that tools have over libraries is that they come with self-healing features, which automatically fix any broken test scripts during regression test runs. If you go with Selenium, you must manually fix each individual broken test script, and that is quite time-consuming. Read more for a Katalon vs. Selenium detailed comparison.

How to create a Data Driven Automation Framework

Consider you want to Test Login functionality of an application.

Step 1) Identify the Test Cases

• Input Correct username and password – Login Success
• Input incorrect username and correct password – Login Failure
• Input correct username and incorrect password – Login Failure

Step 2) Create detailed test Steps for above 3 Test Cases

Test Case#	Description	Test Steps	Test Data	Expected Results
Check Login for valid credentials	Username: valid password: valid	Login Success
Check Login for invalid credentials	Username: invalid password: valid	Login Fail
Check Login for invalid credentials	Username: valid password: invalid	Login Fail

Step 3) Create Test Script

If you observe the Test Steps Remain common through the 3 Test Steps. You need to create a Test Script to execute these steps

// This is Pseudo Code // Test Step 1: Launch Application driver.get(“URL of the Application”); // Test Step 2: Enter Username txtbox_username.sendKeys(“valid”); // Test Step 3: Enter Password txtbox_password.sendKeys(“invalid”); // Test Step 4: Check Results If (Next Screen) print success else Fail

Step 4) Create an excel/csv with the Input Test Data

Step 5) Step Modify the Scrip to Loop over Input Test Data. The input commands should also be parameterized

// This is Pseudo Code // Loop 3 Times for (i = 0; i & lt; = 3; i++) { // Read data from Excel and store into variables int input_1 = ReadExcel(i, 0); int input_2 = ReadExcel(i, 1); // Test Step 1: Launch Application driver.get(“URL of the Application”); // Test Step 2: Enter Username txtbox_username.sendKeys(input_1); // Test Step 3: Enter Password txtbox_password.sendKeys(input_2); // Test Step 4: Check Results If(Next Screen) print success else Fail }

Above are just 3 test cases. The test script can be used to loop over following test cases just by appending test data values to Excel

• Input incorrect username and incorrect password – Login Fail
• Input correct username and password blank – Login Fail
• Input blank username and blank password– Login Fail

And so on

Key Takeaways

• Data-driven is a test automation framework that stores test data in a table or spread spreadsheet format.
• Additionally, a Data-driven Testing framework helps to separate test data from Functional tests. Moreover, it allows testing applications with multiple sets of data values without rewriting the same code across different test scripts.
• Also, we perform data-driven Testing in Selenium using Apache POI. It is a library that helps to read and write data in the Excel Sheet.

Similar Articles

How to work on Data Driven Framework in Selenium Using Apache POI?

Data Driven Framework is one of the most popular Automation Testing Frameworks in the current market. Data Driven automated testing is a method in which the test data set is created in the excel sheet, and is then imported into automation testing tools to feed to the software under test.

Selenium Webdriver is a great tool to automate web-based applications. However, it does not support read and write operations in excel files.

Therefore, we use third party APIs like Apache POI.

What you will learn in this tutorial:

• What is the data driven framework in Selenium WebDriver using excel example
• How to read and write data from an excel sheet in Selenium WebDriver using Apache POI

Table of Contents:

What is Apache POI?

Apache POI (Poor Obfuscation Implementation) is an API written in Java to support read and write operations – modifying office files. This is the most common API used for Selenium data driven tests.

There are several ways to implement a Data Driven Framework, and each differs in the effort required to develop the framework and maintenance.

Developing a Data Driven framework in Selenium using POI helps reduce maintenance and improve test coverage thus providing a good return on investment.

Recommended reads:

Why do data drive tests?

Often there might be may be a number of data sets that have to be used to test a feature of an application. Now running the same test with different data manually is time-consuming, error prone and a boring task.

Let us understand this scenario with an example.

Suppose we need to test the login/Register/ Any form with multiple input fields with 100 different data sets.

To test this, you have three different approaches:

1) Create 100 scripts one for each dataset and execute each test one by one.2) Change the data in the script and execute it multiple times.3) Import the data from the excel sheet and execute the script multiple times with different data.

The first two scenarios are laborious, time-consuming – implying low ROI. Hence, we must follow the third approach.

In the third approach, are implementing the Data Driven framework where all our data resides in an excel sheet where it is imported from and used to test the features of the application.

=> Want to learn more about Data Driven Framework? We have a detailed article that you can check here.

What do we need to do to implement the Data Driven Framework?

In order to follow this approach we must have Eclipse and TestNG properly configured.

Once done, we will look at:

• Various interfaces of Apache POI.
• Integration of Apache POI in the Eclipse.
• Read the Data from the Excel Sheet.
• Send the data to the Excel Sheet.
• Advantages of using Apache POI with Selenium.

Interface in POI

One of the most remarkable features of Apache POI is that it supports read and write operations on both .xls and .xslx files.

Below mentioned are some of the interfaces of the POI.

• XSSFWorkbook: Represents workbook in xlsx file.
• HSSFWorkbook: Represents workbook in xls file.
• XSSFSheet: Represents a sheet in XLSX file.
• HSSFSheet: Represents a sheet in XLS file.
• XSSFRow: Represents a row in a sheet of XLSX file.
• HSSFRow: Represents a row in a sheet of XLS file.
• XSSFCell: Represents a cell in a row of XLSX file.
• HSSFCell: Represents a cell in a row of XLS file.

Fields available in a cell:

• CELL_TYPE_BLANK: Represents a blank cell.
• CELL_TYPE_BOOLEAN: Represents a Boolean cell (true or false).
• CELL_TYPE_ERROR: Represents an error value in a cell.
• CELL_TYPE_FORMULA: Represents a formula result on a cell.
• CELL_TYPE_NUMERIC: Represents numeric data in a cell.
• CELL_TYPE_STRING: Represents string in a cell.

Steps to use Selenium with Apache POI

Let us create an automation script to test the login process of web-based applications.

Here, I have taken LinkedIn as an example.

We import data from an excel sheet and then use it to log into the application and after execution, we write the result in the excel sheet.

We need the following software installed on our system to carry on with the steps to execute the framework:

• Java JDK 1.7+
• Eclipse IDE
• TestNG
• Selenium jars
• Microsoft Office / Open Office

Step #1)

First, we need to configure the Eclipse with Apache POI.

Download jar files for Apache POI.

Step #2)

Unzip the jar file and add the following jars to your project and configure them.

• dom4j-1.6.1.jar
• poi-3.10-FINAL-20140208.jar
• poi-ooxml-3.10-FINAL-20140208.jar
• poi-ooxml-schemas-3.10-FINAL-20140208.jar
• xmlbeans-2.3.0.jar

Step #3)

After configuring the respective jars, create an excel sheet, enter some data in it and save it as TestData.xlsx at your preferred location.

Step #4)

Now let us follow the sample code to read the data from the excel sheet and use it to login to linkedin.com.

package automationFramework; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.concurrent.TimeUnit; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.firefox.FirefoxDriver; import org.openqa.selenium.support.ui.WebDriverWait; import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; /** * @author Admin * */ public class ReadWriteExcel { WebDriver driver; WebDriverWait wait; HSSFWorkbook workbook; HSSFSheet sheet; HSSFCell cell; @BeforeTest public void TestSetup() { // Set the path of the Firefox driver. System.setProperty(“webdriver.gecko.driver”, “C:\\Users\\geckodriver.exe”); driver = new FirefoxDriver(); // Enter url. driver.get(“http://www.linkedin.com/”); driver.manage().window().maximize(); wait = new WebDriverWait(driver,30); driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS); } @Test public void ReadData() throws IOException { // Import excel sheet. File src=new File(“C:\\Users\\Admin\\Desktop\\TestData.xls”); // Load the file. FileInputStream finput = new FileInputStream(src); // Load he workbook. workbook = new HSSFWorkbook(finput); // Load the sheet in which data is stored. sheet= workbook.getSheetAt(0); for(int i=1; i<=sheet.getLastRowNum(); i++) { // Import data for Email. cell = sheet.getRow(i).getCell(1); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.id(“login-email”)).sendKeys(cell.getStringCellValue()); // Import data for password. cell = sheet.getRow(i).getCell(2); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.id(“login-password”)).sendKeys(cell.getStringCellValue()); } } }

Step #5)

Right click on the test case class and click on Run as –> TestNG Test.

Apache POI imports data from the excel sheet and uses it to log into our application. Now that we have seen how to read data from the excel sheet, let’s look at how to write to the sheet.

package automationFramework; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.concurrent.TimeUnit; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.firefox.FirefoxDriver; import org.openqa.selenium.support.ui.WebDriverWait; import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; /** * @author Admin * */ public class ReadWriteExcel { WebDriver driver; WebDriverWait wait; HSSFWorkbook workbook; HSSFSheet sheet; HSSFCell cell; @BeforeTest public void TestSetup() { // Set the path of the Firefox driver. System.setProperty(“webdriver.gecko.driver”, “C:\\Users\\geckodriver.exe”); driver = new FirefoxDriver(); // Enter url. driver.get(“http://www.linkedin.com/”); driver.manage().window().maximize(); wait = new WebDriverWait(driver,30); driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS); } @Test public void ReadData() throws IOException { // Import excel sheet. File src=new File(“C:\\Users\\Admin\\Desktop\\TestData.xls”); // Load the file. FileInputStream finput = new FileInputStream(src); // Load he workbook. workbook = new HSSFWorkbook(finput); // Load the sheet in which data is stored. sheet= workbook.getSheetAt(0); for(int i=1; i<=sheet.getLastRowNum(); i++) { // Import data for Email. cell = sheet.getRow(i).getCell(1); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.id(“login-email”)).sendKeys(cell.getStringCellValue()); // Import data for password. cell = sheet.getRow(i).getCell(2); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.id(“login-password”)).sendKeys(cell.getStringCellValue()); // Write data in the excel. FileOutputStream foutput=new FileOutputStream(src); // Specify the message needs to be written. String message = “Data Imported Successfully.”; // Create cell where data needs to be written. sheet.getRow(i).createCell(3).setCellValue(message); // Specify the file in which data needs to be written. FileOutputStream fileOutput = new FileOutputStream(src); // finally write content workbook.write(fileOutput); // close the file fileOutput.close(); } } }

Note: If you encounter any problems during this process, please check the following points.

• Make sure all the mentioned jars are added to the project and are properly configured.
• Required software is correctly installed.
• Proper use of an interface with respect to excel files, like HSSF for .xls and XSSF for .xlsx.
• Valid rows and column indexes are used.
• The excel file must be closed before execution.
• Proper classes used for the excel file like XSSF used for .xlsx files and HSSF used for .xls files.

Advantages of using Data Driven Framework

• Improves test coverage.
• Re-usability of the code.
• Less maintenance.
• Faster Execution.
• Permits better error handling.

Conclusion

Input/Output from and to a file is a very critical part of the software testing process. Apache POI plays a vital role in making this possible for Selenium Test Automation.

Selenium integration with Apache POI facilitates you to run your script multiple times with different data sets, with all data maintained at a single location. It saves time and maintenance effort on the test script.

About the author: This is a guest post by Vivek, a QA Automation Engineer.

Do you have any queries implementing the data-driven testing framework in Selenium WebDriver using Apache POI? Let us know in the comments section below.

What Is Data-Driven Testing?

Automating Data-Driven Testing Using Selenium

Implementing Data-Driven Test Automation

Advantages of Data-Driven Testing

Limitations of Data-Driven Testing

Best Practices of Data-Driven Testing

How does Data-Driven Testing work?

Examples of DDT with its Implementation Structure

Data-Driven Testing with Testsigma

Creating a Data-Driven Automation Framework

Test Data Generation

Behavior-Driven Testing and DDT

Conclusion

Imagine a scenario where you have to automate a test for an application with multiple input fields. Typically, or for one-off scenarios, you would hardcode those inputs and execute the test. But hardcoding will fail at scale. Hardcoding inputs will quickly become cumbersome, confusing, and impossible to manage when you have to run through many permutations of acceptable input values for best-case, worst-case, positive, and negative test scenarios.

If you could have all test input data stored or documented in a single spreadsheet, you can program the test to “read” the input values from it. That is precisely what data-driven testing tries to achieve.

DDT separates test logic (script) from test data (input values), making both easier to create, edit, use, and manage at scale. Therefore, DDT is a methodology where a sequence of test steps (structured in test scripts as reusable test logic) are automated to run repeatedly for different permutations of data (picked up from data sources) to compare actual and expected results for validations.

So, parametrized testing is a four-step process that involves:

Fetching input data from the data sources like .xls, .csv, .xml files or other databases.

Entering input data into the AUT (application under test) using automated test scripts and variables.

Comparing the actual results with the expected output.

Running the same test again with the next row of data (from the same source).

Let’s explore some scenarios to understand how DDT can be conceptualized and implemented using Selenium.

A login panel in an application asks the user to provide a work email and a password. Allowing only a registered user is critical here: the login feature is built to block unauthorized users from accessing certain features/data in the application.

To authenticate the email and password, you will need to check if both input field types match the data entered at the time of successful sign-up.

To check these individual cases, you will have to input different variables every time a script is run to test the authentication/login process. To parametrize this test, you can save different variables in a storage file and call these values individually in a functional test script.

A DDT test script for this login authentication scenario built with Selenium could look something like this:


pip install seleniumfrom selenium import webdriverdriver = webdriver.Chrome()def site_login():driver.get (“URL”)driver.find_element_by_id(“ID”).send_keys(“username”)driver.find_element_by_id (“ID”).send_keys(“password”)driver.find_element_by_id(“submit”).click()#if you are using FB then,def fb_login():driver.find_element_by_id(“email”).send_keys(‘[email protected]’)driver.find_element_by_id(“pass”).send_keys(“fakepassword1”)driver.find_element_by_id(“loginbutton”).click()from Selenium.webdriver.support import expected_conditions as ECfrom selenium.webdriver.common.by import Byfrom selenium.webdriver.support.ui import WebDriverWait WebDriverWait(driver, until(EC.title_contains("home"))


A test file could look something like this:

For a file upload feature, it is imperative that the format/template of the file that the user uploads is acceptable, but there is more to it than just matching formats. The data in the file is also expected to be picked up by the application that processes it for a specific use case (like generating a graph). Let’s say the application uses a .csv file to store data, then the test should ideally help validate the data type in every cell of the .csv file. Here, testing the content type in every cell according to a certain type takes precedence.

Therefore, the test should be focused on checking the format of supported and unsupported files, failing when the file format doesn’t meet acceptable criteria. Supported files should be enabled and uploaded without any data loss. For instance, if the upload file should have just two columns with a specific header, it should not accept files that don’t meet this criterion.

Let’s say the requirement suggests we upload a file with two columns with a single header each. The rest of the content in the file should not have any special characters between the cell contents. Should the file format be correct, the application is triggered to read the right data in every cell to be processed further. This test has to run for all cells across all rows and columns. This is where DDT can help.

A sample template could look something like this:

All of the aforementioned scenarios can be automated using a DDT-compatible automation framework. This enables teams to create test scripts with placeholders that get replaced with input values from the test data file, which are then compared with the expected results.

In a generic automated test, you include sample data in the test script itself. But in data-driven testing, you must connect your test to an external data source, which can be excel files, .csv and .xml files, or even fully-featured databases like MySQL.

There are three steps by which you can automate data-driven testing:

Choosing the data source.

Connecting the data source.

Assessing the results.

If you have no test automation, you can start off easy with DDT. But to implement DDT in your current automation testing framework, you will need to:

Re-create test scripts to use variables and conditional logic (in order to use test data from an external file/database)

Compare results with expected output data, also stored in a file.

Thus, the single most important thing is knowing how to structure your test data. Take a look at the flow diagram below. This replicates a typical data-driven testing framework.

A data file is the first point of input for any type of DDT framework. You can have multiple files for a range of scenarios that test:

positive and negative test cases,

exception throwing and exception handling test cases,

min-max limit cases, and

all the permutations of input data that amount to appropriate data coverage for your application.

This data file is only usable if you can parse this data through the Driver Script based on the test logic. These data sets can be stored in data storage files such as excel, .xml, .json, .yml, or even an HDP database.

Pro-tip: Include hash (key, value pairs), arrays, and data tables to structure big data coherently for data-driven testing.

Note: If you are using a codeless automation platform that has built-in features for DDT, like Testsigma, you don’t need to manage this.

Driver script ‘carries out’ the actions specified in the test script. It works like an executable file. The driver script reads data from the data files used in the test scripts to execute corresponding tests on the application and then outputs results. It may also compare the output with ‘expected results’ if specified.

A driver script is often a simple piece of code written efficiently. A driver script in DDT usually contains the application code and the implementation code that works together. Overall, DDT revolves around how efficiently an application can be tested. Technically, it’s about how the test data works with the test script to bring out expected results.

Data-Driven scripts are similar to application-specific scripts (pyspark script, JavaScript) programmed to accommodate variable datasets.

Automation scripts with dynamic variables. In DDT, everything is tested dynamically with different variables of data sets. Therefore, you need modified scripts capable of handling dynamic data and its behavior when the application runs.

Duplication of the test design. This is often tricky but doable! The idea is to have the workflow followed in manual testing duplicated or reproduced in the automation workflow. This will allow the same test design that a manual tester would follow to be automated by an automation process. Comparing the two, manual testing cushions the way an application works by manually triggering the next process in real-time, whereas automation workflow tests this transition through code which should work seamlessly without any intervention.

This brings us to the last part of this section.

Within a DDT framework, you understand how your application works with user data. So when your application gives an output, the output needs validation. This validation is achieved by comparing the actual output against the expected ones. If there are mismatches, you find the root cause and fix it without affecting the expected workflow of the product. All of this requires a sturdy feedback loop and collaborative automation tools.

During this process, you might also add new test cases to validate an instance more thoroughly. In such cases, you need to modify the data file and the driver script to fit requirements.

With an efficient test automation architecture, there are many benefits DDT frameworks can bring to your release cycle. This is what we discuss in the next section.

The ability to parametrize your testing is extremely important at scale:

Regression testing: Regression testing revolves around having a set of test cases that are triggered to run at every build. This is to ensure that the additions made to the software for the latest release do not affect the previously working functionality of the software. Data-driven testing (DDT) accelerates this process. Since DDT makes use of multiple sets of data values for testing, regression tests can run for end-to-end workflow for multiple data sets.

Reusability. It creates a clear and logical separation of the test scripts from the test data. In other words, you don’t have to modify the test cases over and over again for different sets of test input data. Separating variables (test data) and logic (test scripts) makes them both reusable. Changes in either the test script or the test data will not affect the other. If a tester wants additional test data to be added, he can do so without disturbing a test case. Likewise, if a programmer wants to change the code in the test script, he can do so without worrying about the test data.

DDT enables scale, but there are certain limitations with its methodology.

In a cycle where you are testing data continuously, the ‘right data set’ is hard to come by! Data validations are time-consuming processes, and the quality of such tests is dependent on the automation skills of the SDETs.

Although DDT separates test scripts and test data, it’s important to not kill the job prematurely. Sometimes, to save time, SDETs program the script to only test up to a certain number of rows in a large dataset, which leaves room for errors with other rows of input data.

For a tester, debugging errors, even in a DDT environment, may be difficult due to the lack of exposure they have to a programming language. They would generally not be able to identify logical errors while a script runs and throws an exception.

Increased documentation. Since DDT approaches testing in a modular way, there will be an increased need to have them documented to make it easier for all team members to know the structure and workflow of the framework and automation. Such documentation would be around script management, its infrastructure, results obtained at different levels of testing, and so on.

To make data-driven testing as efficient as it can get, here is a checklist of best practices you should implement:

Testing with Positive and Negative Data: Everyone tests for positives, but testing the negatives, aka exceptions, is also equally important. A system’s performance is gauged by its ability to handle exceptions. These exceptions can occur because of a worst-case scenario that was reproduced in the system at some point. An efficient system should be designed to handle these exceptions well.

Driving dynamic assertions: Driving dynamic assertions that augment the pre-test values/scenarios into the latest ones are essential. Verifications get critical during code revisions and new releases. At this time, it’s important to have automated scripts that can augment these dynamic assertions, i.e., include what was previously tested into the current test lines.

Minimizing manual effort: Teams often still rely on manual interventions to trigger an automated workflow. This should be minimized. When we have a workflow with multiple navigational or re-directional paths, it’s best to write a test script that can accommodate all of this because a manual trigger is never an efficient way to test a navigational flow. Therefore, it is always a best practice to have the test-flow navigation coded inside the test script itself.

The perspective of the test cases: Perspective should also be considered! This is more of an insightful testing practice than a logical one. If you are interested in checking the workflow, you run basic tests to avoid a break or an exception that is anticipated somewhere in the process. But having the same tests extended for additional capabilities, such as security and performance, will provide fool-proof coverage into the existing network of the design. For instance, you can test the performance of the same workflow by introducing data that meets the max limits of a product – by observing the latency of the load, get-pull from APIs, and so on.

Try to form tests that create their own scenario test data.

Ensure tests don’t make any permanent changes to the database.

Include tests that cover positive as well as negative test scenarios.

Create automated test scripts that can easily be modified for various data scenarios.

Place test data in one storage format for all test cases, be it an excel spreadsheet or a table.

Refer to the test result report generated for any errors in the test execution due to data issues.

Complimentary to the architecture as explained in the previous section, DDT accommodates both positive and negative test cases in a single flow of the test.

Test data is stored in a columnar distribution, a table, or a structure that mimics a spreadsheet format. This storage canvas is a test table/data file. This file contains all inputs to different test scenarios. It also contains values tagged as ‘expected output’ in a separate column.

Assume that we have the following data table recorded that only has positive test cases recorded.

A script is written to read the data from the data file, such that the test input is picked up from every cell of the data file and substituted as the variable in the run-time flow. This is called a Driver Script. Now that tools automate this process, a Driver Script does not need actual programming. Based on the tool’s specifications, all you’ll have to do sometimes is connect data sets to the test cases.

Testsigma enables you to create Test Data Profiles outside your scripts and manage large data sets (positive and negative) in plain English and with a codeless, point-and-click module.

The driver script is written in a way where it’s enabled to ‘read’ a file, and pick up a variable from the data file in a predefined format (for instance, read column A, cell 2A; column B, cell 2B; Name C, cell 3C). It also performs a logic or runs this through an application and ‘writes’ the output in column E. For example, in the above data file, assuming that the script is drafted fairly straightforward, will open the file > read ‘Dan’ > read ‘Brown’ > read ‘Hotel 1’ > Run through code at runtime > outputs a value ‘2’ which is fed in column E. If the script has run 3 times, then the table would look like this:

The highlighted cells are blank, as the assumption here is that the computation was incomplete. Once all the tests run, the outputs recorded in Columns D and E are compared. If they match, the design is passed to production. If not, feedback that lists problems with an RCA (Root Cause Analysis) must be shared across teams.

Let us see an example of DDT. Consider the Login Page of a Hotel Reservation website. A pseudo workflow could be as follows:

A test data file is created as TestData_HotelReservation.csv (Comma Separated Values)

This file contains inputs given to the driver script and expected results in a table

The driver script for the above data file will be,data = open(‘TestData_HotelReservation.csv’).read()lines = data.splitlines()

Steps performed for above driver scripts are as follows:

Read Value1

Read Value2

Read Operator

Calculate the result using an operator on Value1 and value2

Finally, compare the expected result with the actual result

Testsigma enables data-driven testing for the below storage types:

Data tables created within Testsigma

Excel files.

.json files.

The excel and .json files can be easily imported into Testsigma. A plain English test script can then be configured to read data from these data files by the toggle of a button. See how it works.

A test automation framework is a set of components like test-data handling capabilities, object and class repositories, templates, and more that enables teams to automate tests coherently and uniformly. It makes automated tests easier to maintain and reuse.

An automation framework can be built to enable one or more of the following:

Linear test scripting

The Test Library Architecture Framework

The Data-Driven testing Framework

The Keyword-Driven or Table-Driven Testing Framework

The Hybrid Test Automation Framework

Test data can either be the actual data taken from previous operations or a set of artificial data designed for testing purposes. Test data is created depending on the testing environment before the test execution begins.

Test data can be generated in the following ways:

Copy of data from production to testing environment.

Copy of data from client machines.

Manual test data generation process.

Automated test data generation tools, e.g.: SQL data generator, DTM data generator.

Back-end data injection: Back-end Data Injection provides swift data injection in the system. It enables the creation of backdated entries, which is a huge drawback in both manual as well as automated test data generation techniques.

Third party tools: Testsigma has a built-in test data generation feature.

If test data is generated during the test execution phase, it is quite possible that you may exceed your testing deadline. In order to simplify the test management process, sample data should be generated before you begin test execution. Refer here to know more details on test data generation.

Now that we understand what test data is, we very well understand and are in terms of the fact that test data is a critical piece of programming or testing an application. Test Data cannot be random all the time. Therefore, you’ll need to scrape data usable data, which can be from past operations collected and archived. Other ways to gather data are:

Through brainstorming and manually collecting data.

Mass copy from the production environment to the staging environment.

Usage of automated test data generation tools.

Using legacy client systems to duplicate or fork data.

How does one validate such test data and, therefore, test cases? This is where BDT comes into play. In other words, Behavior Driven Testing supports management staff with limited technical knowledge.

Testing need not be a technical function all the time. In complex systems, some applications require logical test data such as dates in terms of duration, discounts and other mathematical calculations, temperature measurements, and so on. This type of data is most often required in real-time systems, such as areas related to logistics and inventory management. The skill set here is inclined more toward logical, rational, and research-based expertise rather than tech skills. These kinds of tasks are often palmed off to management personnel or product owners. Test automation strategy also adapts to this personnel, and that is where Behavior Driven Testing comes into play.

Behavior Driven Testing is focused on the behavior of users for an application rather than the technical functions of the software. The test cases are written in a natural language that is verbose and easily understood by anyone, encouraging clear cross-functional communication across teams.

When you can run, why crawl? Automation has sped up software testing by leaps and bounds, enabling Agile delivery at all scales and scopes. Data-driven testing is just an extension of that automation. In this guide, we took an in-depth look into the nuances of practical parametrized testing that works.

The data-driven testing is a way to automate the creation, use, and management of vast test input data. Automating data-driven testing has several benefits. It improves efficiency by reducing manual efforts and providing consistent and reliable results that avoid human errors.

A data-driven framework will separate the test script logic from the test data, allowing QAs to reuse the same script logic with different data sets. In a data-driven framework, we store test data separately from the test script code, usually in a database or spreadsheet. After that, the test script code is designed to retrieve and execute the test using the data.

Data Driven Framework in Selenium WebDriver | Software Testing Material

What are TestNG DataProviders?

One of the important aspects of TestNG is data-driven testing. DataProviders feature is a part of data-driven testing wherein it provides different values to a test case in a single execution. It means you can run a single test case once but can execute a method with different data values. For instance, you can test a registration page with different inputs but you will be executing the test only once. TestNG data-providers feature passes all these values to the test case one at a time so that it tests different data sets in a single execution.

The need for TestNG DataProviders

TestNG Parameters is a similar feature that provides values to functions via an XML file. Using parameters, you can run multiple functions with a single data set or run a single function with multiple data sets. However, the challenge with parameters is that you can execute the test case with that value only once. In a standard testing project, you need to run test cases with multiple values. In such a case, you have to execute the test case multiple times with different data sets. TestNG data-providers solve this challenge by enabling you to execute the test case only once but use different data sets. It provides arrayed data values to test methods via Annotations.

Data-Driven Testing With Selenium or With an Automated Testing Tool?

There are two options when it comes to data-driven testing:

1. You can build a data-driven testing framework from scratch leveraging other libraries and frameworks (Selenium, Apache POI, etc.).
2. You can use an automation testing tool that comes with a data-driven testing framework that is ready to use immediately.

Let’s compare the two approaches using the example above.

In our example, you can prepare an Excel sheet with four columns: “Username”, “Password”, “Address”, and “Payment Method”. You can add as many rows as you like for different sets of user data.

Username	Password	Address	Payment Method
username1	password1	Street 1 City 1	Visa credit card
username2	password2	Street 2 City 2	Visa debit card
username3	password3	Street 3 City 3	PayPal

After that, you need to create a new Java project in your IDE and add the Selenium WebDriver and TestNG libraries to your project.

Finally, write your test script to incorporate logic to read data. In our case, the script should iterate through each username and password, use them to login to the user account, and then initiate the checkout process where it fills in the address, before initiating the payment method.

If you are not familiar with coding, check out the top automation testing tools on the market that can help you automate this process without having to write a lot of code. This is the second approach. Many automated testing tools already have a DDT framework for you to use.

If you decide to go with this approach, simply create the test cases leveraging their built-in features. Top testing tools should come with helpful test authoring features such as Built-in Keywords or Record-and-Playback, which can drastically reduce your time spent writing code. They even come with Smart Reporting features which automatically generate out-of-the-box reports with rich insights on the quality of the system under test. It’s really simple!

In other words, to do data-driven testing, follow the following steps:

1. Prepare your test data in the form of Excel, CSV, JSON, or a database.
2. Set up a testing framework that supports data-driven testing such as TestNG, JUnit, or libraries like Apache POI or CSVReader. You can also go with tools that have a data-driven testing framework already built-in such as Katalon Platform, Leapwork, or SmartBear so you can start testing right away without having to create everything from scratch.
3. Create a test case with the framework/tool of your choice. Incorporate the logic to read data and execute test steps accordingly.
4. Execute the test script and generate a report to capture test results. If your testing framework doesn’t automatically generate test reports, you also need to implement the functionality. For testing tools, this feature usually comes ready to use.
5. Review, troubleshoot, and debug.

Let’s find out how we can do data-driven testing with Selenium and with Katalon – a comprehensive automation testing tool.

Cách tạo một Data Driven Automation Framwork

1. Dowload Apache POI (https://poi.apache.org/download.html)
2. Tích hợp Apache POI vào eclipse (https://www.softwaretestingmaterial.com/handling-excel-files-using-apache-poi/)
3. Viết kịch bản test
4. Đọc dữ liệu từ file lưu trữ
5. Viết dữ liệu ra file lưu trữ (nếu cần)


import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.concurrent.TimeUnit; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; 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.firefox.FirefoxDriver; import org.testng.annotations.BeforeTest; import org.testng.annotations.Test; public class Driver { private XSSFWorkbook workbook; private XSSFSheet sheet; private XSSFCell cell; private WebDriver driver; private String url = "https://www.facebook.com/"; @BeforeTest public void TestSetup() { System.setProperty("webdriver.gecko.driver", "C:\\\\Users\\\\linhntd\\\\Downloads\\\\geckodriver-v0.20.1-win64\\\\geckodriver.exe"); driver = new FirefoxDriver(); driver.manage().timeouts().implicitlyWait(1, TimeUnit.SECONDS); driver.get(url); System.out.println("Linh"); } @Test public void Read() { // Import file excel File src = new File("D:\\2018_STUDY\\test.xlsx"); FileInputStream fis; try { fis = new FileInputStream(src); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheet("Sheet1"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { /* * I have added test data in the cell A2 as "[email protected]" and B2 as * "password" Cell A2 = row 1 and column 0. It reads first row as 0, second row * as 1 and so on and first column (A) as 0 and second column (B) as 1 and so on */ // Import data for Email. cell = sheet.getRow(i).getCell(0); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.xpath("//input[@id='email']")).clear(); driver.findElement(By.xpath("//input[@id='email']")).sendKeys(cell.getStringCellValue()); // Import data for password. cell = sheet.getRow(i).getCell(1); cell.setCellType(Cell.CELL_TYPE_STRING); driver.findElement(By.id("pass")).clear(); driver.findElement(By.id("pass")).sendKeys(cell.getStringCellValue()); // To click on Login button driver.findElement(By.xpath("//input[@id='u_0_2']")).click(); // To write data in the excel FileOutputStream fos = null; try { fos = new FileOutputStream(src); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } // Message to be written in the excel sheet String message = "Pass"; // Create cell where data needs to be written. sheet.getRow(i).createCell(2).setCellValue(message); // finally write content try { workbook.write(fos); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // To click on Account settings dropdown // driver.findElement(By.xpath("//div[text()='Account Settings']")).click(); // To click on logout button // driver.findElement(By.xpath("//text()[.='Log // Out']/ancestor::span[1]")).click(); // close the file try { fos.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // Load he workbook. // Load the sheet in which data is stored. } }


Conclusion:

• Data-driven is a test automation framework which stores test data in a table or spread spreadsheet format.
• In Data-driven test automation framework, input data can be stored in single or multiple data sources like xls, XML, csv, and databases.
• To create an individual test for each data set is a lengthy and time-consuming process. Data Driven Testing framework resolves this issue by keeping the data separate from Functional tests.
• In Data Driven Testing, it is an ideal option to use realistic information
• It allows testing application with multiple sets of data values during Regression testing
• Drawback of this method is that it is depended on the automation skills of the Implementing team

How To Create Data Driven Framework In Selenium

Himanshu Sheth

Posted On: September 28, 2021

123960 Views

30 Min Read

The efficiency of test automation largely depends on how well the ‘functionality under test’ is behaving against different input combinations. For instance, an email provider would have to verify different screens like login, sign-up, etc., by supplying different input values to the scenarios. However, the effort involved in maintaining the test code rises significantly with new functionalities in the web product.

The question that needs to be asked is, “Should changes in the business rules lead to new test scenarios, or can existing test scenarios be extended to suit the needs of the new rules?” This is where Data Driven Framework in Selenium can come in super handy, as it enables the QA engineer to test different scenarios using different data sets. The beauty of the Data Driven Testing Framework in Selenium lies in the fact that there is a clear bifurcation of the test code and test data. The test data is kept in an external data feed like MS Excel Sheets, CSV Files, and more.

In this Selenium Java tutorial, we deep dive into the nuances of data driven tests in Selenium and how the popular Data Driven Framework in Selenium can be used for realizing data driven testing as well as cross browser testing. The demonstration of the Data Driven Framework in Selenium WebDriver is performed using the TestNG framework and Apache POI (Poor Obfuscation Implementation) API in Selenium.

TABLE OF CONTENTS

• Introduction to Data Driven Testing in Selenium WebDriver
• Data Driven Framework in Selenium WebDriver
• What is Apache POI
• How to create Data Driven Framework in Selenium WebDriver using Apache POI
• Perform Cross Browser Testing in Selenium using Apache POI

Conclusion

Data Driven Testing is one of the ideal ways for performing testing at scale. Separation of test data from Functional tests is one of the major advantages of the Data Driven Framework in Selenium. The separation of test data from the tests ensures that minimal changes in the business rules do not result in changes in the test implementation. This also avoids rewriting of test code when testing against multiple sets of data.

Apache POI is a popular library that provides APIs for performing CRUD (Create, Read, Update, and Delete) operations on external data sources like MS Excel Sheets, MS Access databases, and more. Data Driven Framework in Selenium is used along with Apache POI for performing data-driven testing using different test combinations. External data sources like Excel Sheets can also supply different browser & OS combinations for realizing cross browser testing for web projects.

Data Driven Framework in Selenium WebDriver

Data Driven Framework is a highly effective and widely utilized automation testing framework that enables iterative development and testing. It follows the principles of data-driven testing, allowing you to drive test cases and test suites using external data feeds such as Excel Sheets (xls, xlsx), CSV files (csv), and more. By establishing a connection with the external data source, the test script seamlessly performs the required operations on the test data, ensuring efficient and accurate testing.

Using the Data Driven Framework in Selenium WebDriver, the test data set is separated from the test implementation, reducing the overall effort involved in maintaining and updating the test code. Minimal changes in the business rules will require changes in the test data set, with/without minimal (or no) changes in the test code.

Selenium WebDriver lets you perform automated cross browser testing on web applications; however, it does not have the support to perform create, read, update, and delete (CRUD) operations on external data feeds like Excel sheets, CSV files, and more. This is where third-party APIs like Apache POI has to be used since it lets you access and performs relevant operations on external data sources.

Nhược điểm của Data- Driven testing

1. Chất lượng của quá trình test phụ thuộc vào kỹ năng test tự tộng và thực hiện của team
2. Sẽ mất một khoảng thời gian dài để đọc và tìm kiếm nếu bộ test dữ liệu có kích thước lớn

All rights reserved

Data-Driven Testing Guide With Selenium And Katalon

Working as a software tester, are you sometimes tired of the repetitive and time-consuming nature of the whole process? If yes, a data-driven framework is exactly what you need.

A data-driven framework in Selenium is an approach to automated testing that separates test scripts from test data, enabling testers to run the same test script with different data inputs. It involves storing test data in external sources like spreadsheets or databases and dynamically feeding this data into Selenium scripts during test execution.

Why is this significant? Imagine effortlessly testing your application across various user inputs, browsers, and platforms, all while reducing maintenance efforts. Data-driven frameworks make data-driven testing a breeze, saving you time and resources, and ultimately enhancing the quality of your software. Say goodbye to repetitive testing and hello to streamlined, data-powered success!

In this article, we’ll explain the concept of a data-driven framework in-depth and then provide you with the code for such a framework in Selenium.

Conclusion

Data-driven testing is an amazing approach to improve your efficiency in highly repetitive test scenarios. You can either build a data-driven testing framework from scratch using Selenium and libraries, which is highly customizable for specific scenarios, but once those scripts are broken, maintaining them is quite a daunting task. Or, you can go with an automated testing tool that comes with a prebuilt data-driven testing framework that you can use right away.

What is Data Driven Testing? Learn to create Framework

Data Driven Testing is a software testing method in which test data is stored in table or spreadsheet format. Data driven testing allows testers to input a single test script that can execute tests for all test data from a table and expect the test output in the same table. It is also called table-driven testing or parameterized testing.

Best practices of Data Driven testing:

Below given are Best testing practices for Data-Driven testing:

• It is ideal to use realistic information during the data-driven testing process
• Test flow navigation should be coded inside the test script
• Drive virtual APIs with meaningful data
• Use Data to Drive Dynamic Assertions
• Test positive as well as negative outcomes
• Repurpose Data Driven Functional Tests for Security and Performance

Benefits of Data-driven Testing

For software testers, DDT brings a variety of benefits:

• Efficiency: By systematically adding new cases by updating the data source rather than writing new test scripts from the beginning, DDT significantly reduces the effort required to maintain and expand test suites.
• Reusability: Testers are allowed to reuse the same test scripts with different sets of test data during regression testing. This means they do not have to create separate scripts for each test case, reducing redundancy and maintenance efforts.
• Maintenance: Since test data is stored externally, any changes to test data can be made without changing the test script. This simplifies maintenance and reduces the risk of introducing errors when making updates.
• Scalability: As the application grows and more test scenarios are required, testers can simply add more data to your external sources without modifying the test script itself, which makes the framework utterly scalable.
• Collaboration: As the framework is already established, different team members can work on test data and test scripts concurrently, leading to better collaboration and development.
• Faster test execution: By running the same test logic with multiple data sets in a batch, this framework can improve test execution speed, especially when combined with parallel execution.
• Test coverage: Using various data sets, QA teams can test a wider range of scenarios, including edge cases and boundary conditions, which can improve the overall test coverage.

As a whole, DDT provides better flexibility, maintainability, and scalability for test automation efforts. Indeed, it helps QA teams streamline the testing process and improve efficiency by separating test data from test script logic.

Data-driven Testing Examples

Imagine that you are the tester of an established e-commerce company. Your goal is to ensure that the checkout process is working as expected, so you want to run a test to see if users can authenticate their account seamlessly on your website. We can immediately see that this test scenario poses several challenges:

1. How do we ensure that we cover all possible user authentication scenarios, when there are so many variations of email, passwords, social media accounts, etc. to be checked?
2. Users tend to enter their address in a highly inconsistent manner, so how do we make sure that their address formats are valid so that no issues occur during the shipping process?
3. What are the conditions for valid/invalid card payments?
4. What are the promotional codes/coupons available at the moment and how do we make sure that they cause no conflicts with each other?

Those challenges can easily be addressed with data-driven testing. Instead of manually typing in specific values for each scenario (which is essentially a hard-coding approach), we can simply prepare the test data for all of the possible scenarios in one database, with each row of data representing one scenario, then write a script to read test data and execute the test steps.

Step 4: Install Selenium

Selenium is a popular open-source test automation framework that has been quite popular in recent times. Visit this link to download Selenium software:

https://www.selenium.dev/downloads/

Download the Selenium Standalone Server which comes as a jar file. The latest stable version is 3.141.59. You don’t have to install it. Simply save it to your machine.

What is Data-driven Testing?

Data-driven testing (DDT) is a testing method where you use different types of test data to run the same test script or test case. Instead of creating separate test scripts for each input data, DDT allows you to reuse your test code. Its primary goal is to validate application behavior under a wide range of input conditions, focusing on executing the same test logic using multiple data sets to ensure the application behaves accurately in different scenarios.

At its core, DDT is a process with the following four steps:

• Capturing embedded data from external data sources such as spreadsheets, CSV files, or databases.
• Using available automated test scripts and variables, enter the input data in the AUT (application under test).
• Making a comparison between actual output with expected results.
• Executing the same test logic again for each data set.

Imagine testing a login functionality where you want to check various scenarios: valid login, invalid login, login with different user roles, and so on. Instead of writing separate scripts for each scenario, DDT lets you input different usernames and passwords from your data source, automatically running the tests for all combinations.

First Test with TestNG DataProviders

Now that all the tools are installed, let us run the first test to see how TestNG DataProviders work.

Open the Eclipse IDE and click on New -> Java Project. Provide a name to the project and click next.

On the next screen, click on Library and click on ‘Add external jar’.

Select the Selenium Standalone Server jar file and click ok. Selenium gets added to your project.

Now, click on ‘Add Library’ and select TestNG in the next screen to add TestNG to your project.

Selenium and TestNG are added to your project along with the Java runtime environment.

Click on New -> Package to create a new package.

Create a new class in that package. Eg: ExperitestDataProviders.java

Now, add this code to the class. Note that it contains TestNG DataProviders.

[java] package ExperitestDataProviders; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class ExperitestTestNGDP { @DataProvider (name = “Experitest-DataProvider”) public Object[][] ExperitestDPMethod(){ return new Object[][] {{“Experitest1”}, {“Experitest2”}, {“Experitest3”}, {“Experitest4”}, {“Experitest5”}}; } @Test (dataProvider = “Experitest-DataProvider”) public void ExperiTest (String val) { System.out.println(“Passed Parameter Is : ” + val); } } [/java]

• Here, we have defined a Data provider with a name ‘ Experitest-DataProvider’ and provided 5 values (Experitest1, Experitest2, etc.)
• During the test (@Test), we have called the data provider.
• We wrote a method with a string parameter and stored the value in ‘val’
• The test case will take the string value and print it.

When you run this test case once, it will execute it 5 times and each time one string value is printed. So, using the DataProviders feature, we have run a single test case multiple times.

This is the output that shows that 5 tests ran and 5 tests passed.

How to create a Data Driven Framework in Selenium using Apache POI?

We have learned in the previous article “Read & Write Data from Excel in Selenium” how to read and write data in Excel files using Apache POI and then pass the same data sets as test data to the Selenium tests. But in that script, all the actions of reading data from an Excel file, writing data to the Excel file, passing the data to the Selenium actions were happening in the main method of the class. That format is acceptable if we are just writing one or two test cases. However, when we have to develop an automation framework that will have multiple test scenarios, then it should properly organize and should have a defined folder hierarchy.

A basic thumb rule for the data driven testing framework would be to segregate the test data from the test scripts. Also, the actions to read/write the data from files should segregate and be available as utilities.

Follow the steps as mentioned below to create a basic Data Driven framework, which will be used to automate the “Student Registration Form”.

• Create three New Packages in your Project for testCases, testData, and utilities.
• Under the testData package, put your Excel Sheet that has test data. Using this, we separate the test data from the testCases.
• Under the utilities, create a New Class and name it “ExcelUtils”. It will contain all functions related to Excel used for reading and writing.
• Under the utilities package, create another class “Constants”. It will contain the constant values across the framework like testdata file path, URL of the application, etc.
• Under the testCases package, we will create the test files that contain the Selenium code for interacting with web elements. (For Example, RegisterStudentTest.java)

After performing the above steps, the folder structure will look like:

Let’s understand the details of each of these classes:

1. ExcelUtils Class – It is a utility class that will contain all the methods related to Excel Sheet read and write operations along with initializing the Workbook. You can then reuse the methods in different test cases, by creating an object of Excel Utils Class. The code for this class will be as below –


package utilities; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; public class ExcelUtils { private static HSSFWorkbook workbook; private static HSSFSheet sheet; private static HSSFRow row; private static HSSFCell cell; public void setExcelFile(String excelFilePath,String sheetName) throws IOException { //Create an object of File class to open xls file File file = new File(excelFilePath); //Create an object of FileInputStream class to read excel file FileInputStream inputStream = new FileInputStream(file); //creating workbook instance that refers to .xls file workbook=new HSSFWorkbook(inputStream); //creating a Sheet object sheet=workbook.getSheet(sheetName); } public String getCellData(int rowNumber,int cellNumber){ //getting the cell value from rowNumber and cell Number cell =sheet.getRow(rowNumber).getCell(cellNumber); //returning the cell value as string return cell.getStringCellValue(); } public int getRowCountInSheet(){ int rowcount = sheet.getLastRowNum()-sheet.getFirstRowNum(); return rowcount; } public void setCellValue(int rowNum,int cellNum,String cellValue,String excelFilePath) throws IOException { //creating a new cell in row and setting value to it sheet.getRow(rowNum).createCell(cellNum).setCellValue(cellValue); FileOutputStream outputStream = new FileOutputStream(excelFilePath); workbook.write(outputStream); } }


The above code contains different methods like setExcelFile to initialize the Excel Workbook, getCellValue to retrieve the value present in a particular cell in the file, setCellValue to set some value into a newly created cell. In a similar way, you can create different methods related to excel operations in this class.

1. Constants Class- It is used to put constant values in a file so that the same can be reused across test cases. One more advantage of placing values in separate files is that since these values are common across various tests if there is any change in any of the values, you will just have to update in one place. For example, if the file path is changed, then instead of updating all the test cases with the new value, you can just update it here in one file. The structure and values present in this class are as below –


package utilities; public class Constants { public static final String URL = "https://demoqa.com/automation-practice-form"; public static final String Path_TestData = "E:\\Projects\\src\\testData\\"; public static final String File_TestData = "TestData.xls"; }


1. RegisterStudentTest- It is the test script for the student registration form, which we used to enter the first name, last name, mobile, email, gender, etc for a particular student. Since we have now separated the excel related methods in a separate file, the code of our test case also changes.

We will create an object of ExcelUtils class in this test file and also use Constants to refer to the path of the file.

The updated code now looks like –


package testCases; import org.openqa.selenium.By; import org.openqa.selenium.JavascriptExecutor; import org.openqa.selenium.WebDriver; import org.openqa.selenium.WebElement; import org.openqa.selenium.chrome.ChromeDriver; import utilities.Constants; import utilities.ExcelUtils; import java.io.IOException; import java.util.concurrent.TimeUnit; public class RegisterStudentTest { //creating object of ExcelUtils class static ExcelUtils excelUtils = new ExcelUtils(); //using the Constants class values for excel file path static String excelFilePath =Constants.Path_TestData+Constants.File_TestData; public static void main(String args[]) throws IOException { //set the Chrome Driver path System.setProperty("webdriver.chrome.driver","E:\\Projects\\chromedriver.exe"); //Creating an object of ChromeDriver WebDriver driver = new ChromeDriver(); //launching the specified URL driver.get("https://demoqa.com/automation-practice-form"); //Identify the WebElements for the student registration form WebElement firstName=driver.findElement(By.id("firstName")); WebElement lastName=driver.findElement(By.id("lastName")); WebElement email=driver.findElement(By.id("userEmail")); WebElement genderMale= driver.findElement(By.id("gender-radio-1")); WebElement mobile=driver.findElement(By.id("userNumber")); WebElement address=driver.findElement(By.id("currentAddress")); WebElement submitBtn=driver.findElement(By.id("submit")); //calling the ExcelUtils class method to initialise the workbook and sheet excelUtils.setExcelFile(excelFilePath,"STUDENT_DATA"); //iterate over all the row to print the data present in each cell. for(int i=1;i<=excelUtils.getRowCountInSheet();i++) { //Enter the values read from Excel in firstname,lastname,mobile,email,address firstName.sendKeys(excelUtils.getCellData(i,0)); lastName.sendKeys(excelUtils.getCellData(i,1)); email.sendKeys(excelUtils.getCellData(i,2)); mobile.sendKeys(excelUtils.getCellData(i,3)); address.sendKeys(excelUtils.getCellData(i,4)); //Click on the gender radio button using javascript JavascriptExecutor js = (JavascriptExecutor) driver; js.executeScript("arguments[0].click();", genderMale); //Click on submit button submitBtn.click(); //Verify the confirmation message WebElement confirmationMessage = driver.findElement(By.xpath("//div[text()='Thanks for submitting the form']")); //check if confirmation message is displayed if (confirmationMessage.isDisplayed()) { // if the message is displayed , write PASS in the excel sheet using the method of ExcelUtils excelUtils.setCellValue(i,6,"PASS",excelFilePath); } else { //if the message is not displayed , write FAIL in the excel sheet using the method of ExcelUtils excelUtils.setCellValue(i,6,"FAIL",excelFilePath); } //close the confirmation popup WebElement closebtn=driver.findElement(By.id("closeLargeModal")); closebtn.click(); //wait for page to come back to registration page after close button is clicked driver.manage().timeouts().implicitlyWait(2000,TimeUnit.SECONDS); } //closing the driver driver.quit(); } }


The above class will perform the actions on the student registration page. However, if you notice, methods of the ExcelUtils handle all the excel related code.

So, this is one of the ways you can use the data driven framework in Selenium. Additionally, you can utilize the advantage of running the same test across multiple sets of data.

Keywords searched by users: data driven testing in selenium

Categories: Tìm thấy 46 Data Driven Testing In Selenium

See more here: kientrucannam.vn

See more: https://kientrucannam.vn/vn/