DataDriven Automation in selenium web driver using POI library.

DataDriven Automation in selenium web driver using POI library

The Most important part of any automation project is data-driven operations. Because it’s really hard to input data in your script manually every time. So we need some external file who can manage our data from outside. To perform Data-driven operation using selenium web driver we need to use Apache POI library. It will help to read and write data in excel files. In this tutorial, We are going to cover how to read data from excel using POI lib in selenium web driver as well as how to write data also and what is DataDriven Automation in selenium web driver.

 What is Apache POI library

The most common API used with selenium web driver for data-driven testing. POI library is written in Java to perform read and write operations from external files like excel or word. You can download the POI library from link https://poi.apache.org/download.html.

 Maven dependency for the Apache POI library.

  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

Apache POI library supports both xlsx and xls files. Interface which is start with “X” represents xlsx files and which is start with “H” represent xls files.

Below is the list of interfaces in POI library.

 XSSFWorkbook: Represents workbook in xlsx file.

XSSFSheet: Represents a sheet in xlsx file.

XSSFRow: Represents a row in a sheet of xlsx file.

XSSFCell: Represents a cell in a row of xlsx file.

HSSFWorkbook: Represents workbook in xls file.

HSSFSheet: Represents a sheet in xls file.

HSSFRow: Represents a row in a sheet of xls file.

HSSFCell: Represents a cell in a row of xls file.

What is DataDriven automation in selenium web driver and why we need this in automation?

Let’s consider and scenario to understand in a better way.

We need to automate a login form and there are two fields username and password. We need to test this login form with 100 different users. In this case, it’s really difficult to write 100 scripts for the same login form. It’s really hard to do that so we can import data from an external file and Execute a single script multiple time.

Read data from excel Apache POI is selenium web driver.

 Here is a simple example to read data from an external file. To use POI library you need to configure it in your project. To configure POI library you can download the library and import in the project and the other and most convey way is using Maven dependency. If you are using maven project that I will suggest you use maven dependency it’s really easy to maintain.

public static XSSFSheet readExcel(String Path, String SheetName) {
    try {
      System.out.println(Path);
      // Open the Excel file
      FileInputStream ExcelFile = new FileInputStream(Path);
      // Access the required test data sheet
      ExcelWBook = new XSSFWorkbook(ExcelFile);
      ExcelWSheet = ExcelWBook.getSheet(SheetName);
    } catch (Exception e) {

      System.out.println(e);

    }
    returnExcelWSheet;
  }

In the above method, I am trying to read data from xlsx file. This method having two parameters 1st one is the path of the file and 2nd one will take the name of your worksheet. FileInputStreamclass will create a connection to read data. XSSFWorkbook class will take this connection as an input and return the access of the xlsx file.

ExcelWBook.getSheet(SheetName) method will take sheet name as a parameter and return the worksheet with data.

Above is the custom method now we can call this method to read data.

To call the method:

XSSFSheetexcelSheet = TestListener.readExcel("DataFiles\\testData.xlsx", "Sheet1");

To get the Data.

String msg = excelSheet.getRow(0).getCell(1).getStringCellValue();

Complete Source code to read data from excel in selenium web driver.

import java.util.concurrent.TimeUnit;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import pageObjects.InputFromsPage;
publicclass Main {
  static WebDriver driver;
  publicstatic XSSFSheet excelSheet;
  publicstatic XSSFCell cell;

  publicstaticvoid main(String[] args) {

    System.setProperty("webdriver.chrome.driver", "D:\\SeleniumAutomation\\ChromeDriver\\chromedriver.exe");
    driver = new ChromeDriver();
    driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);

    // Call the method to read data from excel........ and pass two parameter path
    // and sheet name.
    excelSheet = TestListener.readExcel("DataFiles\\testData.xlsx", "Sheet1");

    // Navigate the URL.
    driver.navigate().to("http://www.seleniumeasy.com/test/");

    // Click to menu bar to get input forms
    driver.findElement(By.xpath(InputFromsPage.inputFromsMenu)).click();
    driver.findElement(By.xpath(InputFromsPage.simpleFromDemo)).click();

    // Get data from excelSheet.
    String msg = excelSheet.getRow(0).getCell(1).getStringCellValue();

    // Pass the data in the input field.
    driver.findElement(By.xpath(InputFromsPage.inputMSG)).sendKeys(msg);
    driver.findElement(By.xpath(InputFromsPage.showButton)).click();

  }
}

Write data in excel using Apache POI in selenium web driver.

Public static void write(String path, String data) throws IOException, InvalidFormatException {
    // Create Connection.............
    InputStream inp = new FileInputStream(path);
    // Access excel file.....
    Workbook wb = WorkbookFactory.create(inp);
    // Get the SheetNumber index start with 0.
    Sheet sheet = wb.getSheetAt(0);
    // Get the last row number of the current sheet.
    intnum = sheet.getLastRowNum();
    // Increase the last row number with 1.
    Row row = sheet.createRow(++num);
    // Create a new cell and set the value or data.
    row.createCell(0).setCellValue(data);
    // Now this Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(path);
    // Write the data
    wb.write(fileOut);
    // close the connection
    fileOut.close();
  }

Above Method, We can use to write data in the excel file. At the time of calling this method is required two parameters. 1st one will take the path of the file and the 2nd one will take data or value you want to write or append data in existing the file.

To call the method:

TestListener.write("Path of the file", "Data to Write");

Complete example to write data in excel using POI library using selenium web driver.

import java.io.IOException;
import java.util.concurrent.TimeUnit;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import pageObjects.InputFromsPage;

publicclass Main {
  static WebDriver driver;

  publicstaticvoid main(String[] args) {

    System.setProperty("webdriver.chrome.driver", "D:\\SeleniumAutomation\\ChromeDriver\\chromedriver.exe");
    driver = new ChromeDriver();
    driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);

    // Navigate the URL.
    driver.navigate().to("http://www.seleniumeasy.com/test/");

    // Click to menu bar to get input forms
    driver.findElement(By.xpath(InputFromsPage.inputFromsMenu)).click();
    driver.findElement(By.xpath(InputFromsPage.simpleFromDemo)).click();

    // Pass the data in the input field.
    driver.findElement(By.xpath(InputFromsPage.inputMSG)).sendKeys("Hello");

    // Get the button text
    String BtnText = driver.findElement(By.xpath(InputFromsPage.showButton)).getText();
    try {

      // Call the write method
      TestListener.write("DataFiles\\testData.xlsx", BtnText);
    } catch (InvalidFormatException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOExceptione) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    driver.findElement(By.xpath(InputFromsPage.showButton)).click();

  }

}

 

Add a Comment

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