Table of Contents
Enroll in Selenium Training

In test automation, the test data plays an important role. The test automation framework developers maintain the test data in various formats and Microsoft Excel is one of the easiest and most used ways of storing and maintaining test data. Even the manual QAs keep their test data in Excel files. Now to access this test data in the automation frameworks, Java provides various libraries and Apache POI is one of the most used among them. Subsequently, in this article, we will kickstart our journey of using the Apache POI by understanding how to download apache poi and use the same in an automation framework, by covering the details under the following topics:

  • What is Apache POI used for?
  • How to download Apache POI?
  • How to install POI libraries?
    • And, how to configure POI libraries in Eclipse?

What is Apache POI used for?

Apache POI is an open-source library developed and distributed by Apache Foundation. Moreover, it is mainly used to create, read, and edit Microsoft Office files, majorly Excel files in Java programs. Moreover, it is distributed as a JAR, which provides various methods to manipulate Microsoft Excel files. The image below shows details of various formats and actions that Apache POI supports:

Apache POI actions

The older versions of Apache POI support binary file formats such as doc, xls, ppt etc whereas, from version 3.5 onwards, Apache POI supports OOXML file formats such as docx, xlsx, pptx etc. Additionally, the table below gives a brief summary of various components provided by Apache POI:

Component Explanation Details
POIFS Poor Obfuscation Implementation File System. This component provides the capability to read various files.
HSSF Horrible Spreadsheet Format This component is used to read/write an older format of Excel(xls).
XSSF XML Spreadsheet Format This component is used to read/write a new format of Excel(xlsx).
HPSF Horrible Property Set Format This component is used to extract the “property sets” of various types of MS- Office files.
HWPF Horrible Word Processor Format This component reads/writes an older format of Word(doc).
XWPF XML Word Processor Format This component reads/writes a new format of Word(docx).
HSLF Horrible Slide Layout Format This component reads/writes PowerPoint presentations.
HDGF Horrible DiaGram Format This component reads/writes MS-Visio files.
HPBF Horrible PuBlisher Format This component reads/writes MS-Publisher files.

Let's now quickly see, how we can download *Apache POI *libraries:

How to download Apache POI?

The first step in the process of storing and accessing the test data in Excel files is to download the Apache POI library. Consequently, follow the steps as mentioned below to download the Apache POI  library:

  1. First, navigate to the Apache POI  webpage. After that, click on the Download  link in the left menu. Moreover, it is as highlighted below:

download link on the site

  1. Secondly, clicking on the Download link will navigate to the page showing the latest release of Apache POI. Additionally, it is as highlighted below:

latest release of Apache POI

  1. Thirdly, you can either click on the "Latest Stable Release Link " (as shown by marker 1), which will scroll the page down to the binaries of Apache POI (as shown by marker 2 ), or can directly scroll down to the section of binaries shown by marker 2. Subsequently, after clicking on the "zip " file, it will navigate to the page showing various download links as shown below:

mirror links for downloads

  1. After that, when you click on any of the highlighted links, it will download a zip. Additionally, you can save it in any folder of your choice as shown below:

download apache poi zip file

  1. Fifthly, once you unzip the file, it will show the contents as below:

apache poi libraries

These are various JAR  files that provide the classes and methods that we use for the manipulation of various MS-Office file types. Subsequently, let’s see how we can install these JARs  in our projects and use them for the manipulation of various supported file types.

How to install POI libraries?

As we understood in the above sections that all the Apache POI libraries are available as JARs. Now to access the functionalities of POI, these JARs should be available in the build path of your application/framework. Additionally, we are majorly using Eclipse as IDE in our articles. Consequently,  let's quickly see how we can install the Apache POI JARs in the build path of a project in Eclipse :

How to configure POI libraries in Eclipse?

Follow the steps as mentioned below to add the POI JARs in a project in Eclipse:

  1. Firstly, suppose you have created a JAVA project in Eclipse, as per the steps mentioned in the article "Configure Selenium WebDriver with Eclipse".

  2. After that, right -click on the project in Eclipse. Subsequently, select Build Path >> Configure Build Path as shown below:-

Configure Build Path in Eclipse

  1. Thirdly, it will open the "Properties " of the project. After that, select the Libraries tab. Finally, click on the Add External JARs as highlighted below.

Add External JARs in Eclipse Project

Note: Classpath(as highlighted by marker 1) should be selected when adding the External JARs.

  1. Fourthly, select the JARs in the parent folder of the unzipped POI files. Subsequently, click on the Open button to include them in the Eclipse project:

Parent POI JAR files

  1. Next select the JARs under the ooxml-lib folder in the unzipped POI folder. Moreover, it is as highlighted below:

POI JARs in ooxml lib folder

  1. Sixthly, select the JARs under the lib folder in the unzipped POI  folder. Additionally, it is as highlighted below:

POI JARs in the lib folder

  1. After that, once all the POI JARs add, click on the Apply and Close button. Moreover, it is as highlighted below:

Install POI libraries

  1. Once all the POI libraries successfully install in the Eclipse project, they will reflect under the Referenced Libraries folder in the left pane of the Eclipse project structure, as shown below:

Apache POI JARs in Eclipse Project

So, this completes the installation of Apache POI in an Eclipse project. Subsequently, we can now start using the capabilities of these libraries in our JAVA Project.

Key Takeaways

  • Apache POI libraries provide the capabilities to handle various types of MS-Office files.
  • Additionally, for an automation framework, keeping the test data in a file(eg, Excel file) is one of the common practices and Apache POI makes it very easy to read and write test data to an Excel file.
  • Lastly, Apache POI libraries are available as a set of JAR files, which we can download and install in an Eclipse project by simply including the JAR files in the project.
Page Object Model
Page Object Model
Previous Article
Read & Write Data from Excel in Selenium: Apache POI
Read & Write Data from Excel in Selenium: Apache POI
Next Article

Similar Articles

Feedback