Data Driven using Excel

Data Driven using Excel

When designing tests, parameterizing the tests is inevitable. We will make use of Apache POI - Excel JAR's to achieve the same. It helps us to read and write into excel.

Download JAR

Step 1 : Navigate to the URL - http://poi.apache.org/download.html and download the ZIP format.

Step 2 : Click on the Mirror Link to download the JAR's.

Step 3 : Unzip the contents to a folder.

Step 4 : Unzipped contents would be displayed as shown below.

Step 5 : Now create a New project and add all the 'External JARs' under 'poi-3.10.FINAL' folder.

Step 6 : Now add all the 'External JARs' under 'ooxml-lib' folder.

Step 7 : Now add all the 'External JARs' under 'lib' folder.

Step 8 : The Added JAR is displayed as shown below.

Step 9 : The Package Explorer is displayed as shown below. Apart from that add 'WebDriver' related JAR's

Parameterization

For Demo purposes, we will parameterize the percent calculator test.

Step 1 : We will parameterize all the inputs required for percent calculator using excel. The designed excel is shown below.

Step 2 : Now we will execute all percent calculator for all the specified parameters.

Step 3 : Let us create generic methods to access the excel file using the imported JARS. These methods helps us to get a particular cell data or to set a particular cell data etc.

import java.io.*; import org.apache.poi.xssf.usermodel.*; public class excelutils { private XSSFSheet ExcelWSheet; private XSSFWorkbook ExcelWBook; //Constructor to connect to the Excel with sheetname and Path public excelutils(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 set the rowcount of the excel. public int excel_get_rows() throws Exception { try { return ExcelWSheet.getPhysicalNumberOfRows(); } catch (Exception e) { throw (e); } } //This method to get the data and get the value as strings. public String getCellDataasstring(int RowNum, int ColNum) throws Exception { try { String CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getStringCellValue(); System.out.println("The value of CellData " + CellData); return CellData; } catch (Exception e) { return "Errors in Getting Cell Data"; } } //This method to get the data and get the value as number. public double getCellDataasnumber(int RowNum, int ColNum) throws Exception { try { double CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getNumericCellValue(); System.out.println("The value of CellData " + CellData); return CellData; } catch (Exception e) { return 000.00; } } }

Step 4 : Now add a main method which will access the excel methods that we have developed.

import java.io.*; import org.apache.poi.xssf.usermodel.*; public class excelutils { private XSSFSheet ExcelWSheet; private XSSFWorkbook ExcelWBook; //Constructor to connect to the Excel with sheetname and Path public excelutils(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 set the rowcount of the excel. public int excel_get_rows() throws Exception { try { return ExcelWSheet.getPhysicalNumberOfRows(); } catch (Exception e) { throw (e); } } //This method to get the data and get the value as strings. public String getCellDataasstring(int RowNum, int ColNum) throws Exception { try { String CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getStringCellValue(); //Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); //String CellData = Cell.getStringCellValue(); System.out.println("The value of CellData " + CellData); return CellData; } catch (Exception e) { return "Errors in Getting Cell Data"; } } //This method to get the data and get the value as number. public double getCellDataasnumber(int RowNum, int ColNum) throws Exception { try { double CellData = ExcelWSheet.getRow(RowNum).getCell(ColNum).getNumericCellValue(); //Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); //String CellData = Cell.getStringCellValue(); System.out.println("The value of CellData " + CellData); return CellData; } catch (Exception e) { return 000.00; } } }