Selenium webdriver – get WebTable elements

The below code snippet does following

1. access table
2. get each row
3. capture the text from each column of the row
4. put that in a hashmap
5. dump that hashmap in Array List
6. Print the values

<code><span class="pln">//Web table
resultsTable = driver.findElement(By.id("myTable")); 
List<WebElement>rows = resultsTable.findElements(By.tagName("tr"));
 //List resultData = new ArrayList<HashMap<String,String>>(); String[] colHeaders = {"First Name","Last Name","Work Item Number","Creation Date","Case Number","LAN ID","Work Item Status","Plan State","Work Type Classification","Control Number","EApp Number","Member ID"};
 for (int r = 0; r<rows.size(); r++){
 List <WebElement> cols = rows.get(r).findElements(By.tagName("td"));
 rowData = new HashMap<String, String>(); 
for (int c= 0; c<cols.size();c++){
 if(!cols.get(c).getText().isEmpty()){ 
rowData.put(colHeaders[c],cols.get(c).getText().trim());
 }else { 
rowData.put(colHeaders[c],"BLANK"); 
} 
} 
resultData.add(rowData);
 } 
//Print data
 for (int i = 0;i<resultData.size();i++){
 System.out.println(" "+resultData.get(i).get("First Name") + " "+resultData.get(i).get("Last Name") + " "+resultData.get(i).get("Work Item Number") + " "+resultData.get(i).get("Creation Date") + " "+resultData.get(i).get("Case Number") + " "+resultData.get(i).get("LAN ID") + " "+resultData.get(i).get("Work Item Status") + " "+resultData.get(i).get("Plan State") + " "+resultData.get(i).get("Work Type Classification") + " "+resultData.get(i).get("Control Number") + " "+resultData.get(i).get("EApp Number")+resultData.get(i).get("Member ID")); 
System.out.println("\n");
 }
</span></code>

 

Selenium Webdriver Automation: Dynamic input of Credentials during script run

Mostly, for automation test scripting, we make use of what is called Non-person ID credentials to Login into test applications.

But there could be some situations where in you are required to get the scripting completed and tested, with your own network id (org id) for various reasons (like the request is pending with ITSM team, credentials not working, locked out etc).

So what will you do in such situation? Certainly hard coding of username and password is not a solution as that will be against the security protocols of the company. But still you need to get the test scripting work going on to meet the deadline.

Here is one of the solution. At the start of the script, trigger a function that throws a pop up to accept the user name and password from the user. The strings accepted will be used to perform login. At the end of script run, the variable value will get destroyed.

Below function is written in Java using JOptionPane. I used this to perform login action once when the script is triggered:

 

public static void main(String args[]){
String uid = JOptionPane.showInputDialog("Please enter user ID:");
System.out.println("User id is: "+uid);
JPasswordField pwd2 = new JPasswordField(10);
JOptionPane.showConfirmDialog(null, pwd2,"Enter Password",JOptionPane.OK_CANCEL_OPTION);
System.out.println("Password is: "+new String(pwd2.getPassword()));

}

 

DataBase Testing using Selenium

Usual DB testing involves data entry through UI and querying the DB to check if the data got commited into it or not. The other scenario is where the data gets loaded in DB through some different process and needs to be tested.

In this post, i’m going to discuss about the later.

The Data is coming from a different system and persisted to the DB. This data has to be tested against a catalog – which is source of truth.

The solution I developed was using Java, Apache POI and VB Macros. Ofcourse, cannot rule out the key role played by MS Excel and its formula.

Step#1: Setup Steps

1. Using JDBC, connect to the db.
2. Initiate the Reporting mechanism – I used Extent Reports for Test results reporting. Look up in google for details on Extent Reports

 

public void setup() throws ClassNotFoundException, SQLException{ 
//To write the data to excel eu = new ExcelUtility();
 //connector for db2 database
 Class.forName("com.ibm.db2.jcc.DB2Driver");
 //db path 
String db4URL = "jdbc:db2://10.xxx.xxx.xxx:xxxxx/CHE";

String dbLocation = dbURL; 
//connector 
c = DriverManager.getConnection(dbLocation, id, password);
//TestReport timeStamp = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss").format(new Date());
extentReports=new ExtentReports("C:/SeleniumUtilities/TestReport/ValidationReport"+timeStamp+".html");
 }

 

 

 

