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

I want to read from and write to password p开发者_开发百科rotected Excel files. How can I do so using Apache POI API.

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.

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.

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

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

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.

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

None of the provided solutions worked for me for xlsx file, After various trial and errors I found this working.

Below is my working solution with poi-4.0.1.jar & poi-ooxml-4.0.1.jar

private OutputStream getEncryptingOutputStream(POIFSFileSystem fileSystem, String password) throws IOException, GeneralSecurityException
{
    EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.standard);
    Encryptor encryptor = encryptionInfo.getEncryptor();
    encryptor.confirmPassword(password);
    return encryptor.getDataStream(fileSystem);
}

private void encryptSpreadSheet(String sourcePath, String destinationPath) throws IOException, GeneralSecurityException, InvalidFormatException
{
    try (POIFSFileSystem fileSystem = new POIFSFileSystem())
    {
      //sourcePath = "/var/tmp/dummy.xlsx"
      //destinationPath = "/var/tmp/dummy_encrypted.xlsx"
      try (Workbook wb = WorkbookFactory.create(new File(sourcePath));
            OutputStream out = getEncryptingOutputStream(fileSystem, "password");)
      {
         wb.write(out);
      }

      try (FileOutputStream fileOutputStream = new FileOutputStream(new File(destinationPath)))
      {
          fileSystem.writeFilesystem(fileOutputStream);
      }
    }
}

Please use the below code to save excel file password protected using Apache POI.

FileOutputStream outputStream = null;
XSSFWorkbook workbook = new XSSFWorkbook();

try {
    outputStream = new FileOutputStream("Test.xlsx");

    XSSFSheet shtSummary = workbook.createSheet("Test");


    workbook.write(outputStream);
  } catch (Exception ex) {
        ex.printStackTrace();
  } finally {
        outputStream.close();
  }

  POIFSFileSystem fs = new POIFSFileSystem();
  EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
  Encryptor enc = info.getEncryptor();
  enc.confirmPassword("mypassword");

  OPCPackage opc = OPCPackage.open(new File("Test.xlsx"), PackageAccess.READ_WRITE);
  OutputStream os = enc.getDataStream(fs);
  opc.save(os);
  opc.close();

  FileOutputStream fos = new FileOutputStream("Test.xlsx");
  fs.writeFilesystem(fos);
  fos.close();

This code works great. This solution is for .xslx format.

The below code is for .xls format.

String fname = "Test.xls";

FileInputStream fileInput = null;
BufferedInputStream bufferInput = null;
POIFSFileSystem poiFileSystem = null;
FileOutputStream fileOut = null;

try {
    fileInput = new FileInputStream(fname);
    bufferInput = new BufferedInputStream(fileInput);
    poiFileSystem = new POIFSFileSystem(bufferInput);

    Biff8EncryptionKey.setCurrentUserPassword("mypassword");
    HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);
    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFRow row = sheet.createRow(0);
    Cell cell = row.createCell(0);

    cell.setCellValue("THIS WORKS!");

    fileOut = new FileOutputStream(fname);
    workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), "");
    workbook.write(fileOut);
}
catch (Exception ex) {

    System.out.println(ex.getMessage());
}
finally {
    try {

        bufferInput.close();
    }
    catch (IOException ex) {

        System.out.println(ex.getMessage());
    }

    try {

        fileOut.close();
    }
    catch (IOException ex) {

        System.out.println(ex.getMessage());
    }
}

Both the solution works for me. It's a great solution which I found working.