What is Poi apache?How to read /Write Excel sheet in Selenium.

File Io is a anaytic part for any software  We frequently create a file, open it & update something or delete it in our Computers. Same is the case with Selenium Automation. We need a process to manipulate files with Selenium.

Java provides us different classes for File Manipulation with Selenium. In this  we are going to learn how can we read and write on excel file with the help of Java IO package and Apache  POI library.
What is Apache poi?
For Reading and writing in excel sheet  we have to configure poi jar files.

Step1:Download poi jar files from here.

We just need 5 jar files for reading excel files.

dom4j
xmlbeans-2.3.0.jar
poi-3.6
poi-ooxml-3.6
poi-ooxml-schemes
1.Add these jar files into the project.
2.Add selenium jar files into project.
For reference you can see how to add jar files set up a selenium project.

Click here

3.Yo need to
How to read excel file in selenium?
Step1:

Some prerequisites
(Create a project>Make a new class>Add jar files>Add poi jar files.)

How to read data from Excel sheet.
This is the link for downloading excel file.Please change the path according to your system.

How to read data from excel sheet.
How to get data from rows.
How to set data in Excel sheet.
public class ReadingExcelsheet {
public static void main(String[] args) {
//Make an object of Xls_Reader

Before making object of that class you have to copy this code from here.

Now copy this code into your project.
If you can't download from here.You can copy from below.
Xls_Reader datatable = new Xls_Reader("C:\\Users\\Ritika Gulati\\Documents\\Datatest.xlsx");
Please change path according to your system.
//How to count total number of rows in excel sheet
// datatable.getRowCount(sheetName)
int rowdata =  datatable.getRowCount("Records");
System.out.println("Total Rows in excel sheet           "+rowdata);
//How to get the data from excelsheet
// datatable.getCellData(sheetName, colNum, rowNum)

String data=  datatable.getCellData("Records", "City", 3);
System.out.println("This is data from 3rd row ,colomn name is city and sheet name records       "+data);
String data2=  datatable.getCellData("Records", "City", 2);
System.out.println("data from 2nd row,colomn name is city and sheet name records "+data2);
//How to set data in excel sheet
//datatable.setCellData(sheetName, colName, rowNum, data2)
datatable.setCellData("Records", "City", 10, "new data entered ");
}
}

//Below Reference code if you can't download  code from google drive.
You just have to copy this code and paste into your project.You just have to create an object of this class so that you can use all the methods.

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.util.Calendar;


public class Xls_Reader {
public static String filename = System.getProperty("user.dir")+"\\src\\config\\testcases\\TestData.xlsx";
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 Xls_Reader(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_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.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
 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);
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);
Xls_Reader datatable = null;


datatable = new Xls_Reader("C:\\riti\\Fr_Wles\\data.xlsx");
for(int col=0 ;col< datatable.getColumnCount("TC"); col++){
System.out.println(datatable.getCellData("TC", col, 1));
}
}

}

Read More

What is object repository/Property file in selenium.

Why we used property file in Selenium?
Whats the need of Property File/Repository??
Parameterization of a particular test is needed in order to avoid hard coding of values. The following Example actually demonstrates the usage of parameterization.
Let us assume that our test suite contains more than 1000 test cases and more than 50 different screens and we have thousands of objects on the application under test.  Multiple test cases may be (more than 10) using a particular screen.  If there is a change request from the client to enhance / change any of the object, then we need to update all the test cases. So its very difficult to change in Test cases.For avoiding updation in Testcases.We Create an Object Repository.If there are any changes in the application then we need to update only Object Repository.
What is Object Repository/Property File
In QTP, there is a concept called “Object Repository”.  All the objects are added to Object Repository.
So how we will do this in Selenium..You will used this object repository in framework a lot.
How to use Object Repository In selenium?
Step1:Create a new Project>Click on Finish

Step2:Make a package as config>Click on Finish

Step3:Now Right Click on config(Packagename)>New>Other

Step4:Click on other>ClickGeneral>Click on File

