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
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.
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
jordan outlet
ReplyDeletekyrie shoes
kyrie 7