Advanced Excel Reader useful for selenium WebDriver

Advanced Excel Reader useful for selenium WebDriver

In this post I am going to explain how can we read and write data on excel file with the help 
of Java using Apache POI jar libraries.

Here we can retrieve the data from cell based on row number,column name and data is set into
 cell based on column name and row number.

 Download Apache POI jar libraries in following link  and configure to your project.
 Download Apache POI jar

 

Sample Code:
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;

public class ExcelReader
{
 public static String filename = System.getProperty("user.dir");
 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 static String sActionKeyword=null;

  //constructor for path setting
 public ExcelReader(String path)
 {
  this.path=path;
  try
  {
   fis = new FileInputStream(path);
   workbook = new XSSFWorkbook(fis);
   sheet = workbook.getSheetAt(0);
   fis.close();
  }
  catch (Exception e)
  {
   e.printStackTrace();
  }

 }

  // returns the data from a cell based on row and column name
 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());
 
  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 true if data is set successfully into cell using column name and row number
 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.setCellValue(data);

      fileOut = new FileOutputStream(path);

   workbook.write(fileOut);

      fileOut.close();

   }
  catch(Exception e)
  {
   e.printStackTrace();
   return false;
  }
  return true;
 }
 //Usage
    public static void main(String[] args) throws IOException{
 
    ExcelReader ereader = new ExcelReader("C:/Users/Sudharsan/Desktop/TestData.xlsx");
    System.out.println(ereader.getCellData("Sheet1", "Username", 2));
    ereader.setCellData("Sheet1", "Password", 2, "Sgian@1234");
   }

}

Database connection using selenium webdriver

Database connection using selenium webdriver
In order to do Database testing using Selenium you have to make a connection to the different Databases as per requirement.Please follow Below Steps

Step:1
Make a connection to the different Databases as per requirement
MySQL connection:

 public static void getMySQLConnection(String hostName,
                                                String portConn,
                                                String dbName,
                                                String userName,
                                                String passWord)
            throws ClassNotFoundException, SQLException {

        // With port
  String connectionURL = "jdbc:mysql://" + hostName + ":" + portConn + "/" + dbName;

        // Without port
        String connectionURL = "jdbc:mysql://"
                                + hostName + "/"
                                + dbName
                                + "?characterEncoding=UTF-8&useSSL=false";

        // Connection
        Connection conn = DriverManager.getConnection(connectionURL, userName, passWord);
        System.out.println("--- MySQL database connected ---");
    }

SQL-Server connection by JDTS:

    public static void getSQLServerConnection_JDTS(String hostName,
                                                         String sqlInstanceName,
                                                         String database,
                                                         String userName,
                                                         String passWord,
                                                         String portConn)
                            throws ClassNotFoundException, SQLException {

        /*
         * Syntax: "jdbc:jtds:sqlserver://localhost:1433/testsimple;instance=SQLEXPRESS"
         */
        String connectionURL = "jdbc:jtds:sqlserver://"
                                + hostName + ":"
                                + portConn + "/"
                                + database
                                + ";instance="
                                + sqlInstanceName;

        Connection conn = DriverManager.getConnection(connectionURL, userName, passWord);
        System.out.println("--- SQLSERVER JTDS connected ---");

    }

SQL-Server connection by JDBC:
 public static void getSQLServerConnection_JDBC(String hostName,
                                                         String portConn,
                                                         String sqlInstanceName,
                                                         String database,
                                                         String userName,
                                                         String passWord)
                            throws ClassNotFoundException, SQLException {

        /*
         * Syntax: "jdbc:sqlserver://ServerIp:1433;instance=SQLEXPRESS;databaseName=testmydb"
         */
        String connectionURL = "jdbc:sqlserver://"
                                + hostName + ":"
                                + portConn + ";"
                                + sqlInstanceName + ";"
                                + "databaseName="
                                + database;

        Connection conn = DriverManager.getConnection(connectionURL, userName, passWord);
        System.out.println("--- SQLSERVER JDBC connected ---");

    }

