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.
Table of Contents
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
- Create a Workbook object. eg.
Workbook workbook = WorkbookFactory.create(file, password); - Read specific sheet of workbook. eg.
Sheet sheet = workbook.getSheetAt(0); - Iterate each row of sheet. eg.
Iterator<Row> iterator = sheet.iterator(); - Iterate each cell of row. eg.
Iterator<Cell> cellIterator = row.cellIterator(); - 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.