Java provides different API to Read and Write a File with help of Selenium. In this Post, we will learn how can we read and write from Excel file by using Java IO package,JXL and Apache POI library.
Poi for xlsx read in selenium for mac windows 10

Poi for xlsx read in selenium for mac windows 10

If your are using any Automation Framework you must know About Read and Write fro Excel, We have to use This Concept while working in Data Driven Framework .

Handling excel files Using Apache POI in Selenium WebDriver. As we all know Selenium supports only Web browser automation. We need to get the help of third party API like Apache POI to handle (read and write) excel files using Selenium WebDriver. The Apache POI in Selenium is a widely used API for selenium data driven testing. It is a POI library written in Java that gives users an API for manipulating Microsoft documents like.xls and.xlsx. Users can easily create, modify and read/write into excel files. POI stands for “Poor Obfuscation Implementation.”.

Step to Read Data from Excel Using Apache POI jar

To Read or Write From Excel,Apache Community provides a very widely used library POI. This library have features to read and write both XLS and XLSX file format of Excel.
Selenium

HSSF implementation is provided by POI library to read 'XLS' Files and XSSF implementation of POI library to read 'XLSX' File ,Lets go to Deep Die .......

For user who is using Maven Project can copy Dependency from here

For user Who is using Simple Java Project Can know How To Download Apache POI Jar and Add To JAVA Build Path

Types of Files in MS-Excel

.XLS File
.XLSX File

Types of Classes and InterFace in Apache POI

Apache Interface and Classes

Read Data from Excel File

Poi For Xlsx Read In Selenium For Mac File

Go To Java Project
Right Click on Java Project
Create a New Folder with Name DataFromExcel
Copy Any Blank Excel File from Your PC and Paste Inside Folder 'DataFromExcel'
Open Excel File
Enter The Excel Sheet Name 'LoginDataSheet'
Enter Any Website Login Credential Whom you want To Read

LoginDataSheet

SheetName

Write Data To Excel File

Here we learn how to read data from the excel using JAVA and Apache POI in selenium wedriver . Reading data from excel is an important part of the selenium to make the Automation keyword framework , Hybrid Frame work and Data driven framework to read the data and pass the values in functions. To read the data from excel we need the Input/Output functions of file stream.

Now lets start with some of the key points to read the data from excel

1 ) Apache POI library is required –Complete guide to add the Apache POI library to Eclipse IDE .

Lets have a brief overview of the Apache POI library : Apache POI library contains the interfaces, classes and methods to read,write, update and create the MS-office files. As a part of MS-Excel Apache POI supports both excel format “XSLX” and “XLS”.

What is XLSX format and XLS ?

Poi For Xlsx Read In Selenium For Mac Os

XLS format : All the Ms-excel files that are below the version of 97 supports “XLS ” version . These files ends with the extension “XLS “.

XLSX format : All the MS- Excel files that are version of 97 and above supports “XLSX” version.These files ends with the extension “XLSX “.

Interface	XLSX Class	XLS Class
Workbook represents workbook	XSSFWorkbook	HSSFWorkbook
Sheet represents Sheet	XSSFSheet	HSSFSheet
Row represents Row	XSSFRow	HSSFRow
Cell represents Cell	XSSFCell	HSSFCell

Now we we will learn how to read the data from excel using JAVA in selenium. Reading data from excel is the most important part while automation as Automation frameworks read the input data from excel and write the output results in excel file. Lets take the below sample excel sheet we will read in selenium now.

File Name : Read_Write_Excel_Padhle.xlsx
Sheet Name : Padhle_ReadExcel

Pseudo code for reading excel data in JAVA selenium

1) Create the File Class object to get the Excel file.
2) Create the object of FileInputStream Class to read the file . Pass the File as the parameter in FileInputStream Class.
3) Create the WorkBook object depends on the file extension if it is “.XLSX” file create XSSFWorkbook object, if it is “.XLS” create HSSFWorkbook object. Remember FileInputStream class object is passed as the parameter in workbook object.
4) Now create the Sheet class object and get sheet with the help of workbook object via functions getSheet(sheetname) or getSheetAt(int).
5) Find the number of rows via getLastRowNum() – getFirstRowNum().
6 ) Create row class object .
7 ) Read the cell values .

Java Code : How to read excel File data

Console Output

Hope you are now clear how to read the data from excel , If you have any doubt please mention in the below comments.