Oracle connection:
public static void getOracleConnection(String hostName,
                                                 String sid,
                                                 String userName,
                                                 String password,
                                                 String port)
                            throws ClassNotFoundException, SQLException {

        //Syntax: "jdbc:oracle:thin:@localhost:1521:db11g"
        String connectionURL = "jdbc:oracle:thin:@"
                                + hostName + ":"
                                + port + ":"
                                + sid;

        Connection conn = DriverManager.getConnection(connectionURL, userName, password);
        System.out.println("--- ORACLE database connected ---");

    }

Step:2
Send Queries to the Database and retrieve the data.

//Statement Object to send queries
Statement stmt = con.createStatement();

//send the query to database use execute query and store the results in the Result Set object

ResultSet rs = stmt.executeQuery(select * from employee;);

Step:3
Process the results.

Page Object Model Framework

Page Object Model
Selenium acts on web elements with the help of their properties such ID, name ,XPath, etc. Unlike QTP which has an inbuilt object repository (OR), Selenium has no inbuilt ORs.
Hence we need to build an OR which should also be maintainable and accessible on demand. Page Object Model (POM) is a popular design pattern to create an Object Repository in which each one of those web elements properties a recreated using a class file.

Advantages::

 POM is an implementation where test objects and functions are separated from each other, thereby keeping the code clean.
 The objects are kept independent of test scripts. An object can be accessed by one or more test scripts, hence POM helps us to create objects once and use them multiple times.
 Since objects are created once, it is easy to access as well as update a particular property of an object.POM Flow Diagram:

Objects are created for each one of the pages and methods are developed exclusively to access to those objects. Let us use http://calculator.net for understanding the same.
There are various calculators associated with it and each one of those objects in a particular page is created in a separate class file as static methods and they all are accessed through the 'tests' class file in which a static method would be accessing the objects.

Example
Let us understand it by implementing POM for percent calculator test.
Step 1 : Create a simple class (page_objects_perc_calc.java) file within apackage and create methods for each one of those object identifiers as shownbelow.

package PageObject;
import org.openqa.selenium.*;
public class page_objects_perc_calc
{
private static WebElement element = null;
// Math Calc Link
public static WebElement lnk_math_calc(WebDriver driver)
{
element =
driver.findElement(By.xpath(".//*[@id='menu']/div[3]/a"));
return element;
}

// Percentage Calc Link
public static WebElement lnk_percent_calc(WebDriver driver)
{
element =
driver.findElement(By.xpath(".//*[@id='menu']/div[4]/div[3]/a"));
return element;
}
// Number 1 Text Box
public static WebElement txt_num_1(WebDriver driver)
{
element = driver.findElement(By.id("cpar1"));
return element;
}
// Number 2 Text Box
public static WebElement txt_num_2(WebDriver driver)
{
element = driver.findElement(By.id("cpar2"));
return element;
}
// Calculate Button
public static WebElement btn_calc(WebDriver driver)
{
element =
driver.findElement(By.xpath(".//*[@id='content']/table/tbody
/tr/td[2]/input"));
return element;
}
// Result
public static WebElement web_result(WebDriver driver)
            {
element =
driver.findElement(By.xpath(".//*[@id='content']/p[2]/span/font/b"));
return element;

                  }
             }
// Result
public static WebElement web_result(WebDriver driver)

{
element =
driver.findElement(By.xpath(".//*[@id='content']/p[2]/span/font/b"));
return element;
}
}


Step 2 : Create a class with main and import the package and create methodsfor each one of those object identifiers as shown below.

package PageObject;
import java.util.concurrent.TimeUnit;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class percent_calculator

{
private static WebDriver driver = null;
public static void main(String[] args)
{
driver = new FirefoxDriver();
driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
driver.get("http://www.calculator.net");
// Use page Object library now
page_objects_perc_calc.lnk_math_calc(driver).click();
page_objects_perc_calc.lnk_percent_calc(driver).click();
page_objects_perc_calc.txt_num_1(driver).clear();
page_objects_perc_calc.txt_num_1(driver).sendKeys("10");
page_objects_perc_calc.txt_num_2(driver).clear();
page_objects_perc_calc.txt_num_2(driver).sendKeys("50");
page_objects_perc_calc.btn_calc(driver).click();
String result =
page_objects_perc_calc.web_result(driver).getText();
if(result.equals("5"))
{
System.out.println(" The Result is Pass");
}
else
{
System.out.println(" The Result is Fail");
}
driver.close();
}
}
Output
The test is executed and the result is printed in the console. Given below is the snapshot of the same.

Comparing strings by their alphabetical order

class StringCompareExample {
    public static void main(String args[]){
        String s1 = "Project"; String s2 = "Sunject";
        verboseCompare(s1, s2);
        verboseCompare(s2, s1);
        verboseCompare(s1, s1);
    }

