Rectangle 27 0

Also, be careful about posting usernames and passwords in your scripts to stackoverflow ;)

How to read excel data in java, selenium testing? - Stack Overflow

java selenium junit selenium-webdriver
Rectangle 27 0

I think what you need is a Dataprovider that would read these 2 files and return an iterator combining the values from both the files.

@DataProvider
    public CSVIterator1 genericDataProvider(){

    File input1 = new File("c:\\file1.csv");
    File input2 = new File("c:\\file2.csv");

    return new CSVIterator1(input1,input2);


}




@Test(dataProvider ="genericDataProvider")
public void test1(String s1 , String s2, String i1, String i2){



}

And you implement Iterator interface for our need. This is a CSV iterator. you can implement an excel iterator. You just get the idea.

public class CSVIterator1 implements Iterator{

String line1;
String line2;
BufferedReader rdr1;
BufferedReader rdr2;

CSVIterator1(File input1 ,File input2){

    try {
         rdr1 = new BufferedReader(new FileReader(input1));
         rdr2 =new BufferedReader(new FileReader(input2));
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



}

@Override
public boolean hasNext() {

    try {
        if((line1 = rdr1.readLine()) != null && (line2 = rdr2.readLine()) != null){
            return true;
        }

        // TODO Auto-generated method stub

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

    return false;
}



@Override
public void remove() {
    // TODO Auto-generated method stub

}



@Override
public Object next() {

    if(line1 !=null && line2 !=null){

        return genParams(line1,line2);

    }

    // TODO Auto-generated method stub
    return null;
}



public Object[] genParams(String line1 , String line2){

    String combined = line1+","+line2;


    StringTokenizer st = new StringTokenizer(combined, ",");
    Object[] result = new Object[st.countTokens()];
    int i=0;
    while(st.hasMoreTokens()){

        result[i] = st.nextElement();
        i++;
    }

    return result;
}

How to Pass data from different excel sheet having different data into...

selenium selenium-webdriver testng
Rectangle 27 0

I will suggest, create a Map from xls files and depending upon input use this map to get the data using map key. It will help you a lot as only before suite you create a map and use this map anywhere in your script. If you do this then you don't have to @DataProvider anywhere in script.

Steps: Create a map from xls Read map values using key Pass as a testdata to testcase.

How to Pass data from different excel sheet having different data into...

selenium selenium-webdriver testng
Rectangle 27 0

I would prefer CSV files, which can parsed in JavaScript included in user-extensions.js. With an own defined command (in user-extensions.js too) I would expose/store the parsed CSV data as an object array to a variable and then using the FlowControl commands to iterate over this. Tricky but should work :-)

automation - How to design data driven test using Selenium IDE with ex...

automation automated-tests selenium-ide
Rectangle 27 0

I did this for read data from excel sheet and using that data register into website...

public void readExcel() throws IOException{
 WebDriver driver = null;
 driver = new FirefoxDriver();
String filePath = System.getProperty("user.dir")+"\\src\\testproject\\ExcelDemo.xlsx";
String fileName = "ExcelDemo.xlsx";
String sheetName = "Sheet1";

//Create an object of FileInputStream class to read excel file

FileInputStream inputStream = new FileInputStream(filePath);

Workbook workbook = null;

//Find the file extension by spliting file name in substring and getting only extension name

String fileExtensionName = fileName.substring(fileName.indexOf("."));

//Check condition if the file is xlsx file

if(fileExtensionName.equals(".xlsx")){

//If it is xlsx file then create object of XSSFWorkbook class

workbook = new XSSFWorkbook(inputStream);

}

//Check condition if the file is xls file

else if(fileExtensionName.equals(".xls")){

    //If it is xls file then create object of XSSFWorkbook class

    workbook = new HSSFWorkbook(inputStream);

}

//Read sheet inside the workbook by its name

Sheet guru99Sheet = workbook.getSheet(sheetName);

//Find number of rows in excel file

int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum();

//Create a loop over all the rows of excel file to read it
System.out.print(rowCount);

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

    Row row = guru99Sheet.getRow(i);
    System.out.print(row.getLastCellNum());

    //Create a loop to print cell values in a row

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if(j==0){
            driver.get("webpage name");
            try {
                Thread.sleep(2000);
            } catch (Exception e) {
                // TODO: handle exception
            }
        driver.findElement(By.name("username")).sendKeys(row.getCell(j).toString());
        }
        else{
        driver.findElement(By.name("password")).sendKeys(row.getCell(j).toString());
        }
        //Print excel data in console
        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
        System.out.print(row.getCell(j)+"|| ");
     }
     System.out.println();
 }
driver.findElement(By.xpath("/html/body/div[2]/form/div[2]/div[3]/div[2]/button")).click();
driver.close();
}

