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 1st 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)
_xlsreader.java)
4. Copy paste the below code and change the path of the excel file as per your project specification.
package CommonLibraries;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
importorg.apache.poi.hssf.util.HSSFColor;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.IndexedColors;
importorg.apache.poi.xssf.usermodel.*;
import java.io.*;
importjava.math.BigDecimal;
importjava.sql.Connection;
importjava.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;
publicReadXlsDummy(String path) {
this.path=path;
try {
fis = newFileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
fis.close();
} catch (Exception e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
}
// returns the row count in a sheet
public intgetRowCount(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 booleansetCellData(String sheetName,String colName,int rowNum, String data){
try{
fis = newFileInputStream(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 = newFileOutputStream(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 booleansetCellData(String sheetName,String colName,int rowNum, String data,String url){
//System.out.println("setCellData setCellData******************");
try{
fis = newFileInputStream(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 = newFileOutputStream(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 booleanaddSheet(String sheetname){
FileOutputStream fileOut;
try {
workbook.createSheet(sheetname);
fileOut = newFileOutputStream(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 booleanremoveSheet(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return false;
FileOutputStream fileOut;
try {
workbook.removeSheetAt(index);
fileOut = newFileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
// returns true if column is created successfully
public booleanaddColumn(String sheetName,String colName){
//System.out.println("**************addColumn*********************");
try{
fis = newFileInputStream(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 = newFileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}
// removes a column and all the contents
public booleanremoveColumn(String sheetName, int colNum) {
try{
if(!isSheetExist(sheetName))
return false;
fis = newFileInputStream(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 = newFileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}
// find whether sheets exists
public booleanisSheetExist(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 intgetColumnCount(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 booleanaddHyperLink(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(inti=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 intgetCellRowNum(String sheetName,String colName,String cellValue){
for(inti=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;
importjava.io.FileInputStream;
import java.io.IOException;
importjava.util.Arrays;
importjava.util.Collection;
importjava.util.Properties;
import org.junit.Before;
import org.junit.Test;
importorg.junit.runner.RunWith;
importorg.junit.runners.Parameterized;
importorg.junit.runners.Parameterized.Parameters;
importorg.openqa.selenium.By;
importorg.openqa.selenium.WebDriver;
importorg.openqa.selenium.firefox.FirefoxDriver;
importCommonLibraries.ReadXlsDummy;
import CommonLibraries.xlsgetdata;
importdemo_util.demo_testbase;
import demo_util.demo_util_file;
@RunWith(Parameterized.class)
public class paramtrial extends demo_testbase {
public String Username;
public String Password;
publicparamtrial(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 staticCollection<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);
}
}
0 Comments