DWQA Ask QuestionCategory: QuestionsAssignment-Level_High: Fetch Data from Excel, fill form using Test NG data Provider
admin Staff asked 3 years ago
  1. Navigate to URL: http://the-internet.herokuapp.com/login
  2. Create an excel file with columns as username, password, Status
  3. Enter different values in the sheet but only one correct user name and password
  4. Iterate though the sheet every time fetch user name and password and fill the form and click on login button.
  5. Fetch the message at the top and write it back in the excel file.
  6. Only one row with correct user name and password should have login success message; rest all should have login failed message.

 

Solution 1: Do it using simple for loop.

Solution 2: Do it using Data Provider.

4 Answers
Suraj Gaikwad Staff answered 3 years ago

Form Authentication using  FOR loop with two dimensional array.


public class FormAuthentication {

static WebDriver driver;


public static void invokeBrowser()
{

// browser launch code

}

public static void main(String[] args) {


FormAuthentication.logic();




}


public static void logic()
{
String login_data[][]= new String[][]{
{"suraj","pass"},
{"sagar","xyz"},
{"rahul","abc"},
{"tomsmith","SuperSecretPassword!"},
{"sachin","mnp"}
};


List<String> user = new ArrayList<String>();
List<String> pass = new ArrayList<String>();

//for loop to fetching usernames
for(int row=0;row<1;row++)
{
for(int col=0;col<5;col++)
{

String temp= login_data[col][row];
user.add(temp);

}
}


//for loop to fetching passwords
for(int row=0;row<5;row++)
{
for(int col=1;col<=1;col++)
{

String temp2= login_data[row][col];
pass.add(temp2);

}
}



for(int i=0;i<user.size();i++)
{
String u = user.get(i);
String p = pass.get(i);

invokeBrowser();
logIn(u, p);

}


}

public static void logIn(String userName, String pwd)
{

WebElement username = driver.findElement(By.xpath("//input[@id='username']"));
username.sendKeys(userName);

WebElement password = driver.findElement(By.xpath("//input[@id='password']"));
password.sendKeys(pwd);

WebElement log_in_button = driver.findElement(By.xpath("//button[@class='radius']"));
log_in_button.click();

try {
WebElement logIn_successfull_text = driver.findElement(By.xpath("//div[@class='flash success']"));

if(logIn_successfull_text.isDisplayed())
{
System.out.println("Valid username and password: "+userName+" "+pwd);
}


} catch (Exception e) {

}


try {
WebElement logIn_successfull_text = driver.findElement(By.xpath("//div[@class='flash error']"));

if(logIn_successfull_text.isDisplayed())
{
System.out.println("Invalid username and password: "+userName+" "+pwd);
}


} catch (Exception e) {

}



}

}

 

Suraj Gaikwad Staff replied 3 years ago

ok..thank u sir

admin Staff replied 3 years ago

Hi Suraj, You do not need two for loops here. Since you already are aware that first clm is user name and 2nd clm is password.

//for loop to fetching usernames
for(int row=0;row<1;row++)
{
for(int col=0;col<5;col++)
{

String temp= login_data[col][row];
user.add(temp);

}
}

Nikita dumbhare Staff answered 3 years ago

***********************************Read Data From Excel**********************************************

package herokuappogin;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;

