Reading and writing data from Excel using Selenium Web driver

This code will work for reading data from excel sheet :

import java.io.IOException;
import org.testng.annotations.Test;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
@Test
public class Xlsheet {
public void asd() throws IOException, BiffException, Throwable{
Workbook workbook = Workbook.getWorkbook(new File ("FilePath"));
System.out.println(workbook);
Sheet sheet = workbook.getSheet("Sheet1");
System.out.println(sheet.getRows());

String s = sheet.getCell(1,1).getContents();
System.out.println(s);
}
}

This code will work for Writing data into excel sheet :

package sample;

import java.io.FileOutputStream;

import org.testng.annotations.Test;

import jxl.Workbook;

import jxl.write.WritableWorkbook;

import jxl.write.WritableSheet;

import jxl.write.Label;

@Test

public class ExcelWrite {

public void aa() throws Throwable{

//Creating an excel and naming it. 

FileOutputStream exlFileName= new FileOutputStream("Filepath");

//Creating an instance for the above excel.

WritableWorkbook exlWorkBook = Workbook.createWorkbook(exlFileName);

//Creating Writable work sheets for the above excel.

WritableSheet exlWorkSheet1 = exlWorkBook.createSheet("Shivaprsad",0);

WritableSheet exlWorkSheet2 = exlWorkBook.createSheet("Suresh",1);

//Creating data(cell values) into above excel workbook

Label Sheet1cellContent = new Label(0,0,"Test sheet1");

Label Sheet2cellContent = new Label(0,0,"Test sheet2");

//Adding cell value to its respective sheet.

exlWorkSheet1.addCell(Sheet1cellContent);

exlWorkSheet2.addCell(Sheet2cellContent);

//Writing the values into excel.

exlWorkBook.write();

//Close the workbook

exlWorkBook.close();

}

}

   

        

Verification points in Selenium ...

1.    To check Element Present:

if(driver.findElements(By.xpath("value")).size() != 0){

System.out.println("Element is Present");

}else{

System.out.println("Element is Absent");

}

Or

if(driver.findElement(By.xpath("value"))!= null){

System.out.println("Element is Present");

}else{

System.out.println("Element is Absent");

}

2.    To check Visible:

if( driver.findElement(By.cssSelector("a > font")).isDisplayed()){

System.out.println("Element is Visible");

}else{

System.out.println("Element is InVisible");

}

3.    To check Enable:

if( driver.findElement(By.cssSelector("a > font")).isEnabled()){

System.out.println("Element is Enable");

}else{

System.out.println("Element is Disabled");

}

4.    To check text present:

if(driver.getPageSource().contains("Text to check")){

System.out.println("Text is present");

}else{

System.out.println("Text is absent")

Select methods in selenium Webdriver...

Select Methods

The following are the most common methods used on drop-down elements.

selectByVisibleText() :

deselectByVisibleText():

Selects/deselects the option that displays the text matching the parameter.

Parameter: The exactly displayed text of a particular option

selectByValue()

deselectByValue()

Selects/deselects the option whose “value” attribute matches the specified parameter.

Parameter: value of the “value” attribute remember that not all drop-down options have the same text and “value”, like in the example below.

selectByIndex()

deselectByValue()

Selects/deselects the option at the given index.

Parameter: the index of the option to be selected

isMultiple()

Returns TRUE if the drop-down element allows multiple selections at a time; FALSE if otherwise.

Needs parameters needed

deselectAll ():

Clears all selected entries. This is only valid when the drop-down element supports multiple selections.

No parameters needed

Navigate commands in Selenium Webdriver...

Navigate commands:

 These commands allow you to refresh go-into and switch back and forth between different webpages.

navigate ().to() :        It automatically opens a new browser window and fetches the
                                 page that  you specify inside its parentheses.

                                It does exactly the same thing as the get() method.

navigate().refresh() : It refreshes the current page

navigate().back():       Takes you back by one page on the browser’s history.

navigate().forward():  Takes you forward by one page on the browser’s history.

How can I read background-color of an element in selenium Webdriver?

driver.get("http://www.google.co.in/");

String color = driver.findElement(By.name("btnK")).getCssValue("background-color");

   

System.out.println("The background color of Google search button"+color);

How to find child elements in Selenium Web driver

When same object is getting duplicated for N number of parent, then user can refer parent and child relationship to identify the object in browser.

WebElement bodyElement = driver.findElement(By.id("body"));
WebElement bodyElement1 = bodyElement.findElement(By.id("body"));
System.out.println(bodyElement1.findElement(By.id("TotalScore")).getText());

User want to read all child elements of div(parent) container by using following methods.

WebElement bodyElement = driver.findElement(By.id("body"));   
List divElements = bodyElement.findElements(By.className("div"));
for(WebElement childDiv : divElements){
 List fieldSetEle = childDiv.findElements(By.tagName("fieldset"));
 System.out.println(childDiv.getTagName()+":"+fieldSetEle.size());
    }  

How to Identify popup window in Selenium Webdriver....

1. First Check whether its popup window or IFrame window by using IE Developer or Find bug tools.

2. If its popup window then use following code :

driver.findElement(By.xpath("")).click();

Set s=driver.getWindowHandles();

This method will help to handle of opened windows other than parent 



Iterator ite=s.iterator();

while(ite.hasNext())

{

String popupHandle=ite.next().toString();

if(!popupHandle.contains(mwh))

{

driver.switchTo().window(popupHandle);

/**/here you can perform operation in pop-up window**

//After finished your operation in pop-up just select the main window again

driver.switchTo().window(mwh);

}

}

This method will help to filter out of all opened windows

1) String parentWindowHandle = browser.getWindowHandle(); // save the current window handle.

WebDriver popup = null;

Iterator windowIterator = browser.getWindowHandles();

while(windowIterator.hasNext()) { 

String windowHandle = windowIterator.next(); 

popup = browser.switchTo().window(windowHandle);

if (popup.getTitle().equals("Google") {

break;

}

}

2) Uncomment code for the same popup,

//action in popup "Google". 

popup.findElement(By.name("q")).sendKeys("Thoughtworks");

//action in popup "Google". 

popup.findElement(By.name("btnG")).submit();"


3) After the popup actions, switch the driver back to the parent window,


browser.close(); // close the popup.

browser.switchTo().window(parentWindowHandle); // Switch back to parent window.

If its IFrame then use following line.

String parentWindowHandle = driver.getWindowHandle(); 

driver.switchTo().Frame(driver.findelement(by.id("iframeid")));
// perform ur actions.
//revert back to parent control

driver.switchTo().window(parentWindowHandle);