How to fetch data as input from excel and use it as a input to login or some other page? Please help

[Hemapriya Rajendran]

Assume, I have the excel sheet which contains the input values for my application. While testing the application with selenium webdriver,
It has to fetch the data from excel (for eg: Username and password) and apply it in the login page of the application and send the result back whether it is pass or fail.
I am using TestNG with selenium webdriver.

Please help me I am new to using selenium.

This is my code for entering user name and password and click on login button


import java.net.MalformedURLException;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public class CoreMeasureSTK {
            WebDriver driver;
            WebElement userName;
            WebElement password;

            @BeforeTest
            public void start() throws MalformedURLException {
                        driver = new FirefoxDriver();
                        driver.manage().timeouts().implicitlyWait(10000, TimeUnit.SECONDS);
            }

  @Test
  public void Test() {
              driver.get("url");
                      
                          // Entering user name and password
                        userName = driver.findElement(By.id("Login1_UserName"));
                        userName.sendKeys("qczxn");
                        //String expectedName = userName.getAttribute("value");              
                        password = driver.findElement(By.id("Login1_Password"));
                        password.sendKeys("Change@123");
                        //String expectedPassword = password.getAttribute("value");                      
                        driver.findElement(By.id("Login1_LoginButton")).click();

[chaitanya]

Hi Hemapriya,

In This way you can fetch username and password from Excel and use it as per your tests...

import jxl.Sheet;

import jxl.Workbook;

public void read() throws IOException {

Workbook w;

File inputWorkbook = new File(inputFile);

try {

w = Workbook.getWorkbook(inputWorkbook);

// Get the first sheet in excel workbook

Sheet sheet = w.getSheet(0);

for (int j = 1; j < sheet.getRows(); j++) {

String username = sheet.getCell(0, j).getContents();

String password = sheet.getCell(1, j).getContents();

}

And Once the test is done.you can write the results to the excel sheet as below...

Pass your result as an argument to this method...

public void writeResult(String result) {

synchronized (result) {

try {

// use this in order to write output to text file

// File file =new File("result.txt");

// String outFileName = result + "\\" + (new

// java.sql.Date(today.getTime())) + ".xls";

String fileName = "result-"+version+".ods";

//File file = new File("result.xls");

File file = new File("/home/chaitanya/workspace/"+fileName);

BufferedWriter bufferWritter = new BufferedWriter(

new FileWriter(file.getName(), true));

bufferWritter.write(result+"\t");

bufferWritter.close();

} catch (IOException e) {

e.printStackTrace();

}

[NaveenKumar Reddy R]

HI Hema,

   Refer this http://functionaltestautomation.blogspot.in/2009/10/dataprovider-data-driven-testing-with.html.

--
You received this message because you are subscribed to the Google Groups "Selenium Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to selenium-user...@googlegroups.com.

To post to this group, send email to seleniu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/selenium-users/-/hYaC4KfeJu0J.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Hemapriya Rajendran]

Hi Chaitanya,

Thanks for your help.
I'll try this code and let you know the result.

Regards,
Hemapriya R

[Hemapriya Rajendran]

Hi, It is working fine. Thanks for Chaitanya and Navin inputs.

Regards,
Hemapriya R

[Karthick Nk]

Hi Hemapriya,

 

Can you send me the example of input getting from excel sheet. 

[Abhi Ram]

Hi All,

Use the below java .jar in your projects for reading and writing a data from excel

POIRE

Its a Microsoft Ms-office(Excel,Ms Word,PowerPoint) files handling with Java with the help of the POI classes. This API was develop on the top of the POI library. Its has so many methods for working with the Ms-office files(Excel,Ms Word,PowerPoint). These methods will work on the both the binary(.xls, .doc, .ppt) and XML(.xlsx, .docx, .pptx). with the help of the NPOIFSFileSystem & OPCPackage classes in the POI. with these classes the reading and writing into file would be faster. In this i included so many user friendly methods with the help of POI Library.

GitHub

https://github.com/ssirekumar/POIRE
https://github.com/ssirekumar/POIRE/releases

--

You received this message because you are subscribed to the Google Groups "Selenium Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to selenium-user...@googlegroups.com.
To post to this group, send email to seleniu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/selenium-users/4ad0e9ac-183f-41ae-8ee8-7d5296de4ae7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rajeev singh]

Hi SSire Kumar,

I am new to automation, and have downloaded both standalone and standalone doc and imported into my referenced library, now how can i use this to read a particular cell data of my excel sheet. my excel sheet is imported into eclipse and having under a folder in xlsx extension. i have to read data from row 2, column 3.

Regards,

Rajeev

[Jegadeesh mani]

Hi rajeev singh,

please go through the below link..

http://www.guru99.com/creating-keyword-hybrid-frameworks-with-selenium.html

Thanks,

Jegadeesh

To view this discussion on the web visit https://groups.google.com/d/msgid/selenium-users/69058b49-c1e3-499b-b14a-b60b0bb305dc%40googlegroups.com.

[chethan kumar]

hi Hemapriya Rajendran

please send full code ...login page code ..data takes from excel sheet ..

Thanks 

chethan

[Krishnan Mahadevan]

Chethan,

What exactly is your query ? Can you please add a bit more context ?

Thanks & Regards
Krishnan Mahadevan

"All the desirable things in life are either illegal, expensive, fattening or in love with someone else!"
My Scribblings @ http://wakened-cognition.blogspot.com/

My Technical Scribbings @ http://rationaleemotions.wordpress.com/

To view this discussion on the web visit https://groups.google.com/d/msgid/selenium-users/a4689718-6472-41cb-bc28-86ce66c09bf1%40googlegroups.com.

[purvang talatiya]

Hello Rajendran,

Hope you are doing well,

I want help from you for webdriver, I want a code for multiple email and password can be verified from excel file and give me as well in excel with status column that weather it is pass or fail.

[somasekhar patil]

Hi ,

I used this code but i m not getting hw to fetch data from Excel file,where exectly we have to make changes plz let me know 

1)I have changed URL and Excel path then what i have to change .

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.chrome.ChromeDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

public class yoda2 {

public static void main(String[] args) { 

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

System.setProperty("webdriver.chrome.driver","./drivers/chromedriver.exe");

WebDriver driver;

driver=new ChromeDriver();

driver.get("http://192.168.1.200:91/");

driver.manage().window().maximize();

// Enter YODA URL 

    

// 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("E:\\MFB_NEW_WS\\new3\\Soma\\Excelfile\\Login_ExcelSheet.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(); } }

Advance Thanks 

Somu

On Monday, January 28, 2013 at 1:13:37 PM UTC+5:30, Hemapriya Rajendran wrote:

[somasekhar patil]

Hi 

Please help me out how to fetch the data from excel for login .

Thanks and regards

Somu

Somasekhar Patil

--

You received this message because you are subscribed to the Google Groups "Selenium Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to selenium-users+unsubscribe@googlegroups.com.
To post to this group, send email to selenium-users@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/selenium-users/d1a744a9-a16d-4181-9213-b3da30d0fc30%40googlegroups.com.

[Janaka Ramesh]

hi ji

if in excel sheet there are some 100-200 records how all these datas can be fetch automatically from the excel . can u please help me with the coding using selenium webdriver

i am new to this plz help me frnds.