Selenium : Reading and writing to excel

Reading the data from Excel:

Though a lot of websites out there has the codes showing how to read from Excel, I’m also sharing the code snippet which i customized a bit for my requirement.

public String[][] excelDataReader (String fileLocation) throws IOException{

String[][] signInData = null;

//Create a object of File class to open xlsx file
File file = new File (fileLocation);

//Create an object of FileInputStream class to read excel file
FileInputStream f = new FileInputStream(file);

//Create XLSX workbook object
Workbook signInTestData = new XSSFWorkbook(f);

//Get Sheet
String sheetName = new String("Sheet1");
Sheet testDataSheet = signInTestData.getSheet(sheetName);

//Get RowCount
int rowCount = testDataSheet.getLastRowNum();
System.out.println(testDataSheet.getLastRowNum());

//Declare row variable of type Row Class
Row row = testDataSheet.getRow(0);

//Get COlumn Count
int colCount = row.getLastCellNum();
System.out.println(colCount);

//Declare and intialize the String object, that will store excel data
signInData = new String[rowCount][colCount];

System.out.println(testDataSheet.getRow(0).getCell(1).getStringCellValue());
//Read the excel and store the value in array
//Starting reading from 2nd row, as first row is header
for (int r = 1; r<=rowCount; r++){
row = testDataSheet.getRow(r);
testCaseNum = r;
for(int c = 0;c<row.getLastCellNum();c++){
signInData [r-1][c] = row.getCell(c).getStringCellValue();
}
}
signInTestData.close();
return signInData;
} 

 

Writing to Excel file by creating New file

 

Here i want to output the text value of a HTML object onto the excel sheet.

  1. The function expects test case name, file path and List object.
  2. Test case name is used to name the excel workbook and saved in the file path that is passed.
  3. List object contains list of webElements for which the text value is obtained and then written to excel.
