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.
Classes and Interfaces in POI
They are as follows:
➲ 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.
➲ 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.
➲ 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
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);
➲ 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
Download & Install Apache POI Jars to Eclipse IDE for Selenium WebDriver Project
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.
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 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