How can I access password protected Excel workbook in Java using POI api

Question

I want to read from and write to password protected Excel files. How can I do so using Apache POI API.

Answer 1

POI should be able to open both protected xls files (using org.apache.poi.hssf.record.crypt) and protected xlsx files (using org.apache.poi.poifs.crypt). Have you tried these?

If you're using HSSF (for a xls file), you need to set the password before opening the file. You do this with a call to:

 org.apache.poi.hssf.record.crypto.Biff8EncryptionKey.setCurrentUserPassword(password);

After that, HSSF should be able to open your file.

For XSSF, you want something like:

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("protect.xlsx"));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = new Decryptor(info);
d.verifyPassword(Decryptor.DEFAULT_PASSWORD);
XSSFWorkbook wb = new XSSFWorkbook(d.getDataStream(fs));

.

Alternately, in newer versions of Apache POI, WorkbookFactory supports supplying the password when opening, so you can just do something like:

Workbook wb = WorkbookFactory.create(new File("protected.xls"), "password");

That will work for both HSSF and XSSF, picking the right one based on the format, and passing in the given password in the appropriate way for the format.

Answer 2

If the entire workbook is password protected (by going through the Excel menu File > Save As... > Tools > General Options... then supplying a password) then the file is encrypted and you cannot read from and write to the workbook through POI.