CODEWITHSUNDEEP
javaexcelselenium-webdriverapache-poi
user3286782
user3286782

Reputation: 13

Populating Excel file with poi and Loop

I am new to DDT and I am trying to write my test results in an excel file.

It is a simple code which takes values from a ListArray and sends them to google.translate, it then capture the results and add them to another List. When I loop through the list containing the results it only displays the last result captured, what am I doing wrong with my code? I understand I am wrongly looping through but I just cant figured it out

Any help will be much appreciated. Here is the entire Code

public class writeFileOutPut {
WebDriver driver;
WebElement element;
String baseUrl = "http://translate.google.com/";

@BeforeSuite
public void beforeSuite() {
    driver = new FirefoxDriver();
    driver.get(baseUrl);
}

@Test
public void f() throws Exception{

    List<String> sourceWords = new ArrayList<String>();

    sourceWords.add("house");
    sourceWords.add("car");
    sourceWords.add("bed");

    int listLength = sourceWords.size();
    System.out.println("Array length is " + sourceWords.size());

    for(String temp : sourceWords){
        driver.findElement(By.id("gt-tl-gms")).click();
        driver.findElement(By.xpath("/html/body/div[12]/table/tbody/tr/td[4]/div/div[7]/div")).click();
        driver.findElement(By.id("source")).sendKeys(temp);
        Thread.sleep(1000);
        String transRes = driver.findElement(By.id("result_box")).getText();
        List<String> transWords = new ArrayList<String>();
        transWords.add(transRes);
        System.out.println(transWords);

        driver.findElement(By.id("source")).clear();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("POI WorkSheet");

        for(int i=0; i<listLength; i++){
            HSSFRow row = worksheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(transRes);

        }

        FileOutputStream fos = new FileOutputStream("My/writeDataTest3.xls");
        workbook.write(fos);
        fos.close();

    }

}

@AfterSuite
public void afterSuite() {
    driver.quit();
}

}

Upvotes: 1

Views: 2763

Answers (2)

StrikerVillain
StrikerVillain

Reputation: 3776

As per my understanding, you are trying to store the result(translated words) to the list, "transWords" and when you iterate through this list you find only the last search.

This is because you have placed the list initialization inside the for loop.

List<String> transWords = new ArrayList<String>();

Place this code before the for loop and your list will contain all the results.

I also suggest you write the results to the excel sheet after completing the search ie. outside the for loop (since you are saving the results in a transWords list and this can be stored to the excel sheet in one go).

So you @Test can be written as below :-

@Test
public void f() throws Exception{
    //create source words list
    List<String> sourceWords = new ArrayList<String>();
    sourceWords.add("house");
    sourceWords.add("car");
    sourceWords.add("bed");
    System.out.println("Array length is " + sourceWords.size());

    List<String> transWords = new ArrayList<String>();

    //Iterate through the sourceWords list, search for each word and add result to transWords.
    for(String temp : sourceWords){
        driver.findElement(By.id("gt-tl-gms")).click();
        driver.findElement(By.xpath("/html/body/div[12]/table/tbody/tr/td[4]/div/div[7]/div")).click();
        driver.findElement(By.id("source")).sendKeys(temp);
        Thread.sleep(1000);
        String transRes = driver.findElement(By.id("result_box")).getText();

        transWords.add(transRes);

        driver.findElement(By.id("source")).clear();
    }

    //create workbook
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("new sheet");

    //add all the results
    int i=0;
    for (String transWord : transWords)
        Row row = sheet.createRow((short)i++);
        Cell cell = row.createCell(0);
        cell.setCellValue(transword);
    }

    //write to file
    FileOutputStream fileOut = new FileOutputStream("My/writeDataTest3.xls");
    workbook.write(fileOut);
    fileOut.close();
}

Let me know if this works for you.

Upvotes: 1

barak manos
barak manos

Reputation: 30146

Move the following initializations outside (i.e., before) the for (String temp : sourceWords) loop:

  1. List<String> transWords = new ArrayList<String>();

  2. HSSFWorkbook workbook = new HSSFWorkbook();

  3. HSSFSheet worksheet = workbook.createSheet("POI WorkSheet");

BTW, I'm not familiar with these HSSF classes, but I doubt that what you're doing inside the inner loop for (int i=0; i<listLength; i++) will have any effect on your worksheet...

Upvotes: 1

Related Questions