使用Excel的TestNG数据提供程序

当我们要创建遵循数据驱动的方法并创建框架时，最好的方法是使用Excel存储我们的数据，并且可以使用在上一篇文章中看到的数据提供程序将数据作为参数传递。

逐步过程以使用TestNg数据提供程序实施Excel。

步骤1：使用TestNG Data Provider创建Login Application的测试用例。

第2步： 创建一个测试数据表。

步骤3：建立要开启的功能&从Excel读取数据

步骤4：创建一个TestNg测试用例，以使用数据提供者从Excel接受数据。

步骤5：针对测试数据文件中的测试用例名称运行测试。

使用Apache POI库从Excel工作表中读取数据-

下面是我们的代码，用于打开Excel工作表并在Selenium测试脚本中从中读取数据。为此，我们使用Apache POI库，该库允许我们使用Java读取，创建和编辑Microsoft Office文档。我们将用于从Excel工作表读取数据的类和方法位于org.apache.poi.hssf.usermodel中。

1.下载Apache POI  from the link http://poi.apache.org/ 并将其添加到您的项目构建路径中。

2.使用以下代码从Excel文件读取数据。

package test;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

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;

public class ExcelUtils {

 private static XSSFSheet ExcelWSheet;

 private static XSSFWorkbook ExcelWBook;

 private static XSSFCell Cell;

 private static XSSFRow Row;

 public static void setExcelFile(String Path,String SheetName) throws Exception {
   
     try {

    // Open the Excel file

    FileInputStream ExcelFile = new FileInputStream(Path);

    // Access the required test data sheet

    ExcelWBook = new XSSFWorkbook(ExcelFile);

    ExcelWSheet = ExcelWBook.getSheet(SheetName);

    } catch (Exception e){

     throw (e);

    }

  }
 public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception {

  String[][] tabArray = null;

  try {

   FileInputStream ExcelFile = new FileInputStream(FilePath);

   // Access the required test data sheet

   ExcelWBook = new XSSFWorkbook(ExcelFile);

   ExcelWSheet = ExcelWBook.getSheet(SheetName);

   int startRow = 1;

   int startCol = 1;

   int ci, cj;

   int totalRows = ExcelWSheet.getLastRowNum();

   // you can write a function as well to get Column count

   int totalCols = 2;

   tabArray = new String[totalRows][totalCols];

   ci = 0;

   for (int i = startRow; i <= totalRows; i++, ci++) {

    cj = 0;

    for (int j = startCol; j <= totalCols; j++, cj++) {

     tabArray[ci][cj] = getCellData(i, 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);
 
    int dataType = Cell.getCellType();
 
    if  (dataType == 3) {
 
     return "";
 
    }else{
 
     String CellData = Cell.getStringCellValue();
 
     return CellData;
    }
 
    }catch (Exception e){
 
    System.out.println(e.getMessage());
 
    throw (e);
 
    }
 
   
 
  }
 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);

   }

  }
 public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow)    throws Exception
  
 {   

    String[][] tabArray = null;

    try{

     FileInputStream ExcelFile = new FileInputStream(FilePath);

     // Access the required test data sheet

     ExcelWBook = new XSSFWorkbook(ExcelFile);

     ExcelWSheet = ExcelWBook.getSheet(SheetName);

     int startCol = 1;

     int ci=0,cj=0;

     int totalRows = 1;

     int totalCols = 1;

     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);

 }
    }

 3.使用要传递给测试用例的数据作为参数创建excelfile。

4.使用Excel中的参数更新测试用例。

package test;


import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
 
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
 
import org.testng.annotations.Test;
 
import org.testng.annotations.BeforeMethod;
 
import org.testng.annotations.AfterMethod;
 
import org.testng.annotations.DataProvider;
 

 
public class TestCaseExcelData {
 
 private String sTestCaseName;
 
 private int iTestCaseRow;
 
 WebDriver driver;
 
  @BeforeMethod
 
  public void beforeMethod() throws Exception {
 
   System.setProperty("webdriver.chrome.driver", "C:\\Softwares\\chromedriver_win32\\chromedriver.exe");
  
   driver=new ChromeDriver();
      
      driver.get("http://www.yubo666.com");
 
      Thread.sleep(5000);
  } 
 
  @Test(dataProvider = "search")
  
  public void 测试(String searchtext) throws Exception {
 
   
      driver.findElement(By.xpath("*//input[@class='search-field']")).sendKeys(searchtext);
      
      driver.findElement(By.xpath("*//input[@class='search-submit']")).click();
 
      
  }
 
  @AfterMethod
 
  public void afterMethod() {
 
    driver.close();
 
  }
 
  @DataProvider
 
  public Object[][] search() throws Exception{
 
     // Setting up the Test Data Excel file
 
   ExcelUtils.setExcelFile("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1");
 
   sTestCaseName = this.toString();
 
    // From above method we get long test case name including package and class name etc.
 
    // The below method will refine your test case name, exactly the name use have used
 
    sTestCaseName = ExcelUtils.getTestCaseName(this.toString());
 
     // Fetching the Test Case row number from the Test Data Sheet
 
     // Getting the Test Case name to get the TestCase row from the Test Data Excel sheet
 
   iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0);
 
     Object[][] testObjArray = ExcelUtils.getTableArray("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1",iTestCaseRow);
 
      return (测试ObjArray);
 
  }
 
}