Apache POI is an open-source Java-based library that can be used to handle the Microsoft Office Document by using Java-based programming language.
This Apache POI tutorial is designed for beginners and professionals to provide basic and advanced concepts of Apache POI technology.
In this tutorial, we will cover download and installation of 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 Apache POI
Apache POI contains a list of different Java Interfaces and classes for reading the XLS and XLSX format files.
List of different Java Interfaces in the Apache POI API
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.
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 is 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 a grid of cells. HSSFSheet and XSSFSheet classes implement sheet interface.
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.
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 values, or formulas. A string cell cannot hold 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
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 the 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, the syntax is given below:
XSSFWorkbook ws = new XSSFWorkbook();
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.
HSSFWorkbook wb = new HSSFWorkbook();
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:
XSSFSheet sheet = wb.createSheet("spreadSheet"); // Return type of the createSheet() method is an XSSFSheet. The sheet is a variable that is used to store the return value. 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"); XSSFRow
XSSFRow is a class that 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 a spreadsheet is given below:
XSSFRow row = spreadsheet.createRow((short)1);
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 a spreadsheet of XLS file is given below:
HSSFRow row = spreadsheet.createRow((short)1);
XSSFCell
XSSFCell is a class that 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 a spreadsheet of XLSX file.
To create a cell in the spreadsheet, first, we need to create a row like this:
// 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);
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 spreadsheet of XLS file are 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 5.0.0 Jars in Eclipse IDE
There are the 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 zip file from the link http://poi.apache.org/download.html.
After downloading, you need to extract all Jars to a local file folder on your computer as shown in the below screenshot.
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.
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 the name it as Excelfolder. Local Disk(c) ➝ AutomationProject ➝ ExcelFolder
11. Once the Excel folder is created, open the Excel sheet from MS Office Excel and rename the first sheet of the file as a TestSheet1.
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:ExcelfolderDataSheet.xlsx”.
If you are using Maven in your project, we need to set the dependency on the 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 formatting. | POI API supports rich text formatting. |
Hope that this Apache POI tutorial has covered almost all the important basic points related to the Apache POI API. I hope that you will have understood this topic and enjoyed it.
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⇐ PrevNext ⇒