How to Read Data from Password Protected Excel using Java and Apache POI?

Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs for the job. In this article, we are going to discuss how to read the data from the password-protected Excel sheet using the Apache POI. For any protected Excel file, we needed to automate it and ask for the password, in the beginning, to handle us using the Apache POI.

Pre-requisites:

• Create the maven project in the Eclipse.

For creating a maven project the article check out this article

• Add the Apache POI  and poi-ooxml dependency in the POM.xml file

Apache POI:

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>5.2.2</version>
</dependency>
 

Apache poi-ooxml

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>5.2.2</version>
</dependency>
 

Example

Let us have an Excel file that is protected by the password "geeks". It has the following data in it.

We have to open the protected file and read the data in the file using Apache POI. For opening protected file Apache POI provides the following method "workbookfactory":

XSSFWorkbook workbook=(XSSFWorkbook)WorkbookFactory.create(file,password);

Program for reading data from Password protected Excel using Java and Apache POI

Code Explanation

• Open the Excel file in the input stream and store the Password as a string.
• Apache POI provides the method workbookfactory for opening the protected files, in this method we are passing the file and password.

WorkbookFactory.create(file,password);

• Now read the cell data using Iterator.
• Print the data in the console based on the data type using a switch case.

Output

After we run the code, we get all the cell values in the console as mentioned below.