Last Updated:

Reading an Excel (xls, xlsx) file in Java using Apache POI

Recently, I had to programmatically parse .xls and .xlsx files. To parse an Excel file, you needed the Apache POI library. With this library, you can parse not only files in .xls format, but also DOC, PPT, and formats that appeared in the 2007 version of Microsoft Office.

In this article, we will learn how to read data from or a file in Java using the Apache POI library. As always, a little theory on the basics and practice on the example of reading a simple file. An example of creating a new Excel file is shown here.

xlsxlsxxls

Connect the library to work with Excel in Java

First, you need to create a Maven project and in the pom file.xml write the following dependency code

 

Reading an Excel file in Java

The Apache POI library provides an easy-to-use API for reading any xls file. Below we will look at the most used classes to read the contents of Excel spreadsheets:

Code to read Excel documents in xls format

The following listing shows a typical example of initializing and reading data from files.

HSSFWorkbookHSSFSheet.xls

Code for reading Excel documents in .xlsx format

The following is a code snippet for initializing working with Excel files in the format:.xlsx

Practice. Create a simple Excel for work

Writing a parser in Java

Let's name the ExcelParser class.java with the parse method, which takes the text parameter fileName

 

Create the main class-runner, where we will run the parser:

 

Excel Parser Result

 

Working with a password-protected Excel file

In Apache POI, with each new version, new features are added to work with closed/password files. For example, we can work with protected XLS files (using ) and protected XLSX files (using ).

org.apache.poi.hssf.record.cryptorg.apache.poi.poifs.crypt

If you are using HSSF (for an XLS file), then the isWriteProtected() method will help us to check whether it is passworded. And to work with it, you need to specify a password just before opening the file:

After that, we can work with the file.

For us, we'll need something like this:XSSF

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("protected.xlsx"));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = new Decryptor(info);
d.verifyPassword(Decryptor.DEFAULT_PASSWORD);
XSSFWorkbook wb = new XSSFWorkbook(d.getDataStream(fs));

When working with newer versions of Apache POI, you can simply specify a password when creating:Workbook

This code will work for both HSSF and XSSF.