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.