How to fetch data as input from excel and use it as a input(username and password) to login in application.

Answered

How to fetch data as input from excel and use it as a input(username and password) to login in application.?

Any body have this code ready plz share with me.

Thanks

Train Asked on July 13, 2015 in Selenium WebDriver.
Add Comment
  • 2 Answer(s)
    Best answer

    /* Code to Fetch Login and password from Excel file and supply it to goibibo login page */

    package apache.poi.datadriven.anonymous;

    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.util.concurrent.TimeUnit;

    import junit.framework.Assert;

    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.junit.After;
    import org.junit.AfterClass;
    import org.junit.Before;
    import org.junit.BeforeClass;
    import org.junit.Test;
    import org.openqa.selenium.By;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.WebElement;
    import org.openqa.selenium.firefox.FirefoxDriver;

    public class LoginClass {

    private static XSSFWorkbook XLWBook;
    private static XSSFSheet XLSheet;
    private static XSSFCell XLCell;
    private static XSSFRow XLRow;
    private static final String XLFile = “D://Logins.xlsx”;                       // Path to Excel file where the Login and Password needs to be fetched

    private static WebDriver wd;
    private static String BaseURL;
    private static int rowStart = 0, colStart =0;
    private String login, pswd;

    @BeforeClass
    public static void initialize(){
    wd = new FirefoxDriver();
    wd.manage().window().maximize();
    BaseURL = “https://www.goibibo.com/accounts/login/?next=http://www.goibibo.com/offers/mega-summer-sale/?gclid=Cj0KEQjww42tBRCO-sfEiO3DvYMBEiQAHeqMKKU_ttZYUjuCpl6V4iTXmvcQE_yv5nCT2NnVPb846LcaAlCY8P8HAQ&utm_source=google&utm_medium=cpc&utm_campaign=DF-Brand-EM&utm_adgroup=Only%20Goibibo&xtor=SEC-10111[DF-Brand-EM]-GOO-[Only%20Goibibo]–[S]-[goibibo]&ef_id=UzqQHgAAAP8Lqq6F:20150713074225:s”;
    }

    @Before
    public void LoadBrowser(){
    wd.navigate().to(BaseURL);
    wd.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
    }

    @Test
    public void datadriven() throws Exception{
    //Enter Username from Excel
    setExcelFile(XLFile, “Sheet1”);
    for(rowStart=0 ;rowStart<XLSheet.getPhysicalNumberOfRows();rowStart++){
    login = getCellData(rowStart, colStart);
    pswd = getCellData(rowStart,colStart+1);
    System.out.println(“Trying with: ” + login +”/”+ pswd);
    wd.findElement(By.id(“id_username”)).clear();
    wd.findElement(By.id(“id_username”)).sendKeys(login);
    wd.findElement(By.id(“id_password”)).clear();
    wd.findElement(By.id(“id_password”)).sendKeys(pswd);
    wd.findElement(By.id(“signinBtn”)).click();
    }
    }

    @After
    public void closeBrowser(){

    }

    @AfterClass
    public static void Closedown() throws InterruptedException{

    wd.close();
    }

    public static void setExcelFile(String XLFile, String Sheetname) throws Exception{

    try{
    FileInputStream ExcelFile = new FileInputStream(XLFile);
    XLWBook = new XSSFWorkbook(ExcelFile);
    XLSheet = XLWBook.getSheet(Sheetname);
    }catch(Exception e){
    throw(e);
    }

    }

    //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
    public static String getCellData(int RowNum, int ColNum) throws Exception{

    try{

    XLCell = XLSheet.getRow(RowNum).getCell(ColNum);

    String Cellvalue = XLCell.getStringCellValue();

    return Cellvalue;

    }catch (Exception e){

    return””;

    }

    }

    }

    Train Answered on July 13, 2015.
    Add Comment

    Script is working .
    Thanks a  ton Suhabe_Shaikh,

    Train Answered on July 14, 2015.
    Add Comment
  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.