How to Read XLSX File in Java using Apache POI

At times you might be required to read data from an Excel file. We will see how to read XLSX file in Java using Apache POI in this post. It works on similar principles that we saw in reading a text file chapter. It is just that we have different classes to import and a tad different mannerism to read data here.

Apache POI is one of those renowned APIs that is used for grabbing text from different resources. We are going to make use of its classes to achieve our goal here. It has its very own set of functions that can be used to grab data from different resources.

An XLSX file is nothing but a Microsoft Excel Open XML Spreadsheet File that has been created by Microsoft Excel 2007 and later versions.

Let’s see how to read XLSX file in Java using Apache POI.

Reading an Excel XLSX File

In your existing code you need to first specify the location of your XLSX file. I have saved an XLSX file titled “dc” in the D: drive. I have also put some data in it to be retrieved.

Here’s what my dc.xlsx file has:

We will read both the rows herein.

Even before we begin we will need Apache POI jars so that Eclipse retains all the required classes beforehand.

How to Download Apache POI Jars

Here are the steps to download Apache POI Jars. It is better to be prepared beforehand than to go looking for it when time comes.

Step 1: Navigate to the following URL:

http://poi.apache.org/download.html

Step 2: Click on the following zip file located in Binary Distribution section:

They generally showcase their latest versions here.

Step 3: Doing so will take you to the following page. Click on the first link which will start your download.

Step 4: Extract the folder and its contents. You will get the following jar files:

Step 5: Go to your project in Eclipse and right click on it. Then  put your mouse cursor on Build Path then click on Configure Build Path as shown in the figure below:

Step 6: It will open Properties for your project dialog box. Select Libraries tab and then click on Add External Jars…

Doing so will open a dialog box to select the jar files.

Step 7: Select all the jar files that you had obtained from Apache POI’s site and then click on Open.

Step 8: The Java Build Path will now begin to show all the jar files that you had selected. Just click on Apply and then Ok.

Steps on How to Read XLSX File in Java

Now that we have imported the pre-requisite Apache POI Files it is time to move on to our coding bit.

Step 1: Type the following piece of code specifying the location of the path:

File file = new File(PATHNAME);

Replace the PATHNAME with the actual location of your file. So mine would be something like this:

File file = new File("D:\\dc.xlsx");

Step 2: As soon as you type the above your Eclipse might flare an error on File Class. Simply import the package

import java.io.File;

Step 3: We need to instantiate FileInputStream to read from the file specified. Type the following:

 FileInputStream fis = new FileInputStream(file);

It might redden again. Just import the following package to get rid of the errors:

or simply manually type the below where you have written your previous import:

import java.io.FileInputStream;

Even though that error might no longer be there, you might face another towards  the RHS. On keeping the mouse there you realize the following error message flaring:

Just click on Surround with try/catch to handle it. If you wish to see how exceptions are handled you could check out that tutorial as well.

Sweet mother of Eclipse! It takes care of everything.

XSSFWorkbook Class

Step 4: Now time to make use of the XSSFWorkbook class. Just type the following inside the try block immediately after your FileInputStream code:

XSSFWorkbook wb = new XSSFWorkbook(fis);

Finding the error underline? Just keep your mouse cursor on it.

Step 5: Click on Import XSSFWorkbook to import the following packages:

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 6: The XSSFWorkbook class on the RHS might be still flaring an error. You need to catch the exception. Just click on it and it will automatically create an extra catch block handling the IOException.

As you can see it has automatically put a catch IOException in a separate catch block.

XSSFSheet Class

Now we need to address the Sheet and bring the focus to the required area. We can do so by using the XSSFSheet Class. Let’s instantiate it and use the XSSFWorkbook’s  getSheetAt(index) method.

Step 6: Type the following code now:

XSSFSheet sheet1 = wb.getSheetAt(0);

Here writing index as 0 will bring the control towards the first Sheet, i.e. Sheet1.

If you find an error red line flaring  underneath XSSFSheet, just import the following package:

or just type the following in the import area:

import org.apache.poi.xssf.usermodel.XSSFSheet;

Time to use the big gun (XSSFSheet instance) which we have declared as “sheet1” to actually start retrieving the actual cell data.

Popular Methods to Read Data from XLSX File

We will make use of  the following methods to access data from the XLSX file:

• getRow(int rownum)
• getCell(int cellnum)
• getStringCellValue()
• getNumericCellValue()

Follow up the aforementioned code by using the sheet1 instance.

Step 7: Just type the following to grab the cell value from column A and Row 1:

 System.out.println(sheet1.getRow(0).getCell(0).getStringCellValue());

Here getRow(0) will look into the first row, and getCell(0) will look into the first column, i.e. A1.

To retrieve the String value we are making use of getStringCellValue in the end.

So the whole code must look something like this:

If you run the above code you will get the following result:

URL

As you already know that’s the data on A1 in our sheet1 of dc.xlsx.

Let’s retrieve the remaining data by including the following code in the existing one:

 System.out.println(sheet1.getRow(0).getCell(1).getStringCellValue());
 System.out.println(sheet1.getRow(1).getCell(0).getStringCellValue());
 System.out.println(sheet1.getRow(1).getCell(1).getNumericCellValue());

As you can see we have used getNumericCellValue() method for retrieving a numeric value. If you use getStringCellValue there it wouldn’t work.

Running the code will give you now:

URL
Number
https://www.google.com
46.0

Don’t like the “.0” in the end? You can remove it by using downcasting in Java as explained in the Downcasting section of How to write a program to add two numbers.

 System.out.println((int) sheet1.getRow(1).getCell(1).getNumericCellValue());

Now that you will run the code you will get exactly what you were aiming for:

URL
Number
https://www.google.com
46

Here’s a glimpse at our final code:

I have just removed the extra Exceptions and replaced it with simply Exception to make our code look less ugly. 😛

Not working for you? Try using a different Apache POI version jar file.

Alright now that you know how to read xlsx file in Java, we will see how to write in one.

We will see that in the next chapter about writing in Excel Files using Java.