Data Driven Framework with Apache POI – Excel

Most commercial automated software tools on the market support some sort of data driven testing, which allows you to automatically run a test case multiple times with different input and validation values. As Selenium Webdriver is more an automated testing framework than a ready-to-use tool, you will have to put in some effort to support data driven testing in your automated tests. I usually prefer to use Microsoft Excel as the format for storing my parameters. An additional advantage of using Excel is that you can easily outsource the test data administration to someone other than yourself, someone who might have better knowledge of the test cases that need to be run and the parameters required to execute them.

Reading data from the Excel

We need a way to open this Excel sheet and read data from it within our 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.

How to do it…

1) Download JAR files of Apache POI  and Add Jars to your project library. You can download it from here. That’s all about configuration of Apache POI with eclipse. Now you are ready to write your test.

2) Create a ‘New Package‘ file and name it as ‘testData’, by right click on the Project and select New > Package. Place all of your test data in this folder (package) whether it is a sql file, excel file or anything.

3) Place a Excel file in the above created package location and save it as TestData.xlsx. Fill the data in the excel like below image:

4) Add two constant variables (testData package path & Excel file name) in the Constant class.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

package utility;    public class Constant {       public static final String URL = "http://www.store.demoqa.com";       public static final String Username = "testuser_1";       public static final String Password = "Test@123";       public static final String Path_TestData = "D://ToolsQA//OnlineStore//src//testData//"       public static final String File_TestData = "TestData.xlsx"    }

5) 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/write methods.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109

package utility;          import java.io.FileInputStream;             import java.io.FileOutputStream;             import org.apache.poi.xssf.usermodel.XSSFCell;          import org.apache.poi.xssf.usermodel.XSSFRow;          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;          private static XSSFRow Row;      //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 {        try {            // Open the Excel file FileInputStream ExcelFile = new FileInputStream(Path); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e){ throw (e); } }      //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num          public static String getCellData(int RowNum, int ColNum) throws Exception{        try{            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);            String CellData = Cell.getStringCellValue();            return CellData;            }catch (Exception e){ return"";            }     }      //This method is to write in the Excel cell, Row num and Col num are the parameters      public static void setCellData(String Result,  int RowNum, int ColNum) throws Exception {        try{            Row  = ExcelWSheet.getRow(RowNum); Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL); if (Cell == null) { Cell = Row.createCell(ColNum); Cell.setCellValue(Result); } else { Cell.setCellValue(Result); }           // Constant variables Test Data path and Test Data file name            FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);            ExcelWBook.write(fileOut);            fileOut.flush(); fileOut.close(); }catch(Exception e){ throw (e); } } }

6) Once we are done with writing Excel functions we can go ahead and modify the SignIn_Action module to accept the test data from excel file.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

package appModules;         import org.openqa.selenium.WebDriver;         import pageObjects.Home_Page;         import pageObjects.LogIn_Page;         import utility.ExcelUtils;     // Now this method does not need any arguments     public class SignIn_Action { public static void Execute(WebDriver driver) throws Exception{ //This is to get the values from Excel sheet, passing parameters (Row num & Col num)to getCellData method String sUserName = ExcelUtils.getCellData(1, 1); String sPassword = ExcelUtils.getCellData(1, 2); Home_Page.lnk_MyAccount(driver).click(); LogIn_Page.txtbx_UserName(driver).sendKeys(sUserName); LogIn_Page.txtbx_Password(driver).sendKeys(sPassword); LogIn_Page.btn_LogIn(driver).click();         } }

Note: In the later chapters we will see how to parameterise the row column as well, as we also have to avoid hard coded values in the scripts. This is just to give you an idea to use Excel and we will move forward step by step towards proper framework.

7) Create a ‘New Class‘ and name it as Apache_POI_TC by right click on the ‘automationFramework‘ Package and select New > Class. In this we will read the values from the Excel sheet to use them as the test data and write the test result in the Excel.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

package automationFramework; import java.util.concurrent.TimeUnit; import org.openqa.selenium.WebDriver; import org.openqa.selenium.firefox.FirefoxDriver; import pageObjects.*; import utility.Constant; // Import Package utility.* import utility.ExcelUtils; import appModules.SignIn_Action; public class Apache_POI_TC { private static WebDriver driver = null; public static void main(String[] args) throws Exception {         //This is to open the Excel file. Excel path, file name and the sheet name are parameters to this method         ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"Sheet1");         driver = new FirefoxDriver();         driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);         driver.get(Constant.URL);         SignIn_Action.Execute(driver);         System.out.println("Login Successfully, now it is the time to Log Off buddy.");         Home_Page.lnk_LogOut(driver).click();         driver.quit();         //This is to send the PASS value to the Excel sheet in the result column.         ExcelUtils.setCellData("Pass", 1, 3); } }

Give it a run, see how beautify your script will execute the code.

8) Once it finished open the Excel file and check for the result.

Your Project explorer window will look like this now.

Do not worry about the red cross signs on your test scripts, it is because we have removed arguments from SignIn_Action class.

Data Driven framework can be build up by using TestNg Data Provider.