Java Selenium Using Apache POI - Reading an Excel File Pulling Both Rows Together

Question

I have used the following code (Reference link) to read data from an Excel file and using the data in two cells to search the Google site. However, when the program is run, data from two rows are placed in the searchbox together, not one after another in separate iteration as I'm expecting it by using the For loop. Looking forward to help. Here is the code:

package readdatafile;

import java.io.*;
import java.util.concurrent.TimeUnit;

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.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
// import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDrivenUsingExcelFile {

    String reafilefrom = "I:\\2000 QA\\Testing    Tools\\Selenium\\Examples\\TestData\\testdata.xlsx";
    public static void main(String[] args) throws IOException {

        DataDrivenUsingExcelFile obj1 = new DataDrivenUsingExcelFile();

        System.setProperty("webdriver.chrome.driver",  "C://ChromeDriverForSelenium/chromedriver.exe");
        WebDriver driver = new ChromeDriver();

        driver.get("https://www.google.com");

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

        WebElement searchBox = driver.findElement(By.name("q"));

        try{

        FileInputStream file = new FileInputStream(new File(obj1.reafilefrom));
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(0);

        for (int i=1; i<= sheet.getLastRowNum(); i++){

            String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

            searchBox.sendKeys(keyword);

            searchBox.submit();

            driver.manage().timeouts().implicitlyWait(8, TimeUnit.SECONDS);
        }

            workbook.close();
            file.close();
        } catch (FileNotFoundException fnfe){
            fnfe.printStackTrace();
        }

        // Waiting a little bit 
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {

            e.printStackTrace();
        }           
        driver.close();
        driver.quit();
    }

}

Test data screenshot is below:

Thank you.

Sudharsan Selvaraj · Accepted Answer · 2016-11-09 21:25:33Z

3

For every iteration in the for loop, you need to clear the searchtext input field before entering the new search string. Try the below code

for (int i=1; i<= sheet.getLastRowNum(); i++){
        String keyword = sheet.getRow(i).getCell(0).getStringCellValue();
        searchBox.clear();
        searchBox.sendKeys(keyword);
        searchBox.submit();
        driver.manage().timeouts().implicitlyWait(8, TimeUnit.SECONDS);
    }

answered Nov 9, 2016 at 21:25

Sudharsan Selvaraj

4,8323 gold badges16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sohel Over a year ago

Thanks Sudharsan, search items are separated now. I also placed a sleep function in between iteration after the searchbox.Submit to wait bit longer after the first iteration. I will place the updated code in next comment.

Sohel Over a year ago

Here is the code: for (int i=1; i<= sheet.getLastRowNum(); i++){ // WebElement searchBox = driver.findElement(By.name("q")); String keyword = sheet.getRow(i).getCell(0).getStringCellValue(); searchBox.clear(); searchBox.sendKeys(keyword); searchBox.submit(); try { Thread.sleep(5000); } catch (InterruptedException e) { e.printStackTrace(); } driver.manage().timeouts().implicitlyWait(8, TimeUnit.SECONDS); }

Collectives™ on Stack Overflow

Java Selenium Using Apache POI - Reading an Excel File Pulling Both Rows Together

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related