Step5:Now enter the name of propertyfile..or.properties>Click on Finish

Step6:Now Add the details in property file

propertyfile for gmail login..

How to use Property file

Create a new class as ReadingPropertyFile

//Here is the code for Reading property file
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
public class ReadingPropertyFile {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
//For reading property file we have to create object
    //Create an object of property class
//import property files.
Properties prop = new Properties();
//Now make an object of file input stream
//import the files after creating object
FileInputStream ip = new FileInputStream("D:\\User\\workspace2\\PropertyDemo\\src\\config\\OR.properties");
prop.load(ip);
System.out.println("load all propeties");
//launch instance of any browser
WebDriver driver = new FirefoxDriver();
//now open gmail/browser
driver.get("http://gmail.com");
//now pass the xpath by property file
driver.findElement(By.cssSelector(prop.getProperty("username"))).sendKeys("ritu7180");

driver.findElement(By.cssSelector(prop.getProperty("password"))).sendKeys("password");

driver.findElement(By.cssSelector(prop.getProperty("signup"))).click();
}

}

Reading property file is very easy.
Just Follow four steps:

Step1:make an object of Property class 

Properties prop = new Properties();

Step2:Make an Object of InputStreamClass
FileInputStream ip = new FileInputStream ip();
Step3:Pass the path of propertyfile in the fileinputstream constuctor.

Step4:load the property file by..
prop.load(ip);

Read More

Contains in xpath

How to work With dynamics id.

Why we use startwith...
When you are automating a web application, first step is to identify the ID for a web element. But when the ID of a web element is dynamically generated, then your tests to fail because the automation tool can’t identify the web element being tested.
So what are these Dynamic ID’s and how we can overcome them?
Dynamic IDs are automatically generated ID attributes attached to any web element such as buttons, text-fields and labels. They typically look like some identifier text combined with an auto-incremented number like these samples: ext-gen216, ext-gen217,ext-gen218
Generally these IDs are generated sequentially in an application. They may change from session to session, or even from window to window. Ext JS in Sencha is a common source of dynamic IDs.
One of the big problems with dynamic IDs is maintaining scripts between versions and even between different runs.
Solutions: How we can overcome the difficulties caused by dynamic IDs?
Use startwith function
suppose <input id= "ext-gen216"/input>
After a new session id is <input id= "ext-gen217"/input>
So every time it is generating a new id so we will take xpath as
//input[starts-with(@id, 'ext-gen')]
Now start-with function will pass your test..

Why use contains function in xpath.??
If you want your xpath shouldn't fail then try using contains function.
How it works?
If text is available there then use
Syntax1- //tagname[contains(text(),'YourText')]

Sometime xpath given by xpath fails so we use contains 

If text is available there then use

Sometime text is not available so we can also use contains..

<input class="search-bar-submit fk-font-13 fk-font-bold" type="submit" value="Search"/>
Now in this text is not then we can also use contains..
Syntax2-//tagname[contains(@class,"search-bar-submit fk-font-13 fk-font-bold")]
contains will match the text matching patterns are like..example( my name is ritika)
Patterns
pattern1 = my na
pattern2 = name is
pattern3 = s ritika
<input class="search-bar-submit fk-font-13 fk-font-bold" type="submit" value="Search"/>
No need to write whole value "search-bar-submit fk-font-13 fk-font-bold"
xpath1=//input[contains(@class,"search-bar-submit")] 
 xpath2=//input[contains(@class,"13 fk-font-bold")]
 xpath3=//input[contains(@class,"submit fk-font-13 fk")

Syntax3=//input[contains(@value,"Search")]

Read More

What is Continuous Integration/Jenkins/Selenium integration with Jenkins.

Continous Intgration is a Software Development Practice where members of the team integrate their work frequently usually each persion integrate daily leading to multiple integration per day..Each integration is verified by an automated build to detect integration errors as quicky as possible...
Why CI?

1. Rapid FeedBack
2. Reduced Risk
3. Collective OwnerShip
4. Continous Deployment

CI – What does it really mean? 