java - use excel data to register for a website in selenium - Stack Ov...

java excel insert selenium-webdriver webpage
Rectangle 27 0

You can achieve this by having glance at below framework. This depends on how your framework is designed. You can do parametrized testing. Also using GRID is good idea. You can use dataProvider from testNG and read data from Excel file and pass it to appropriate script.

grid - How to execute selenium scripts parallely in multiple test envi...

selenium grid webdriver testng
Rectangle 27 0

i have used following method to use input data from excel sheet: Need to import following as well

import jxl.Workbook;
Workbook wBook = Workbook.getWorkbook(new File("E:\\Testdata\\ShellData.xls"));
//get sheet
jxl.Sheet Sheet = wBook.getSheet(0); 
//Now in application i have given my Username and Password input in following way
driver.findElement(By.xpath("//input[@id='UserName']")).sendKeys(Sheet.getCell(0, i).getContents());
driver.findElement(By.xpath("//input[@id='Password']")).sendKeys(Sheet.getCell(1, i).getContents());
driver.findElement(By.xpath("//input[@name='Login']")).click();

make Sure Excel file should be is in 97-2000 format

java - How to read Data from Excel sheet in selenium webdriver - Stack...

java excel logging
Rectangle 27 0

Your problem is that log4j has not been initialized. It does not affect the outcome of you application in any way, so it's safe to ignore or just initialize Log4J, see: How to initialize log4j properly?

But it opens firefox only. Application is also not loading.I'm trying this first time.

java - How to read Data from Excel sheet in selenium webdriver - Stack...

java excel logging
Rectangle 27 0

With Excel, data entry is easy and accessible to non-technical testers. It has good sorting ability and the data can be organised in a pretty intuitive fashion. However, you cannot add or alter data during a test easily (I understand you could do this but I would this this is overboard). This means data must be previously organised and specific to the test requirements.

In a database data can come from queries and be altered on the fly. You could write helper functions that populate required data if it can't be found in the db. This means tests can run without worrying about what data is currently in the db (to an extent) and can alleviate some project issues. However, with a database there can be issues importing/exporting and there could be a lot of coding overhead. Also, obviously non-technical testers will find it hard to change the data.

As I said this is really an opinion in the end and is very much a project by project decision.

+1, a lot of comes down to how 'technical' the testers are. If they feel comfortable with a database and SQL, then I would choose the database since it gives you a lot more freedom and power with your tests.

Also adding one more point excel is pretty much limited to windows but with database you can use it on any platform. Also accessing database located on a server is easier than an excel located on a server.

automated tests - what to use excel or databse for data driven framewo...

selenium automated-tests selenium-webdriver
Rectangle 27 0

For reading and writing Excel I created an open source library called POIRE which includes many user-friendly methods.

It does Microsoft Ms-office(Excel,Ms Word,PowerPoint) file handling with Java using the POI classes. This API was developed on the top of the POI library. It has 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 a file would be faster.

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

How to read excel data in java, selenium testing? - Stack Overflow

java selenium junit selenium-webdriver
Rectangle 27 0

You can use @DataProvider annotation and define what sheet do you need for your tests.

You must create DataProvider class, name the data provider and use annotation @Test inside your test class.

@Test(testName = "test", dataProviderClass = ReturnExcelSheetData.class, dataProvider = "dataP") //dataProviderClass is optional

Maybe it's not direct answer to your question, but i strongly recommend using annotation. It gives you control, what test do you run, in what order, and you can adjust your @DataProvider to select 3 different excels or sheets for 3 different tests.

@Test
public class StackOverflow extends Ustawienia {
    Logowanie page; //new constructor for login page

