JXL API Data-Driven framework

Data-Driven testing is a software-testing methodology, which is an iterative process to assert the actual value with the expected value that fetches test input data from an external or internal data source. Data-Driven tests are generally carried out with bulk input data.

Java Excel API is termed the JXL API. It is the most widely used API for executing Selenium Data-Driven tests that allows the user to read, write, create, and modify sheets in an Excel Binary (.xls) workbook at runtime. JXL API has no support for SpreadsheetML (.xlsx) workbooks.

Reading and writing in an Excel sheet

The API reads data from Excel Binary workbooks with versions Excel 95, 97, 2000, XP, and 2003. The following snippet tells you how to read an Excel Binary workbook:

FileInputStream fi = new FileInputStream("C:\...inputdata.xls");
Workbook wb = Workbook.getWorkbook(fi);
Sheet ws = wb.getSheet(0);
String a[][] = new String[ws.getRows()][ws.getColumns()];

for (int rowCnt = 1; rowCnt < ws.getRows(); rowCnt++) {
  driver.get("www.example.com");
  driver.findElement(By.locatorType("path")).sendKeys(ws.getCell(0, rowCnt).getContents());
...
}

Here, the getWorkbook() method fetches the workbook as a file and not as string; the getSheet() method accesses the sheet of the workbook; and the getRows() and getColumns() methods store row and column counts using two-dimensional arrays.

The JXL library also allows the users to create an Excel workbook and write data into the workbook. The following snippet tells you how to write an Excel Binary workbook:

FileOutputStream fo = new FileOutputStream("C:\...outputdata.xls");
WritableWorkbook wb = Workbook.createWorkbook(fo);
WritableSheet ws = wb.createSheet("Sheet1", 0);

for (int rowCnt = 1; rowCnt < wrksheet.getRows(); rowCnt++) {
  driver.get("www.example.com");
  driver.findElement(By.locatorType("path")).sendKeys(wrksheet.getCell(0, rowCnt).getContents());
  driver.findElement(By.locatorType("path")).click();

  boolean resultfound = isElementPresent(By.locatorType("path"));

  if (resultfound) {
    //Writes data into 3rd column
    Label l3 = new Label(2, rowCnt, "pass");
    ws.addCell(l3);
    else {
      //Writes data into 3rd column
      Label l2 = new Label(2, rowCnt, "fail");
      ws.addCell(l2);
    }
  }
wb.write();
wb.close();
}

Here, the write() method returns the values to be saved in the Excel workbook and the close() method quits the current workbook session. The getContents() method returns all the values from the cell. Here's the syntax for this method:

wrksheet.getCell(0, rowCnt).getContents()

Let's see an example test method to read and write data in an Excel Binary workbook:

