In last chapter, we created Action Keywords and placed them in to the 'DataEngine' excel sheet, now we need Apache POI in place to connect with the excel sheet in the Selenium Test.

Set Up Data Engine - Apache POI (Excel)

We need a way to open this Excel sheet and read data from it within this Selenium test script. For this purpose, I use the Apache POI library, which allows you to read, create and edit Microsoft Office-documents using Java. The classes and methods we are going to use to read data from Excel sheet are located in the 'org.apache.poi.hssf.usermodel' package.

Reading Data from Excel

1. 'Download JAR files' of Apache POI. You can download Apache POI Jar files from here.  Note: The latest version in Sep'14 is 'poi - 3.10.1'
2. Once you downloaded the JAR files, then 'Add Jars'  files to your project library. That’s all about the configuration of Apache POI with eclipse. Now you are ready to write code.
3. Create a ‘New Package’ and name it as ‘utility’, by right click on the Project folder and select New > Package.
4. Create a ‘New Class‘ file, by right click on the ‘utility‘ Package and select New > Class and name it as ‘ExcelUtils‘. First we will write basic read method.

package utility;

import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    public class ExcelUtils {
                private static XSSFSheet ExcelWSheet;
                private static XSSFWorkbook ExcelWBook;
                private static XSSFCell Cell;

            //This method is to set the File path and to open the Excel file
            //Pass Excel Path and SheetName as Arguments to this method
            public static void setExcelFile(String Path,String SheetName) throws Exception {
	                FileInputStream ExcelFile = new FileInputStream(Path);
	                ExcelWBook = new XSSFWorkbook(ExcelFile);
	                ExcelWSheet = ExcelWBook.getSheet(SheetName);
                   }

            //This method is to read the test data from the Excel cell
            //In this we are passing parameters/arguments as Row Num and Col Num
            public static String getCellData(int RowNum, int ColNum) throws Exception{
            	  Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
                  String CellData = Cell.getStringCellValue();
                  return CellData;
            	}

    	}

Now Modify your main 'DriverScript'. With the help of Excel Utility, open the Excel file, read Action Keywords one by one and on each Action Keyword perform the required step.

package executionEngine;

import config.ActionKeywords;
import utility.ExcelUtils;

public class DriverScript {

    public static void main(String[] args) throws Exception {
    	// Declaring the path of the Excel file with the name of the Excel file
    	String sPath = "D://Tools QA Projects//trunk//Hybrid Keyword Driven//src//dataEngine//DataEngine.xlsx";

    	// Here we are passing the Excel path and SheetName as arguments to connect with Excel file 
    	ExcelUtils.setExcelFile(sPath, "Test Steps");

    	//Hard coded values are used for Excel row & columns for now
    	//In later chapters we will replace these hard coded values with varibales
    	//This is the loop for reading the values of the column 3 (Action Keyword) row by row
    	for (int iRow=1;iRow<=9;iRow++){
		    //Storing the value of excel cell in sActionKeyword string variable
    		String sActionKeyword = ExcelUtils.getCellData(iRow, 3);

    		//Comparing the value of Excel cell with all the project keywords
    		if(sActionKeyword.equals("openBrowser")){
                        //This will execute if the excel cell value is 'openBrowser'
    			//Action Keyword is called here to perform action
    			ActionKeywords.openBrowser();}
    		else if(sActionKeyword.equals("navigate")){
    			ActionKeywords.navigate();}
    		else if(sActionKeyword.equals("click_MyAccount")){
    			ActionKeywords.click_MyAccount();}
    		else if(sActionKeyword.equals("input_Username")){
    			ActionKeywords.input_Username();}
    		else if(sActionKeyword.equals("input_Password")){
    			ActionKeywords.input_Password();}
    		else if(sActionKeyword.equals("click_Login")){
    			ActionKeywords.click_Login();}
    		else if(sActionKeyword.equals("waitFor")){
    			ActionKeywords.waitFor();}
    		else if(sActionKeyword.equals("click_Logout")){
    			ActionKeywords.click_Logout();}
    		else if(sActionKeyword.equals("closeBrowser")){
    			ActionKeywords.closeBrowser();}

    		}
    	}
 }

Here we are done with our initial set up of Keyword Driven Framework. The only idea behind the Keyword Driven framework is to accept the action keywords from the Excel, so that all the test cases can be easily created in Excel file. But this Framework is just a draft version. There are plenty of things can be done on it to make it more robust and effective. In the following chapters, we will enhance this draft version.