Java Example to Read Password-protected Excel Files Using Apache POI

Details
Written by  
Last Updated on 30 May 2019   |   Print  Email


This Java tutorial shows you how to read a password-protected Microsoft Excel file using Apache POI - a Java library dedicated for manipulating Microsoft Office documents. There’s a slightly difference between reading password-protected Excel 2003 and Excel 2007 formats, but the WorkbookFactory makes things simple.

Now, let’s explore the details.

 

1. Reading Password-protected Excel 2003 Document (XLS)

To open an Excel 2003 document which is secured by a password at the document level, use the following statement before opening a Workbook:

Biff8EncryptionKey.setCurrentUserPassword(String password);

So the code will look something like this:

NPOIFSFileSystem fileSystem = new NPOIFSFileSystem(new File("..."), true);
Biff8EncryptionKey.setCurrentUserPassword("...");
Workbook workbook = new HSSFWorkbook(fileSystem);

Then you can proceed reading as regular Excel file.

Here’s the content of an Excel 2003 document which is protected with password ‘nimda’:

Excel 2003 doc password protected

And the following is code of a small program that reads content of the first sheet in the above Excel document:

package net.codejava.excel;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
 * This program illustrates how to read a password-protected Excel document
 * in 2003 format - XLS
 * @author www.codejava.net
 *
 */
public class Excel2003PasswordReaderExample {
	public static void main(String[] args) throws IOException {
		String excelFilePath = "Contracts.xls";
		boolean readOnly = true;
		
		NPOIFSFileSystem fileSystem = new NPOIFSFileSystem(new File(excelFilePath), readOnly);
		
		Biff8EncryptionKey.setCurrentUserPassword("nimda");
		
		Workbook workbook = new HSSFWorkbook(fileSystem);
		
		Sheet firstSheet = workbook.getSheetAt(0);
		Iterator<Row> iterator = firstSheet.iterator();
		
		while (iterator.hasNext()) {
			Row nextRow = iterator.next();
			Iterator<Cell> cellIterator = nextRow.cellIterator();
			
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				
				switch (cell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						System.out.print(cell.getNumericCellValue());
						break;
				}
				System.out.print("\t");
			}
			System.out.println();
		}
		
		workbook.close();
		fileSystem.close();
	}
}

Running this program would print the following output:

Company Name	Contract Number	Contract Value	
Microsoft	1.123456789E10	1.0E8	
Oracle	2.958191059E10	6.0E7

 

2. Reading Password-protected Excel 2007 Document (XLSX)

The process to read a password-protected Excel in XLSX format is like the following code snippet:

NPOIFSFileSystem fileSystem = new NPOIFSFileSystem(new File("..."), true);
EncryptionInfo info = new EncryptionInfo(fileSystem);
Decryptor decryptor = Decryptor.getInstance(info);
if (!decryptor.verifyPassword("...")) {
	throw new RuntimeException("Unable to process: document is encrypted.");
}
InputStream dataStream = decryptor.getDataStream(fileSystem);
Workbook workbook = new XSSFWorkbook(dataStream);

Suppose that we have an Excel 2007 protected with password ‘twentysixteen’ which looks like the following screenshot:

Excel 2007 doc password protected

And here is a small Java program that reads the above XLSX document with the provided password:

package net.codejava.excel;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.security.GeneralSecurityException;
import java.util.Iterator;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * This program illustrates how to read a password-protected Excel document
 * in 2007 format - XLSX.
 * @author www.codejava.net
 *
 */
public class Excel2007PasswordReaderExample {
	public static void main(String[] args) {
		String excelFilePath = "Customers.xlsx";
		
		try {
			NPOIFSFileSystem fileSystem = new NPOIFSFileSystem(new File(excelFilePath));
			EncryptionInfo info = new EncryptionInfo(fileSystem);
			Decryptor decryptor = Decryptor.getInstance(info);
			
			if (!decryptor.verifyPassword("twentysixteen")) {
				System.out.println("Unable to process: document is encrypted.");
				return;
			}
			
			InputStream dataStream = decryptor.getDataStream(fileSystem);
			
			Workbook workbook = new XSSFWorkbook(dataStream);
			Sheet firstSheet = workbook.getSheetAt(0);
			Iterator<Row> iterator = firstSheet.iterator();
			
			while (iterator.hasNext()) {
				Row nextRow = iterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();
				
				while (cellIterator.hasNext()) {
					Cell cell = cellIterator.next();
					System.out.print(cell.getStringCellValue() + "\t");
				}
				System.out.println();
			}
			
			workbook.close();
			dataStream.close();		
			fileSystem.close();
		} catch (GeneralSecurityException | IOException ex) {
			ex.printStackTrace();
		}
	}
}

Output:

Customer Name	Email Address	
Alice Smith	alicesm@gmail.com	
Biran Young	birany@google.com	
Carol Bush	carolb@us.gov

 

3. Reading Password-protected Excel file using the WorkbookFactory

As we can see, there are two different ways for reading the XLS and XLSX formats which is not convenient. Fortunately, the Apache POI library provides the WorkbookFactory utility class that makes our lives easier by encapsulating the boilerplate code in simple factory methods.

The code will be very simple like this:

Workbook workbook = WorkbookFactory.create(new File(excelFilePath), password);
Sheet firstSheet = workbook.getSheetAt(0);

The create() method detects the corresponding format and return a Workbook object represents the document, so we don’t have to deal with format type XLS or XLSX any more.

For example, the following program can read any password-protected Excel document without dealing with the format difference:

package net.codejava.excel;

import java.io.File;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * This Java program illustrates reading a password-protected Excel document
 * in both format XLS and XLSX.
 * @author www.codejava.net
 *
 */
public class ExcelPasswordReaderExample {

	public static void main(String[] args) {
		String excelFilePath = "Customers.xlsx";
		String password = "twentysixteen";
		
		try {
			Workbook workbook = WorkbookFactory.create(new File(excelFilePath), password);
			Sheet firstSheet = workbook.getSheetAt(0);
			Iterator<Row> iterator = firstSheet.iterator();

			while (iterator.hasNext()) {
				Row nextRow = iterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();

				while (cellIterator.hasNext()) {
					Cell cell = cellIterator.next();

					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						System.out.print(cell.getBooleanCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						System.out.print(cell.getNumericCellValue());
						break;
					}
					System.out.print("\t");
				}
				System.out.println();
			}

			workbook.close();
		} catch (EncryptedDocumentException | IOException 
				| InvalidFormatException ex) {
			ex.printStackTrace();
		}
	}
}

 

Maven dependency for Apache POI:

If you are using Maven, add the following XML snippet to project’s pom.xml file (3.15 is the latest version of Apache POI when this article is being written):

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

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

Otherwise you need to add the poi-VERSION.jar and poi-ooxml-VERSION.jar to the project’s classpath.

You can download the full source code under the Attachments section below.

 

Related Java Excel Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.



Attachments:
Download this file (JavaExcelPasswordReaderExamples.zip)JavaExcelPasswordReaderExamples.zip[Reading Password-protected Excel files Examples]3 kB

Add comment

   


Comments 

#4Anurag2021-02-02 08:10
Nice article, could you please tell me what if I don't have a password for the excel fileQuoting gumuruh:
what if we don't know the password encrypted file?
is it possible to remove it?
Quote
#3chithra2019-11-12 00:47
Very useful..thank u
Quote
#2gumuruh2019-09-21 03:59
what if we dont know the password encrypted file?
is it possible to remove it?
Quote
#1piyush2017-08-10 02:54
I WANT LEARN PROGRAMMING
Quote