public class ExcelUtilsForRead{

static String projectPath;
static XSSFWorkbook workbook;
static XSSFSheet sheet;

public ExcelUtilsForRead(String excelPath,String sheetName){
try {

workbook = new XSSFWorkbook(excelPath);
sheet= workbook.getSheet(sheetName);


} catch (IOException e) {

e.printStackTrace();
}


}

public static void main(String[] args) throws Exception {
getRowCount();
getColCount();
getCellDataString(0,0);
WriteCellData();
// getCellDataNumber(1,1);

}


public static void WriteCellData() throws Exception{



FileInputStream fileOut = new FileInputStream("D:\\SeleniumWorkSpace\\DataProvider_Login\\excel\\HeroLogin.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fileOut);
XSSFSheet sheet = workbook.getSheet("Sheet1");
sheet.getRow(1).createCell(2).setCellValue("pass");
fileOut.close();
FileOutputStream outFile =new FileOutputStream(new File("D:\\SeleniumWorkSpace\\DataProvider_Login\\excel\\HeroLogin.xlsx"));
workbook.write(outFile);
outFile.close();


}

public static int getRowCount(){
int rowCount=0;
try{


rowCount=sheet.getPhysicalNumberOfRows();
System.out.println("No of row count :"+rowCount);
}catch(Exception exp){
System.out.println(exp.getMessage());
exp.getCause();
exp.printStackTrace();
}
return rowCount;
}

public static int getColCount(){
int colCount =0;
try{

colCount=sheet.getRow(0).getPhysicalNumberOfCells();
System.out.println("No of col count :"+colCount);
}catch(Exception exp){
System.out.println(exp.getMessage());
exp.getCause();
exp.printStackTrace();
}
return colCount;
}



public static String getCellDataString(int rowNum,int colNum){
String cellData =null;
try {

cellData=sheet.getRow(rowNum).getCell(colNum).getStringCellValue();
//System.out.println("Cell data is: "+cellData);

}catch(Exception exp){

System.out.println(exp.getMessage());
exp.getCause();
exp.printStackTrace();


}
return cellData;



}






// public static void WriteCellResult(int rowNum,int colNum){
//
// }
// public static void getCellDataNumber(int rowNum,int colNum){
//
// try {
//
// double cellData=sheet.getRow(rowNum).getCell(colNum).getNumericCellValue();
// System.out.println("Cell data is: "+cellData);
//
// }catch(Exception exp){
//
// System.out.println(exp.getMessage());
// exp.getCause();
// exp.printStackTrace();
//
//
// }
//
//
//
// }



}

Nikita dumbhare Staff answered 3 years ago

*********************************************Write Data *********************************************
package herokuappogin;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CreatingCellToWrite {

public static void main(String args[]) throws IOException {
try {
WriteCellData();
} catch (Exception e) {

e.printStackTrace();
}
}
// Workbook wb = new HSSFWorkbook();
public static void WriteCellData() throws Exception{

FileInputStream fileOut = new FileInputStream(“D:\\SeleniumWorkSpace\\DataProvider_Login\\excel\\HeroLogin.xlsx”);
XSSFWorkbook workbook = new XSSFWorkbook(fileOut);
XSSFSheet sheet = workbook.getSheet(“Sheet1”);

XSSFRow row = sheet.getRow(1);
row.createCell(2).setCellValue(“pass”);

//sheet.getRow(1).createCell(2).setCellValue(“pass”);
fileOut.close();
FileOutputStream outFile =new FileOutputStream(new File(“D:\\SeleniumWorkSpace\\DataProvider_Login\\excel\\HeroLogin.xlsx”));
workbook.write(outFile);
outFile.close();

}

}

 
Nikita dumbhare Staff answered 3 years ago
****************************HeroKuAppLogin********************************
package herokuappogin;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class herokuappLoginPage {

WebDriver driver;
@BeforeMethod
public void beforeMethod() {
System.setProperty("webdriver.chrome.driver", "D:\\chromedriver.exe");
driver=new ChromeDriver();
driver.manage().window().maximize();
driver.get("http://the-internet.herokuapp.com/login");
}


@Test(dataProvider ="test1data") //Call DataProvider
public void Test1(String username,String password) throws Exception{
System.out.println(username+" | "+password);
// driver.get("http://the-internet.herokuapp.com/login");

driver.findElement(By.xpath("//input[@id='username']")).sendKeys(username);
driver.findElement(By.xpath("//input[@id='password']")).sendKeys(password);
Thread.sleep(2000);
driver.findElement(By.xpath("//button[@class ='radius']")).click();
WebElement msg=driver.findElement(By.xpath("//div[@id='flash-messages']"));
String result=msg.getText().replaceAll("×", "");
System.out.println("message" +result);
String Output = "You logged into a secure area!";

if(result.contains(Output)){
CreatingCellToWrite excelCellWrite =new CreatingCellToWrite();
excelCellWrite.WriteCellData();

}
else{

System.out.println("Inalid");
}





}


@DataProvider(name = "test1data") //Create DataProvider
public Object[][] getData(){
String excelPath ="D:\\SeleniumWorkSpace\\DataProvider_Login\\excel\\HeroLogin.xlsx";
String sheetName ="Sheet1";

Object data[][]=testData(excelPath, sheetName); //Create Object array
return data;


}

public static Object[][] testData(String excelPath,String sheetName) //Return Type Object[][]

{

ExcelUtilsForRead excelData =new ExcelUtilsForRead(excelPath,sheetName);

int rowCount=excelData.getRowCount();
int colCount =excelData.getColCount();

Object data[][] = new Object[rowCount-1][colCount];

for(int i =1;i<rowCount;i++){
for(int j =0;j<colCount;j++){

String cellData =excelData.getCellDataString(i, j);
//System.out.print(cellData+ " | ");
data[i-1][j]=cellData;


}
System.out.println();

}
return data;

}

}