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

Question

我需要编写一段代码，通过一个Excel文件（.xlsx） 的工作表，然后通过WebDriver逐个使用这些值。

更具体地说，一张表拥有搜索引擎链接，另一张表持有查询。

我只需要你帮我弄清楚如何使用Excel文件 中的链接，而不是作为硬编码值的一种方式来遍历它们。

这是我到目前为止有：

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

这并浏览清单，但我怎么把它传递这样的webdriver将能够使用这个值？

Answer 1

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

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

• 阅读与搜索引擎（搜索引擎迭代）
• 使用去打开URL
• 迭代查询
• 重复用于下一个搜索ENGINS

Answer 2

的XLSX文件值可以说，你从Excel文件中获取的值是名称和邮件标识。

将这两个值都存储在它们的数据类型变量中。

可以说名称和mailid是两个必需的变量。

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

以下只是您可以实现的示例实现。

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. 

    } 
} 

Answer 3

1. 保存价值的数据结构。
2. 遍历值。
3. 创建一个帮助函数，它将逐个传递给Web驱动程序。

伪代码： -

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