'read username and password from the excel file

Below is my code. How can I read this data?

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;


public class Yoda {

    public static void main(String[] args)

     {
    // Launch FireFox
    WebDriver driver = new FirefoxDriver();

    //Enter YODA URL
    driver.get("https://q1yda1m2.madc.att.com:20621/YodaGUI/index.jsp");

    //Enter UserID
    driver.findElement(By.name("userid")).sendKeys("xx3517");

    //Enter Password
    driver.findElement(By.name("password")).sendKeys("123123a");

    //Click on Submit button after entering User ID and Password
    driver.findElement(By.name("btnSubmit")).click();

    //Click on Remind Me later
    driver.findElement(By.name("successOK")).click();

    //Click on Search Button on YODA home page
    driver.findElement(By.className("MenuBarItemSubmenu")).click();
    driver.findElement(By.xpath("//*[text()='Fulfillment Details']")).click();
    driver.findElement(By.name("location")).sendKeys("Q400");
    driver.findElement(By.name("activity")).sendKeys("OY");
    driver.findElement(By.name("orderID")).sendKeys("3955349");
    driver.findElement(By.id("fulfillment_submit")).click();
    }

}

Solution 1:^[1]

Your question is a bit confusing. Do you want to read data from the excel file (the image you pasted)? Then you have the right imports (apache poi) though you haven't used it.

