Read Excel file using Java – Apach POI

Let’s see how can you Read Excel file using Java using Apache POI. Java doesn’t provide direct API to read and write Excel document, you have to rely on third party libraries. To read and write XLS or XLSX file you can use JXL open source library or Apache POI library.

I will suggest to Apache POI instead of JXL because JXL supports only .XLS format(produced by Microsoft Officer version prior to 2007 e.g. MS Office 2000 and 2003) on the other hand Apache POI supports both formats .XLS (produced by Microsoft Officer version prior to 2007 e.g. MS Office 2000 and 2003) and .XLSX (created by Microsoft Office 2007 onwards e.g. MS Office 2010 and 2013).

 

Read Excel file using Java

With Apache POI, you can easily create, read, write and update Excel files using this library. It uses terms like workbook, worksheet, cell, row to keep itself aligned with Microsoft Excel and that’s why it is very easy to use.

How to Configure Apache POI in Java Project

Before start with Apache POI. First, you need to configure the POI libraries with your project. To configure the Apache POI, follow the below mentioned steps –

  1. Download Apache POI from apache’s Offical website.
  2. You will get the .zip file. Unzip the file.
  3. Keep it at some secure place.
  4. Add the all libraries (.jar files) inside your project as External jar files.

Done, You have configured it.

 

Read xlsx file using Apache POI

Let’s see how can you read the .xlsx file using Apache POI.

First, Create an Excel file and save it as Excel workbook(*.xlsx) extension. I have created the file named TestData.xlsx and saved at location (D:\Framework\SeleniumTraining\TestData.xlsx) . Now let’s see, How can we read this file.

The above code will read your TestData.xlsx file and print the data in the console.

 

Read xls file using Apache POI

We have seen, How can we read the .xlsx, Now we will see, How can we read the .xls file using Apache POI. I have created and sample file named TestData.xls at location – D:\TestData.xls

 

This will read my .xls file and print the same in the console.

 

Have you noticed the difference in reading the .xlsx and .xls file. The only difference was in WorkBook and WorkSheet object class.

While reading the .xlsx file we were using XSSF (XML SpreadSheet Format) to reading XML (XLSX) format files and while reading the .xls version we were using HSSF (Horrible SpreadSheet Format)to read  Microsoft Excel (XLS) format files.

That’s all, How can we read the .xls and .xlsx file using Apache POI.

 

 

0 Comment

Leave a Reply