In this tutorial, we are going to learn a very important technology Apache POI API. This tutorial is designed for beginners and professionals to provide basic and advanced concepts of Apache POI technology. It is open source Java-based libraries that can be used to handle the Microsoft Office Document by using Java-based programming language.

We will also guide you to install the Apache POI Jar files in the Eclipse IDE for Selenium WebDriver project.

Apache POI Data-Driven Framework


➲ Apache Poor Obfuscation Implementation (POI) is a set of open source Java-based libraries developed by the Apache Foundation which is used to manipulate Microsoft Excel documents such as .xls and .xlsx file formats.

➲ It is an extensive API (Application Programming Interface) used to automate Selenium Data-driven tests by which we can create, modify, read, and write excel data by using the Java program.

➲ POI API supports both Binary and Spreadsheet workbook. It has many features to work with modern Microsoft products.

➲ The test performance with the .xlsx file will be slower as compared to the .xls file in the Apache POI. 
➲ Apache POI is dependent on the xmlbeans library which must be added in the build path before executing any test.

Classes and Interfaces in POI


Apache POI contains a list of different Java Interfaces and classes for reading the XLS and XLSX  format file.
Apache POI classes and interfaces
They are as follows:
List of different Java Interfaces in the Apache POI
1. Workbook
➲ The term "Workbook" represents Microsoft Excel file.
➲ It is a super-interface of the HSSFWorkbook and XSSFWorkbook classes and presents in org.apache.poi.ss.usermodel package.
➲ It is used to create and maintain the spreadsheet. A workbook may contain many sheets. ➲ HSSFWorkbook and XSSFWorkbook implement workbook interface. 

2. Sheet 
➲ A sheet is a super-interface of the HSSFSheet and XSSFSheet classes and presents in the org.apache.poi.ss.usermodel package.
➲ It creates a high-level or low level of spreadsheets with a specific name. A spreadsheet is a page in a Microsoft Excel file that contains the number of rows and columns and identified with a specific name.
➲ The columns and rows are identified with alphabets and numbers respectively.
➲ The most common type of spreadsheet is a worksheet that represents as a grid of cells. ➲ HSSFSheet and XSSFSheet classes implement this interface.

3. Row 
➲ A row represents a collection of cells. 
➲ It is a super-interface of the HSSFRow and XSSFRow classes and presents in the org.apache.poi.ss.usermodel package.
➲ It is used to represent a row in the spreadsheet. HSSFRow and XSSFRow classes implement the row interface.

4. Cell 
➲  A cell represents a collection of columns in the spreadsheet. When we enter data into a spreadsheet, the data is always stored in a cell. A cell is identified by the level of rows and columns.
➲ It is a super-interface of all the classes which represents a cell in a row of the spreadsheet.
➲ HSSFCell and XSSFCell classes implement the cell interface.

Types of Cells
The cell type represents that a cell may contain strings, numeric value, or formulas. A string cell cannot hold the numeric values and a numeric cell cannot hold string values. The following are the types of cells, their value, and syntax. 
SN Type of Cell value Syntax type
1 String cell value XSSFCell.CELL_TYPE_STRING
2 Numeric cell value XSSFCell.CELL_TYPE_NUMERIC
3 Boolean cell value XSSFCell.CELL_TYPE_BOOLEAN
4 Error cell value XSSFCell.CELL_TYPE_ERROR
5 Blank cell value XSSFCell.CELL_TYPE_BLANK

List of different Java classes in the Apache POI
1. XSSFWorkbook 
➲ XSSFWorkbook stands for XML Spreadsheet Format. It is a class representation of XLSX file that is used to read and write xlsx file format of MS-Excel file. It is compatible with MS-Office version 2007 or later. 
➲ It is present in the org.apache.poi.xssf.usermodel package and implements workbook interface.
To create an object of XSSFWorkbook class which represents an excel workbook, Syntax is given below.
Syntax
   XSSFWorkbook ws=new XSSFWorkbook(); 

2. HSSFWorkbook 
➲ HSSFWorkbook stands for Horrible SpreadSheet Format. It is a class representation of XLS file that is used to read and write xls file format of MS-Excel file. It is compatible with MS-Office 97-2003 version.
➲ It is present in the org.apache.poi.hssf.usermodel package and implements workbook interface. It has the following syntax to create a blank workbook.
Syntax
   HSSFWorkbook wb=new HSSFWorkbook(); 

3. XSSFSheet 
➲ XSSFSheet is a class representing a sheet in an XLSX file format which is used to create an Excel spreadsheet from the workbook.
➲ It is under org.apache.poi.xssf.usermodel package. The syntax to create a blank spreadsheet is given below.
Syntax
   XSSFSheet sheet=wb.createSheet("spreadSheet"); // Return type of the createSheet() method is an XSSFSheet. The sheet is a variable which is used to store the return value.

4. HSSFSheet 
➲ HSSFSheet is a class representing a sheet in an xls file format which is used to create an Excel spreadsheet from the workbook.
➲ It is present in org.apache.poi.hssf.usermodel package. It has the following syntax to create a blank spreadsheet.
Syntax
    HSSFSheet sheet=wb.createSheet("spreadSheet");

5. XSSFRow 
➲ XSSFRow is a class which is present in org.apache.poi.xssf.usermodel package and implements Row interface.
➲ It is used to create rows in the spreadsheet of XLSX file. The syntax to create a row in spreadsheet is given below.
Syntax
   XSSFRow row=spreadsheet.createRow((short)1);

6. HSSFRow 
➲ HSSFRow is a class which is present in org.apache.poi.xssf.usermodel package and implements Row interface.
➲ It is used to create rows in the spreadsheet of XLS file. The syntax to create a row in spreadsheet of XLS file is given below.
Syntax
   HSSFRow row=spreadsheet.createRow((short)1);

7. XSSFCell
➲ XSSFCell is a class which is present in org.apache.poi.xssf.usermodel package and implements cell interface.
➲ It is used to create a cell in a row of the spreadsheet of XLSX file. It has the following steps to create a cell in a row of spreadsheet of XLSX file.

To create a cell in the spreadsheet, First, we need to create a row.
// Create the first row in a spreadsheet.
     XSSFRow row=spreadsheet.createRow(0); 
// Create the first cell on a created row.
     XSSFCell cell=row.createCell(0);

8. HSSFCell 
➲ HSSFCell is a class which is present in org.apache.poi.xssf.usermodel package and implements cell interface.
➲ It is used to create a cell in a row of the spreadsheet of XLS file. The steps to create a cell in a row of spredsheet of XLS file is as follows.

To create a cell in the spreadsheet, First, we need to create a row.
// Create the second row in a spreadsheet.
     HSSFRow row=spreadsheet.createRow(1); 
// Create the first cell on a created row.
     HSSFCell cell=row.createCell(0);

Download & Install Apache POI Jars to Eclipse IDE for Selenium WebDriver Project


There are following steps to follow for adding Apache POI Jars to the Project Library. They are as follows:
1. First, you download the latest version of POI Jars from the link http://poi.apache.org/download.html  and download the latest zip file. After downloading, you need to extract all Jars to a local file folder as shown in below screenshot.
Download Apache POI jar files
2. Launch eclipse.
3. You need to choose the workspace or create a new location to be the workspace for this project.
4. Now Go to File ➝ click on New ➝ Java Project and create a new Java Project and give an appropriate name according to the project ➝ Finish.
5. Right click on the project name, go to the Build path and select the 'Configure Build Path'.
6. Click on the 'Add External Jars' into libraries and go to the folder where you have kept the POI Jars files.
7. Add all Jars files from the lib folder, ooxml-lib folder  ➝ click on Apply ➝ Ok. See the below screenshot.
Installation of Apache POI jar files
8. After adding all jars files, now move to the src folder of your project. click right on the src folder to create a package. 
Go to the src folder ➝ New ➝ Package ➝ Give Name according to the project ➝ Finish.
9. Once the package is created, create a Java class file(main test file).
Right click on Package ➝ New ➝ Class ➝ Give name such as ReadExcelFileTest.
10. Create a folder with the project name and give name it as Excelfolder.
       Local Disk(c) ➝ AutomationProject ➝ ExcelFolder

11. Once the Excel folder is created, Open the Excel sheet from the MS-Office Excel and rename the first sheet of the file as a TestSheet1.
How to create excel sheet in apache poi api
13. Now you can write the test data in the excel file for the test. You can see the above sample texts.
14. Once the excel sheet is ready, You save it by giving a name as Datasheet into the ExcelFolder.
15 Click right and go to the properties of the Datasheet file to see the path of the file. 
     Right click on Datasheet ➝ Properties ➝ Security ➝ Copy like "C:\Excelfolder\DataSheet.xlsx".

If you are using Maven in your project, we need to set the dependency in maven project which helps to run the application. The dependency is given below:
    <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>

Difference between JXL Jar and POI Jar


SN JXL Jar POI Jar
1 JXL supports binary based format i.e ".xls format". It does not support Excel 2007 and XML based format i.e ".xlsx" format. POI Jar supports both .xls and .xlsx format.
2 JXL API is faster than POI API. POI API is slower than POI API.
3 JXL API was last updated in the year 2009. POI API is regularly updated and released.
4 The JXL documentation is not as comprehensive as that of POI The POI documentation is highly comprehensive and well prepared.
5 JXL API does not support rich text formattimg. POI API support rich text formatting.
Final words
Hope that this tutorial has covered almost all the important basic points related to the Apache POI API. I hope that you will have understood and enjoyed this tutorial. In the next tutorial, we will learn how to read test data from an excel sheet using POI API in a simple way and step by step.  
Thanks for reading!
Next ➤ Reading Excel file in Java using POI API
Next ⏩