To read from the excel, here's the code:

      try {
            FileInputStream fStream = new FileInputStream(new File(
                    "sample.xlsx"));

            // Create workbook instance referencing the file created above
            XSSFWorkbook workbook = new XSSFWorkbook(fStream);

            // Get your first or desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0); // getting first sheet

            // Iterate through the rows present in sheet
            Iterator<Row> rIterator = sheet.iterator();
            while (rIterator.hasNext()) {
                Row row = rIterator.next();
                Iterator<Cell> cIterator = row.cellIterator();// Iterate through
                                                                // the columns
                                                                // present in
                                                                // that row
                while (cIterator.hasNext()) {
                    Cell cell = cIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        System.out.println(cell.getNumericCellValue() + "\t");
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        System.out.println(cell.getStringCellValue() + "\t");
                }
            System.out.println(" ");    
            }
            fStream.close();

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

The above code will read a "Sample.xlsx" file and process it to output it's content on the console.

Also, check this link out : how to read all cell value using Apache POI?

EDIT
If you are sure from which column you exactly want to read the values from, the you can use this easier approach:

package example;

import java.io.File;
import java.io.FileInputStream;

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;

public class ReadFromExcel {

    public static void main(String[] args) {
        try {
            FileInputStream fStream = new FileInputStream(new File(
                    "sample.xlsx"));

            // Create workbook instance referencing the file created above
            XSSFWorkbook workbook = new XSSFWorkbook(fStream);

            // Get your first or desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0); // getting first sheet

            XSSFRow row = sheet.getRow(1);
            XSSFCell cell1 = row.getCell(0);
            XSSFCell cell2 = row.getCell(1);
            XSSFCell cell3 = row.getCell(2);

            String location = cell1.toString();
            String activity = cell2.toString();
            String order = cell3.toString();

            System.out.println(location + order + activity);
            fStream.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Now, simply pass the value of the Strings "location", "order" and "activity" through your SendKeys()

Edit 2
Your final code should look something like this:

package example;

import java.io.File;
import java.io.FileInputStream;

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.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class Yoda {

    public static void main(String[] args)

    {
        String location = null;
        String activity = null;
        String order = null;

        // Launch FireFox
        WebDriver driver = new FirefoxDriver();

        // Enter YODA URL
        driver.get("https://q1yda1m2.madc.att.com:20621/YodaGUI/index.jsp");

        // Enter UserID
        driver.findElement(By.name("userid")).sendKeys("xx3517");

        // Enter Password
        driver.findElement(By.name("password")).sendKeys("123123a");

        // Click on Submit button after entering User ID and Password
        driver.findElement(By.name("btnSubmit")).click();

        // Click on Remind Me later
        driver.findElement(By.name("successOK")).click();

        try {
            FileInputStream fStream = new FileInputStream(new File(
                    "C:\\Users\\xxxx\\Desktop\\sample.xlsx")); //Enter the path to your excel here

            // Create workbook instance referencing the file created above
            XSSFWorkbook workbook = new XSSFWorkbook(fStream);

            // Get your first or desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0); // getting first sheet

            XSSFRow row = sheet.getRow(1);
            XSSFCell cell1 = row.getCell(0);
            XSSFCell cell2 = row.getCell(1);
            XSSFCell cell3 = row.getCell(2);

            location = cell1.toString();
            activity = cell2.toString();
            order = cell3.toString();

            fStream.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        // Click on Search Button on YODA home page
        driver.findElement(By.className("MenuBarItemSubmenu")).click();
        driver.findElement(By.xpath("//*[text()='Fulfillment Details']"))
                .click();
        driver.findElement(By.name("location")).sendKeys(location);
        driver.findElement(By.name("activity")).sendKeys(activity);
        driver.findElement(By.name("orderID")).sendKeys(order);
        driver.findElement(By.id("fulfillment_submit")).click();
    }

}

Solution 2:^[2]

There are multiple ways to read data from excel and one of the simplest way would be :

try
        {

            File file = new File("D:/TestData.xlsx");
            FileInputStream iFile = new FileInputStream(file);

            XSSFWorkbook wb = new XSSFWorkbook(iFile);
            XSSFSheet sheet = wb.getSheet("Sheet1");

            int rowCount = sheet.getLastRowNum();
            System.out.println("the no of rows are : " + rowCount);
            for (int row=1; row<=rowCount; row++)
            {

                String location = sheet.getRow(row).getCell(0).getStringCellValue();

                String activity = sheet.getRow(row).getCell(1).getStringCellValue();

                int order=(int) sheet.getRow(row).getCell(2).getNumericCellValue();

                System.out.println(location + " , " + activity + " , " +order );

            }

            iFile.close();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }

Solution 3:^[3]

package package1;



import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;


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.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

public class yoda2 {


public static void main(String[] args) { 
// Launch FireFox WebDriver driver = new FirefoxDriver();

// Enter YODA URL 
driver.get("https://q1yda1m2.madc.att.com:20621/YodaGUI/index.jsp");    

// Enter UserID 
driver.findElement(By.name("userid")).sendKeys("xx3517"); 

 // Enter Password driver.findElement(By.name("password")).sendKeys("123123a"); 

 // Click on Submit button after entering User ID and Password driver.findElement(By.name("btnSubmit")).click(); 

// Click on Remind Me later driver.findElement(By.name("successOK")).click(); 

 try { File file = new File("D:/TestData.xlsx"); 

 FileInputStream iFile = new FileInputStream(file);

 XSSFWorkbook wb = new XSSFWorkbook(iFile);      

XSSFSheet sheet = wb.getSheet("Sheet1"); 

int rowCount = sheet.getLastRowNum();

System.out.println("the no of rows are : " + rowCount);

for (int row=1; row<=rowCount; row++)
{ String location = sheet.getRow(row).getCell(0).getStringCellValue();

 String activity = sheet.getRow(row).getCell(1).getStringCellValue();

 int order=(int) sheet.getRow(row).getCell(2).getNumericCellValue();

 System.out.println(location + " , " + activity + " , " +order );
 }

 iFile.close();

 }

 catch (IOException e) { e.printStackTrace();
 } 

// Click on Search Button on YODA home page 

driver.findElement(By.className("MenuBarItemSubmenu")).click(); 

driver.findElement(By.xpath("//*[text()='Fulfillment Details']")).click();

driver.findElement(By.name("location")).sendKeys(location); 

driver.findElement(By.name("activity")).sendKeys(activity); 

driver.findElement(By.name("orderID")).sendKeys(order); 

driver.findElement(By.id("fulfillment_submit")).click(); } }

Solution 4:^[4]

// ReUsable Code for excel data

public class ExcelLibrary {

public String getExcelData(String sheetname, int rownum, int cellnum) {
    String retVal=null;
    try {
        FileInputStream fis=new FileInputStream("F:.........xlsx");
        Workbook wb=WorkbookFactory.create(fis);
        Sheet s=wb.getSheet(sheetname);
        Row r=s.getRow(rownum);
        Cell c=r.getCell(cellnum);
        retVal=c.getStringCellValue();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (EventException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return retVal;
}

public int getLastRowNumber(String sheetname) {
    int retVal=0;
    try {
        FileInputStream fis=new FileInputStream("F:\\selenium\\eclipse java\\excel.xlsx");
        Workbook wb=WorkbookFactory.create(fis);
        Sheet s=wb.getSheet(sheetname);
        retVal=s.getLastRowNum();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (EventException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return retVal;
}

}

// create a new class and call the previous method public class LoginLogout {

public static void main(String[] args) {
    ExcelLibrary xlib=new ExcelLibrary();
    System.setProperty("webdriver.gecko.driver","F:\\selenium\\WebDrivers\\geckodriver.exe");
    WebDriver driver=new FirefoxDriver();
    driver.manage().window().maximize();
    driver.get("https://demo.actitime.com/login.do");
    driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(30));
    int lastrow=xlib.getLastRowNumber("Sheet1");

    for (int i = 1; i <= lastrow; i++) {
        String un=xlib.getExcelData("Sheet1", i, 0);
        String pw=xlib.getExcelData("Sheet1", i, 1);
        driver.findElement(By.id("username")).sendKeys(un);
        driver.findElement(By.name("pwd")).sendKeys(pw);
        driver.findElement(By.id("loginButton")).click();
        driver.findElement(By.id("logoutLink")).click();
    }
}

}

Solution 5:^[5]

Here's one way, but you need to deal with web-only libraries lint. Also, this way you get a handle to control the window.

// ignore: avoid_web_libraries_in_flutter
import 'dart:html' as html;

...

html.WindowBase _popup = html.window
        .open(url, name, 'left=100,top=100,width=800,height=600');
if (_popup.closed!) {
      throw("Popups blocked");
    }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Community
Solution 2	Radhika Kanle
Solution 3	rlb.usa
Solution 4
Solution 5	Pavel

'read username and password from the excel file

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]