Introduction to Apache POI API

In the previous post, we have seen creating the object repository using properties file in Selenium.In this post , we will see what is Apache POI API and its use in File I/O.

File I/O is very important part of any automation project irrespective of tool used like Selenium, QTP etc. As in our computer systems, we perform functions with files like create file, write to file, update to file, read data from file , delete file etc. , the same can be done in Selenium.

When do we require to perform File I/O in Selenium?

There may be many reasons for performing the operations like creating file, writing to file etc.

• Parameterization:  Input data will be passed to the scripts.
• Storing Output: The output captured during execution can be stored in files.
• Reporting: A good reporting is important feature of any automation project. We need to use files for generating portable reports.

Introduction to Apache POI API:

For achieving the requirements mentioned above, a good option is to use Microsoft Excel Files. With Excel files, We can manipulate all the operation like creating Excel file, reading, writing etc. and use them to generate a good report. In java , we don’t have any in-build mechanism to manipulate the excel files, but we can use some some open source APIs for this requirement.

For achieving the target, Apache POI APIs come very handy. By using them , we can perform all the manipulations on an Excel file. There is one more API for Excel manipulations, that is called JExcel. But we will give the preference to Apache POI because of the following reasons.

• JExcel doesn’t support xlsx format whereas POI supports both xls and xlsx formats.
• Apache POI provides stream-based processing, that is suitable for large files and requires less memory.
• It has support forums from Apache foundation.
• Apache POI provides excellent support for additional excel features such as working with Formulas, creating cell styles by filling colors and borders, fonts, headers and footers, data validations, images, hyperlinks etc.

Where to get Apache POI APIs?

You can download POI API from http://poi.apache.org/download.html. Download the latest version of zip file from Binary distribution. (as shown in the figure below).

Extract the zip file and you will see the jar files as shown below in main folder  and ooxml folder. These are important jars which allow us to work with Excel files. Add the below Jars to your class where you will write the code for Excel file handling.

To work on Excel, we have 2 types of pure java implementations of POI APIs.

1. POI HSSF (.Xls)
2. POI XSSF (.Xlsx)

POI-HSSF is used to work with Excel ’97(-2007) file format i.e XLS and POI -XSSF is used to work with Excel 2007 OOXML (.xlsx) file format(and later version).

In the next post, we will learn to write the data into Excel File.