  @Test(testName = "test", description = "", enabled = true, dataProviderClass = StackDataProvider, dataProvider = "excel")
  public void Testing(login, password) throws Exception {
      driver.get("some random page");
      page = new Logowanie(driver, writer);
      page.methodFromLogowanieClass();

TestNG uses ANNOTATIONs like @Test to read code. For example you can use @BeforeClass and @AfterClass to set what program have to do before running proper test, and what to do after this test (for example - clean data created)

@DataProvider
@DataProvider(name = "excel")
public static Object[][] returnExcelSheetData(Method method)
        throws BiffException, IOException 
        {
    String absolutePath = filePath.concat("/").concat(fileName);
    FileInputStream file = new FileInputStream(new File(absolutePath));
    Workbook workbook = Workbook.getWorkbook(file);
    Sheet worksheet = workbook.getSheet(sheetName);

    int ROWS = worksheet.getRows() - 1;
    int COLS = worksheet.getColumns();

    Object[][] dataset = new Object[ROWS][COLS];
    for (int rowCount = 0; rowCount < ROWS; rowCount++) {
        for (int colCount = 0; colCount < COLS; colCount++) {
            dataset[rowCount][colCount] = worksheet.getCell(colCount,
                    rowCount + 1).getContents();
        }
    }
    workbook.close();
    file.close();
    return dataset;

It iterates through excel sheet and returns data. If you have colums login and password as headers - it will work pretty fine. The next line (2) will be read from @DataProvider to @Test with specific @DataProvider.

This is my @DataProvider and i use also an interface to get data so it might not work for you. You can find plenty dataProvider examples which iterates through excel.

I am in need of that code which works as i explained above.. please help me if possible.

How to Read the data from 2 sheets of same Excel document using Seleni...

java selenium webdriver testng
Rectangle 27 0

package com.test.utitlity;

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;

public class readExcel extends globalVariables {

    /**
     * @param args
     * @throws IOException 
     */
    public static void readExcel(int rowcounter) throws IOException{

        XSSFWorkbook srcBook = new XSSFWorkbook("./prop.xlsx");     
        XSSFSheet sourceSheet = srcBook.getSheetAt(0);
        int rownum=rowcounter;
        XSSFRow sourceRow = sourceSheet.getRow(rownum);
        XSSFCell cell1=sourceRow.getCell(0);
        XSSFCell cell2=sourceRow.getCell(1);
        XSSFCell cell3=sourceRow.getCell(2);
        System.out.println(cell1);
        System.out.println(cell2);
        System.out.println(cell3);



}

}

java - How to read Data from Excel sheet in selenium webdriver - Stack...

java excel logging
Rectangle 27 0

Yes the above is possible. I still don't get it where is the problem statement. Did you try above? is it failing? if yes then what is the error? 1) Yes, You can have 2 @Test methods in one class using same data provider. 2) If in the same sheet if you are using same data for this tests then there is no problem at all. if there is different data for different test then in your data provider toggle between test data using method name. Let me know if you need further help.

I got this error.when im passing the parameter from the same excel sheet to both test in same class. Error:The data provider is trying to pass 4 parameters but the method com.TestCases_Sprint1.twoMethods#_Sprint1_updaterole takes 1 ...thanks @ Mrunal

It seems your TestUtil.getData is returning 4 column values which align to 4 arguments to your @Test annotated method. If you can post minimal working code which can re-create this issue, i can look into it.

how to read the data from excel sheet using two @Test in same class in...

selenium webdriver testng
Rectangle 27 0

You should first store your user-password combinations in a local object, like a Map. Then you need to provide accessors to that Map. It can be a simple get(), or something like get(int index), or however you would like to access the data. In your selenium testcases, you then have to call that accessor method and then use the returned values in your textfield write operation.

Depending on your reqirements, you can or should implement the data provider thread-safe or as singlton, or as simple object you instanciate. Whatever fits your needs best.

Edit: If you think about instanciating the object everytime you need it, think about performance for reading the excel sheet. I never did that before, so I don't know how performant it is. But you might want to convert the excelsheet into a simple CSV file before using it.

String name = sh.getCell(0,0); String pw = sh.getCell(0,1); driver.findElement(By.name("firstname").sendkeys(name); driver.findElement(By.name("password").sendkeys(pw);

So , say i have an excel sheet with 10 rows of data . I use "String name = sh.getCell(0,0); String pw = sh.getCell(0,1); driver.findElement(By.name("firstname").sendkeys(name); driver.findElement(By.name("password").sendkeys(pw);"within a loop ,will it do the same work for all the 10 rows?

A loop with exactly that code would be useless as the getCell(0,0) and getcell(0,1) will always fetch the first entry from the Excelsheet. Use the Loop-Index to go through the lines in the Excel: for(int index = 0; index < MAX_ITTERATIONS; ++index) and then sh.getCell(index,0); and sh.getCell(index,1);

I have to read the data from an excel file and click the corresponding radio button on a web page .. "String sex = sh.getCell(index,2).toString(); driver.findElement(By.name("sex")).click(sex);" i used this , is it right?

java - use excel data to register for a website in selenium - Stack Ov...

java excel insert selenium-webdriver webpage
Rectangle 27 0

There are many API's in java available to read data from Excel.

Save Excel as CSV files (in case you don't want to use any external API)and read them normally as a text file. But use Comma as Separator.

How to read excel data in java, selenium testing? - Stack Overflow

java selenium junit selenium-webdriver
Rectangle 27 0

You can add Apache POI jar files in your project for using Excel(XLSX) files. Also, attaching a sample code for your reference

void readXLSXFile(String fileName) throws IOException
    {
        FileInputStream fs= new FileInputStream(fileName);
        XSSFWorkbook wb = new XSSFWorkbook(fs);

        XSSFSheet ws = wb.getSheetAt(0);//you can change the value to index value of your required sheet
        Iterator<Row> rows = ws.rowIterator();

        XSSFRow row;            
        while(rows.hasNext())
        {
        row=(XSSFRow) rows.next();
        XSSFCell cell=row.getCell(7);//8th cell of the row
        String CName= cell.getStringCellValue(); //getting the cell value as string

        countryNames(CName);// Your method for passing the country name to the method for your requirement
        }

        wb.close();
    }

java - Selenium Webdriver to read data from Excel sheet - Stack Overfl...

java selenium selenium-webdriver
Rectangle 27 0

1.Read excel example


    InputStream stream = new FileInputStream(projectFile.getPath());
    ExcelWorkbookContainer container = new ExcelWorkbookContainer(stream);
    Iterator<ExcelRecord> recordIterator = container.getRecordIterator(0);
    //NO TITLE
    recordIterator.next();

    while(recordIterator.hasNext()) {
        ExcelRecord record = recordIterator.next();
         clusterObj.setBrand(record.getValue(ClusterColum.BRAND.getColumIndex()));
    }
    stream.close();

///// poi example

public class ExcelWorkbookContainer {

    private String filename;
    private XSSFWorkbook workbook;

    /**
     * @param filename
     * @throws FileNotFoundException, IOException
     */
    public ExcelWorkbookContainer(String filename)
            throws FileNotFoundException, IOException {
        this(new FileInputStream(filename));
        this.filename = filename;
    }

    /**
     * @param inputStream
     * @throws FileNotFoundException, IOException
     */
    public ExcelWorkbookContainer(InputStream inputStream)
            throws IOException {
        if (inputStream == null) {
            throw new NullPointerException("Input Stream is null");
        }

        workbook = new XSSFWorkbook(inputStream);
    }

    /**
     * Create a composite iterator on the excel records
     * 
     * @param sheetId the id of the sheet for data extraction
     * @return composite iterator on the excel records
     */
    public Iterator<ExcelRecord> getRecordIterator(int sheetId) {
        return new ExcelRecordIterator(workbook.getSheetAt(sheetId));
    }

    /**
     * Create a composite iterator on the excel records
     * 
     * @param sheetId the id of the sheet for data extraction
     * @return composite iterator on the excel records
     */
    public Iterator<ExcelRecord> getRecordIterator(int sheetId, String[] headers) {
        return new ExcelRecordIterator(workbook.getSheetAt(sheetId), headers);
    }

    public String getFilename() {
        return filename;
    }

    public XSSFWorkbook getWorkbook() {
        return workbook;
    }
}

How to read excel data in java, selenium testing? - Stack Overflow

java selenium junit selenium-webdriver
Rectangle 27 0

Don't know about what the error you are facing exactly. But log4j:WARN No appenders could be found for logger error, is due to the log4j jar file that you have included in your project.

Initializing log4j is needed but actually Log4j is not necessary for your project. So Right click on your Project Properties Java Build Path Libraries.. Search for log4j jar file and remove it.

java - How to read Data from Excel sheet in selenium webdriver - Stack...

java excel logging