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 with in 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

‘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‘

Once you downloaded the JAR files, then ‘Add Jars‘ files to your project library. That’s all about configuration of Apache POI with eclipse. Now you are ready to write code.

Create a ‘New Package’ and name it as ‘utility’, by right click on the Project folder and select New > Package.

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.

// 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(intiRow=1;iRow<=9;iRow++){

//Storing the value of excel cell in sActionKeyword string variable

StringsActionKeyword=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();}

elseif(sActionKeyword.equals("navigate")){

ActionKeywords.navigate();}

elseif(sActionKeyword.equals("click_MyAccount")){

ActionKeywords.click_MyAccount();}

elseif(sActionKeyword.equals("input_Username")){

ActionKeywords.input_Username();}

elseif(sActionKeyword.equals("input_Password")){

ActionKeywords.input_Password();}

elseif(sActionKeyword.equals("click_Login")){

ActionKeywords.click_Login();}

elseif(sActionKeyword.equals("waitFor")){

ActionKeywords.waitFor();}

elseif(sActionKeyword.equals("click_Logout")){

ActionKeywords.click_Logout();}

elseif(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 following chapters we will enhance this draft version.