使用Java和TestNG的Selenium -用Excel DataSheet测试登录

Question

创建2个类(DataProviderWithExcel、ExcelUtils)并导出登录详细信息(用户名、密码)。但是它在“Registration_data”类中跳过了"DataProviderWithExcel“。

DataProviderWithExcel类

package practiceTestCases;

import java.util.concurrent.TimeUnit;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
import org.testng.annotations.DataProvider;
import utility.ExcelUtils;

public class DataProviderWithExcel {

    WebDriver driver;
    private String sTestCaseName;
    private int iTestCaseRow;

    @BeforeClass
    public void beforeMethod() throws Exception {
    System.setProperty("webdriver.chrome.driver","E://chromedriver.exe");
        driver=new ChromeDriver();
        driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
        driver.get("http://www.store.demoqa.com");     
    }

@Test(dataProvider="Authentication")
public void Registration_data(String sUserName,String sPassword)throws  Exception{
    driver.findElement(By.xpath(".//*[@id='account']/a")).click();
   driver.findElement(By.id("log")).sendKeys(sUserName);
 System.out.println(sUserName);
 driver.findElement(By.id("pwd")).sendKeys(sPassword);
System.out.println(sPassword);
driver.findElement(By.id("login")).click();
System.out.println(" Login Successfully, now it is the time to Log Off buddy.");
 driver.findElement(By.xpath(".//*[@id='account_logout']/a")).click();
    }

@DataProvider()
public Object[][] Authentication() throws Exception{
     ExcelUtils.setExcelFile("E://My Work Place//Excel//src//testData//TestData.xlsx","Sheet1");
     sTestCaseName = this.toString();
      sTestCaseName = ExcelUtils.getTestCaseName(this.toString());
     iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0);
    Object[][] testObjArray = ExcelUtils.getTableArray("E://My Work Place//Excel//src//testData//TestData.xlsx//TestData.xlsx","Sheet1",iTestCaseRow);
       return (testObjArray);
    }

@AfterClass
public void afterMethod() {
      driver.close();
    }
}

ExcelUtils类

package utility;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

    private static XSSFSheet ExcelWSheet;
    private static XSSFWorkbook ExcelWBook;
    private static XSSFCell Cell;

public static void setExcelFile(String Path,String SheetName) throws Exception {
       try {
            FileInputStream ExcelFile = new FileInputStream(Path);
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
            } catch (Exception e){
                throw (e);
            }
    }

public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow)    throws Exception
{   
   String[][] tabArray = null;
   try{
       FileInputStream ExcelFile = new FileInputStream(FilePath);
       ExcelWBook = new XSSFWorkbook(ExcelFile);
       ExcelWSheet = ExcelWBook.getSheet(SheetName);
       int startCol = 1;
       int ci=0,cj=0;
       int totalRows = 1;
       int totalCols = 2;
       tabArray=new String[totalRows][totalCols];
          for (int j=startCol;j<=totalCols;j++, cj++)
           {
               tabArray[ci][cj]=getCellData(iTestCaseRow,j);
              System.out.println(tabArray[ci][cj]);
           }
    }

    catch (FileNotFoundException e)
    {
        System.out.println("Could not read the Excel sheet");
        e.printStackTrace();
    }

    catch (IOException e)
    {
        System.out.println("Could not read the Excel sheet");
        e.printStackTrace();
    }
    return(tabArray);
}

public static String getCellData(int RowNum, int ColNum) throws Exception{
   try{
      Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
      String CellData = Cell.getStringCellValue();
      return CellData;
      }catch (Exception e){
        return"";
        }
    }

public static String getTestCaseName(String sTestCase)throws Exception{
    String value = sTestCase;
    try{
        int posi = value.indexOf("@");
        value = value.substring(0, posi);
        posi = value.lastIndexOf(".");    
        value = value.substring(posi + 1);
        return value;
            }catch (Exception e){
        throw (e);
                }
    }

public static int getRowContains(String sTestCaseName, int colNum) throws Exception{
    int i;
    try {
        int rowCount = ExcelUtils.getRowUsed();
        for ( i=0 ; i<rowCount; i++){
            if  (ExcelUtils.getCellData(i,colNum).equalsIgnoreCase(sTestCaseName)){
                break;
            }
        }
        return i;
            }catch (Exception e){
        throw(e);
        }
    }

public static int getRowUsed() throws Exception {
        try{
            int RowCount = ExcelWSheet.getLastRowNum();
            return RowCount;
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw (e);
        }
    }
}

误差

SKIPPED: Registration_data
java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at org.testng.internal.MethodInvocationHelper.invokeDataProvider(MethodInvocationHelper.java:161)
    at org.testng.internal.Parameters.handleParameters(Parameters.java:429)
    at org.testng.internal.Invoker.handleParameters(Invoker.java:1383)

Answer 1

如果使用POM.xml，则缺少jar文件或其依赖项，然后将下面的依赖项添加到POM文件中

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
</dependency>

如果没有，则从Apache站点下载poi.jar v3.9和POI -ooxmlv3.9，并将其添加到项目的类路径中。