2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Open and parse password protected excel files

I have an excel spreadsheet that is password-protected. I need to open this spreadsheet and read the data from it.

I am using Apache POI to this activity and search a lot the solution but failure. May be this feature is not available in Apache POI API.

I need a better solution to do this job.

userimage

You can use JExcelApi. Here you can find the sample code for this.


Workbook workbook = Workbook.getWorkbook(new File("/path/to/protected.xls"));
workbook.setProtected(false);
WritableWorkbook copy = Workbook.createWorkbook(new File("/path/to/unprotected.xls"), workbook);
WritableSheet[] sheets = copy.getSheets();

for (WritableSheet sheet : sheets){
    sheet.getSettings().setProtected(false);
}

copy.write();
copy.close();

userimage

EncryptionInfo was introduced in POI in v3.7 or v3.8. And it 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).

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));


For complete details you can visit POI Encryption documentation page

Answer is