Selenium+excel implementation of parametric automation testing

Techniques used: POI parsing of Excel, selenium automated testing, JUnit

Test Case: Login www.1905.com Perform login-exit operation

To perform the steps:

1, first create an Excel, there is a user name and password column

2. Create a new Java class to parse Excel

 PackageCom.m1905.java;ImportJava.io.FileInputStream;Importjava.io.FileNotFoundException;Importjava.io.IOException;ImportJava.io.InputStream;Importjava.util.ArrayList;Importjava.util.List;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.poifs.filesystem.POIFSFileSystem;/*** Read the test data in Excel file, divide the data into two groups of user name and password **/ Public classexcelworkbook{String username;    String password; /*** Read the user list in Excel file, that is, the first column *@throwsIOException **/     PublicList<string> readusername (String filesrc)throwsioexception{List<String> userlist =NewArraylist<string>(); //reading Excel FilesInputStream is =NewFileInputStream (FILESRC); Poifsfilesystem FS=NewPoifsfilesystem (IS); Hssfworkbook WB=NewHssfworkbook (FS); Hssfsheet sheet= Wb.getsheetat (0); if(sheet==NULL) {System.out.println ("No data, please enter test data"); }        //gets the number of file rows        introws =Sheet.getlastrownum (); //gets the number of file columns        /*int cols = sheet.getrow (0). Getphysicalnumberofcells ();        Gets the first row of data, typically the first behavior property value, so here you can ignore String colValue1 = Sheet.getrow (0). toString (); String colValues2 = Sheet.getrow (1). toString ();*/        //Remove the first column of the user name and remove the title from the first row         for(intI =1;i<rows+1;i++) {username= Sheet.getrow (i). Getcell (0). toString ();            SYSTEM.OUT.PRINTLN (username);        Userlist.add (username);        } System.out.println (userlist); returnuserlist; }        /*** Get the second column, get the password list *@throwsIOException **/     PublicList<string> Readpassword (String filesrc)throwsioexception{List<String> passwordlist =NewArraylist<string>(); //reading Excel FilesInputStream is =NewFileInputStream (FILESRC); Poifsfilesystem FS=NewPoifsfilesystem (IS); Hssfworkbook WB=NewHssfworkbook (FS); Hssfsheet sheet= Wb.getsheetat (0); if(sheet==NULL) {System.out.println ("No data, please enter test data"); }        //Remove the password value from the second column, removing the title from the first row            intRows=Sheet.getlastrownum ();  for(inti=1;i<rows+1;i++) {Password= Sheet.getrow (i). Getcell (1). toString ();                SYSTEM.OUT.PRINTLN (password);            Passwordlist.add (password);        } System.out.println (Passwordlist); returnpasswordlist; }}

3. Create a JUnit test class to test the login process

 PackageCom.m1905.junit;Importjava.io.IOException;Importjava.util.List;ImportJava.util.concurrent.TimeUnit;ImportOrg.junit.AfterClass;ImportOrg.junit.BeforeClass;Importorg.junit.Test;Importorg.openqa.selenium.By;ImportOrg.openqa.selenium.WebDriver;Importorg.openqa.selenium.WebDriver.Navigation;Importorg.openqa.selenium.WebElement;ImportOrg.openqa.selenium.firefox.FirefoxDriver;Importorg.openqa.selenium.support.ui.ExpectedConditions;Importorg.openqa.selenium.support.ui.WebDriverWait;ImportCom.m1905.java.ExcelWorkBook; Public classLogintest {Private StaticWebdriver Driver; Private StaticNavigation Navigate; Private StaticString url= "Http://www.1905.com"; Private StaticString filesrc = "Userandpassword.xls"; @BeforeClass Public Static voidSetupbeforeclass ()throwsException {//Load BrowserDriver =NewFirefoxdriver (); Navigate=driver.navigate ();        Navigate.to (URL);    Driver.manage (). window (). Maximize (); } @AfterClass Public Static voidTeardownafterclass ()throwsException {if(driver!=NULL) {driver.close ();        Driver.quit (); }} @Test Public voidTest ()throwsIOException {//Initialize ExcelWorkbook ClassExcelWorkbook Excelbook =NewExcelWorkbook (); Driver.manage (). Timeouts (). Implicitlywait (10, Timeunit.seconds); //Take out the username and put it in the UserList collection .List<string> userlist =Excelbook.readusername (FILESRC); //Take out the password and put it in the Passwordlist collection .List<string> passwordlist =Excelbook.readpassword (FILESRC); //enter the data into the user name and password input box in the interface.        intUsersize =userlist.size ();  for(inti=0;i<usersize;i++) {driver.manage (). Timeouts (). Implicitlywait (30, Timeunit.seconds); //Click the login/Register buttonWebelement Logandreg = driver.findelement (By.xpath (".//*[@id = ' site_nav_md ']/ul/li[2]/a"));            Logandreg.click (); //use XPath to navigate to the username input boxWebelement username = driver.findelement (By.xpath (".//*[@id = ' inputusername ']")); //use XPath to navigate to the Password input boxwebelement password = driver.findelement (By.xpath (".//*[@id = ' Inputpassword ']")); //Navigate to the login button via XPathWebelement login = driver.findelement (By.xpath (".//*[@id = ' Loginreg ']/div/div[1]/form/p/button")); //clear the contents of the Username input boxusername.clear (); //take out the data in the Usernamelist and writeString name =Userlist.get (i);            Username.sendkeys (name); //enter the corresponding password value             for(intJ=0;j<passwordlist.size (); j + +) {password.clear (); String Pass=Passwordlist.get (j);            Password.sendkeys (pass); }            //Click to loginLogin.click (); //driver.manage (). Timeouts (). implicitlywait (timeunit.seconds);webdriverwait wait =NewWebdriverwait (driver,10); Webelement E1= Wait.until (Expectedconditions.elementtobeclickable (By.xpath (".//*[@id = ' site_nav_md ']/ul/li[3]/a[2]"))); //Find the Exit login buttonE1.click ();//webelement Logoutbutton = driver.findelement (By.xpath (""));//Logoutbutton.click ();        }    }}

Selenium+excel implementation of parametric automation testing