Datadriven + JXL API | TestNG | Method 1

JXL API
Download Jxl.jar

This is one of the Methods I use for Data-driven using Jxl API.  Here, you can perform data driven by creating a reusable library file.

Method 1

Step 1 : Create a library file with all the below mentioned functionality

1. Open Excel Sheet using ExcelSheetPath
2. Read Excel Sheet Row Count
3. Read Cell value from a specified location
4. Create a Dictionary to store Excel Sheet Column name
5. Create a function to read from the Dictionary

The Source code looks like this.

package packagename;

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

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

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
wrkbook = Workbook.getWorkbook(new File(ExcelSheetPath));
  
//For Demo purpose the excel sheet path is hardcoded, but not recommended :)
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); 
}
} 
}


Next we are going to create actual test file which is going to perform intended operation.

Step 2: Create a TestNG Class file to perform test

Following example perform search by reading values from Excel sheet

Source Code looks like this


package packagename;

import java.io.IOException;
import jxl.read.biff.BiffException;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
//import org.openqa.selenium.*;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

import com.thoughtworks.selenium.SeleneseTestCase;

public class className extends SeleneseTestCase {

//Global initialization of Variables
static ExcelSheetDriver xlsUtil;

//Constructor to initialze Excel for Data source
public className() throws BiffException, IOException
{
//Let's assume we have only one Excel File which holds all Testcases. weird :) Just for Demo !!!
xlsUtil = new ExcelSheetDriver("C:\\Data.xls");
//Load the Excel Sheet Col in to Dictionary for Further use in our Test cases.
xlsUtil.ColumnDictionary();
}

private WebDriver driver;

@BeforeTest
public void setUp() throws Exception {
driver = new FirefoxDriver();
driver.get("http://www.xyz.com");
}

@Test
public void methodName() throws Exception {

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

//Enter search keyword by reading data from Excel
driver.findElement(By.id("source Element")).sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("columnName"), rowCnt));
}
} 
}

Create an Excel sheet that resembles this:

Note:- Text highlighted in Orange-color has to modified by User