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

Java

import org.testng.annotations.Test;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Iterator;
 
import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class Geeks {

    @Test

    public void geeksforgeeks() throws IOException{

         FileInputStream file = new FileInputStream("C:\\Users\\ADMIN\\Desktop\\geeks.xlsx");

         String password="geeks";

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

         XSSFSheet sheet=workbook.getSheet("Sheet1");

         Iterator<Row> iterator=sheet.iterator();

         while(iterator.hasNext()) {

            Row nextrow=iterator.next();

            Iterator<Cell> celliterator=nextrow.cellIterator();

            while(celliterator.hasNext()) {

                Cell cell=celliterator.next();

                switch(cell.getCellType())

                {

                case STRING:

                    System.out.print(cell.getStringCellValue()); break;
 
                case NUMERIC:

                    System.out.print(cell.getNumericCellValue()); break;
 
                }

                System.out.print("|");

            }

          System.out.println();

        }  

    }
}

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.