i
Search icon

Selenium Step By Step Guide

Self Learning   /   Study Material   /   TestNG Parameterization Excel Reading
Previous
Next

TestNG Parameterization Excel Reading

We have seen earlier how to do the parameterization in TestNG annotations. Now let us discuss the TestNG parameterization with the help of an excel file. We have DataProvider annotation in TestNG to provide multiple sets of data as a parameter to the test method. We can provide data from an excel sheet to the method using Data Provider.

DataProvider annotation:

@DataProvider(name=“dataProviderMethod”)

Data provider helps to provide multiple sets of data to a test case. We need to handle some test cases where a different set of data needs to be tested on the same test case. Let us see the steps to use DataProvider annotation and fetching data to the data provider method by using an excel file.

  1. Create a TestNG test case with Data provider without excel.
  2. Add the Apache Poi library into the project.
  3. Create an Excel reading class to read and fetch the data.
  4. Implement the Data provider method in the TestNG test case with excel.
  5. Run the test class.

Create a TestNG test class:

Let us see the below sample code of a TestNG test class:

package SeleniumTest.SeleniumTest;

 

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.AfterMethod;

import org.testng.annotations.BeforeMethod;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

public class DataProviderTestNG {

                WebDriver driver;

                @BeforeMethod

                void setup()

                {

                                driver= new ChromeDriver();

                                this.driver.get("https://selflearning.io/login");

                }

                @Test(dataProvider="testDataProvider")

                void parameterized(String username,String password) throws InterruptedException

                {

                                Thread.sleep(3000);

                                driver.findElement(By.xpath("//*[@id='admin_login']/div[1]/input")).sendKeys(username);

        driver.findElement(By.xpath("//*[@id='admin_login']/div[2]/input")).sendKeys(password);

                }

                @DataProvider(name="testDataProvider")

                public static Object [][] loginDetails()

                {

                                return new Object [][]{{"adminU1","adminP1"},{"adminU2","adminP2"}};                        

                }

                @AfterMethod

                void cleanup()

                {

                                driver.quit();

                }

}

As we can see that the data is hardcoded in the above sample code. Here we need to provide the same data with the help of an excel file.

Add the Apache Poi library into the project:

We need to import the Apache Poi library for making a connection to read the excel data. Let us add the maven dependency of Apache Poi library to write the code to read the Excel file with our Selenium test code.

<dependency>

    <groupId>org.apache.poigroupId>

    <artifactId>poi-ooxmlartifactId>

    <version>3.12version>

dependency>

We can check the latest version of the same in the maven repository.

Now let us create an excel file that contains the username and password of two different users. Let us see the content of excel file below:

Test

Username

Password

Test1

user1

password1

Test2

user2

password2

 

We can save this file as testData.xlsx and keep it in an accessible file location as we need to pass the file path of this file to read the excel for our data provider.

Let us write see the sample code to read from the Excel. This class is named as “ExcelReading” class:

package SeleniumTest.SeleniumTest;

        import java.io.FileInputStream;

            import java.io.FileNotFoundException;

            import java.io.IOException;

            import org.apache.poi.xssf.usermodel.XSSFCell;

            import org.apache.poi.xssf.usermodel.XSSFRow;

            import org.apache.poi.xssf.usermodel.XSSFSheet;

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

    public class ExcelReading {

                  private static XSSFSheet ws;

                  private static XSSFWorkbook wb;

                  private static XSSFCell Cell;

                  private static XSSFRow Row

            public static Object[][] getExcelArray(String filePath, String sheetName) throws Exception {  

                  String[][] tabArray = null;

               try {

                     FileInputStream file = new FileInputStream(filePath);

                     // Excel sheet reading

                     wb = new XSSFWorkbook(file);

                     ws = wb.getSheet(sheetName);

                     int startRow = 1;

                     int startCol = 1;

                     int ci,cj;

                     int totalRows = ws.getLastRowNum();

                     int totalCols = 2;

                     tabArray=new String[totalRows][totalCols];

                     ci=0;

                     for (int i=startRow;i<=totalRows;i++, ci++) {             

                          cj=0;

                           for (int j=startCol;j<=totalCols;j++, cj++){

                                 tabArray[ci][cj]=getCellData(i,j);

                                 System.out.println(tabArray[ci][cj]); 

                                    }

                              }

                        }

                  catch (FileNotFoundException e){

                        System.out.println("Excel sheet is not readable");

                        e.printStackTrace();

                        }

                  catch (IOException e){

                        System.out.println("Excel sheet is not readable");

                        e.printStackTrace();

 

                        }

                  return(tabArray);

                  }

            public static String getCellData(int RowNum, int ColNum) throws Exception {

                  try{

                        Cell = ws.getRow(RowNum).getCell(ColNum);

                              String CellData = Cell.getStringCellValue();

                              return CellData;                      

                  }

                        catch (Exception e){

                        System.out.println(e.getMessage());

                        throw (e);

                        }

 

                  }

 

      }

Now instead of passing hardcoded data, we can call the getExcelArray() method to get the array of data to pass it to the data provider method in our TestNG test case. Let us see the modified “DataProviderTestNG” test class:

package SeleniumTest.SeleniumTest;

import java.io.File;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.AfterMethod;

import org.testng.annotations.AfterTest;

import org.testng.annotations.BeforeMethod;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Optional;

import org.testng.annotations.Parameters;

import org.testng.annotations.Test;

public class DataProviderTestNG {

                WebDriver driver;

                static String file="C://Users //workspace//SeleniumTestBlog//testData.xlsx";

                @BeforeMethod

                void setup()

                {

                               

                                System.setProperty("webdriver.chrome.driver","C:\\Users \\chromedriver.exe");

                                driver= new ChromeDriver();

                                this.driver.get("https://selflearning.io/login");

                }

                @Test(dataProvider="loginDetails")

                void parameterized(String username,String password) throws InterruptedException

                {

                                Thread.sleep(3000);

                                driver.findElement(By.xpath("//*[@id='admin_login']/div[1]/input")).sendKeys(username);

        driver.findElement(By.xpath("//*[@id='admin_login']/div[2]/input")).sendKeys(password);

       

                }

                @DataProvider

                public static Object [][] loginDetails() throws Exception

                {                             

                                Object [][] tabArray=ExcelReading.getExcelArray(file, "Sheet1");

                                return tabArray;  

                }

                @AfterMethod

                void cleanup()

                {

                                driver.quit();

                }

}

Now let us run the test class “DataProviderTestNG”. We see that the data provider pics the data from the excel sheet and pass it the test method for username and password.

© All rights Reserved | SelfLearning.io