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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s