•  At a regular frequency (ideally at every commit), the system integrated
•  All changes up until that point are combined into the project 
•  Built 
•  The code is compiled into an executable or package 
•  Tested 
•  Automated test suites are run 
•  Archived 
•  Versioned and stored so it can be distributed as is, if desired 
•  Deployed 
•  Loaded onto a system where the developers can interact with it 

CI – Benefits 

•  Immediate bug detection 
•  No integration step in the lifecycle 
•  A deployable system at any given point 
•  Record of evolution of the project 

Ques:Which is the most widely used continuous integration tool?

Ans:Jenkins...

Jenkins is an open-source continuous integration software tool written in the Java programming language.

Jenkins is a highly configurable system by itself.

This diagram will show you Jenkins process:
Jenkins - History 
 2005 - Hudson was first release by Kohsuke Kawaguchi of 
Sun Microsystems 
 2010 – Oracle bought Sun Microsystems 
 Due to a naming dispute, Hudson was renamed to Jenkins 
 Oracle continued development of Hudson (as a branch of the 

original) 
What is the Use of Jenkins in Selenium
Scenario:
Suppose your Boss or Team lead assigns you 200 testcase to execute in a day.So how you will do this.If your team lives  in different areas.One is in Banglore,Other is in Noida Third one is in Us.
By using Jenkins you can do this.
We will create our testcases and deploy on svn.(A Repository).What Jenkins will do?Jenkins will run those test case which are on repository.
Repository is a central hub where you all will store your Test case.
We can run our testcase using build.xml or Svn using BatchFile or Git
One of the most important feature of jenkins is 
Scheduling
We can schedule our build periodically.
Suppose you need to run build 1 at 10 am
Suppose you need to run a build2 at 2 Pm tomorrow.
Notifications:
It also provide email notification whether the test case is passed or failed.(Depends on the Configuration).
Step By Step Process to install and Configure Jenkins With your Script.
Step1.Go to Link

Step2:Keep This Jenkins.war File where your project is lying/Where you have workspace for Example:
C:Users\WorkSpace\ProjectName

Jenkins.War

                                              This is my Project Workspace
Step3:Open Command prompt and go till project home directory and append "Java -jar Jenkins.war and run it.

After adding java -jar jenkins.war.Run it.By pressing enter.

Now Jenkins is fully up.
Step4:Open your Browser and give localhost url :> http://localhost:8080

This is a Start up page.Now Click on Mange Jenkins.
Step5:Now click on Configure System

Step6:Now Set The jdk path.Uncheck the install automatically

Step7:Add the path of your jdk.Now click on Add jdk

Step8:Now Create a New Job.
To run your TestCase.We have to Create a New Job
Click on New Item

Add a name into ItemName
Click on radio Button of FreeStyleProject

Now Click on Advanced...

Step9:Now Add the Path of yourProject...In directory..
Note:EveryTime if you will create a new job you have to give the path of your project.

Step10:Click on Add build step:

Select Excecute Window BatchCommand:
Add BatFile into it..

How To Make BatFile
Step10:To Run Your TestCase/Job.First Create a Bat file into your ProjectDirectory

Note:
Command for Making Batfile:
java -cp bin;Jarfile/* org.testng.TestNG testng.xml
Jarfile is name of Jarfile which is in ProjectDirectory.
Jarfile should contain all the JarFile
Step11:Now next step is 
Open commandprompt and SetClassPath

set classpath=yourprojectpath\bin;YourProjectpathagain\Jarfile\*;

Step11:Now Go to Jenkins.Add run.bat 

After adding run.bat into it
Click on Save and Apply..
Step12:Now job is ready.You can run your TestCase..

 Click on Build Now
Note:You should have mention path in project directory..(Step9) is should be there.

This is the output of Test.If it is in blue  then Pass. if it is red then Fail.

How to Do Scheduling for TestCase.
Suppose you want to run your TestCase after every hour..

You can add any cron pattern for scheduling....
Refer to Wikipedia  for cron Pattern..
Linkk for cron pattern

Read More