Validate tabular data against Excel Data

Map is a collection useful to store the things with a unique ID.Classes that implements Map are
HashTable,LinkedHashMap,HashMap,HashTable.
The HashMap is unsorted and unordered Map and useful to store the null key as well.In this post we will focus on using HashMap for storing the values from the Excel and storing the values in another HashMap by reading the values using the automation tools like Selenium WebDriver. Comparing the two HashMap and display the results.

Real Time Example Scenario
Validate the stock prices displayed in the Rediff.com website
1. Read the data in the XLS file.
2. Store the data in HashMap1.
3. Read the data using Selenium Webdriver.
4. Store the data in HashMap2.
5. Compare the values stored in the two HashMaps.
Store the data in the XLS in rows and columns as mentioned below:
HDFC Bank
1,031.35
SBI
184.45
Create a Maven Project in Eclipse
Add the following dependencies in the pom.xml

<dependency>
		<groupid>net.sourceforge.jexcelapi</groupid>
		<artifactid>jxl</artifactid>
		<version>2.6</version>
</dependency>
<dependency>
		<groupid>org.seleniumhq.selenium</groupid>
		<artifactid>selenium-java</artifactid>
		<version>3.141.59</version>
</dependency>
<dependency>
		<groupid>io.github.bonigarcia</groupid>
		<artifactid>webdrivermanager</artifactid>
		<version>3.6.2</version>
</dependency>

<groupid>net.sourceforge.jexcelapi</groupid>

</dependency>

<groupid>org.seleniumhq.selenium</groupid>

<artifactid>selenium-java</artifactid>

</dependency>

<groupid>io.github.bonigarcia</groupid>

<artifactid>webdrivermanager</artifactid>

</dependency>

package org.totalqa.seleniumexample;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import io.github.bonigarcia.wdm.WebDriverManager;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
 
public class  HashMapSeleniumExample 
{
	public static HashMap<string,string> sharePriceExcelValuesfromXLS;
	public static HashMap<string,string> sharePriceExcelValuesfromWEB;

	public static void main(String args[]) throws BiffException, IOException
	{
		 
		sharePriceExcelValuesfromXLS = readXLSFile("SharePriceList.xls");
		sharePriceExcelValuesfromWEB = readDatafromWEB();
		 
//		  HashMap1 : Storing the values from WebPage
//		  HashMap2 : Storing the value from Excel
//		  Compare the values to validate the data
		  
		String expected = sharePriceExcelValuesfromXLS.get("Quess Corp");
		String actual   = sharePriceExcelValuesfromWEB.get("Quess Corp");

		if(actual.equals(expected))
		{
			System.out.println("TC Pass");
		}
		else
		{
			System.out.println("TC Fails");
		}

		Set<string> keyList = sharePriceExcelValuesfromXLS.keySet();
		for(String key : keyList)
		{
			System.out.println(key);
			if(sharePriceExcelValuesfromXLS.get(key).equals(sharePriceExcelValuesfromWEB.get(key)))
				System.out.println("Pass for the " + key);

			else
				System.out.println("Fail for the " + key);

		}
	}
	public static HashMap<string,string> readDatafromWEB()
	{
		WebDriverManager.chromedriver().setup();
		WebDriver driver = new ChromeDriver();
		driver.get("https://money.rediff.com/losers/bse/daily/groupa?src=gain_lose");
		List<webelement> currentPriceList = driver.findElements(By.xpath("//table[@class='dataTable']/tbody/tr/td[4]"));
		List<webelement> companyList = driver.findElements(By.xpath("//table[@class='dataTable']/tbody/tr/td[1]"));
		System.out.println("Size of the List ::" +currentPriceList.size() );
		HashMap<string,string> sharePriceExcelValuesfromWEB = new HashMap<string,string>();
		for(int i = 0 ;i <7;i++)
		{
			System.out.println("Key ::" +companyList.get(i).getText().trim()  +"Value ::"+currentPriceList.get(i).getText().trim() );
			sharePriceExcelValuesfromWEB.put(companyList.get(i).getText().trim(), currentPriceList.get(i).getText().trim());
		}
		return sharePriceExcelValuesfromWEB;

	}
	public static HashMap<string,string> readXLSFile(String fileName) throws IOException, BiffException
	{
		//Reading the data from XLS
		FileInputStream fis = new FileInputStream(new File(fileName));
		Workbook wb = Workbook.getWorkbook(fis);
		Sheet sheet = wb.getSheet(0);
		int rows = sheet.getRows();
		HashMap<string,string> sharePriceExcelValuesfromXLS = new HashMap<string,string>();
		for(int i = 0;i<rows;i=i+1) 
               { 
                      	Cell cell = sheet.getCell(0,i);
			String key = cell.getContents();

			cell  = sheet.getCell(1,i);
			String value= cell.getContents();

			System.out.print("Key  " + key);
			System.out.print("Value " + value);
			sharePriceExcelValuesfromXLS.put(key,value);
			System.out.println();
		}
		return sharePriceExcelValuesfromXLS;
	}

}
                                       
                             
 </string,string></string,string></string,string></string,string></string,string></webelement></webelement></string,string></string></string,string></string,string>

100

101

102

103

package org.totalqa.seleniumexample;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.HashMap;

import java.util.List;

import java.util.Set;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import io.github.bonigarcia.wdm.WebDriverManager;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

public class HashMapSeleniumExample

{

public static HashMap<string,string> sharePriceExcelValuesfromXLS;

public static HashMap<string,string> sharePriceExcelValuesfromWEB;

public static void main(String args[]) throws BiffException, IOException

{

sharePriceExcelValuesfromXLS = readXLSFile("SharePriceList.xls");

sharePriceExcelValuesfromWEB = readDatafromWEB();

// HashMap1 : Storing the values from WebPage

// HashMap2 : Storing the value from Excel

// Compare the values to validate the data

String expected = sharePriceExcelValuesfromXLS.get("Quess Corp");

String actual = sharePriceExcelValuesfromWEB.get("Quess Corp");

if(actual.equals(expected))

{

System.out.println("TC Pass");

}

else

{

System.out.println("TC Fails");

}

Set<string> keyList = sharePriceExcelValuesfromXLS.keySet();

for(String key : keyList)

{

System.out.println(key);

if(sharePriceExcelValuesfromXLS.get(key).equals(sharePriceExcelValuesfromWEB.get(key)))

System.out.println("Pass for the " + key);

else

System.out.println("Fail for the " + key);

}

public static HashMap<string,string> readDatafromWEB()

{

WebDriverManager.chromedriver().setup();

WebDriver driver = new ChromeDriver();

driver.get("https://money.rediff.com/losers/bse/daily/groupa?src=gain_lose");

List<webelement> currentPriceList = driver.findElements(By.xpath("//table[@class='dataTable']/tbody/tr/td[4]"));

List<webelement> companyList = driver.findElements(By.xpath("//table[@class='dataTable']/tbody/tr/td[1]"));

System.out.println("Size of the List ::" +currentPriceList.size() );

HashMap<string,string> sharePriceExcelValuesfromWEB = new HashMap<string,string>();

for(int i = 0 ;i <7;i++)

{

System.out.println("Key ::" +companyList.get(i).getText().trim() +"Value ::"+currentPriceList.get(i).getText().trim() );

sharePriceExcelValuesfromWEB.put(companyList.get(i).getText().trim(), currentPriceList.get(i).getText().trim());

}

return sharePriceExcelValuesfromWEB;

}

public static HashMap<string,string> readXLSFile(String fileName) throws IOException, BiffException

{

//Reading the data from XLS

FileInputStream fis = new FileInputStream(new File(fileName));

Workbook wb = Workbook.getWorkbook(fis);

Sheet sheet = wb.getSheet(0);

int rows = sheet.getRows();

HashMap<string,string> sharePriceExcelValuesfromXLS = new HashMap<string,string>();

for(int i = 0;i<rows;i=i+1)

{

Cell cell = sheet.getCell(0,i);

String key = cell.getContents();

cell = sheet.getCell(1,i);

String value= cell.getContents();

System.out.print("Key " + key);

System.out.print("Value " + value);

sharePriceExcelValuesfromXLS.put(key,value);

System.out.println();

}

return sharePriceExcelValuesfromXLS;

}

</string,string></string,string></string,string></string,string></string,string></webelement></webelement></string,string></string></string,string></string,string>

Expected Result:
Comparison of HashMap is successful.

Total-QA

Automation Tools

Validate tabular data against Excel Data

Leave a Reply Cancel reply