[java] How to read Data from Excel sheet in selenium webdriver

I'm trying to read username and password from the excel file, below is my code but it shows following error :

log4j:WARN No appenders could be found for logger (org.apache.http.impl.conn.tsccm.ThreadSafeClientConnManager). log4j:WARN Please initialize the log4j system properly.

Code:

public static void main(String[] args) throws BiffException, IOException {      
    Sheet s;
    WebDriver driver = new FirefoxDriver();
    FileInputStream fi = new FileInputStream("E:\\myExcelWorkBook.xls");
    Workbook W = Workbook.getWorkbook(fi);

    s = W.getSheet(0);

    for(int row = 0;row <= s.getRows();row++){

    String Username = s.getCell(0,row).getContents();
    System.out.println("Username" +Username);
    driver.get("AppURL");
    driver.findElement(By.id("txtUserName")).sendKeys(Username);

    String password= s.getCell(1, row).getContents();
    System.out.println("Password "+password);

    driver.findElement(By.id("txtPassword")).sendKeys(password);

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

Please help me out.

This question is related to java excel selenium logging jxl

The answer is


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.

Hope it will work fine now.


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?


import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

String FilePath = "/home/lahiru/Desktop/Sample.xls";
FileInputStream fs = new FileInputStream(FilePath);
Workbook wb = Workbook.getWorkbook(fs);

String <variable> = sh.getCell("A2").getContents();

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);



}

}

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

import jxl.Workbook;

then

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();

it will Work


Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to selenium

SessionNotCreatedException: Message: session not created: This version of ChromeDriver only supports Chrome version 81 session not created: This version of ChromeDriver only supports Chrome version 74 error with ChromeDriver Chrome using Selenium Selenium: WebDriverException:Chrome failed to start: crashed as google-chrome is no longer running so ChromeDriver is assuming that Chrome has crashed WebDriverException: unknown error: DevToolsActivePort file doesn't exist while trying to initiate Chrome Browser Class has been compiled by a more recent version of the Java Environment How to configure ChromeDriver to initiate Chrome browser in Headless mode through Selenium? How to make Firefox headless programmatically in Selenium with Python? element not interactable exception in selenium web automation Selenium Web Driver & Java. Element is not clickable at point (x, y). Other element would receive the click How do you fix the "element not interactable" exception?

Examples related to logging

How to redirect docker container logs to a single file? Console logging for react? Hide strange unwanted Xcode logs Where are logs located? Retrieve last 100 lines logs Spring Boot - How to log all requests and responses with exceptions in single place? How do I get logs from all pods of a Kubernetes replication controller? Where is the Docker daemon log? How to log SQL statements in Spring Boot? How to do logging in React Native?

Examples related to jxl

How to read Data from Excel sheet in selenium webdriver What is the better API to Reading Excel sheets in java - JXL or Apache POI