Implementation of Testng @DataProvider using EXCEL (Data Driven Framework) & JXL to Read XLS

11:07 PM 1 Comments

@DataProvider
In order to store the multiple set of input data we use @DataProvider.
We are going to read the xls file which contains input data and store in @DataProvider.
Steps to Achieve :
  a. Store the input data in an xls file.
  b. Read the data from the xls using an third party jar jxl2.6.jar
   Link for Downloading JXL Jar ->http://www.findjar.com/index.x?query=jxl
   jxl stands for -> java excel library
 c. Add the jar to the build path.
 d. Store the data in @Dataprovider
 e. Use the @Dataprovider in the @Test methods.
JExcel API ->http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/
  WorkBook contains sheets
  Sheets contains rows and column
  Each and every values stored in the rows and columns in a cell.
  Retrieve the contents of the cell using row index and column index and store in an array
Program:
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class ReadExcelJXL {
@Test(dataProvider="DP")
public void sales_coupon_count(String username,String password)
{

System.out.println("The value of username" + username);
System.out.println("The value of password" + password);

}
@DataProvider(name="DP")
public String[][] readExcel(  )
{
File file = new File("inputData.xls");
String inputData[][] = null;
Workbook w;

try {
w = Workbook.getWorkbook(file);

// Get the first sheet
Sheet sheet = w.getSheet(0);

int colcount=sheet.getColumns();

int rowcount=sheet.getRows();

inputData= new String[rowcount][colcount];

for ( int i=0;i<sheet.getRows();i++)
{
for (int j=0;j<sheet.getColumns();j++)
{
Cell cell=sheet.getCell(j,i);
inputData[i][j]=cell.getContents();
}
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return inputData;
 }
}
The value of username-->xyz
The value of password-->abc
The value of username-->abc
The value of password-->welcome
The value of username-->ab1123
The value of password-->welcome123
PASSED: sales_coupon_count("xyz", "abc")
PASSED: sales_coupon_count("abc ", "welcome ")
PASSED: sales_coupon_count("ab1123 ", "welcome123")

===============================================
    ReadExcelJXL
    Tests run: 3, Failures: 0, Skips: 0
===============================================


===============================================
DataDriven
Total tests run: 3, Failures: 0, Skips: 0
===============================================
inputData.xls