Wednesday, October 29, 2014

Chapter 26- Data Driven using Excel

Data Driven using Excel

When designing tests, parameterizing the tests is inevitable. We will make use of Apache POI - Excel JAR's to achieve the same. It helps us to read and write into excel.

Download JAR

Step 1 : Navigate to the URL - http://poi.apache.org/download.html and download the ZIP format.
selenium_ide_152
Step 2 : Click on the Mirror Link to download the JAR's.
selenium_ide_153
Step 3 : Unzip the contents to a folder.
selenium_ide_154
Step 4 : Unzipped contents would be displayed as shown below.
selenium_ide_155
Step 5 : Now create a New project and add all the 'External JARs' under 'poi-3.10.FINAL' folder.
selenium_ide_147
Step 6 : Now add all the 'External JARs' under 'ooxml-lib' folder.
selenium_ide_148
Step 7 : Now add all the 'External JARs' under 'lib' folder.
selenium_ide_149
Step 8 : The Added JAR is displayed as shown below.
selenium_ide_150
Step 9 : The Package Explorer is displayed as shown below. Apart from that add 'WebDriver' related JAR's
selenium_ide_151

Parameterization

For Demo purposes, we will parameterize the percent calculator test.
Step 1 : We will parameterize all the inputs required for percent calculator using excel. The designed excel is shown below.
selenium_ide_156
Step 2 : Now we will execute all percent calculator for all the specified parameters.
Step 3 : Let us create generic methods to access the excel file using the imported JARS. These methods helps us to get a particular cell data or to set a particular cell data etc.
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
    
public class excelutils 
{
   private XSSFSheet ExcelWSheet;
   private XSSFWorkbook ExcelWBook;

   //Constructor to connect to the Excel with sheetname and Path
   public excelutils(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);           
   }
   }
              
    //This method is to set the rowcount of the excel.
    public int excel_get_rows() throws Exception 
    {
   try 
   {
      return ExcelWSheet.getPhysicalNumberOfRows();           
   } 
   catch (Exception e)
   {
    throw (e);
         
   }
    }
        

    //This method to get the data and get the value as strings.
    public String getCellDataasstring(int RowNum, int ColNum) throws Exception
    {
    try
    {
     String CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getStringCellValue();
     System.out.println("The value of CellData " + CellData);
     return CellData;
    }
    catch (Exception e)
    {
      return "Errors in Getting Cell Data";
    }
    }
    
 
  //This method to get the data and get the value as number.
    public double getCellDataasnumber(int RowNum, int ColNum) throws Exception
    {
    try
    {
    double CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getNumericCellValue();
    System.out.println("The value of CellData " + CellData);
    return CellData;
  }
  catch (Exception e)
  {
   return 000.00;
  }
    }
}
Step 4 : Now add a main method which will access the excel methods that we have developed.
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
    
public class excelutils 
{
   private XSSFSheet ExcelWSheet;
   private XSSFWorkbook ExcelWBook;

   //Constructor to connect to the Excel with sheetname and Path
   public excelutils(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);           
    }
   }
              
    //This method is to set the rowcount of the excel.
    public int excel_get_rows() throws Exception 
    {
    try 
    {
     return ExcelWSheet.getPhysicalNumberOfRows();           
  } 
  catch (Exception e)
  {
   throw (e);
  
  }
    }
      

    //This method to get the data and get the value as strings.
    public String getCellDataasstring(int RowNum, int ColNum) throws Exception
    {
    try
    {
     String CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getStringCellValue();
    //Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
    //String CellData = Cell.getStringCellValue();
    System.out.println("The value of CellData " + CellData);
    return CellData;
  }
  catch (Exception e)
  {
   return "Errors in Getting Cell Data";
  }
    }
    
   //This method to get the data and get the value as number.
    public double getCellDataasnumber(int RowNum, int ColNum) throws Exception
    {
    try
    {
     double CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getNumericCellValue();
    //Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
    //String CellData = Cell.getStringCellValue();
    System.out.println("The value of CellData " + CellData);
    return CellData;
  }
  catch (Exception e)
  {
   return 000.00;
  }
    }    
}

Output

Upon Executing the script, the output is displayed in console as shown below.
Selenium IDE 157

No comments: