WebDriver

Write data in .xls and .xlsx (Excel) file using POI

Posted on

As we know that file is very most important in our day to day life, we frequently use to create a file, open it and update or delete something from file. Same thing in selenium automation as well we need to manipulate the file.

So, In this blog we will learn how to install the poi jar file, what jar file are required for that and how to perform the read operation on excel with the help of JAVA IO package and APACHE POI library.

POI library is enough to read write both XLS and XLSX file.

Note : For .XLS file read write, we can use .JXL jar file but it won’t support to .xlsx file.

 

Let’s begin the journey: – 

Step 1. If you haven’t eclipse then Install the Eclipse latest version.

Step 2. If you haven’t added WebDriver then download the webdriver jar file and add to library.

Step 3. If you are using Maven then use the following dependencies.

                           <!– https://mvnrepository.com/artifact/org.apache.poi/poi –>

                                                        <dependency>

                                                                <groupId>org.apache.poi</groupId>

                                                                <artifactId>poi</artifactId>

                                                                <version>4.0.1</version>

                                                         </dependency>

Or else you can directly download latest POI jar file from https://poi.apache.org/download.html

Step 4. Once the downloaded jar file then unzip the file and add library file into your project.

Right click on your project -> Build Path -> Configure Build Path-> Library -> Add External Jar-> ok

Note: Below are java interfaces and classes that we will use for read/write xls and xlsx file in POI

XSSFWorkbook : – Is a class representation of XLSX file.

HSSFWrokbook  : – Is a class represent of XLS file.

XSSFSheet :- XSSFSheet classes implement this interface represent XLSX file’s sheet.

HSSFSheet : – HSSFSheet classes implement this interface XLS file’s sheet.

XSSFRow :- XSSFSheet classes implement this interface represent to a row of XLSX file.

HSSFRow : – HSSFSheet classes implement this interface represent to a row of XLS file.

XSSFCell :- XSSFSheet classes implement this interface represent a cell in a row of XLSX file.

HSSFCell : – HSSFSheet classes implement this interface represent a cell in a row of XLS file.

 

Write Data in Excel sheet

package Account;

import java.io.File;

import java.io.FileInputStream;

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 WriteInExcel {

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

 

String ctVal= “Status”;

 

// File Location

File src = new File(“D://Dazeworks//Selenium Projects//Credential.xlsx”);

// Open File using FileInputStream class

FileInputStream fis = new FileInputStream(src);

 

// Create Workbook instance holding reference to .xlsx file

XSSFWorkbook workbook = new XSSFWorkbook(fis);

 

// Get first/desired sheet from the workbook

XSSFSheet sheet = workbook.getSheetAt(0);

// Entering a value in Row[0] and Cell[4]. Here cell is nothing but Column number

sheet.getRow(0).createCell(4).setCellValue(ctVal);

 

// If user want to enter value as per choice row then Needs to create row where he wants to get enter the value

XSSFRow r = sheet.createRow(14);

//Create a cell for the value where you want to enter.

XSSFCell c = r.createCell(10);

//Entering the Value for particular cell

c.setCellValue(“How’s Josh????”);

//Using FileOutputStream we will get in which file we have to write

FileOutputStream fos = new FileOutputStream(src);

// Writing in the values in workbook

workbook.write(fos);

//File close

fos.close();

 

System.out.println(“Value inserted successfully”);

}

 

}

 

Advertisements

Common problems in the software development process?

Posted on

There are 5 common problems in software development process

Poor Requirement: –   Unclear details, Incomplete requirement, and too general.

Inadequate testing: – No one knows whether your program is any good till the customers complains or system crashes.

Features: – A request to pile on new features after development is underway, Actually this extremely common.

Miscommunication: – If developers don’t know what’s needed or customers have erroneous expectations problem are guaranteed

Read Excel data from .xls and .xlsx file type using POI

Posted on Updated on

As we know that file is very most important in our day to day life, we frequently use to create a file, open it and update or delete something from file. Same thing in selenium automation as well we need to manipulate the file.

So, In this blog we will learn how to install the poi jar file, what jar file are required for that and how to perform the read operation on excel with the help of JAVA IO package and APACHE POI library.

POI library is enough to read write both XLS and XLSX file.

Note : For .XLS file read write, we can use .JXL jar file but it won’t support to .xlsx file.

Let’s begin the journey: – 

Step 1. If you haven’t eclipse then Install the Eclipse latest version.

Step 2. If you haven’t added WebDriver then download the webdriver jar file and add to library.

Step 3. If you are using Maven then use the following dependencies.

<!– https://mvnrepository.com/artifact/org.apache.poi/poi –>

<dependency>

         <groupId>org.apache.poi</groupId>

           <artifactId>poi</artifactId>

            <version>4.0.1</version>

</dependency>

 

Or else you can directly download latest POI jar file from https://poi.apache.org/download.html

Step 4. Once the downloaded jar file then unzip the file and add library file into your project.

Right click on your project -> Build Path -> Configure Build Path->Library -> Add External Jar-> select the below listed jar file from unzip file -> ok

pandeycrm_addingjarfile

 

Add all the listed jar file below.

pandeycrm_poi_4.0.1pandeycrm_poi_4.0.2pandeycrm_poi_4.0.4pandeycrm_poi_4.0.3

pandeycrm_poi_4.0.5pandeycrm_poi_4.0.6

 

 

 

 

 

Below are java interfaces and classes that we will use for read/write xls and xlsx file in POI

XSSFWorkbook : – Is a class representation of XLSX file.

HSSFWrokbook  : – Is a class represent of XLS file.

XSSFSheet :- XSSFSheet classes implement this interface represent XLSX file’s sheet.

HSSFSheet : – HSSFSheet classes implement this interface XLS file’s sheet.

XSSFRow :- XSSFSheet classes implement this interface represent to a row of XLSX file.

HSSFRow : – HSSFSheet classes implement this interface represent to a row of XLS file.

XSSFCell :- XSSFSheet classes implement this interface represent a cell in a row of XLSX file.

HSSFCell : – HSSFSheet classes implement this interface represent a cell in a row of XLS file.

For Example.

pandeycrm_excelfile

Read Data from Excel file to Login with Salesforce

 

package Account;

 

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

 

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

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;

import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill;

import org.apache.poi.ss.usermodel.Workbook;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

 

public class excelSheetReadWrite {

WebDriver driver;

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

// TODO Auto-generated method stub

String file=”D://Selenium Projects//Credential.xlsx”;

FileInputStream fis= new FileInputStream(file);

XSSFWorkbook workbook= new XSSFWorkbook(fis);

XSSFSheet sheet= workbook.getSheet(“Sheet1”);

/*Reading data from Excel

System.out.println(sheet.getSheetName());

System.out.println(sheet.getLastRowNum());

String userName=sheet.getRow(1).getCell(1).toString();

System.out.println(sheet.getRow(2).getCell(0));

System.out.println(userName);

*/

//Open Chrome browser

WebDriver driver;

System.setProperty(“webdriver.chrome.driver”,”D:\\SeleniumDrivers\\Chrome\\

                             chromedriver.exe”);

driver = new ChromeDriver();

driver.manage().window().maximize();

//Login With Salesforce using Webdriver

driver.get(“https://login.salesforce.com/&#8221;);

//XSSFRow r= sheet.getRow(1);

int noOfColumns = sheet.getRow(1).getLastCellNum();

String[] Headers = new String[noOfColumns];

for(int i=0;i < noOfColumns ;i++){

Headers[i]=sheet.getRow(0).getCell(i).getStringCellValue();

}

for(int j=0; j< noOfColumns;j++){

if(Headers[j].equals(“UserName”)){

driver.findElement(By.id(“username”)).sendKeys(sheet.getRow(1).getCell(j).getStringCellValue());

}

if(Headers[j].equals(“Password”)){                     driver.findElement(By.id(“password”)).sendKeys(sheet.getRow(1).getCell(j).getStringCellValue());

break;

}

}

driver.findElement(By.id(“Login”)).click();

fis.close();

}

}

 

pandeycrm_login

pandeyerm_HomePage

How to Validate Salesforce Login page using page object model in selenium Webdriver

Posted on Updated on

What is Page Object Model(POM)?

It is a design pattern to create object repository for web UI elements. Under the POM for each web page in the application, there should be a corresponding page class. This Page class will find the WebElements of that web page and contains Page methods which perform operations on those WebElements. This Page class will find the WebElements of that web page and contains Page methods which perform operations on those WebElements.

Shashikant_POM

Advantage of Page Object model

The main advantage of Page Object Model is that if the UI changes for any page, it doesn’t require us to change any tests, we just need to change only the code within the page objects (Only at one place).

POM creates our testing code maintainable and reusable.

How to design the page object model.

Step 1 If eclipse not installed in your PC then click on download

  • Double click on downloaded .exe file.
  • Select Eclipse IDE for Java Developers.
  • Click on Install
  • Open the eclipse

Step 2 Create a Java project (SalesforcePMO)

Shashikant_JavaProject

Step 3 Right click on Project and Create a package(PageObjectModule).

Shashikant_Package

Step 4 Add the selenium Library files your package.

  • Download selenium latest version
  • Right click on the project
  • Select the properties from open window
  • Select java build path and then click on the libraries
  • Click on the Add External JAR’s
  • Open your downloaded selenium jar file.
  • Click on Apply and close button.

Shashikant_Slib.png

Step 5 Add TestNG library file.

  • Right click on the project
  • Select the properties from open window
  • Select Java build path and then click on the libraries
  • Click on the Add Library
  • Select TestNG
  • Click on Next
  • Click on finish button
  • Click on Apply and close button.

Shashikant_Slib

Step 6 Inside the PageObjectModule Package create one class LoginPOM.java

Note: – Each page in the application will contains separate class

Shashikant_class.png

Step 7 Create one more package with name TestPackage.

Step 8 Create a class LoginTest.java inside the TestPackage.

Step 9 Copy the below code and paste into the related java file.

LoginPOM.java

package PageObjects;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

 

public class LoginPOM {

//All web element which are used on the Login page

WebElement usernameBox;

WebElement passwordBox;

WebElement cookiesCheckBox;

WebElement loginButton;

WebElement verifyButton;

public LoginPOM(WebDriver driver) {

//for maximize the window size

driver.manage().window().maximize();

//Find the web element by id

usernameBox = driver.findElement(By.id(“username”));

passwordBox = driver.findElement(By.id(“password”));

cookiesCheckBox =driver.findElement(By.id(“rememberUn”));

loginButton = driver.findElement(By.id(“Login”));

}

//Login with cookies method

public void LoginwithCookies(String username, String password) {

usernameBox.sendKeys(username);

passwordBox.sendKeys(password);

cookiesCheckBox.click();

loginButton.click();

}

// Login without cookies

public void LoginWithoutCookies(String username, String password) {

usernameBox.sendKeys(username);

passwordBox.sendKeys(password);

loginButton.click();

}

}

LoginTest.java

package Tests;

import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.*;

import PageObjects.LoginPOM;

public class LoginTests {

WebDriver driver = null;

String testUrl = “https://login.salesforce.com/&#8221;;

@BeforeMethod

//Browser open

public void setUp(){

//Download the ChromeDriver. To download click here

//Set the system property to open the chrome browser.

System.setProperty(“webdriver.chrome.driver”,        “C:\\Users\\Lenovo\\Downloads\\SeleniumDrivers\\chromedriver.xe”);

driver = new ChromeDriver();

driver.get(testUrl);

driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);

}

@Test

public void validLoginPOM() throws InterruptedException {

LoginPOM loginpage= new LoginPOM(driver);

loginpage.LoginWithoutCookies(“Enter user name”, “Enter password”);

Thread.sleep(25000); // wait 25 second, meanwhile you will get email verification code.

//Enter verification code manually which you received on email

WebElement verifyButton = driver.findElement(By.id(“save”));

verifyButton.click();

}

@Test

public void validLoginPOM1() throws InterruptedException {

LoginPOM loginpage= new LoginPOM(driver);

loginpage.LoginwithCookies(“Enter Username”, “Enter Password”);

Thread.sleep(25000);

//Enter verification code manually.

WebElement verifyButton = driver.findElement(By.id(“save”));

verifyButton.click();

driver.quit();// close your browser

}

@AfterMethod

public void TestClose() throws Exception {

driver.quit();

}

}

Note: If the user doesn’t want to enter verification code then user have to login with developer Org and Go to setup-> type profile in quick find box -> open system administrator profile -> click on Login IP Ranges -> click on new button. set the IP ranges from 0.0.0.0 to 255.255.255.255 and save the IP Rang.

Sources: –

SeleniumHQ, Guru99Eclipse