如何使用Excel工作表中的单元格值作为输入？

I need to write a code that will go through a sheet of an Excel file (.xlsx) and then use those values from the cell one by one via WebDriver.

我需要编写一个代码,它将遍历一张Excel文件(.xlsx),然后通过WebDriver逐个使用来自单元格的值。

To be more specific, one sheet holds search engines links and another sheet holds queries.

更具体地说,一张表保存搜索引擎链接,另一张表保存查询。

I only need you to help me figure out how to use the links from the Excel file, and not as hard coded values, a way to iterate through them.

我只需要你帮我弄清楚如何使用Excel文件中的链接,而不是硬编码值,这是一种迭代它们的方法。

This is what I have so far:

这是我到目前为止:

package a.utilities;

import java.awt.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

import a.utilities.ChromeDriverInit;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.*;
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.WebDriver;

public class ExcelUtils {
    static WebDriver driver;
    protected static XSSFSheet excelWSheet;
    protected static XSSFWorkbook excelWBook;
    private static XSSFCell cell;
    private static XSSFRow row;
    //
    static String web;
    static String query;

    // Setting the file to read from
    public static void setExcelFile() throws FileNotFoundException {
        FileInputStream file = null;

        try {
            file = new FileInputStream("ExcelWorkSheets/SearchEngines.xlsx");
            excelWBook = new XSSFWorkbook(file);

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

        } finally {
            if (file != null) {
                try {
                    file.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    // Counting the used rows in every work sheet and give you the work sheets data
    public static void getSheetData() {
        int index = 0;  
        for (int i = 0; i < excelWBook.getNumberOfSheets(); i++) {
            index++;
            System.out.println("Sheet Name: " + "["
                    + excelWBook.getSheetName(i) + "] --> " + "Sheet index: "
                    + "[" + index + "]\n");

        }
        int rowIndex = 0;
        for (int i = 0; i < excelWBook.getSheetAt(0).getLastRowNum()+1; i++) {
            excelWSheet = excelWBook.getSheetAt(0);
            rowIndex++;

        }
        System.out.println("Number of rows including the header: --> " + rowIndex);
        System.out.println("Number of rows not including the header: --> " +excelWSheet.getLastRowNum());
        System.out.println();


        int rowIndex2 = 0;
        for (int i = 0; i < excelWBook.getSheetAt(1).getLastRowNum()+1; i++) {
            excelWSheet = excelWBook.getSheetAt(1);
            rowIndex2++;

        }
        System.out.println("Number of rows including the header: --> " + rowIndex2);
        System.out.println("Number of rows not including the header: --> " +excelWSheet.getLastRowNum());
        System.out.println();


    // Going through the SearchEngines work sheet to get the data from every used cell and prints it out

        Iterator<Row> rowIterator = excelWSheet.iterator();
        while(rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cell.getNumericCellValue() + "\t\t");
                case Cell.CELL_TYPE_STRING:
                    web = cell.getStringCellValue();
                    System.out.println(web + "\t\t");

                default:
                    break;
                }
            }
            System.out.println("");
        }
        // Going through the queries work sheet to get the data from every used cell and prints it out
        Iterator<Row> rowIterator2 = excelWSheet.iterator();
            while(rowIterator2.hasNext()) {
                Row row = rowIterator2.next();

                Iterator<Cell> cellIterator = row.cellIterator();
                while(cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.println(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.println(cell.getStringCellValue() + "\t\t");
                        break;

                    default:
                        break;
                    }
                }
                System.out.println("");
            }
    }        

This does go through the list, but how do I pass it on so WebDriver would be able to use this values?

这确实是通过列表,但我如何传递它,因此WebDriver将能够使用此值?

3 个解决方案

#1

So try to write the program so that one value is read from the file. Store that value in a java variable and use this variable as required with webdriver. Then iterate over it as required.

因此,尝试编写程序,以便从文件中读取一个值。将该值存储在java变量中,并根据webdriver的需要使用此变量。然后根据需要迭代它。

I think the flow should be like this.

我认为流程应该是这样的。

• Read the xlsx file value with search engines (Iteration on Search Engines)

使用搜索引擎读取xlsx文件值(搜索引擎上的迭代)

• use get to open the url

使用get打开网址

• iterate over the queries

迭代查询

• Repeat for next search engins

重复下一次搜索引擎

#2

Lets say the values which you are fetching from an excel file are name and mail-id.

假设您从excel文件中获取的值是name和mail-id。

Store both the values in their Data-type variables.

将这两个值存储在其数据类型变量中。

Lets say name and mailid are the two required variables.

让我们说name和mailid是两个必需的变量。

Now create one method with the parameters created above to send it to web-page from Excel file.

现在使用上面创建的参数创建一个方法,将其从Excel文件发送到网页。

Below is just the sample implementation how you can do it.

以下是如何实现它的示例实现。

public static  void runTest(String name,String mailid) throws InterruptedException 
    {       
        System.out.println("Inputing name: "+name+" and mailid: "+mailid);

        driver.findElement(By.xpath("html/body/div[6]/div[1]/div/div[4]/form/table/tbody/tr[3]/td/table/tbody/tr[1]/td[2]/input")).clear();
        driver.findElement(By.xpath("html/body/div[6]/div[1]/div/div[4]/form/table/tbody/tr[3]/td/table/tbody/tr[2]/td[2]/input")).clear();

        driver.findElement(By.xpath("html/body/div[6]/div[1]/div/div[4]/form/table/tbody/tr[3]/td/table/tbody/tr[1]/td[2]/input")).sendKeys(name);
        driver.findElement(By.xpath("html/body/div[6]/div[1]/div/div[4]/form/table/tbody/tr[3]/td/table/tbody/tr[2]/td[2]/input")).sendKeys(mailid);

        System.out.println("Inputted name: "+name+" and mailid: "+mailid);

        Thread.sleep(2000); // Sleeping 2 seconds so that each entry is detected.

    }
}

#3

1. Save values in a datastructure.

将值保存在数据结构中。

2. Iterate through the values.

迭代价值观。

3. Create a helper function which will pass value one by one in to Web driver.

创建一个帮助函数,它将值逐个传递给Web驱动程序。

Pseudocode:-

    //Instead of printing the values 
    //Save all the site in 
    List<String> sites = new ArrayList<String>(0);
    //Save all the queries in
    List<String> queries = new ArrayList<String>(0);
    for(String site : sites){
    System.out.println("Time consumed:- " + runQueriesForSite(site, queries));
    }




     private int runQueriesForSite(site, queries){
        int searchTime = 0;
        for(String query : queries) {
          searchTime += runQueryForSite(query, site); //Webdriver will use this function to connect and return result
         }
      return searchTime;
    }


int runQueryForSite(query, site)
{
//Hope you'll find the time calculation algorithm yourself
WebDriver driver = new HtmlUnitDriver();

        // And now use this to visit Google
        driver.get(site);

        // Find the text input element by its name
        WebElement element = driver.findElement(By.name("q"));

        // Enter something to search for
        element.sendKeys(query);

        // Now submit the form. WebDriver will find the form for us from the element
        element.submit();

        // Check the title of the page
        System.out.println("Page title is: " + driver.getTitle());

        driver.quit();
}