How can I access password protected Excel workbook

问题:

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

回答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.

回答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.

回答3:

CODE:

FileInputStream fileInput = new FileInputStream("2.xls");         
BufferedInputStream bufferInput = new BufferedInputStream(fileInput);            
POIFSFileSystem poiFileSystem = new POIFSFileSystem(bufferInput);            
Biff8EncryptionKey.setCurrentUserPassword("test"); 
HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true); 

jars used:

• commons-codec-1.5.jar
• commons-logging-1.1.jar
• dom4j-1.6.jar
• jsr173_1.0_api.jar
• junit-4.11.jar
• log4j-1.2.13.jar
• poi-3.10-FINAL-20140208.jar
• poi-excelant-3.10-FINAL-20140208.jar
• poi-ooxml-3.10-FINAL-20140208.jar
• poi-ooxml-schemas-3.10-FINAL-20140208.jar
• resolver.jar
• xbean.jar
• xbean_xpath.jar
• xmlbeans-qname.jar
• xmlpublic.jar

回答4:

The official documentation link provides in detail the encryption and decryption possibilities f for xls,xlsx and other supported formats: http://poi.apache.org/encryption.html

i.e. In case of Binary Formats(xls):

// XOR/RC4 decryption for xls
Biff8EncryptionKey.setCurrentUserPassword("pass");
NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("file.xls"), true);
HSSFWorkbook hwb = new HSSFWorkbook(fs.getRoot(), true);
Biff8EncryptionKey.setCurrentUserPassword(null);

For XML based formats (xlsx):

EncryptionInfo info = new EncryptionInfo(filesystem);
Decryptor d = Decryptor.getInstance(info);

try {
    if (!d.verifyPassword(password)) {
        throw new RuntimeException("Unable to process: document is encrypted");
    }

    InputStream dataStream = d.getDataStream(filesystem);

    // parse dataStream

} catch (GeneralSecurityException ex) {
    throw new RuntimeException("Unable to process encrypted document", ex);
}

回答5:

Apache POI is good api for excel processing. But POI api provides only option to read the password protected excel files. No option for writing the excel file with pasword.

Create a password protected excel file using apache poi?

Check this thread for know more about password protected excel.

回答6:

try 
{
    InputStream is = new FileInputStream("myfile.xlsx"); 
    POIFSFileSystem pfs = new POIFSFileSystem(is);

    EncryptionInfo info = new EncryptionInfo(pfs);
    Decryptor d = Decryptor.getInstance(info);

    if (!d.verifyPassword("passwordForFile")) 
    {
        System.out.println("Incorrect password!");
    }
    else 
    {
        System.out.println("Correct password!");

        //InputStream dataStream = d.getDataStream(pfs);

        // parse dataStream
    }
}
catch (Exception ex) 
{
    throw new RuntimeException("Unable to process encrypted document", ex);
}