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.
➲ 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
➲ 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.
XSSFWorkbook ws=new XSSFWorkbook();
➲ 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.
➲ 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 which is used to store the return value.
➲ 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.
➲ 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.
➲ 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.
➲ 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.
// Create the first cell on a created row.
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:
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.|
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!