Last Updated on April 27, 2020 by Scientech Easy
Apache POI is open-source Java-based libraries 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.
➲ 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
➲ 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 a grid of cells.
➲ HSSFSheet and XSSFSheet classes implement this interface.
➲ 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.
➲ 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 numeric values and a numeric cell cannot hold string values. The following are the types of cells, their value, and syntax.
|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 syntaxto create a blank workbook.
HSSFWorkbook wb = new HSSFWorkbook();
➲ 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.
➲ It is present in org.apache.poi.hssf.usermodel package. It has the following syntax to create a blank spreadsheet.
HSSFSheet sheet = wb.createSheet("spreadSheet");
➲ It is used to create rows in the spreadsheet of XLSX file. The syntax to create a row in spreadsheet is given below.
XSSFRow row = spreadsheet.createRow((short)1);
➲ 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.
HSSFRow row = spreadsheet.createRow((short)1);
➲ 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);
➲ 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 4.1.2 Jars in Eclipse IDE
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 in your computer as shown in the below screenshot.
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.
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 ⇒