How to Read password-protected Excel in java?

This article shows you how to read password-protected Microsoft Excel documents using Apache POI in java. Microsoft Excel documents generally come in two different formats Excel 97(-2003) and Excel(2007+). ie XLS and XLSX formats.

Reading protected documents is format-dependent and needs to be implemented per format differently.

But using WorkbookFactory class, we would write a generic implementation for reading Excel(XLS and XLSX) file formats.

Reading password-protected XLS file in Java

Use Biff8EncryptionKey.setCurrentUserPassword(String password) to specify the decryption password before reading the workbook of the excel file.

Let’s see the below example to read-protected XLS files. Here the excel file is protected with “javacodepoint” password,

package com.javacodepoint.excel;

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

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ReadProtectedXLSFile {

	public static void main(String[] args) throws IOException {

		// Creating a xls file object with specific file path
		File xlsFile = new File("E:/Excel/protected.xls");

		// Providing the file password = 'javacodepoint'
		// It should be called before start reading the workbook
		Biff8EncryptionKey.setCurrentUserPassword("javacodepoint");

		try (FileInputStream inputStream = new FileInputStream(xlsFile);
				HSSFWorkbook workbook = new HSSFWorkbook(inputStream)) {

			// Reading the first sheet of the excel file
			HSSFSheet sheet = workbook.getSheetAt(0);

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

			// Iterating all the rows
			while (iterator.hasNext()) {
				Row nextRow = iterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();

				// Iterating all the columns in a row
				while (cellIterator.hasNext()) {

					Cell cell = cellIterator.next();

					switch (cell.getCellType()) {
					case STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case BOOLEAN:
						System.out.print(cell.getBooleanCellValue());
						break;
					case NUMERIC:
						System.out.print(cell.getNumericCellValue());
						break;
					default:
						break;
					}
					System.out.print(" | ");
				}
				System.out.println();
			}

		}

	}

}

Reading password-protected XLSX file in Java

To read a password-protected XLSX file is a little complex than reading a protected XLS file. Below is the sample code snippet to understand this,

POIFSFileSystem fs = new POIFSFileSystem(xlsxFile);
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor decryptor = Decryptor.getInstance(info);
             
 //Verifying the password
 if (!decryptor.verifyPassword("javacodepoint")) {
        throw new RuntimeException("Incorrect password: Unable to process");
 }
             
InputStream dataStream = decryptor.getDataStream(fs);
 
// Now parse dataStream

Let’s see the below complete example to read-protected XLSX files. Here also the excel file is protected with “javacodepoint” password,

package com.javacodepoint.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.POIFSFileSystem;
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.xssf.usermodel.XSSFWorkbook;

public class ReadProtectedXLSXFile {

	public static void main(String[] args) {

		// Creating a xlsx file object with specific file path to read
		File xlsxFile = new File("E:/Excel/protected.xlsx");
		
		try {
			POIFSFileSystem fs = new POIFSFileSystem(xlsxFile);
			EncryptionInfo info = new EncryptionInfo(fs);
			Decryptor decryptor = Decryptor.getInstance(info);
			
			//Verifying the password
			if (!decryptor.verifyPassword("javacodepoint")) {
				throw new RuntimeException("Incorrect password: Unable to process");
			}
			
			InputStream dataStream = decryptor.getDataStream(fs);

			// Now parse dataStream
			
			XSSFWorkbook workbook = new XSSFWorkbook(dataStream);
			// Reading the first sheet of the excel file
			Sheet sheet = workbook.getSheetAt(0);

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

			// Iterating all the rows
			while (iterator.hasNext()) {
				Row nextRow = iterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();

				// Iterating all the columns in a row
				while (cellIterator.hasNext()) {
					Cell cell = cellIterator.next();
					switch (cell.getCellType()) {
					case STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case BOOLEAN:
						System.out.print(cell.getBooleanCellValue());
						break;
					case NUMERIC:
						System.out.print(cell.getNumericCellValue());
						break;
					default:
						break;
					}
					System.out.print(" | ");
				}
				System.out.println();
			}
			workbook.close();
		} catch (GeneralSecurityException | IOException ex) {
			throw new RuntimeException("Unable to process encrypted document", ex);
		}
	}
}

Generic logic for Reading both XLS and XLSX file

In Apache POI, WorkbookFactory is a factory class available for creating the appropriate kind of Workbook (eg. HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.

For example- WorkbookFactory.create(java.io.File file, java.lang.String password);

Steps to Read protected XLS and XLSX file

  1. Create a Workbook object. eg. Workbook workbook = WorkbookFactory.create(file, password);
  2. Read specific sheet of workbook. eg. Sheet sheet = workbook.getSheetAt(0);
  3. Iterate each row of sheet. eg. Iterator<Row> iterator = sheet.iterator();
  4. Iterate each cell of row. eg. Iterator<Cell> cellIterator = row.cellIterator();
  5. Read cell data based on CellType.

Let’s see the below complete example to read-protected Excel(XLS and XLSX) file:

package com.javacodepoint.excel;

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

import org.apache.poi.EncryptedDocumentException;
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;

public class ReadProtectedExcelFile {
	public static void main(String[] args) throws IOException {

		// File object with specific path to read
		File xlsxFile = new File("E:/Excel/protected.xlsx");

		try {

			// Creating workbook using WorkbookFactory with password
			// It works for both excel format xls and xlsx
			Workbook workbook = WorkbookFactory.create(xlsxFile, "javacodepoint");

			// Reading the first sheet of the excel file
			Sheet sheet = workbook.getSheetAt(0);

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

			// Iterating all the rows
			while (iterator.hasNext()) {
				Row nextRow = iterator.next();
				Iterator<Cell> cellIterator = nextRow.cellIterator();

				// Iterating all the columns in a row
				while (cellIterator.hasNext()) {

					Cell cell = cellIterator.next();

					switch (cell.getCellType()) {
					case STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case BOOLEAN:
						System.out.print(cell.getBooleanCellValue());
						break;
					case NUMERIC:
						System.out.print(cell.getNumericCellValue());
						break;
					default:
						break;
					}
					System.out.print(" | ");
				}
				System.out.println();
			}

			workbook.close();
		} catch (EncryptedDocumentException | IOException ex) {
			throw new RuntimeException("Unable to process encrypted document", ex);
		}
	}
}

Conclusion

In this article, you have seen how to read a password-protected Excel document. For the different Excel file formats, we have seen different-different implementations and we use WorkbookFactory to implement a generic logic for both file formats.


Related Articles

Share with friends

Leave a Comment

Your email address will not be published.