@Test
public void readandwrite() throws Exception {

  // Read data from excel sheet
  FileInputStream fi = new FileInputStream("C:\...inputdata.xls");
  Workbook wrkbook = Workbook.getWorkbook(fi);
  Sheet wrksheet = wrkbook.getSheet(0);
  String a[][] = new String[wrksheet.getRows()][wrksheet.getColumns()];
  // Write the input data into another excel file
  FileOutputStream fo = new FileOutputStream("C:\...outputdata.xls");
  WritableWorkbook wwb = Workbook.createWorkbook(fo);
  WritableSheet ws = wwb.createSheet("customsheet", 0);

  System.out.println("Total Rows: " + wrksheet.getRows());
  System.out.println("Total Columns: " + wrksheet.getColumns());

  for (int i = 0; i < wrksheet.getRows(); i++) {

    for (int j = 0; j < wrksheet.getColumns(); j++) {
      a[i][j] = wrksheet.getCell(j, i).getContents();
      Label l = new Label(j, i, a[i][j]);
      Label l1 = new Label(2, 0, "Result");
      ws.addCell(l);
      ws.addCell(l1);
    }
  }

  for (int rowCnt = 1; rowCnt < wrksheet.getRows(); rowCnt++) {

    driver.get("www.example.com");
   //Enter search keyword by reading data from Excel [Here it read from 1st column]

    driver.findElement(By.locatorType("path")).sendKeys(wrksheet.getCell(0, rowCnt).getContents());
    driver.findElement(By.locatorType("path")).click();
    Thread.sleep(5000);

    boolean resultfound = isElementPresent(By.locatorType("path"));

    if (resultfound) {
      //Writes data into 3rd column
      Label l3 = new Label(2, rowCnt, "pass"); 
      ws.addCell(l3);
      else {
        //Writes data into 3rd column
        Label l2 = new Label(2, rowCnt, "fail"); 
        ws.addCell(l2);
      }
    }
  wwb.write();
  wwb.close();
}

Simple Data-Driven approach

The Data-Driven technique is a repetitive process of any step with multiple sets of data. The Selenium WebDriver API doesn't have any built-in support for Data-Driven tests. Regardless of that, the JExcel/JXL library is a third-party API for Selenium-based tests to perform Data-Driven tasks. In this approach, the script to activate the Data-Driven function is embedded in the test class itself instead of the reusable library being put to use. Let's see an example with a pass condition and a fail condition using a simple Data-Driven technique from JXL API. The following screenshot is an Excel data source of the given example:

Simple Data-Driven approach

Here, we search for a set of keywords on every test run and finally assert the title on the Google search results page. TestNG is the unit-testing framework used here to prioritize and execute tests. The following script is built based on the simple Data-Driven approach using JXL API:

public class SimpleDataDriven {
  WebDriver driver;
  Sheet s;

  @BeforeTest
  public void setUp() {
    driver = new ChromeDriver();
    driver.get("https://www.google.com/");
  }

  @Test(priority = 1)
  public void Googlepass() throws Exception {
    FileInputStream fi = new FileInputStream("/Users/.../data.xls");
    Workbook w = Workbook.getWorkbook(fi);
    s = w.getSheet(0);
    for (int row = 1; row <= s.getRows() - 1; row++) {
      String input1 = s.getCell(0, row).getContents();
      String output1 = s.getCell(1, row).getContents();

      driver.findElement(By.name("q")).clear();
      driver.findElement(By.name("q")).sendKeys(input1);
      driver.findElement(By.name("q")).sendKeys(Keys.ENTER);
      Thread.sleep(3000);
      try {
        Assert.assertEquals(output1, driver.getTitle());
      } catch (Error e) {
        verificationErrors.append(e.toString());
      }
    }
  }

  @Test(priority = 2)
  public void Googlefail() throws Exception {
    FileInputStream fi = new FileInputStream("/Users/.../data.xls");
    Workbook w = Workbook.getWorkbook(fi);
    s = w.getSheet(0);
    for (int row = 1; row <= s.getRows() - 1; row++) {
      String input2 = s.getCell(2, row).getContents();
      String output2 = s.getCell(3, row).getContents();

      driver.findElement(By.name("q")).clear();
      driver.findElement(By.name("q")).sendKeys(input2);
      driver.findElement(By.name("q")).sendKeys(Keys.ENTER);
      Thread.sleep(3000);
      try {
        Assert.assertEquals(output2, driver.getTitle());
      } catch (Error e) {
        verificationErrors.append(e.toString());
      }
    }
  }
}

Data-Driven testing using reusable library

The JExcel library does way more than the simple Data-Driven methodologies explained in the above section. It lets you perform any Java-oriented Data-Driven tasks by creating a reusable library file. The reusable library is in the form of a class rather than a JAR file; it can be easily understood and customized based upon the given specifications:

Create a library file with the following guidelines:

  1. Locate and initialize the Excel Binary workbook.
  2. Obtain the worksheet and read the Excel sheet row count.
  3. Create a function to read the cell value.
  4. Create a dictionary using the Hash table and store the Excel sheet column names.

The following is the reusable library class file (ExcelSheetDriver.java) built using JXL API:

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.IOException;
import java.util.Hashtable;

public class ExcelSheetDriver {

  // create sheet name
  static Sheet wrksheet;
  // create workbook name
  static Workbook wrkbook = null;
  static Hashtable dict = new Hashtable();
  //Create a Constructor
  public ExcelSheetDriver(String ExcelSheetPath) throws BiffException, IOException {
    //Initialize the workbook
    wrkbook = Workbook.getWorkbook(new File(ExcelSheetPath));

    //Here, the worksheet is pointed to Sheet1
    wrksheet = wrkbook.getSheet("Sheet1");
  }

  //Returns the Number of Rows
  public static int RowCount() {
    return wrksheet.getRows();
  }

  //Returns the Cell value by taking row and Column values as argument
  public static String ReadCell(int column, int row) {
    return wrksheet.getCell(column, row).getContents();
  }

  //Create Column Dictionary to hold all the Column Names
  public static void ColumnDictionary() {
    //Iterate through all the columns in the Excel sheet and store the  value in Hashtable
    for (int col = 0; col < wrksheet.getColumns(); col++) {
      dict.put(ReadCell(col, 0), col);
    }
  }

  //Read Column Names
  public static int GetCell(String colName) {
    try {
      int value;
      value = ((Integer) dict.get(colName)).intValue();
      return value;
    } catch (NullPointerException e) {
      return (0);
    }
  }
}

Now, let's create a test class to perform a simple Google search by reading values from the Excel sheet. To do so, first create a constructor to initialize the Excel data source and then create a loop to iterate through the Excel sheet cell values. The following screenshot is an Excel data source of the preceding example:

Data-Driven testing using reusable library

Here, we do a simple Google search for a set of keywords from the Excel file. TestNG is the unit-testing framework used here for executing tests. The following script (Googlesearch.java) is built based on the Data-Driven reusable library using JXL API.

public class GoogleSearch {

  //Global initialization of Variables
  static ExcelSheetDriver xlsUtil;

  //Constructor to initialze Excel for Data source
  public GoogleSearch() throws BiffException, IOException {
    xlsUtil = new ExcelSheetDriver("/Users/.../data.xls");
    //Load the Excel Sheet Col in to Dictionary
    xlsUtil.ColumnDictionary();
  }

  private WebDriver driver;

  @BeforeTest
  public void setUp() throws Exception {
    driver = new ChromeDriver();
    driver.get("https://www.google.com/");
  }

  @Test
  public void Test01() throws Exception {

    //Create a for loop to iterate through the Excel sheet
    for (int rowCnt = 1; rowCnt < xlsUtil.RowCount(); rowCnt++) {

      driver.findElement(By.name("q")).clear();

      //Enter search keyword by reading data from Excel
      driver.findElement(By.name("q")).sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("keyword1"), rowCnt));
      driver.findElement(By.name("q")).sendKeys(Keys.ENTER);
      Thread.sleep(2000);

      driver.findElement(By.name("q")).clear();

      //Enter search keyword by reading data from
      Exceldriver.findElement(By.name("q")).sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("keyword2"), rowCnt));
      driver.findElement(By.name("q")).sendKeys(Keys.ENTER);
      Thread.sleep(2000);
    }
  }
}

