Tuesday 28 August 2018

TestNG Data Provider with Excel

When we want to create follow data driven approach and create a framework then best way is to use Excel to store our data and we can pass the Data as parameters using Data providers which we have seen in our previous post.

Step by step process to Implement Excel with TestNg Data Provider.

Step 1: Create a test case of Login Application with TestNG Data Provider.

Step 2:  Create a Test Data sheet.

Step 3: Create functions to Open & Read data from Excel

Step 4: Create a TestNg test case for accepting data from Excel using Data Provider.

Step 5: Run the test against the Test Case name in the Test Data file.

Read Data From Excel Sheet using Apache POI library -

Below is our code to open Excel sheet and read data from it within our Selenium test script. For this purpose, we are using the Apache POI library, which allows us to read, create and edit Microsoft Office-documents using Java. The classes and methods we are going to use to read data from Excel sheet are located in the org.apache.poi.hssf.usermodel.

1. Download the Apache POI  from the link http://poi.apache.org/ and add it in your project build path.

2. Use Below Code to read Data from Excel file.


package test;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

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 ExcelUtils {

 private static XSSFSheet ExcelWSheet;

 private static XSSFWorkbook ExcelWBook;

 private static XSSFCell Cell;

 private static XSSFRow Row;

 public static void setExcelFile(String Path,String SheetName) throws Exception {
   
     try {

    // 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){

     throw (e);

    }

  }
 public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception {

  String[][] tabArray = null;

  try {

   FileInputStream ExcelFile = new FileInputStream(FilePath);

   // Access the required test data sheet

   ExcelWBook = new XSSFWorkbook(ExcelFile);

   ExcelWSheet = ExcelWBook.getSheet(SheetName);

   int startRow = 1;

   int startCol = 1;

   int ci, cj;

   int totalRows = ExcelWSheet.getLastRowNum();

   // you can write a function as well to get Column count

   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("Could not read the Excel sheet");

   e.printStackTrace();

  }

  catch (IOException e) {

   System.out.println("Could not read the Excel sheet");

   e.printStackTrace();

  }

  return (tabArray);

 }

 public static String getCellData(int RowNum, int ColNum) throws Exception {
 
   try{
 
    Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
 
    int dataType = Cell.getCellType();
 
    if  (dataType == 3) {
 
     return "";
 
    }else{
 
     String CellData = Cell.getStringCellValue();
 
     return CellData;
    }
 
    }catch (Exception e){
 
    System.out.println(e.getMessage());
 
    throw (e);
 
    }
 
   
 
  }
 public static String getTestCaseName(String sTestCase)throws Exception{
   
  String value = sTestCase;

  try{

   int posi = value.indexOf("@");

   value = value.substring(0, posi);

   posi = value.lastIndexOf("."); 

   value = value.substring(posi + 1);

   return value;

    }catch (Exception e){

   throw (e);

     }

  }

 public static int getRowContains(String sTestCaseName, int colNum) throws Exception{

  int i;

  try {

   int rowCount = ExcelUtils.getRowUsed();

   for ( i=0 ; i<rowCount; i++){

    if  (ExcelUtils.getCellData(i,colNum).equalsIgnoreCase(sTestCaseName)){

     break;

    }

   }

   return i;

    }catch (Exception e){

   throw(e);

   }

  }

 public static int getRowUsed() throws Exception {

   try{

    int RowCount = ExcelWSheet.getLastRowNum();

    return RowCount;

   }catch (Exception e){

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

    throw (e);

   }

  }
 public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow)    throws Exception
  
 {   

    String[][] tabArray = null;

    try{

     FileInputStream ExcelFile = new FileInputStream(FilePath);

     // Access the required test data sheet

     ExcelWBook = new XSSFWorkbook(ExcelFile);

     ExcelWSheet = ExcelWBook.getSheet(SheetName);

     int startCol = 1;

     int ci=0,cj=0;

     int totalRows = 1;

     int totalCols = 1;

     tabArray=new String[totalRows][totalCols];

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

      {

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

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

      }

  }

  catch (FileNotFoundException e)

  {

   System.out.println("Could not read the Excel sheet");

   e.printStackTrace();

  }

  catch (IOException e)

  {

   System.out.println("Could not read the Excel sheet");

   e.printStackTrace();

  }

  return(tabArray);

 }
    }

 3. Create excelfile with Data which we want to pass to our test case as parameter.


4. Update the Test case for using parameters from excel.


package test;


import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
 
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
 
import org.testng.annotations.Test;
 
import org.testng.annotations.BeforeMethod;
 
import org.testng.annotations.AfterMethod;
 
import org.testng.annotations.DataProvider;
 

 
public class TestCaseExcelData {
 
 private String sTestCaseName;
 
 private int iTestCaseRow;
 
 WebDriver driver;
 
  @BeforeMethod
 
  public void beforeMethod() throws Exception {
 
   System.setProperty("webdriver.chrome.driver", "C:\\Softwares\\chromedriver_win32\\chromedriver.exe");
  
   driver=new ChromeDriver();
      
      driver.get("http://www.qaautomated.com");
 
      Thread.sleep(5000);
  } 
 
  @Test(dataProvider = "search")
  
  public void test(String searchtext) throws Exception {
 
   
      driver.findElement(By.xpath("*//input[@class='search-field']")).sendKeys(searchtext);
      
      driver.findElement(By.xpath("*//input[@class='search-submit']")).click();
 
      
  }
 
  @AfterMethod
 
  public void afterMethod() {
 
    driver.close();
 
  }
 
  @DataProvider
 
  public Object[][] search() throws Exception{
 
     // Setting up the Test Data Excel file
 
   ExcelUtils.setExcelFile("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1");
 
   sTestCaseName = this.toString();
 
    // From above method we get long test case name including package and class name etc.
 
    // The below method will refine your test case name, exactly the name use have used
 
    sTestCaseName = ExcelUtils.getTestCaseName(this.toString());
 
     // Fetching the Test Case row number from the Test Data Sheet
 
     // Getting the Test Case name to get the TestCase row from the Test Data Excel sheet
 
   iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0);
 
     Object[][] testObjArray = ExcelUtils.getTableArray("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1",iTestCaseRow);
 
      return (testObjArray);
 
  }
 
}

11 comments:

  1. Get the Best Selenium Training in Bangalore from TIB Academy with 100% Placement Assistance

    ReplyDelete
  2. I read Many Post about Excel and Other Courses but I really Impressed about your Writing Way and How to Express to words.. It’s really helpful for us Thanks for sharing,, keep writing
    Advanced excel training in delhi
    Advanced excel training in Noida
    Advanced excel training in Gurgaon

    ReplyDelete
  3. There is lots of Writer but your writing way is so good and different. It’s really helpful for us and knowledgeable so thanks for sharing...
    Advanced Excel Training in Delhi
    Advanced Excel Training in Noida
    Advanced Excel Training in Gurgaon

    ReplyDelete
  4. There is lots of Writer but your writing way is so good and different. It’s really helpful for us and knowledgeable so thanks for sharing...
    Advanced Excel Training in Delhi
    Advanced Excel Training in Noida
    Advanced Excel Training in Gurgaon

    ReplyDelete
  5. Thanks for the info. Its very useful.
    I have a question here as I see you used class name as a test case name in your excel.
    sTestCaseName = this.toString();

    But i want to use actual test case method name(ex: "test" is the testcase name in your example.)I may have 20 test cases in single class and each test case has different set of data. Please let me know if you have answer for this

    ReplyDelete
  6. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point. keep it up
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete