Skip to content

Iptv Assist

Learn More from us

Menu
  • HOW TO
  • Firestick
  • Chromecast
  • PC Apps
  • Lg Smart TV
  • IPTV Services
  • Automation Testing
  • Smart TV
  • Software Testing Tools
  • Contact Us
Menu

Data Driven Framework in Selenium WebDriver Using Apache POI

Posted on January 22, 2023

Best Iptv Service Provider 2023 With 40k+ Channels And 100k+ VOD . 24/7 Suppport . Paypal Supported

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

Best Iptv Service Provider 2023 With 40k+ Channels And 150k+ VOD . Hurry Up

Data Driven Framework is one of the popular Automation Testing Framework 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.

Recommended IPTV Service Providers

  1. IPTVGREAT – Rating 4.8/5 ( 600+ Reviews )
  2. IPTVRESALE – Rating 5/5 ( 200+ Reviews )
  3. IPTVGANG – Rating 4.7/5 ( 1200+ Reviews )
  4. IPTVUNLOCK – Rating 5/5 ( 65 Reviews )
  5. IPTVFOLLOW -Rating 5/5 ( 48 Reviews )
  6. IPTVTOPS – Rating 5/5 ( 43 Reviews )

 

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

Therefore, we use third party APIs like Apache POI.

What you will learn in this tutorial:

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

What You Will Learn:

  • What is Apache POI?
  • Why data drive tests?
  • What do we need to implement Data Driven Framework?
  • Interface in POI
  • The steps to use Selenium with Apache POI
  • Advantages of using Data Driven Framework
  • Conclusion

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.

Data Driven Framework in Selenium using Apache POI

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

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

Recommended reads:

  • Most Popular Test Automation Frameworks
  • Data Driven Testing in SoapUI Pro

Why 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.

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 you can check here.

What do we need to implement Data Driven Framework?

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

Once done, we will look at:

  • Various interfaces of Apache POI.
  • Integration of Apache POI in the Eclipse.
  • Read Data from the Excel Sheet.
  • Write 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 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.

The steps to use Selenium with Apache POI

Let us create an automation script to test the login process of a 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)

Firstly, we need to configure 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.

excel sheet

Step #4)

Now let us follow the sample code to read 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 saw 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 file, like HSSF for .xls and XSSF for .xlsx.
  • Valid row and column index is used.
  • 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 code.
  • Less maintenance.
  • Faster Execution.
  • Permits better error handling.

Conclusion

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

Selenium integrated 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 comments below.

Related

Best Iptv Service Provider 2023 With 40k+ Channels And 150k+ VOD . Hurry Up

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • IPTV List: Best iptv lista 2023
  • IPTV Premium: Best Premium IPTV Service Provider List And Benefits
  • Nikon IPTV Review: Over 10,000 Live Channels for $12/Month
  • Iptvwings. Com Review: +18000 Live IPTV Channels ,+70000 Movies, +40000 TV show For $15/1 month
  • IPTVUNO Review: More Than 16000 Live TV channels, 55,000 Movies & VOD For $15/Month

Recent Comments

  1. IPTV List: Play lista iptv 2022 - Iptv Assist on Best IPTV Player in 2023 for Watching Live TV
  2. Cola IPTV – Over 18,000 Live Channels for $12/Month - Iptv Assist on FileLinked – How to Install on Firestick/Fire TV and Android Devices
  3. Cola IPTV – Over 18,000 Live Channels for $12/Month - Iptv Assist on 50+ Best IPTV Service Providers for Streaming Live TV 2023
  4. XoomsTV IPTV – Over 11,000 Channels & VOD for Under $13/Month on Best VPN for IPTV in 2023 and How to Install on Firestick/Android
  5. Voodoo Streams IPTV Review – Over 12,000 Channels for $11/Month - Iptv Assist on Dynasty TV IPTV Review – Over 6,000 Channels for $10/Month

Archives

  • January 2023

Categories

  • Activate
  • Agile Testing
  • Alternatives
  • Android
  • APK
  • Apple TV
  • Automation Testing
  • Basics of Software Testing
  • Best Apps
  • Breakfast Hours
  • Bug Defect tracking
  • Career in Software Testing
  • Chromebook
  • Chromecast
  • Cross Platform
  • Database Testing
  • Delete Account
  • Discord
  • Error Code
  • Firestick
  • Gaming
  • General
  • Google TV
  • Hisense Smart TV
  • HOW TO
  • Interview Questions
  • iPhone
  • IPTV
  • IPTV Apps
  • Iptv Service SP
  • IPTV Services
  • JVC Smart TV
  • Kodi
  • Lg Smart TV
  • Manual Testing
  • MI TV
  • Mobile Testing
  • Mod APK
  • newestiptv.com
  • News
  • Nintendo Switch
  • Panasonic Smart TV
  • PC Apps
  • Performance Testing
  • Philips Smart TV
  • PS4
  • PS5
  • Python
  • QA Certifications
  • QA Leadership
  • QA Team Skills
  • Quality Assurance
  • Reddit
  • Reviews
  • Roku
  • Samsung Smart TV
  • Screenshot
  • Selenium Tutorials
  • Sharp Smart TV
  • Skyworth Smart TV
  • Smart TV
  • Soft Skills For Testers
  • Software Testing Templates
  • Software Testing Tools
  • Software Testing Training
  • Sony Smart TV
  • Sports
  • Streaming Apps
  • Streaming Devices
  • Tech News
  • Test Management Tools
  • Test Strategy
  • Testing Best Practices
  • Testing Concepts
  • Testing Methodologies
  • Testing News
  • Testing Skill Improvement
  • Testing Tips and Resources
  • Toshiba Smart TV
  • Tutorials
  • Twitch
  • Types of Testing
  • Uncategorized
  • Vizio Smart TV
  • VPN
  • Web Testing
  • What is
  • Xbox
©2023 Iptv Assist | Design: Newspaperly WordPress Theme