public void createExcelOutputFile(String testName, String filePath, List <WebElement> element){

XSSFWorkbook newWorkBook = new XSSFWorkbook();
XSSFSheet newSheet = newWorkBook.createSheet(testName);

//start from 2nd Row
int rowNum = 1;

//For each webelement in the List, get the text value and store it in separate rows
for (WebElement eachElement: element){
Row row = newSheet.createRow(rowNum);
Cell cell = row.createCell(0);
cell.setCellValue(eachElement.getText());
rowNum++;
}

//Name the workbook with TestCase name,save and then close
try {
FileOutputStream out = new FileOutputStream(new File(filePath+testName+".xlsx"));
newWorkBook.write(out);
out.close();
Reporter.log("Excel written successfully..");

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

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

 

 

Negative Scenarios Testing using Selenium and Test NG

Any Functional test suite would involve a fair amount of Negative Test scenarios in it. Usually the expectation here is that whenever a user is doing something which is not supposed to do, he should be stopped and should be shown appropriate error message informing why he cannot proceed any further.

So, how do we accomplish such scenarios scripting in Selenium? I have tried to make use of Java’s Exception handing feature here.

UseCase:

  1. New User setup is in process
  2. In the screen where user phone number and Password has to be entered, we get a error message if we enter invalid details

For the purpose of scripting, i considered two scenarios here”

Scenario 1: User enters invalid detail for phone number

Script Alogrithm:

  1. Create a custom exception class called invalid phone number
  2. Data validation on the app happens when user clicks Next button on the screen
  3. hence, I’ve a separate method which just clicks on the Next button and then starts searching for Error message objects on the screen, one after another
  4. Finally, i start throwing exceptions
    1. Throw exception for each error message separately. For example – phone number exception. Throw this if only phone number is wrong
    2. Throw exception for multiple error messages in one shot. For example – if both phone number and password is invalid, throw one common exception for it.

As you can see, point#4 above has to be designed keeping in mind what types of negative scenarios you are automating. You may have create multiple exceptions depending on how many errors each screen may throw. And also you need to keep in mind the multiple error messages that appears on screen, when user inputs wrong data in more than one field on the screen, you need to create separate exception for each kind of combination. Also, you need modify your Test class accordingly, where you catch these exceptions

package testNGTests;

import java.io.File;
import java.lang.reflect.Method;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.Reporter;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import exceptionPackage.PasswordAndPhoneException;
import sam.pomEnrollSupportPersonDetails;
import sam.pomEnterSupportPersonDetails_Exception_SAM;
import sam.pomHomeSAM;
import sam.pomLoginSAM;
import sam.pomPersonnelSupportSAM;
import sam.pomPreviewSupportPersonSAM;
import utilityPackage.utilityClass;

public class CreateSAMPersonnelNegativeTest_InvalidPhonePassword {

WebDriver driver;
pomLoginSAM l;
pomHomeSAM h;
pomPersonnelSupportSAM p;
pomEnrollSupportPersonDetails enr;
pomEnterSupportPersonDetails_Exception_SAM ent;
pomPreviewSupportPersonSAM pr;
String userIDCreated;

int ChromeInvokeCount;
int IEInvokeCount;


@BeforeTest
public void setup(){

ChromeInvokeCount=0;
//Launch Chrome Browser and navigate to SAM
Reporter.log("Opened Chrome Browser");
File file = new File("C:/Selenium/JarFiles/chromedriver.exe");
System.setProperty("webdriver.chrome.driver", file.getAbsolutePath());

driver = new ChromeDriver();
driver.get("https://sunview-aitc.suntrust.com/s1gcb/logon/csr");

//Login to SAM here itself, so that login step doesnt get iterated
l= new pomLoginSAM(driver);
l.loginToSAM("chromeUser", "password2");


}


@Test (dataProvider = "dp")
public void chromeTest(String FN, String LN, String userID) throws Exception{
ChromeInvokeCount++;
Reporter.log("Chrome Test Iteration number "+ChromeInvokeCount);
CreatePersonnel (FN,LN,userID,"Chrome");
}


@DataProvider (name = "dp")
public String[][] dataFeeder(Method m) throws Exception{

String[][] TestData = null;
String filePathChrome = "C:\\Selenium\\TestData\\SAMCreatePersonnelChromePhnNumExcep.xlsx";


utilityClass u = new utilityClass();
TestData = u.excelDataReader(filePathChrome);

return TestData;
}

public void CreatePersonnel (String FN, String LN, String userID,String Browser) throws Exception {

//* Assign class variables to respective object instance

h= new pomHomeSAM(driver);
p = new pomPersonnelSupportSAM(driver);
enr = new pomEnrollSupportPersonDetails(driver);
ent = new pomEnterSupportPersonDetails_Exception_SAM(driver);
pr = new pomPreviewSupportPersonSAM(driver);


Reporter.log("Logged into SAM in "+Browser+" Browser");

//Click Personnel tab
h.navigateToPersonnelTab();

//Click on Enroll support person link
p.clickenrollSupportPersonLink();

//Enter the ID and Name of support personnel
enr.enrollSupportPerson(FN, LN, userID);

//Enter the details of support personnel. leave phn number and password blank
ent.enterSupportPersonDetails("", "");

/*Click Preview button. 2 Error messages are thrown at same time.
* One, the phone number
* Two, the blank password error
*/
try{

ent.clickPreviewButton();

}catch(PasswordAndPhoneException b){

ent.enterValidPhoneNumber("4444444444");

ent.enterValidPassword("password1");
}

//click preview again now
try{
ent.clickPreviewButton();
}catch(PasswordAndPhoneException k){
Reporter.log("PhoneNumber/password is not valid 2nd time too. Check Test case");
}

waitForSomeTime();

//Support Person details summary
pr.previewSupportPerson();

//Go to Home tab to accept Next TC
h.clickHomeTab();

Reporter.log("Created user "+userID+"in "+Browser+" Successfully");
}

public void waitForSomeTime(){
try {
Thread.sleep(8000);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

 

Inside the Function that clicks Next button

public void clickPreviewButton() throws BlankPhoneNumberException,BlankPasswordException,PasswordAndPhoneException {


previewBtn = driver.findElement(By.name("actionAddPreview"));
//Click preivew button
previewBtn.click();

/*code below will search for error messages now
* Note that, order in which you search error messages ,should be the order in
* which the error message will come on screen, when you violate all field-validation rules.
* If you follow the order, it helps during test case design.
*/


//Search for Phone number error message text
List <WebElement> phoneNumberErrorMSg = driver.findElements(By.xpath("//li[contains(text(), 'Customer Service Phone is required.')]"));

//search for blank password error text. (Not confirm password)
List <WebElement> passwordBlankErrorMSg = driver.findElements(By.xpath("//li[contains(text(), 'Password is required.')]"));



//if both Phone and password error message is there then, throw this exception
if (passwordBlankErrorMSg.size()>0 && phoneNumberErrorMSg.size()>0){
throw new PasswordAndPhoneException();
}

//If only Phone error message is present, throw this exception
if (phoneNumberErrorMSg.size()>0){

throw new BlankPhoneNumberException();
}

//If error message is present, throw exception
if (passwordBlankErrorMSg.size()>0){

throw new BlankPasswordException();
}

}

 

 

 

 

 

 

CrossBrowser Testing using Selenium-TestNG. Use common DataProvider method

There are lot of references out there in the Net which can explain how to perform the crossBrowser testing using Selenium and TestNG. So what i did here is extend the same concept so as to fit it into my data driven framework.

I tried the below two options:

Option1:

Initially, i had difficulty knowing how this parallel threads work. Hence i created two separate @Tests – 1 each for Chrome and IE. There were 2 separate DataProviders for these 2 @Tests. One DP was reading excel file of chrome test and other was reading excel file of IE Test.

This is what the tests class looked like


<code>public class createSAMPersonnel {

WebDriver driver;
String userIDCreated;


@BeforeTest
@Parameters("Browser")
public void setup(String BrowserName){

/*
* Create the instance of Login class here itself,
* as we need to login to 2 different browsers
* with different login id
*/


/* Run the Test in IE and Chrome
*/


if (BrowserName.equalsIgnoreCase("Chrome")){

//Chrome Browser setup and login

}else if (BrowserName.equalsIgnoreCase("IE")){

//Chrome Browser setup and login
Reporter.log("Logged in to SAM");
}else{
Reporter.log("Pass the right browser parameter");
}
}


@Test (dataProvider = "chromeDP")
public void chromeTest(String FN, String LN, String userID) throws Exception{
//Test steps
}

@Test(dataProvider = "IEDP")
public void IETest(String FN, String LN, String userID) throws Exception{
//Test steps
}


@DataProvider (name = "chromeDP")
public String[][] chromeDataFeeder() throws Exception{


return chromeData;

}

@DataProvider (name = "IEDP")
public String[][] IEDataFeeder() throws Exception{


return IEData;

}
</code>

 

 

XML File was as below:

Note that i had to exclude @Test of chrome in IETest and vice versa

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Default suite" parallel = "tests" thread-count = "2">
	<listeners>
	<listener class-name="org.uncommons.reportng.HTMLReporter"/>
	<listener class-name="org.uncommons.reportng.JUnitXMLReporter"/>
</listeners>
<test name="TestChrome">
<parameter name="Browser" value="chrome" />
<classes>
<class name="testNGTests.createSAMPersonnel">
<methods>
<include name="chromeTest" />
<exclude name="IETest" />
</methods>
</class>
</classes>
</test>
<test name="TestIE">
<parameter name="Browser" value="IE" />
<classes>
<class name="testNGTests.createSAMPersonnel">
<methods>
<include name="IETest" />
<exclude name="chromeTest" />
</methods>
</class>
</classes>
</test>
</suite> 

But there was redundancy introduced. Though both tests are doing same job in 2 different browsers, i was not liking the idea of have 2 @Test and 2 DP. Thus came up with another idea explained below.

Option 2:

I was able to discard the redundant code. How? Because i understood how the parallel thread works –

  1. After the @BeforeTest, TestNG will invoke the dataprovider first
  2. Lets say  Thread 1 is Chrome, All i have to do is make my DP method intelligent enough to know which <Test> is calling it. ITestContext of TestNG helped me to inject that intelligence into my DP method
  3. Do You need to build that? Not required from execution perspective. But if you writing logs into your output file and want to know which data is created in which browser etc, you may want to know the <Test> name within your @Test too.
    1. But there is a catch here. If you try to catch ITestContext in your @Test method, there is a problem. Your @Test thinks that the data for this parameter too is coming from DP. Hence your test may fail
    2. Hence i declared global string variable to catch the test name. I initialized it in DP. Since DP gets invoked before @Test, the string variable gets initialized and ready for use in @Test.
public class CreateSAMPersonnelSameDPandTest {

WebDriver driver;
String testName;

int ChromeInvokeCount;
int IEInvokeCount;


@BeforeTest
@Parameters("Browser")
public void setup(String BrowserName){

if (BrowserName.equalsIgnoreCase("Chrome")){

//Chrome Browser setup and login

}else if (BrowserName.equalsIgnoreCase("IE")){

//Chrome Browser setup and login
Reporter.log("Logged in to SAM");
}else{
Reporter.log("Pass the right browser parameter");
}


@Test (dataProvider = "dp")
public void createSAMPersonTest(String FN, String LN, String userID) throws Exception{
//Test Steps
}



@DataProvider (name = "dp")
public String[][] dataFeeder(ITestContext ctx) throws Exception{

testName = ctx.getCurrentXmlTest().getName();
Reporter.log("DataProvider Invoked for "+testName);

String[][] TestData = null;
String filePathChrome = "Chrome.xlsx";
String filePathIE = "IE.xlsx";

utilityClass u = new utilityClass();

/**When the call comes from chrome Test, pass the chrome excel
* Else if the call comes from IE Test, pass the IE excel sheet
*/
if(testName.equalsIgnoreCase("TestChrome")){
TestData = u.excelDataReader(filePathChrome);
}else{
TestData = u.excelDataReader(filePathIE);
}

return TestData;
}

public void CreatePersonnel (String FN, String LN, String userID) throws Exception {

if(testName.equalsIgnoreCase("TestChrome")){
ChromeInvokeCount++;
Reporter.log("Chrome Test Iteration number "+ChromeInvokeCount);
}else{
IEInvokeCount++;
Reporter.log("IE Test Iteration number "+IEInvokeCount);
}



Reporter.log("Logged into SAM in "+testName+" Browser");

Reporter.log("Created user "+userID+"in "+testName+" Successfully");
}



}

 

XML file is as below

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Default suite" parallel = "tests" thread-count = "2">
	<listeners>
	<listener class-name="org.uncommons.reportng.HTMLReporter"/>
	<listener class-name="org.uncommons.reportng.JUnitXMLReporter"/>
</listeners>
<test name="TestChrome">
<parameter name="Browser" value="chrome" />
<classes>
<class name="testNGTests.CreateSAMPersonnelSameDPandTest">
</class>
</classes>
</test>
<test name="TestIE">
<parameter name="Browser" value="IE" />
<classes>
<class name="testNGTests.CreateSAMPersonnelSameDPandTest">
</class>
</classes>
</test>
</suite>