We have seen Introduction of Apache POI. In this post, we will see how can we read an excel file using Apache POI library.

Earlier, Apache POI used to provide different classes to read/write .xls and .xlsx excel sheets.

  1. Class HSSFWorkbook – For xls
  2. Class XSSFWorkbook – For xlsx

But now Apache POI provides a package which supports both type of excel files. Just we need to pass actual extension of excel file. As per official document of Apache POI:-

HSSF is our port of the Microsoft Excel 97 (-2003) file format (BIFF8) to pure Java. XSSF is our port of the Microsoft Excel XML (2007+) file format (OOXML) to pure Java. SS is a package that provides common support for both formats with a common API. They both support read and write capability.

You don’t need to use different classes based on excel type. SS package of Apache POI provides a class named “WorkbookFactory“. This class is for creating the appropriate kind of Workbook be it HSSFWorkbook or XSSFWorkbook by auto-detecting from the supplied input.

WorkbookFactory has a static method named “create(File file)” which creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

So, let’s learn the whole program now:-

We don’t need to put all Apache POI libraries in our project., If we need to use with excel, we need only POI-Core and POI-ooxml libraries. I will suggest you to use Maven as it will download all required transitive dependencies as well.

I have added below dependencies in pom.xml as per latest available version. Always use latest versions. You can find that in Maven Central Repo.

First create an excel sheet as below and keep in resource folder:-

You can see it is a .xlsx file. Let’s familiar with basic excel terminologies. Below image should give you a better idea.

Every cell can be represented uniquely using ( rowNo, ColNo). Row and Column index starts from zero. If I say a cell which represented as (0,0), it means I need to go first row then first column. “EmpID” will be cell value as per above excel. Below is pictorial representation: –

Let’s see now detailed Java Code. I will print all data of excel one by one:-

Output:-

You can download/clone above sample project from here.

If you have any doubt, feel free to comment below.
If you like my posts, please like, comment, share and subscribe.
#ThanksForReading
#HappyLearning

You can find all Selenium related post here.
You can find all API manual and automation related posts here.
You can find frequently asked Java Programs here.

Author: Amod Mahajan

My name is Amod Mahajan and I am an IT employee with 6+ years of experience in Software testing and staying in Bengaluru. My area of interest is Automation testing. I started from basics and went through so many selenium tutorials. Thanks to Mukesh Otwani as his tutorials are easy and cover basics to advance. I have habit of exploring concepts by deep diving. I used to make notes. I thought of sharing my knowledge through posts and now I am here. #KeepLearning #ShareLearning

2 thoughts on “Are You Still Using Apache POI – HSSFWorkbook & XSSFWorkbook To Read/Write Excel? Try WorkbookFactory

  1. Hi Amod,

    In my project the Test Data Excel sheet contains single column & multiple rows of Order ID’s.I need to pass different order ID for each test case.
    How can achieve this in BDD Cucumber framework?

    For Eg –
    Feature file1 – pass data from cell(0,0)
    Feature file2- pass data from cell(1,0)
    Feature file3- pass data from cell(2,0) & So on

Leave a Reply