_edited_edited.png)
Introduction:
The Apache POI in Selenium is a widely used API for Selenium Data-Driven Testing.
POI stands for "Poor Obfuscation Implementation". Apache POI is an API provided by Apache foundation which is a collection of different Java libraries. This libraries gives the facility to read, write and manipulate different Microsoft files such as excel sheets, power-point, and word files.
We frequently create a file, open it and update something or delete it on our Computers. The same is the case with Selenium Automation. We need a process to manipulate files with Selenium.
We are going to learn how can we read and write on an Excel file with the help of the Apache POI.
We will see below topics:
Why use an Excel file?
How to add "Apache POI" dependencies/libraries to projects?
How to Create and Call Excel Read Functions?
Get Row Count Function
Get Cell Data String Function
Get Cell Data Number Function
Why use Excel Files in Java Project or Maven Project?
In Selenium we can get data from external files like "Excel" and "CSV" so that we do not need to hard code that data within our scripts. It is way to make our scripts independent of data handling.
External files are used most commonly "Excel" and "CSV".
MS Excel is the most commonly used file and this will also enable us to manage our data separately to add updates and all the modifications outside the framework. So whenever we have to make any changes to our data we need not go inside the framework and make any changes.
We can handle everything from these Excel Files. Also, this will be the first step towards making our data-driven framework.
How to add "Apache POI" dependencies in projects?
To Read and Write Excel files in Simple Java or Maven Project, Apache provides a very famous library POI . This library is capable enough to read and write both "XLS" and "XLSX" file formats of excel.
To read "XLS" files, an HSSF implementation is provided by the POI library.
To read "XLSX", "XSSF" implementation of the POI library.
There are two ways for installing an Apache POI jar files depending upon the type of project.
If the project is a Simple Java Project then you can download jar files from POI download.
If the project is MAVEN then add dependency in the pom.xml file in the project.
Steps to Create Maven Project and also we will see "How to create and call Excel Functions"
Open Eclipse and go to "File"--->click New--->click "Other"
Then you will see below screen click "Maven"--->click next--->click next--->click next--->give the details.
Create a new package-
To create a new package right click on the src/test/java which is below the created project--->click New--->click Package--->give details then you will see the package underneath of project to the left panel.
Create a new Class
To create a class right click on the created Package--->click New --->click Class--->give details then you will see the created class under the package.
Add the following dependencies in the pom.xml
Here is the link for the Apache POI dependency
Copy above Apache POI dependencies in pom .xml file
Maven dependency is added when the pom.xml file is saved after copying the maven dependency. You can check added dependency below the "Maven Dependencies".
How to create Excel file and save data
First go to your project--->right click New --->go down click "Folder"--->select Project --->create new folder--->select project--->give folder name--->click Finish.
Then inside the folder create Excel File, insert data and save it
To create Excel file right click on created folder--->click properties--->click location--->click on created folder--->create Excel file.
Next screen will appear after you click the properties, then create excel file
In following screen you will see the created Excel file, first refresh the project and then check.
How to call Excel functions.
Below Excel file used in this Maven project to read data.
Here is the logic to read the data from XLSX
Continue...
Continue with output (please ignore WARNING)
Create one more new class to call Excel functions and Validate. So you can use this class anywhere in framework.
Additional information which might be interesting to know.
Apache POI (Poor Obfuscation Implementation File System) is the Java API for Microsoft Documents such as XLX, XLSX, DOC and PPT as well.
HSSF (Horrible Spreadsheet Format)
Read or write an Excel file format - XLS
XSSF (XML Spreadsheet Format)
Read or write an Excel file format - XLSX
How to create references for Workbook:
For new Excel format (XLSX)
XSSFWorkbook workbook = new XSSFWorkbook("excel file location")
For old Excel format (XLS)
HSSFWorkbook workbook = new HSSFWorkbook("excel file location")
Following is a list of different Java Interfaces and Classes in POI for reading XLS and XLSX files.
Hope this blog was useful!!
Thank you!!