Data-Driven testing using TestNG with the @dataProvider annotation

TestNG is a Java-based advanced unit-testing framework similar to JUnit 4, as mentioned in our previous chapters. In this method, we use the TestNG @dataProvider annotation to fetch keywords from an Excel sheet and pass arguments into the test method. This framework using DataProvider is highly recommended for use and an advised Data-Driven framework for implementing Java-based Selenium tests. Let's discuss this with a couple of methods in this section.

In the first method (with Excel), the Excel Binary workbook is used as a data source. This method is usually applied on projects with high data volumes. Here, the TestNG annotation, @dataProvider, allows parameters to pass through the whole iteration process on the Test method. The following screenshot is an Excel data source of the given example:

Data-Driven testing using TestNG with the @dataProvider annotation

Create a test class by getting the array of tables in a two-dimensional array as shown in the following code:

public class DataDrivenWithExcel {
  WebDriver driver;
  private String baseUrl;

  @DataProvider(name = "Test")
  public Object[][] createPayId() throws Exception {
    Object[][] retObjArr = getTableArray("C:\...\data.xls", "Sheet1", "Test01");
    return (retObjArr);
  }

  @BeforeClass
  public void BeforeClass() {
    driver = new FirefoxDriver();
    driver.manage().window().maximize();
    baseUrl = "http://www.google.co.in";
  }

  @Test(dataProvider = "Test", description = "Testing ")
  public void Test01(String column1, String column2, String column3, String column4) throws Exception {

    driver.get(baseUrl + "/");
    driver.findElement(By.name("q")).sendKeys(column1);
    driver.findElement(By.name("q")).sendKeys(Keys.RETURN);
    Thread.sleep(2000);
    driver.findElement(By.name("q")).clear();
  }

  public String[][] getTableArray(String xlFilePath, String sheetName, String tableName) throws Exception {
    String[][] tabArray = null;

    Workbook workbook = Workbook.getWorkbook(new File(xlFilePath));
    Sheet sheet = workbook.getSheet(sheetName);
    int startRow, startCol, endRow, endCol, ci, cj;
    Cell tableStart = sheet.findCell(tableName);
    startRow = tableStart.getRow();
    startCol = tableStart.getColumn();

    Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 100, 64000, false);

    endRow = tableEnd.getRow();
    endCol = tableEnd.getColumn();
    System.out.println("startRow=" + startRow + ", endRow=" + endRow + ", " + "startCol=" + startCol + ", endCol=" + endCol);
    tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
    ci = 0;

    for (int i = startRow + 1; i < endRow; i++, ci++) {
      cj = 0;
      for (int j = startCol + 1; j < endCol; j++, cj++) {
        tabArray[ci][cj] = sheet.getCell(j, i).getContents();
      }
    }
    return (tabArray);
  }
}

In the second method (without Excel), the Test class makes use of a built-in data source within a class. Here, the TestNG annotation @dataprovider plays a major role by maintaining effective Data-Driven tests as said in the preceding section. This method is applicable only to projects with lightweight data. Create a Test class by storing data in memory with a two-dimensional array, as shown in the following code:

public class DataDrivenWithoutExcel {
  WebDriver driver;
  private String baseUrl;

  @DataProvider(name = "Test")
  public String[][] y() {

    return new String[][] {
      {
        "prashanth sams", "prashanth"
      }, {
        "selenium esentials", "selenium"
      }, {
        "seleniumworks", "prashanth sams"
      }
    };
  }

  @BeforeClass
  public void BeforeClass() {
    driver = new FirefoxDriver();
    driver.manage().window().maximize();
    baseUrl = "http://www.google.co.in";

  }

  @Test(dataProvider = "Test")
  public void Google(String actual, String expected) throws Exception
  {

    driver.get(baseUrl + "/");
    driver.findElement(By.name("q")).sendKeys(actual);
    driver.findElement(By.name("q")).sendKeys(Keys.RETURN);
    Thread.sleep(2000);
    boolean b = driver.getPageSource().contains(expected);
    Assert.assertTrue(b);

  }

  @AfterClass
  public void AfterClass() {
    driver.quit();
  }

}
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset