Test NG @Dataprovider with APACHE POI and JExcel

Test NG Data Provider Example with Apache POI XSSF

Lets assume this is a form where we need to enter out data

I will create the data in a excel sheet

I will read the excel sheet using the below method

package com.sudas.utility.dataProvider;

import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;

public class ExcelData {

 static File file;
 int totalNumberOfColumns;
 static String data[][];

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  ExcelData ed = new ExcelData(new File("/Users/S_Das/Documents/Java/PersonalDetails.xlsx"));
  ExcelData.readExcelData();
  // dataGen();

 }

 public ExcelData(File file) {
  // TODO Auto-generated constructor stub
  ExcelData.file = file;
 }
@DataProvider(name="excelDP")
 public static String[][] readExcelData() {
  XSSFWorkbook workbook = null;
  try {

   workbook = new XSSFWorkbook(file);
   // taking the firstSheet
   XSSFSheet sheet = workbook.getSheetAt(0);
   // this give total ROWS in the sheet
   /**
    * For data generator each row should have equal number of columns
    * Total number of rows = number of times test gets executed total
    * number of column = number of argument in test method
    */
   int totalRows = sheet.getPhysicalNumberOfRows();
//   int totalRows = sheet.getLastRowNum()-sheet.getFirstRowNum();
   int totalColumns = sheet.getRow(1).getPhysicalNumberOfCells();
   
   // create a String [][] object
   data = new String[totalRows][totalColumns];
   // the first row is header so starting from 1
   for (int i = 1; i < totalRows; i++) {
    // total number of columns / row
    int totalNumberOfColumns = sheet.getRow(i).getPhysicalNumberOfCells();
    // System.out.println(totalNumberOfColumns);
    for (int j = 0; j < totalNumberOfColumns; j++) {
     XSSFCell x = sheet.getRow(i).getCell(j);
     if (x != null) {
      x.setCellType(Cell.CELL_TYPE_STRING);
      System.out.println(x.getStringCellValue());
//System.out.print(i);
//System.out.println(j);
      data[i][j] = x.getStringCellValue();
      

     }

    }
   }

  }

  catch (InvalidFormatException | IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return data;

 }
/**
 * Test Method
 * @return
 */
@Deprecated
 public static Object[][] dataGen() {
  String s[][] = new String[3][1];
  for (int i = 0; i < s.length; i++) {

   for (int j = 0; j < s[i].length; j++) {
    System.out.print(i);
    System.out.println(j);
   }
  }
  return new Object[][] { {}, {} };

 }

}

Finally I will use a TestNG data provider to pass the values from excel.

 package com.sudas.utility.dataProvider;
 
 import java.io.File;
 
 import org.testng.annotations.BeforeSuite;
 import org.testng.annotations.Test;
 
 public class EnterDataTest {
  @BeforeSuite
  public void before()
  {
   ExcelData data = new ExcelData(new File("/Users/S_Das/Documents/Java/PersonalDetails.xlsx"));
   ExcelData.readExcelData();
  }
  
 
  @Test(dataProvider="excelDP",dataProviderClass=com.sudas.utility.dataProvider.ExcelData.class)
  public static void enterDataInForm(String FirstName, String MiddleName, String LastName, String Age, String Address,
    String Occupation, String State, String Postcode, String Country, String Email, String Phone) {
 
   
   
   
  }
 
 }

This is what you will get as output

[TestNG] Running:
  /private/var/folders/xd/h0rwdyp90g740mr3rt5lrpgc0000gn/T/testng-eclipse-1286226461/testng-customsuite.xml

Tom
T
TomT
20
TomLand, Australia
Servive
NSW
2000
Australia
tom@t.com
123456
Dick
D
DickD
30
Dickland, Australia
Business
NSW
2000
Australia
Dick@d.com
999999
Harry
H
HarryH
40
HarryLand, Australia
Cook
NSW
2000
Australia
Harry@h.com
231421323
Dave
DaveD
60
HarryLand, Australia
Retired
NSW
2000
Tom
T
TomT
20
TomLand, Australia
Servive
NSW
2000
Australia
tom@t.com
123456
Dick
D
DickD
30
Dickland, Australia
Business
NSW
2000
Australia
Dick@d.com
999999
Harry
H
HarryH
40
HarryLand, Australia
Cook
NSW
2000
Australia
Harry@h.com
231421323
Dave
DaveD
60
HarryLand, Australia
Retired
NSW
2000
PASSED: enterDataInForm(null, null, null, null, null, null, null, null, null, null, null)
PASSED: enterDataInForm("Tom", "T", "TomT", "20", "TomLand, Australia", "Servive", "NSW", "2000", "Australia", "tom@t.com", "123456")
PASSED: enterDataInForm("Dick", "D", "DickD", "30", "Dickland, Australia", "Business", "NSW", "2000", "Australia", "Dick@d.com", "999999")
PASSED: enterDataInForm("Harry", "H", "HarryH", "40", "HarryLand, Australia", "Cook", "NSW", "2000", "Australia", "Harry@h.com", "231421323")
PASSED: enterDataInForm("Dave", null, "DaveD", "60", "HarryLand, Australia", "Retired", "NSW", "2000", null, null, null)

===============================================
    Default test
    Tests run: 5, Failures: 0, Skips: 0
===============================================


===============================================
Default suite
Total tests run: 5, Failures: 0, Skips: 0
===============================================

[TestNG] Time taken by org.testng.reporters.XMLReporter@7a46a697: 15 ms
[TestNG] Time taken by org.testng.reporters.EmailableReporter2@77afea7d: 8 ms
[TestNG] Time taken by [FailedReporter passed=0 failed=0 skipped=0]: 4 ms
[TestNG] Time taken by org.testng.reporters.jq.Main@1055e4af: 44 ms
[TestNG] Time taken by org.testng.reporters.SuiteHTMLReporter@2e5c649: 15 ms
[TestNG] Time taken by org.testng.reporters.JUnitReportReporter@57fa26b7: 4 ms

As you can see in the first test instead of header text (which is our first row) i am sending null, this would be a good negative test case by passing invalid / null values. [Ideally i should have eliminated that.

******************************************************************************************************************************************************************

Example

 package TestNG;  
 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.FileNotFoundException;  
 import java.io.IOException;  
 import org.apache.poi.xssf.usermodel.XSSFRow;  
 import org.apache.poi.xssf.usermodel.XSSFSheet;  
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
 import org.testng.annotations.Test;  
 import jxl.Cell;  
 import jxl.Sheet;  
 import jxl.Workbook;  
 import jxl.read.biff.BiffException;  
 public class DataProvider {  
      public static void main(String[] args) {  
           // TODO Auto-generated method stub  
 //          new DataProvider().datagen();  
      }  
      private int collength;  
      private String[][] doubleDim;  
      private String[][] jxlarray;  

       @org.testng.annotations.DataProvider (name="dpTest")  
      // data generator using APACHE POI  
      public String[][] datagen() {  
           File file = new File("C://Test//SQL.xlsx");  
           try {  
                XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                System.out.println(sheet.getLastRowNum());  
                int rowlength = sheet.getLastRowNum() - sheet.getFirstRowNum();  
                XSSFRow rows;  
                //rows.getLastCellNum() - rows.getFirstCellNum();  // if you want value of col 2  
                collength =1;           // if you need value of only col 1  
                doubleDim = new String[rowlength][collength];  
                for (int i = 0; i < rowlength; i++) {  
                      rows= sheet.getRow(i);  
                     for (int j = 0; j < collength; j++) {  
                          System.out.println(rows.getCell(j).getStringCellValue());  
                          doubleDim[i][j] = rows.getCell(j).getStringCellValue();  
                     }  
                }  
 workbook.close();  
           } catch (FileNotFoundException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return doubleDim;  
      }  
       @Test(dataProvider="dpTest1",priority=1)  
       public void testjxlDP(String reportName)  
       {  
           System.out.println(reportName);   
       }  
       @Test(dataProvider="dpTest",priority=0)  
       public void testpoiDP(String reportName)  
       {  
           System.out.println(reportName);   
       }  
       @org.testng.annotations.DataProvider(name ="dpTest1")  
           public String[][] datagenJXL() {  
                Workbook workbook;  
                try {  
                     File file = new File("C://Test//JSQL.xls");  
                     workbook = Workbook.getWorkbook(file);  
                     Sheet sheet = workbook.getSheet(0);  
                     int totalRows = sheet.getRows();  
                     int totalColumns = 1;          //sheet.getColumns(); // I am using only Col1 values  
                     jxlarray = new String[totalRows][totalColumns];  
                     for (int i = 0; i < totalRows; i++) {  
                          for (int j = 0; j < totalColumns; j++) {  
                               Cell cells = sheet.getCell(j,i);  
                               jxlarray[i][j]=cells.getContents();  
                               System.out.println(cells.getContents());  
                          }  
                     }  
                } catch (BiffException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                } catch (IOException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
                return jxlarray;  
           }  
 }  

OUTPUT

Database Testing Framework using JDBC driver

Often in any back end testing project, ETL Data Warehousing project, JDBC driver testing project, Business Intelligence project/ Report Testing  where data validation is important, this particular framework is vary handy to perform data validation .
No matter what the project / product is the ultimate aim of the tester is to validate the data that is appearing on the final business layer .
For example

Here in the below code a String array is used to hold the SQL queries. When you have such hundreds of queries you can put them in an excel file and iterate over the excel column to read those queries.

Java code to refresh SQL queries from the spread sheet one by one.
I am using Apache POI XSSF to read the spreadsheet.
The two important methods here are
connection method - this returns a connection object
core method - this returns an String array for every SQL query executed.


Java code to refresh SQL

 package TestNG;  
 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.FileNotFoundException;  
 import java.io.IOException;  
 import java.sql.Array;  
 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.ResultSet;  
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.Arrays;  
 import java.util.HashMap;  
 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
 import org.apache.poi.xssf.usermodel.XSSFCell;  
 import org.apache.poi.xssf.usermodel.XSSFComment;  
 import org.apache.poi.xssf.usermodel.XSSFRow;  
 import org.apache.poi.xssf.usermodel.XSSFSheet;  
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
 public class DBHelper01 {  
      private static XSSFCell column1;  
      private static XSSFCell column2;  
      private static String value;  
      private static String key;  
      private static String VALUE;  
      private static int totalRows;  
      public static Connection connection;  
      private static String rsSQL;  
      private static DriverManager dmanager;  
      private static ArrayList<String> rsarray;  
      // Main method is used for testing  
      public static void main(String[] args) {  
            
      }  
      public static String getQueries(String KEY) {  
           File file = new File("C://Test//sqlqueries.xlsx");  
           HashMap<String, String> sqlmap = new HashMap<String, String>();  
           try {  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());  
                // System.out.println("Total Rows " + totalRows);  
                for (int i = 2; i <= totalRows; i++) { // excluding the header  
                                                                  // starting from 1  
                     XSSFRow row = sheet.getRow(i);  
                     column1 = row.getCell(0);  
                     column2 = row.getCell(1);  
                     if (column1 == null || column2 == null) {  
                     } else {  
                          if (column1 != null) {  
                               // System.out.println(column1.getStringCellValue());  
                               key = column1.getStringCellValue();  
                          }  
                          if (column2 != null) {  
                               // System.out.println(column2.getStringCellValue());  
                               value = column2.getStringCellValue();  
                          }  
                          sqlmap.put(key, value);  
                     }  
                }  
                VALUE = sqlmap.get(KEY);  
                workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return VALUE;  
      }  
      public static Connection connection(DriverManager dmanager, String URL, String USER_NAME, String PASSWORD) {  
           try {  
                Class.forName("com.ggg.arsys.jdbc.core.Driver");  
                connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);  
           } catch (SQLException | ClassNotFoundException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return connection;  
      }  
      public static ArrayList<String> core(String KEY) {  
           // Arrays.asList(a)  
           String sqlquery = DBHelper01.getQueries(KEY);  
           try {  
                ResultSet rs = connection.createStatement().executeQuery(sqlquery);  
                int colCount = rs.getMetaData().getColumnCount();  
                rsarray = new ArrayList<String>();  
                while (rs.next()) {  
                     for (int j = 1; j <= colCount; j++) {  
                          rsSQL = rs.getString(j);  
                          rsarray.add(rsSQL);  
                     }  
                }  
                if (rsarray.size() == 0) {  
                     System.out.println("No data for query");  
                } else if (rsarray.size() > 0) {  
                     System.out.println(rsarray);  
                }  
           } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return rsarray;  
      }  
 }  

Test NG Class for the DBHelper

 package TestNG;  
 import org.testng.annotations.Test;  
 import org.testng.annotations.BeforeMethod;  
 import org.testng.annotations.AfterMethod;  
 import org.testng.annotations.DataProvider;  
 import org.testng.annotations.BeforeClass;  
 import org.testng.annotations.BeforeGroups;  
 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import org.apache.poi.xssf.usermodel.XSSFCell;  
 import org.hamcrest.Matchers;  
 import org.junit.Assert;  
 import org.junit.Ignore;  
 import org.testng.annotations.AfterClass;  
 import org.testng.annotations.AfterGroups;  
 import org.testng.annotations.BeforeTest;  
 import org.testng.annotations.AfterTest;  
 import org.testng.annotations.BeforeSuite;  
 import org.testng.annotations.AfterSuite;  
 public class DBHelperTestCase {  
      private static DriverManager dmanager;  
      @Test(dataProvider = "dp")  
      public void f(Integer n, String s) {  
      }  
      @BeforeMethod  
      public void beforeMethod() {  
      }  
      @AfterMethod  
      public void afterMethod() {  
      }  
      @DataProvider  
      public Object[][] dp() {  
           return new Object[][] { new Object[] { 1, "a" }, new Object[] { 2, "b" }, };  
      }  
      @BeforeClass(alwaysRun=true)  
      public void beforeClass() {  
           DBHelper01.connection(dmanager, "jdbc:arserver://vl-pun-atm-qa12:0;mode=new", "Demo", "bmcAdm1n");  
           DBHelper02.connection(dmanager, "jdbc:oracle:thin:@vl-pun-atm-qa12:1521:ORA112DB", "system", "bmcAdm1n");  
      }  
      @AfterClass(enabled = false)  
      public void afterClass() {  
           try {  
                DBHelper01.connection.close();  
                System.out.println("db connection closed");  
           } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  
      @BeforeTest  
      public void beforeTest() {  
      }  
      @AfterTest  
      public void afterTest() {  
      }  
      @BeforeSuite  
      public void beforeSuite() {  
      }  
      @AfterSuite  
      public void afterSuite() {  
      }  
      @BeforeGroups("sanity-test")  
      public void t() {  
           System.out.println("sanity-test");  
      }  
      @AfterGroups("regression")  
      public void tt() {  
           System.out.println("regression");  
      }  
      @Test(groups = "sanity", enabled = false)  
      public void testCase01() {  
           ArrayList<String> t1 = DBHelper01.core("Test Case 01");  
           ArrayList<String> t2 = DBHelper02.core("Test Case 01");  
           Assert.assertArrayEquals(t1.toArray(), t2.toArray());  
      }  
      @Test(groups = "regression", enabled = false)  
      public void testCase02() {  
           DBHelper01.core("Test Case 02");  
      }  
      @Test(groups = "sanity", enabled = true) // enable =false = @ignore  
      public void testCase03() {  
           DBHelper01.core("Test Case 03");  
      }  
      @Test(groups = "regression", enabled = true)  
      public void testCase04() {  
           DBHelper01.core("Test Case 04");  
      }  
      @Test(groups = "regression", enabled = true)  
      public void testCase05() {  
           DBHelper01.core("Test Case 05");  
      }  
 }  

Test NG XML

<suite name="JDBC Test Suite">
<test name="dbtest">
<groups>
<run>
<include name="core" />
<include name="regression" />
</run>
</groups>
<classes>
<class name="TestNG.DBHelperTestCase" />
<methods>

</methods>
</classes>
</test>
</suite>

Results

i18N

/***
 * So you are basically trying to test i18n (Internationalization) because more than one language not L10n(localization)
 * I often play around with what you are looking for in my day in day out job. What we look for is ---
 * Users have a regional settings set up in the application, for example when user A log in userA should see
 * the application in ja where as when userB log in userB should see the application UI in cn(Chinese).
 * 
 * Use case for i18N automation 
 * 1. Verify welcome message on UI after user login
 * 2. Verify error messages
 * 3. Verify tooltips
 * 4. Verify common webelements like button/ dropdown etc
 * 5. Verify the email template, some application has a predefined email template to send mails/ if any translation service
 * 6. Verify  welcome message on UI after userA login, userA log out immediately followed by UserB login, to check whether browser
 * cache holds the previous translation (should not)
 * 7. If your database is UNICODE check for data as well.
 * 8. If the application User_Name honors Region/ timezone check that as well
 * 
 */

// just talking about the Selenium part , though there is more to it, you can do something like
package snadbox;

public class i18N {
static String HOME_PAGE_TEXT = "国際交流基金(ジャパンファウンデーション) ";

public static void main(String[] args) {
// TODO Auto-generated method stub

if ("国際交流基金(ジャパンファウンデーション) ".equals(HOME_PAGE_TEXT)) {
System.out.println("String correct");
}

}

}

Difference between equal method and ==

Java == check if bot the object are at the same memory location in heap.
Where as java .equal method compares the content of the object

 public class Class2 {  
      public static void main(String[] args) {  
           // TODO Auto-generated method stub  
           new Class2().testEqual();  
      }  
      public void testEqual() {  
           List<String> list1 = Arrays.asList("Apple", "Banana", "Oranges");  
           List<String> list2 = Arrays.asList("Apple", "Banana", "Oranges");  
           List<String> list3 = list1;  
           if (list1 == list2) {  
                System.out.println("true");  
           }  
           else if (list1 == list3) {  
 System.out.println("list1 == list3");  
           }   
           else if (list1.equals(list2))               ;  
           {  
                System.out.println("equal");  
           }  
      }  
 }  

OUTPUT

list1 == list3
equal




Java Thread

Use of Java Threads

Using thread run same method multiple times.
My requirement is to run below method multiple times. The number of times the method should be run depends on the number of times the class creates a new instance

 public   void counter()
 {
  for (int i = 0; i < 6; i++) {
   System.out.println(i);
   try {
    Thread.sleep(1000);
   } catch (InterruptedException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }

Running the above method multiple times dynamically

package com.bmc.arsystem.sr;

public class Sandbox {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  multiProcess();

 }

 public static void multiProcess() {

  Thread th[] = new Thread[4];
  for (int i = 0; i < th.length; i++) {
   th[i] = new Thread(new Runnable() {

    @Override
    public void run() {
     // TODO Auto-generated method stub
     try {
      Threads instance = Threads.class.newInstance();
      instance.counter();

     } catch (InstantiationException | IllegalAccessException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }

    }
   });
   th[i].start();

  }

 }
}

Implementation Example

 package thread;  
 public class Thread01 {  
      public static void main(String[] args) {  
           // TODO Auto-generated method stub  
           new Thread01().thread01();  
           new Thread01().thread02();  
           new Thread01().nonthread01();  
           new Thread01().nonthread02();  
      }  
      public void thread01() {  
           Runnable runnable = new Runnable() {  
                @Override  
                public void run() {  
                     // TODO Auto-generated method stub  
                     int i;  
                     for (i = 0; i <= 10; i++) {  
                          System.out.println(i);  
                          try {  
                               Thread.sleep(1000);  
                          } catch (InterruptedException e) {  
                               // TODO Auto-generated catch block  
                               e.printStackTrace();  
                          }  
                     }  
                }  
           };  
           Thread thread = new Thread(runnable, "thread one");  
           thread.start();  
      }  
      public void thread02() {  
           Runnable runnable = new Runnable() {  
                @Override  
                public void run() {  
                     // TODO Auto-generated method stub  
                     int i;  
                     for (i = 0; i <= 10; i++) {  
                          System.out.println("a" + i);  
                          try {  
                               Thread.sleep(1000);  
                          } catch (InterruptedException e) {  
                               // TODO Auto-generated catch block  
                               e.printStackTrace();  
                          }  
                     }  
                }  
           };  
           Thread thread = new Thread(runnable, "thread one");  
           thread.start();  
      }  

      public void nonthread01() {  
           for (int i = 0; i <= 10; i++) {  
                System.out.println("a" + i);  
                try {  
                     Thread.sleep(1000);  
                } catch (InterruptedException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
           }  
      }  
      public void nonthread02() {  
           for (int i = 0; i <= 10; i++) {  
                System.out.println("b" + i);  
                try {  
                     Thread.sleep(1000);  
                } catch (InterruptedException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
           }  
      }  
 } 

2nd way of implementation 

 package thread;  
 public class Thread02 {  
      static String name1;  
      static String name2;  
      public static void main(String[] args) {  
           // TODO Auto-generated method stub  
           Thread t1 = new Thread(new T1());  
           t1.start();  
           name1 = t1.getName();  
           Thread t2 = new Thread(new T2());  
           t2.start();  
           name2 = t2.getName();  
      }  
 }  
 class T1 extends Thread02 implements Runnable {  
      @Override  
      public void run() {  
           // TODO Auto-generated method stub  
           int i;  
           for (i = 0; i <= 10; i++) {  
                System.out.println(i);  
                System.out.println(name1);  
                try {  
                     Thread.sleep(1000);  
                } catch (InterruptedException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
           }  
      }  
 }  
 class T2 extends Thread02 implements Runnable {  
      @Override  
      public void run() {  
           // TODO Auto-generated method stub  
           int i;  
           for (i = 0; i <= 10; i++) {  
                System.out.println(i);  
                System.out.println(name2);  
                try {  
                     Thread.sleep(1000);  
                } catch (InterruptedException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
           }  
      }  
 }  

Apache POI XSSF examples

Create File Example

    public void createXL() {  
           XSSFWorkbook workbook = new XSSFWorkbook();  
           XSSFSheet sheet = workbook.createSheet();  
           for (int i = 0; i < 10; i++) {  
                XSSFRow row = sheet.createRow(i);  
                XSSFCell cell = row.createCell(0);  
                cell.setCellValue("Hello World" + i);  
           }  
           File file = new File(file_path);  
           if (file.exists()) {  
                file.delete();  
           }  
           try {  
                file.createNewFile();  
                FileOutputStream fos = new FileOutputStream(file);  
                workbook.write(fos);  
                workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Modify Excel file

   public void modifyXL() {  
           File file = new File(file_path);  
           try {  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                XSSFRow row = sheet.getRow(0);  
                XSSFCell cell = row.getCell(2);  
                if (cell == null)  
                     cell = row.createCell(2);  
                cell.setCellValue("new addition");  
                FileOutputStream fos = new FileOutputStream(file);  
                workbook.write(fos);  
                workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Read Excel File

      public void readXL() {  
           try {  
                File file = new File(file_path);  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                System.out.println("first "+sheet.getFirstRowNum());  
                System.out.println("Last "+sheet.getLastRowNum());  
                int totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());  
                System.out.println(totalRows);  
                for (int i=0;i<=totalRows;i++)  
                {  
                     XSSFRow rows = sheet.getRow(i);  
                     System.out.println(rows.getCell(0).getStringCellValue());  
                }  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Read Excel In Key Value Pair

 package TestNG;  
 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.FileNotFoundException;  
 import java.io.IOException;  
 import java.util.HashMap;  
 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
 import org.apache.poi.xssf.usermodel.XSSFCell;  
 import org.apache.poi.xssf.usermodel.XSSFComment;  
 import org.apache.poi.xssf.usermodel.XSSFRow;  
 import org.apache.poi.xssf.usermodel.XSSFSheet;  
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
 public class DatabaseHelper {  
      XSSFCell column1;  
      XSSFCell column2;  
      String value;  
      String key;  
      String VALUE;  
      // Main method is used for testing   
      public static void main(String[] args) {  
 System.out.println(new DatabaseHelper().getQueries("Test Case 01"));  
                     ;  
      }  
      public String getQueries(String KEY) {  
           File file = new File("C://Test//sqlqueries.xlsx");  
 HashMap<String, String> sqlmap = new HashMap<String, String>();  
           try {  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                int totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());  
 //               System.out.println("Total Rows " + totalRows);  
                for (int i = 1; i <= totalRows; i++) {  
                     XSSFRow row = sheet.getRow(i);  
                     column1 = row.getCell(0);  
                     column2 = row.getCell(1);  
                     if (column1 != null) {  
 //                         System.out.println(column1.getStringCellValue());  
                          key = column1.getStringCellValue();  
                     }  
                     if (column2 != null) {  
 //                         System.out.println(column2.getStringCellValue());  
                          value = column2.getStringCellValue();  
                     }  
                     sqlmap.put(key, value);  
                }  
                VALUE = sqlmap.get(KEY);  
 workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return VALUE;  
      }  
 }  

Alternative way to read Rows and Columns

      public void datagen() {  
           File file = new File("C://Test//sqlqueries.xlsx");  
           try {  
                XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                int rowlength = sheet.getLastRowNum() - sheet.getFirstRowNum();  
                doubleDim = new String[rowlength][collength];  
                for (int i = 0; i < rowlength; i++) {  
                     XSSFRow rows = sheet.getRow(i);  
                     collength = rows.getLastCellNum() - rows.getFirstCellNum();  
                     for (int j = 0; j < collength; j++) {  
                          System.out.println(rows.getCell(j).getStringCellValue());  
 //                         doubleDim[j][i] = rows.getCell(j).getStringCellValue();  
                     }  
                }  
           } catch (FileNotFoundException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

POM , Page Object Model design pattern without using page factory class (WORK IN PROGRESS)

POM is a design pattern that is implemented using selenium webdriver, where every webpage or a major functionality within a page is consider as a object. For every page there is a java class file.
The page object pattern simply abstracts your business logic away from the physical structure of your pages. What the PageFactory class gives you is the ability to use annotations to automatically find the elements on the page without resorting to explicit findElement calls.


I will give you example around the below application under test for implementing POM. The POM would be implemented not using PageFactory class.

Let me describe the application first. The user will first login to the application using the login page. Depending on user ID (authorization) application link and report for the application would be visible. For example lets say a user with user ID "test@abc.com" log into the application. For this user only application A and application C is visible on the application page, and this user can access all reports related to application A and C.


Test Scenario - 1. 
Verify all reports for users refresh without any error and find the report refresh time.

My first step is to create a java properties file (for example objectrepo.properties) and enter all the locator for the web element on the file
The naming convention followed is

• locator prefix with L,
• ID prefixed with I
• Xpath prefixed with X
• CSS selector prefixed with C
• sendKeys parameters prefix with S. 

Object Repository Example

properties file

To use all the locators in every class of Page Object Model, I will create a ObjectRepository.class.
Within this class I will have a method which will find the "Value" for a corresponding "Key" in properties file. This method would have String return type, which is basically returning the property "Value" against a "Key". All the methods within this class would be class methods i.e. static methods instead of instance method. So that the methods can be called in other classes without creating object of the ObjectRepository.class class.

This is my locator objector repository. The locators in object repository can be accessed through java Properties class. The method getPROPERTIES method accepts an argument string i.e the KEY and return a VALUE.


Example:

      public static String getPROPERTIES(String KEY) {  
           try {  
                File file = new File("C:\\FRAMEWORK\\object.properties");  
                FileInputStream inStream = new FileInputStream(file);  
                Properties property = new Properties();  
                property.load(inStream);  
                VALUE = property.getProperty(KEY);  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return VALUE;  
      }  

* ObjectRepository Class

Now let's design the ObjectRepository.class. The main objective of this class is to have several methods which would return the value from the property file. For that I will have methods like

• getURL ---> This will return me server_name:port/abc
• getUserName---> This will return me username-id
• getPassword---> This will give me pwd-id 

That's it we are done with the ObjectRepository.class. The more key value pair you add in the properties file either the WebElement locator or the sendKey argument or any other argument add a static field and a getmethod in the ObjectRepository.class.

Building the POM

* First Page

On the application under test my first page is a login page. Following the POM design pattern, I will create a class for the login  page. The AUT (application under test) opens a new window (pop) every time I click on any of the application (application -A and application -C in my test). For further navigation to next window which is a new window I have to get hold of the new window handle and switch to the new window. As every time the only way to go to the next screen is through a new window the login method within the login.class i will return the handles as list. I can further iterate through the list in other pages/ class and get the windows handles.

* Second Page
Now I have already done the login and clicked on application -A and opened reports for application -A. Here my task is to find all the reports name which are present inside a table.