Hello guys,
I am on the process of doing a java code which should read the excel file to get the values from the excel sheet to use them in my selenium test cases. I have problem in iterating through each row to check them in my test cases sequentially.
this is my driver script.I have also attached all my files
package com.scripts;
import java.io.IOException;
import java.util.Vector;
import org.testng.annotations.Test;
import com.cases.Scenario1;
import com.thoughtworks.selenium.Selenium;
public class DriverScript {
@Test
public void methodtest()
{
Utilities util = new Utilities();
String pathValue = Utilities.LocatingXls("Data.xls");
Vector contVector = util.contentReading(pathValue, "Scenario1");
Scenario1 scnTst=new Scenario1(contVector);
scnTst.check1();
}this is Utilities.java
package com.scripts;
import static com.thoughtworks.selenium.grid.tools.ThreadSafeSeleniumSessionStorage.closeSeleniumSession;
import static com.thoughtworks.selenium.grid.tools.ThreadSafeSeleniumSessionStorage.session;
import static com.thoughtworks.selenium.grid.tools.ThreadSafeSeleniumSessionStorage.startSeleniumSession;
import java.io.File;
import java.io.IOException;
import java.util.Locale;
import java.util.Vector;
import com.thoughtworks.selenium.Selenium;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
public class Utilities {
@SuppressWarnings("rawtypes")
final Vector listOfWorksheetNames = new Vector();
@SuppressWarnings("rawtypes")
final Vector returnValues = new Vector();
public String projectName = null ;
public String admin = null ;
public String adminPass = null;
public String userName = null;
public String password = null;
public static String LocatingXls(String Datasheet){
String Mainfile = null;
try {
File directory = new File (".");
String basepath = directory.getCanonicalPath();
Mainfile = basepath + File.separator + File.separator + "src" + File.separator +File.separator+ "com" +File.separator+ File.separator + "scripts"
+ File.separator +File.separator+ "Data" + File.separator +File.separator+ Datasheet;
} catch (Exception ex) {
ex.printStackTrace();
}
return Mainfile;
}
public static void getHeadingFromXlsFile(Workbook wb) {
try {
int sheetCount = wb.getNumberOfSheets();
char[] headingNames;
for (int i=0 ; i<sheetCount; i++)
{
Sheet sheet;
sheet = wb.getSheet(i);
int columnCount = sheet.getColumns();
for (int j = 0; j < columnCount; j++) {
//System.out.println(sheet.getCell(j, 0).getContents());
headingNames =sheet.getCell(j, 0).getContents().toCharArray();
System.out.println(headingNames[j]);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public Vector contentReading(String pathname,String sheetName) {
System.out.println("inside");
WorkbookSettings ws = null;
Workbook workbook = null;
Sheet workingSheet = null;
Cell rowData[] = null;
int index = '0';
int rowCount = '0';
int columnCount = '0';
int totalSheet = 0;
try {
System.out.println("inside");
ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
workbook = Workbook.getWorkbook(new File(pathname), ws);
totalSheet = workbook.getNumberOfSheets();
if(totalSheet!=0) {
System.out.println("Total Sheet Found:" + totalSheet);
for(int j=0;j<totalSheet ;j++) {
//System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
//vector for adding sheetnames and process
listOfWorksheetNames.add(workbook.getSheet(j).getName());
}
}else{
System.out.println("No Sheets found");
}
if(listOfWorksheetNames.contains(sheetName))
{
index = listOfWorksheetNames.indexOf(sheetName);
System.out.println("The sheet has been found at index" +index + "proceeding further");
workingSheet = workbook.getSheet(index);
}
//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
System.out.println("Total Rows inside Sheet:" + workingSheet.getRows());
rowCount = workingSheet.getRows();
//Total Total No Of Columns in Sheet
System.out.println("Total Column inside Sheet:" + workingSheet.getColumns());
columnCount = workingSheet.getColumns();
//Utilities.getHeadingFromXlsFile(workbook);
System.out.println("avoiding the First row for the label headings");
//Reading Individual Row Content
if (index ==0){
System.out.println("entering into the first scenario's sheet and setting the values");
for (int i = 1; i < rowCount; i++) {
//Get Individual Row
rowData = workingSheet.getRow(i);
if (rowData[0].getContents().length() != 0) { // the first date column must not null
for (int j = 0; j < columnCount; j++) {
switch (j) {
case 0:
projectName = rowData[j].getContents();
returnValues.add(projectName);
System.out.println("Project:" + rowData[j].getContents());
break;
case 1:
admin = rowData[j].getContents();
returnValues.add(admin);
System.out.println("Admin:" + rowData[j].getContents());
break;
case 2:
adminPass = rowData[j].getContents();
returnValues.add(adminPass);
System.out.println("AdminPass:" + rowData[j].getContents());
break;
case 3:
String roleName = rowData[j].getContents();
returnValues.add(roleName);
System.out.println("roleName:" + rowData[j].getContents());
break;
case 4:
String roleDesc = rowData[j].getContents();
returnValues.add(roleDesc);
System.out.println("roleDesc:" + rowData[j].getContents());
break;
case 5:
String userName = rowData[j].getContents();
returnValues.add(userName);
System.out.println("userName:" + rowData[j].getContents());
case 6:
String userPassword = rowData[j].getContents();
returnValues.add(userPassword);
System.out.println("userPassword:" + rowData[j].getContents());
default:
break;
}
}
}
}
}
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
return returnValues;
}
public Selenium loginFunct(String seleniumHost, int seleniumPort,String browser, String webSite,
String userName, String userPassword){
startSeleniumSession(seleniumHost,seleniumPort,browser,webSite);
Selenium sel = session();
sel.open("/testlink/login.php");
sel.type("login", userName);
sel.type("tl_password", userPassword);
sel.click("login_submit");
sel.waitForPageToLoad("30000");
return sel;
}
}This is my Test case
Scenario1.java
package com.cases;
import java.io.IOException;
import java.util.Vector;
import org.testng.Assert;
import org.testng.annotations.Test;
import com.scripts.*;
import com.thoughtworks.selenium.Selenium;
public class Scenario1 {
private Vector Scene1 =new Vector();
public Scenario1(Vector scene1) {
this.Scene1=scene1;
// TODO Auto-generated constructor stub
}
public String[] temp;
@Test
public void check1()
{
Utilities util = new Utilities();
Selenium sceario1 = util.loginFunct("localhost", 4444, "*firefox","http://localhost/" , Scene1.elementAt(1).toString(), Scene1.elementAt(2).toString());
sceario1.open("/testlink/index.php");
sceario1.selectFrame("mainframe");
sceario1.click("link=Role Management");
sceario1.waitForPageToLoad("30000");
sceario1.click("doCreate");
sceario1.waitForPageToLoad("30000");
sceario1.type("rolename", Scene1.elementAt(3).toString());
String tempporary = Scene1.elementAt(4).toString();
System.out.println("text @ temp :" + tempporary);
temp = tempporary.split(",");
for (int i=0; i<temp.length;i++)
{
sceario1.click(temp[i]);
}
sceario1.click("role_mgmt");
sceario1.waitForPageToLoad("30000");
sceario1.click("link=View Users");
sceario1.waitForPageToLoad("30000");
sceario1.click("doCreate");
sceario1.waitForPageToLoad("30000");
sceario1.type("login", Scene1.elementAt(5).toString());
sceario1.type("firstName", "test");
sceario1.type("lastName", "test");
sceario1.type("password", Scene1.elementAt(6).toString());
sceario1.type("email", "test@test1.com");
sceario1.select("rights_id", Scene1.elementAt(3).toString());
sceario1.click("do_update");
sceario1.waitForPageToLoad("30000");
}
}here i hard coded everything for only one row. What if i need to loop through all the row data in the excel file and pass it to the Scenario.java one by one.