Parameterization in Selenium Webdriver. (Reading data using excel sheet)

Parameterization in Selenium Webdriver. (Reading data using excel sheet)

Junit 4 has introduced a new feature Parameterized tests. Parameterized tests allow you  to run the same test over and over again using different values.

Objective : Our main objective is to store testdata in excel sheet and read the excel sheet and run the testcases , so that it can be changed as per Testing needs. Many a time the regression suite needs to be run on different environments. In that case, test data must be changed. Also if we want to run the same test for multiple test data, it can be done using selenium Junit parameterization.

Advantages:

1. It is not advisable to use hardcoded data in test scripts.

2. Same test may have to be run in several environments. So test data may differ and we may need to change the data as per testing needs.

3. The test needs to be run for different set of data (eg. Valid and Invalid).

Steps for Reading data from excel in selenium webdriver using Junit Framework.      

    1.        Create a folder name Config files in your project structure.

    2.       Create an excel file TestData.xls with the data in below format with the required test data. In the excel sheet, rename the sheet name as the “Test Name” to be used.

* The 1^st column of the sheet should be the keywords with which the data will be accessed.(They are basically variables names used in the code).

* The 2nd column of the sheet should be the test data.

    3.       Now create a package named demo_util. Inside it create a utility to read excel file (demo
_xlsreader.java)

   4.       Copy paste the below code and change the path of the excel file as per your project specification.

 package CommonLibraries;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFHyperlink;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.IndexedColors;

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

import java.io.*;

import java.math.BigDecimal;

import java.sql.Connection;

import java.util.Calendar;

public class ReadXlsDummy extends xlsgetdata{

      //public static String filename = System.getProperty("user.dir")+"\\src\\config\\TestData_WF.xlsx";

      public static String filename = System.getProperty("D:\\Workspace\\DemoSiteAutomation\\ConfigFiles\\TestData.xls");

      public  String path;

      public  FileInputStream fis = null;

      public  FileOutputStream fileOut =null;

      private XSSFWorkbook workbook = null;

      private XSSFSheet sheet = null;

      private XSSFRow row   =null;

      private XSSFCell cell = null;

     

      public ReadXlsDummy(String path) {

           

            this.path=path;

            try {

                  fis = new FileInputStream(path);

                  workbook = new XSSFWorkbook(fis);

                  sheet = workbook.getSheetAt(0);

                  fis.close();

            } catch (Exception e) {

                  // TODO Auto-generated catch block

                  e.printStackTrace();

            }

           

      }

      // returns the row count in a sheet

      public int getRowCount(String sheetName){

            int index = workbook.getSheetIndex(sheetName);

            if(index==-1)

                  return 0;

            else{

            sheet = workbook.getSheetAt(index);

            int number=sheet.getLastRowNum()+1;

            return number;

            }

           

      }

     

      // returns the data from a cell

      public String getCellData(String sheetName,String colName,int rowNum){

            try{

                  if(rowNum <=0)

                        return "";

           

            int index = workbook.getSheetIndex(sheetName);

            int col_Num=-1;

            if(index==-1)

                  return "";

           

            sheet = workbook.getSheetAt(index);

            row=sheet.getRow(0);

            for(int i=0;i<row.getLastCellNum();i++){

                  //System.out.println(row.getCell(i).getStringCellValue().trim());

                  if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))

                        col_Num=i;

            }

            if(col_Num==-1)

                  return "";

           

            sheet = workbook.getSheetAt(index);

            row = sheet.getRow(rowNum-1);

            if(row==null)

                  return "";

            cell = row.getCell(col_Num);

           

            if(cell==null)

                  return "";

            //System.out.println(cell.getCellType());

            if(cell.getCellType()==Cell.CELL_TYPE_STRING)

                    return cell.getStringCellValue();

            else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

                   

                    String cellText  = String.valueOf(cell.getNumericCellValue());

                    if (HSSFDateUtil.isCellDateFormatted(cell)) {

                       // format in form of M/D/YY

                          double d = cell.getNumericCellValue();

                          Calendar cal =Calendar.getInstance();

                          cal.setTime(HSSFDateUtil.getJavaDate(d));

                        cellText =

                         (String.valueOf(cal.get(Calendar.YEAR))).substring(2);

                       cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +

                                  cal.get(Calendar.MONTH)+1 + "/" +

                                  cellText;

                      

                       //System.out.println(cellText);

                     }

                   

                   

                    return cellText;

              }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)

                  return "";

              else

                    return String.valueOf(cell.getBooleanCellValue());

           

            }

            catch(Exception e){

                 

                  e.printStackTrace();

                  return "row "+rowNum+" or column "+colName +" does not exist in xls";

            }

      }

     

      // returns the data from a cell

      public String getCellData(String sheetName,int colNum,int rowNum){

            try{

                  if(rowNum <=0)

                        return "";

           

            int index = workbook.getSheetIndex(sheetName);

            if(index==-1)

                  return "";

           

     

            sheet = workbook.getSheetAt(index);

            row = sheet.getRow(rowNum-1);

            if(row==null)

                  return "";

            cell = row.getCell(colNum);

            if(cell==null)

                  return "";

           

        if(cell.getCellType()==Cell.CELL_TYPE_STRING )

              return cell.getStringCellValue();

        else if( cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

             

              String cellText  = String.valueOf(cell.getNumericCellValue());

              if (HSSFDateUtil.isCellDateFormatted(cell)) {

                 // format in form of M/D/YY

                    double d = cell.getNumericCellValue();

                    Calendar cal =Calendar.getInstance();

                    cal.setTime(HSSFDateUtil.getJavaDate(d));

                  cellText =

                   (String.valueOf(cal.get(Calendar.YEAR))).substring(2);

                 cellText = cal.get(Calendar.MONTH)+1 + "/" +

                            cal.get(Calendar.DAY_OF_MONTH) + "/" +

                            cellText;

                 

                // System.out.println(cellText);

               }

             

             

              return cellText;

        }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)

            return "";

        else if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC)

              return String.valueOf(BigDecimal.valueOf(cell.getNumericCellValue()));

        else

              return String.valueOf(cell.getBooleanCellValue());

            }

            catch(Exception e){

                 

                  e.printStackTrace();

                  return "row "+rowNum+" or column "+colNum +" does not exist  in xls";

            }

      }

     

      // returns true if data is set successfully else false

      public boolean setCellData(String sheetName,String colName,int rowNum, String data){

            try{

            fis = new FileInputStream(path);

            workbook = new XSSFWorkbook(fis);

            if(rowNum<=0)

                  return false;

           

            int index = workbook.getSheetIndex(sheetName);

            int colNum=-1;

            if(index==-1)

                  return false;

           

           

            sheet = workbook.getSheetAt(index);

           

            row=sheet.getRow(0);

            for(int i=0;i<row.getLastCellNum();i++){

                  //System.out.println(row.getCell(i).getStringCellValue().trim());

                  if(row.getCell(i).getStringCellValue().trim().equals(colName))

                        colNum=i;

            }

            if(colNum==-1)

                  return false;

            sheet.autoSizeColumn(colNum);

            row = sheet.getRow(rowNum-1);

            if (row == null)

                  row = sheet.createRow(rowNum-1);

           

            cell = row.getCell(colNum);  

            if (cell == null)

              cell = row.createCell(colNum);

          // cell style

          //CellStyle cs = workbook.createCellStyle();

          //cs.setWrapText(true);

          //cell.setCellStyle(cs);

          cell.setCellValue(data);

          fileOut = new FileOutputStream(path);

            workbook.write(fileOut);

          fileOut.close();   

            }

            catch(Exception e){

                  e.printStackTrace();

                  return false;

            }

            return true;

      }

     

     

      // returns true if data is set successfully else false

      public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){

            //System.out.println("setCellData setCellData******************");

            try{

            fis = new FileInputStream(path);

            workbook = new XSSFWorkbook(fis);

            if(rowNum<=0)

                  return false;

           

            int index = workbook.getSheetIndex(sheetName);

            int colNum=-1;

            if(index==-1)

                  return false;

           

           

            sheet = workbook.getSheetAt(index);

            //System.out.println("A");

            row=sheet.getRow(0);

            for(int i=0;i<row.getLastCellNum();i++){

                  //System.out.println(row.getCell(i).getStringCellValue().trim());

                  if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))

                        colNum=i;

            }

           

            if(colNum==-1)

                  return false;

            sheet.autoSizeColumn(colNum); //ashish

            row = sheet.getRow(rowNum-1);

            if (row == null)

                  row = sheet.createRow(rowNum-1);

           

            cell = row.getCell(colNum);  

            if (cell == null)

              cell = row.createCell(colNum);

                 

          cell.setCellValue(data);

          XSSFCreationHelper createHelper = workbook.getCreationHelper();

          //cell style for hyperlinks

          //by default hypelrinks are blue and underlined

          CellStyle hlink_style = workbook.createCellStyle();

          XSSFFont hlink_font = workbook.createFont();

          hlink_font.setUnderline(XSSFFont.U_SINGLE);

          hlink_font.setColor(IndexedColors.BLUE.getIndex());

          hlink_style.setFont(hlink_font);

          //hlink_style.setWrapText(true);

          XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);

          link.setAddress(url);

          cell.setHyperlink(link);

          cell.setCellStyle(hlink_style);

           

          fileOut = new FileOutputStream(path);

            workbook.write(fileOut);

          fileOut.close();   

            }

            catch(Exception e){

                  e.printStackTrace();

                  return false;

            }

            return true;

      }

     

     

     

      // returns true if sheet is created successfully else false

      public boolean addSheet(String  sheetname){          

           

            FileOutputStream fileOut;

            try {

                   workbook.createSheet(sheetname);  

                   fileOut = new FileOutputStream(path);

                   workbook.write(fileOut);

                 fileOut.close();            

            } catch (Exception e) {            

                  e.printStackTrace();

                  return false;

            }

            return true;

      }

     

      // returns true if sheet is removed successfully else false if sheet does not exist

      public boolean removeSheet(String sheetName){        

            int index = workbook.getSheetIndex(sheetName);

            if(index==-1)

                  return false;

           

            FileOutputStream fileOut;

            try {

                  workbook.removeSheetAt(index);

                  fileOut = new FileOutputStream(path);

                  workbook.write(fileOut);

                fileOut.close();             

            } catch (Exception e) {            

                  e.printStackTrace();

                  return false;

            }

            return true;

      }

      // returns true if column is created successfully

      public boolean addColumn(String sheetName,String colName){

            //System.out.println("**************addColumn*********************");

           

            try{                   

                  fis = new FileInputStream(path);

                  workbook = new XSSFWorkbook(fis);

                  int index = workbook.getSheetIndex(sheetName);

                  if(index==-1)

                        return false;

                 

            XSSFCellStyle style = workbook.createCellStyle();

            style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);

            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

           

            sheet=workbook.getSheetAt(index);

           

            row = sheet.getRow(0);

            if (row == null)

                  row = sheet.createRow(0);

           

            //cell = row.getCell();

            //if (cell == null)

            //System.out.println(row.getLastCellNum());

            if(row.getLastCellNum() == -1)

                  cell = row.createCell(0);

            else

                  cell = row.createCell(row.getLastCellNum());

             

              cell.setCellValue(colName);

              cell.setCellStyle(style);

             

              fileOut = new FileOutputStream(path);

                  workbook.write(fileOut);

                fileOut.close();             

            }catch(Exception e){

                  e.printStackTrace();

                  return false;

            }

           

            return true;

           

           

      }

      // removes a column and all the contents

      public boolean removeColumn(String sheetName, int colNum) {

            try{

            if(!isSheetExist(sheetName))

                  return false;

            fis = new FileInputStream(path);

            workbook = new XSSFWorkbook(fis);

            sheet=workbook.getSheet(sheetName);

            XSSFCellStyle style = workbook.createCellStyle();

            style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);

            XSSFCreationHelper createHelper = workbook.getCreationHelper();

            style.setFillPattern(HSSFCellStyle.NO_FILL);

           

         

     

            for(int i =0;i<getRowCount(sheetName);i++){

                  row=sheet.getRow(i);   

                  if(row!=null){

                        cell=row.getCell(colNum);

                        if(cell!=null){

                              cell.setCellStyle(style);

                              row.removeCell(cell);

                        }

                  }

            }

            fileOut = new FileOutputStream(path);

            workbook.write(fileOut);

          fileOut.close();

            }

            catch(Exception e){

                  e.printStackTrace();

                  return false;

            }

            return true;

           

      }

  // find whether sheets exists    

      public boolean isSheetExist(String sheetName){

            int index = workbook.getSheetIndex(sheetName);

            if(index==-1){

                  index=workbook.getSheetIndex(sheetName.toUpperCase());

                        if(index==-1)

                              return false;

                        else

                              return true;

            }

            else

                  return true;

      }

     

      // returns number of columns in a sheet  

      public int getColumnCount(String sheetName){

            // check if sheet exists

            if(!isSheetExist(sheetName))

             return -1;

           

            sheet = workbook.getSheet(sheetName);

            row = sheet.getRow(0);

           

            if(row==null)

                  return -1;

           

            return row.getLastCellNum();

           

           

           

      }

      //String sheetName, String testCaseName,String keyword ,String URL,String message

      public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){

            //System.out.println("ADDING addHyperLink******************");

           

            url=url.replace('\\', '/');

            if(!isSheetExist(sheetName))

                   return false;

           

          sheet = workbook.getSheet(sheetName);

         

          for(int i=2;i<=getRowCount(sheetName);i++){

            if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){

                  //System.out.println("**caught "+(i+index));

                  setCellData(sheetName, screenShotColName, i+index, message,url);

                  break;

            }

          }

            return true;

      }

      public int getCellRowNum(String sheetName,String colName,String cellValue){

           

            for(int i=2;i<=getRowCount(sheetName);i++){

            if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){

                  return i;

            }

          }

            return -1;

           

      }

           

      // to run this on stand alone

      public static void main(String arg[]) throws IOException{

           

            //System.out.println(filename);

            ReadXlsDummy datatable = null;

           

                        datatable = new ReadXlsDummy("D:\\Workspace\\DemoSiteAutomation\\ConfigFiles\\TestData.xls");

                        //datatable = new Xls_Reader("D:\\MMR_Automation_Final\\MMRAutomation\\src\\config\\TestData_WF.ods");

                        for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){

                              System.out.println(datatable.getCellData("TC5", col, 1));

                        }

      }

public static boolean skipFlag=false;

     

      public static Connection con=null;

     

     

     

     

     

      // get the skip condition

      // true - N

      // false - Y

      public static Boolean isSkip(String testCase){

           

            for(int rowNum=2 ; rowNum<=datatable.getRowCount("Test Cases");rowNum++){

                  if(testCase.equals(datatable.getCellData("Test Cases", "TCID", rowNum))){

                        if(datatable.getCellData("Test Cases", "Runmode", rowNum).equals("Y"))

                              return false;

                        else

                              return true;

                  }

                  }

           

            return false;

      }

                 

           

     

      public static ReadXLUtil datatable=null;

     

      public static Object[][] getData(String testName){

            // return test data;

            // read test data from xls

            if(datatable == null){

                  // load the suite 1 sheet

                  //datatable = new ReadXLUtil(System.getProperty("user.dir")+"//src//ConfigFiles//TestData.xls");

                  datatable = new ReadXLUtil("D:\\Workspace\\DemoSiteAutomation\\ConfigFiles\\TestData.xls");

                 

                  //datatable = new Xls_Reader(System.getProperty("user.dir")+"//src//config//TestData_WF.ods");

                 

            }

           

            int rows=datatable.getRowCount(testName)-1;

            if(rows <=0){

                  Object[][] testData =new Object[1][0];

                  return testData;

                 

            }

            System.out.println("chk");

          rows = datatable.getRowCount(testName);  // 3

            int cols = datatable.getColumnCount(testName);

            System.out.println("Test Name -- "+testName);

            System.out.println("total rows -- "+ rows);

            System.out.println("total cols -- "+cols);

            Object data[][] = new Object[rows-1][cols];

           

            for(int rowNum = 2 ; rowNum <= rows ; rowNum++){

                 

                  for(int colNum=0 ; colNum< cols; colNum++){

                        data[rowNum-2][colNum]=datatable.getCellData(testName, colNum, rowNum);

                  }

            }

           

            return data;

           

      }

     

     

}

       5. Now create a test case in Junit and Annotate test class with @RunWith(Parameterized.class)

      When a class is annotated with @RunWith or extends a class annotated with @RunWith, JUnit will invoke the class it references to run the tests in that class instead of the runner built into JUnit.

6.       Create a public static method annotated with @Parameters that returns a Collection of Objects (as Array) as test data set.

7.       Create a public constructor that takes in what is equivalent to one "row" of test data.

8.       Create an instance variable for each "column" of test data.

9.       Create your tests case(s) using the instance variables as the source of the test data.

10.   Change the name of the excel sheet and path according to your project.

11.   Now write the code for any application and replace the values of the hardcoded values from excel sheet.

 package Testcases;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Arrays;

import java.util.Collection;

import java.util.Properties;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.junit.runners.Parameterized;

import org.junit.runners.Parameterized.Parameters;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

import CommonLibraries.ReadXlsDummy;

import CommonLibraries.xlsgetdata;

import demo_util.demo_testbase;

import demo_util.demo_util_file;

@RunWith(Parameterized.class)

public class paramtrial extends demo_testbase {

     

      public String Username;

      public String Password;

     

      public paramtrial(String Username,String Password)

      {

            this.Username=Username;

            this.Password=Password;

      }

     

     

      @Test

       public void Test01() throws Throwable{

           

           

            Properties Config=new Properties();

            FileInputStream file=new FileInputStream(System.getProperty("user.dir")+"\\ConfigFiles\\Config.properties");

            Config.load(file);

           

            Properties Object=new Properties();

            FileInputStream file1=new FileInputStream(System.getProperty("user.dir")+"\\ConfigFiles\\ObjectRepo.properties");

            Object.load(file1);

           

            Properties excel=new Properties();

            FileInputStream file2=new FileInputStream(System.getProperty("user.dir")+"\\ConfigFiles\\TestData.xls");

            Object.load(file2);

     

     

            WebDriver driver = new FirefoxDriver();

            //driver.get("URL");

            driver.get("http://demo.mahara.org/");

           

            // driver.get(CONFIG.getProperty("URL"));

System.out.println("Testing opening of site");

            driver.findElement(By.id(Object.getProperty("demo_username_txtbox"))).sendKeys(Username);

            driver.findElement(By.id(Object.getProperty("demo_password_txtbox"))).sendKeys(Password);

            driver.findElement(By.id(Object.getProperty("demo_login_btn"))).click();

           

           

      /*getObject("demo_username_txtbox").sendKeys(Username);

            getObject("demo_password_txtbox").sendKeys(Password);

            getObject("demo_login_btn").click();*/

       }

     

     

     

     

       @Parameters

       public static Collection<Object[]> dataSupplier(){

                  System.out.println("Collecting data");

                 

                  // read data fromthkYou xls file and write in into Object array.

            //   

                  Object[][] data = ReadXlsDummy.getData("Login");

                  //System.out.println(data);

                  return Arrays.asList(data);

                 

       }

}

 

12.   The username and password values should get picked up from excel sheet and input in the textboxes.