How To Read Data From Excel File In Java Using Selenium

In this article we learn the how to read data from excel file in java using selenium webdriver and Apache POI.

Table of Contents

How To Read Data From Excel File In Java Using Selenium Webdriver

Excel File Operations in Selenium:

Apache POI (Poor Obfuscation Implementation) provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint, and Excel. It has different components for reading and writing different Microsoft Office files:

1) POIFS for OLE 2 Documents

2) HSSF and XSSF for Excel Documents

3) HWPF and XWPF for Word Documents

4) HSLF and XSLF for PowerPoint Documents

5) HPSF for OLE 2 Document Properties

6) HDGF and XDGF for Visio Documents

7) HPBF for Publisher Documents

8) HMEF for TNEF (winmail.dat) Outlook Attachments

9) HSMF for Outlook Messages

JDK does not provide a direct API to read or write Microsoft Excel or Word documents. We have to rely on the third-party library that is Apache POI.

Maven Dependencies: –

We will need to include following maven dependencies for Apache POI in our pom.xml file.

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

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

</dependency>

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

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

<artifactId>poi-ooxml</artifactId>

</dependency>

How we read an excel file in Java?

We use HSSF and XSSF component provided by Apache POI to read and write excel in Java”. or Common spreadsheet (SS) usermodel provided by Apache POI to read and write excel in Java”. Common SS usermodel can read/write excel with both extensions .xls and .xlsx.

HSSF -Horrible SpreadSheet Format.

XSSF– XML SpreadSheet Format.

We will find excel with two extensions: –

1) .xls (Microsoft Excel 2003 file)

2) .xlsx (Microsoft Excel 2007 file or later)

Apache POI provides different components for each type of extension of excel, which are given below:

.xls – HSSF (Horrible SpreadSheet Format)- We need to download “poi” jar files.

.xlsx –XSSF (XML SpreadSheet Format)- We need to download “poi-ooxml” jar files.

If We do not want to read write excel based on its extension, we can use common spreadsheet usermodel which requires poi-ooxml and core poi libraries. Common spreadsheet is used widely now as it can read excel with any extension with same lines of code. No need of using HSSF or XSSF for workbook and sheet.

WorkBook/Sheet/Row/Cell– These are the interfaces

How to Read Excel file?

Step1: – Gaining Access to Workbook

String FilePath = “d://filepath.xls”;

FileInputStream fs = new FileInputStream (FilePath);

Workbook wb = Workbook.getWorkbook(fs);

Step2: – Get the access to the particular sheet

Sheet sh = wb. getSheet (“Sheet Name”);

This is to get the access to Sheet1.

int lastRowNum=s1.getLastRowNum();

Gets the number of the last line (numbering starts from 0). Empty rows are included.

int physicalRowNum=s1.getPhysicalNumberOfRows();

Returns the number of physically defined rows (NOT the number of rows in the sheet).

Step3: – Identify Rows.

Row r1=s1.getRow(0);

int lastcellNum=r1.getLastCellNum();

Gets the index of the last cell contained in this row. (Numbering starts from 1).

int physicalcellNum=r1.getPhysicalNumberOfCells();

Returns the number of physically defined cell (NOT the number of Cell in the sheet).

Step4: – Read data from cell.

Cell c1=r1.getCell();

String data=c1.getStringCellValue(); System.out.println(“Data:”+data);

The cell that contains the data, we can read the data in different formats like String, Date, Number using the different methods which are based upon the format of the cell we specify in the excel sheet.

String – getStringCellValue ()

Get the value of the cell as a string

For numeric cells It throw an exception. For blank cells it return an empty string. For formulaCells that are not string Formulas, it throw an exception.

Number – getNumericCellValue ()

Get the value of the cell as a number.

For strings it throws an exception. For blank cells it returns a 0. For formulas or error cells it returns the precalculated value;

Date – getDateCellValue ()

Get the value of the cell as a date.

For strings it throw an exception. For blank cells it return a null.

How to Write in Excel file

Step1: – Gaining Access to Workbook

String FilePath = “d://filepath.xls”;

FileInputStream fs = new FileInputStream (FilePath); Workbook wb = Workbook.getWorkbook(fs);

Step2: – Get the access to the particular sheet

Sheet sh = wb. getSheet (“Sheet Name”);

This is to get the access to Sheet1.

Step3: – Set a value for the cell.

s1. getRow (1). createCell (2). setCellValue (“Pass”);

s1. getRow (2). createCell (2). setCellValue (“Fail”);

s1. getRow (3). createCell (2). setCellValue (“Pass”);

getRow ()-> Returns the Row this cell belongs to

createCell ()->Use this to create new cells within the row and return it.

setCellValue ()->Set a string value for the cell.

Step4: – Write and close the file output stream

FileOutputStream fos=new FileOutputStream (“Path of excel file”). wb. Write(fos);

fos. Close ();

FileOutputStream is an output stream for writing data to a File. It is a subclass of OutputStream, which accepts output bytes and sends them.

Write-Write out this workbook to an OutputStream.

Close-Closes this file output stream and releases any system resources associated with this stream. This file output stream may no longer be used for writing bytes.