    public static void verboseCompare(String s1, String s2){
        System.out.println("Comparing \"" + s1 + "\" to \"" + s2 + "\"...");

        int comparisonResult = s1.compareTo(s2);
        System.out.println("The result of the comparison was " + comparisonResult);

        System.out.print("This means that \"" + s1 + "\" ");
        if(comparisonResult < 0){
            System.out.println("lexicographically precedes \"" + s2 + "\".");
        }else if(comparisonResult > 0){
            System.out.println("lexicographically follows \"" + s2 + "\".");
        }else{
            System.out.println("equals \"" + s2 + "\".");
        }
        System.out.println();
    }
}

Static keyword in java

Static keyword in java

The static keyword is used in java mainly for memory management. It is used with variables, methods, blocks and nested class. It is a keyword that are used for share the same variable or method of a given class. This is used for a constant variable or a method that is the same for every instance of a class. The main method of a class is generally labeled static.

No object needs to be created to use static variable or call static methods, just put the class name before the static variable or method to use them. Static method can not call non-static method.

In java language static keyword can be used for following

• variable (also known as class variable)
• method (also known as class method)
• block
• nested class

Static variable

If any variable we declared as static is known as static variable.

• Static variable is used for fulfill the common requirement. For Example company name of employees,college name of students etc. Name of the college is common for all students.
• The static variable allocate memory only once in class area at the time of class loading.

Advantage of static variable

Using static variable we make our program memory efficient (i.e it saves memory).

When and why we use static variable

Suppose we want to store record of all employee of any company, in this case employee id is unique for every employee but company name is common for all. When we create a static variable as a company name then only once memory is allocated otherwise it allocate a memory space each time for every employee.

Syntax for declare static variable:

public static variableName;

Syntax for declare static method:

public static  void methodName()
{
.......
.......
}

Syntax for access static methods and static variable

Syntax

className.variableName=10;
className.methodName();

Example

public static final double PI=3.1415;
public static void main(String args[])
{
......
......
}

Difference between static and final keyword

static keyword always fixed the memory that means that will be located only once in the program where as final keyword always fixed the value that means it makes variable values constant.

Note: As for as real time statement there concern every final variable should be declared the static but there is no compulsion that every static variable declared as final.

Example of static variable.

In the below example College_Name is always same, and it is declared as static.

Example

class Student
{
int roll_no;
String name;
static String College_Name="ITM";
}
class StaticDemo
{
public static void main(String args[])
{
Student s1=new Student();
s1.roll_no=100;
s1.name="abcd";
System.out.println(s1.roll_no);
System.out.println(s1.name);
System.out.println(Student.College_Name); 
Student  s2=new  Student();
s2.roll_no=200;
s2.name="zyx";
System.out.println(s2.roll_no);
System.out.println(s2.name);
System.out.println(Student.College_Name); 
}
}

Example

Output:
100
abcd
ITM
200
zyx
ITM

In the above example College_Name variable is commonly sharable by both S1 and S2 objects.

In the above image static data variable are store in method are and non static variable is store in java stack. Read more about this in JVM Architecture chapter

why main method is static ?

Because object is not required to call static method if main() is non-static method, then jvm create object first then call main() method due to that face the problem of extra memory allocation.

Read more about these things in separate

• Static variable
• Static method
• Static block

Java – Reverse String without using Reverse function

package JavaPrograms;
import java.util.*;
public class ReverseString {
public static void main(String args[])
{
String original, reverse ="";
Scanner in = new Scanner(System.in);
System.out.println("Enter a string to reverse");
original = in.nextLine();
int length = original.length();
for ( int i = length - 1 ; i >= 0 ; i-- )
reverse = reverse + original.charAt(i);
System.out.println("Reverse of entered string is "+reverse);
}
}