Step#2: Getting the SQL results and loading it into Excel

1. SQL query will be run on the database and result is saved in string array
2. This data is then transferred onto Excel workbook(File#1) using Apache POI
3. A standalone Visual Basic Script (.vbs) is then triggered by Java.
4. This VB script will then trigger a Macro residing inside the Excel File#1.
5. This Macro code will extract data from the above File#1 workbook, transform it with some basic transformation rules and then finally load it on another excel workbook(File#2).
6. This file#2 excel workbook has the formula present in it.
7. Formulas in file#2 will compare the data (loaded into it… which is nothing but the data obtained from the DB) against the product catalog – the source of truth
8. Formulas will output “Pass” or “Fail” in the relevant columns in file#2

Why transfer the data from file#1 to 2? Why not have the formula in file#1 itself?

When we ask Java to write the data in an excel with the help of Apache POI, the formulas in the file will not run automatically. You need to use Evaluate function provided by POI to evaluate the formula in each and every cell. The performance gets impacted pretty badly. For just 1300 rows of data having some 20 columns, it took almost close to an hour to do formula evaluation!

Hence i decided to separate them out and have VB do the job of transferring the data from File#1 to 2. The formulas would auto-run without any issues in file#2. It took less than 10 secs in this case, for same volume of data!!

Why use use standalone VB Script ?

The job of the this script is just to fire the Macro inside the file#1.

This is simply because Java cannot fire the Macro residing inside a Excel directly by itself. Whereas Java can run the standalone VB script residing in file system.

Why use excel formula? Why not do the validation of results in Java Program?

While that would be the best option to do from performance perspective, it was not as simple as it sounds. Multiple columns and multiple tabs had the data in source document. Also, it was not just one column of data for which the testing was required, multiple columns of data in the database had to be tested and all of them had the source information spread across multiple columns and tabs in the source file.

 

public  void runPCVSQL() {
 try{ 
//Statement object 
Statement sqlStatement = c.createStatement(); 
//SQL query 
String query = "SELECT aasd";
 //Result set object - holds the query output
ResultSet rs = sqlStatement.executeQuery(query); 
//Get the row count 
while (rs.next()){
rowCount+=1;
} 
System.out.println("rowcount: "+rowCount);
 //pointer is at the last row of rs object. Cannot bring it back to row1. hence use another object. 
ResultSet rs2 = sqlStatement.executeQuery(query); 
 resultSetMatrix =new String [rowCount][6]; 
outputExcelPath = "C:\\SeleniumUtilities\\TestData\\DataMatch.xlsm"; 
excelSheetName = "PLAN_BENEFIT"; 
int r= 0; 
while (rs2.next()){
for(int c= 1;c<=5;c++){
 resultSetMatrix[r][c-1] = rs2.getString(c); 
}
 r=r+1; 
} 
System.out.println("resultSetmatrix size:"+resultSetMatrix.length); 
//output the data to excel 
eu.outputThePlanDataToExcelType2(outputExcelPath, resultSetMatrix, rowCount,5, excelSheetName);
 //run excel macro mr = new MacroRunner();
 mr.runExcelMacro("C:\\SeleniumUtilities\\TestData\\runMacro.vbs"); 
Thread.sleep(5000); 
}catch (Exception e){
 e.printStackTrace();
 }
 }

 

Macro Runner code:

public class MacroRunner {public class MacroRunner {
 public void runExcelMacro(String vbsFilePath){
 try {
 Runtime.getRuntime().exec( "wscript "+vbsFilePath ); 
catch( IOException e ) {
System.out.println(e);
System.exit(0); 
 }
 }
}

 

Visual Basic program code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\SeleniumUtilities\TestData\DataMatch.xlsm")

objExcel.Application.Visible = False
objExcel.Application.Run("copyPasteData")
objWorkbook.Save
objExcel.Application.Quit

WScript.Quit

 

Above program was written in a notepad and saved as .vbs extension file.

A simple macro called “CopyPasteData” was written in file#1. The above VB program, when triggered by Java, would trigger the macro in file#1 which will copy the data in it to file#2

Step#3: Result Reporting

HTML reports were created using Extent Reports jar files. Each row of data was considered as one test case