CODEWITHSUNDEEP
javaexcelapache-poixlsxpassword-protection
yaki33
yaki33

Reputation: 65

How to Protect Excel Workbook Via SXSSF?

I have a program that generates reports with a large amount of data. I got things working properly using the Apache POI SXSSF to generate a xlsx file. http://poi.apache.org/spreadsheet/index.html

The thing that I couldn't find in their documentation is how to password protect the ENTIRE WORKBOOK. I want it so that if someone tries to open the file they need to enter a password in order to see the data.

Keep in mind that this is different than password protecting a single worksheet where they are still able to open the file and see the data but have read only access.

I didn't find anything in the SXSSFWorkbook documentation: https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html

Looks like there is a method for XSSFWorkbook called setWorkbookPassword but that doesn't exist for SXSSF and didn't work on the SXSSFWorkbook. https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#setWorkbookPassword(java.lang.String,%20org.apache.poi.poifs.crypt.HashAlgorithm)

Anyone know how this can be done? Alternative workarounds will also be considered.

Thanks in advance.

UPDATE

I thought about maybe using a macro enabled workbook with script to password protect it as suggested here. Java Apache Poi SXSSF with Excel Macros

And I used the VBA code from here to do that: http://analysistabs.com/excel-vba/protect-unprotect-workbook/ and then use that file as a template when creating the Excel file but while I was playing around with the Macro it turns out it's not sufficient. Some computer security settings are set to "High" and will disable macros so when I opened the file, I did get a prompt for password but then I also got a warning message that said that Macros are disabled and I was able to view the workbook contents.

Any suggestions?

Upvotes: 0

Views: 3269

Answers (2)

Durgababu Balepalli
Durgababu Balepalli

Reputation: 1

/**
 * create a new POIFSFileSystem, which is the container to encrypt xml based files.
 * Setup encryption and pass your respected password.
 * Take encrypted output stream and Encrypt the work book.
 * Finally write the encrypted version of workbook to Filesystem
 */
public static void getExcelEncryption(XSSFWorkbook workbook,HttpServletResponse response,String password) {
    try (POIFSFileSystem fs = new POIFSFileSystem()) {
        EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
        Encryptor enc = info.getEncryptor();
        String paswordForExcel = !password.isEmpty() ? password : "";
        enc.confirmPassword(paswordForExcel);
        try (OutputStream os = enc.getDataStream(fs)) {
            workbook.write(os);
        } catch (Exception e) {
            logger.error("OutputStream error while encrypting excel : {}",e.getMessage());
        }

        fs.writeFilesystem(response.getOutputStream());
    } catch (Exception e) {
        logger.error("POIFSFileSystem error while encrypting excel : {}",e.getMessage());
    }
}

Upvotes: 0

kiwiwings
kiwiwings

Reputation: 3446

" ... I couldn't find in their documentation is how to password protect ..."???

have you seen that menu entry "Encryption support" in the left menu on the poi main page?

to request a password prompt when opening (i.e. reading) the file, you need to encrypt it - see "XML-based formats - Encryption"

and as stackoverflow likes to have everything in one place - here is the code:

// create a new POIFSFileSystem, which is the container for 
// encrypted OOXML-based files  
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);

// setup the encryption
Encryptor enc = info.getEncryptor();
enc.confirmPassword("foobaa");

// after writing to the SXSSF workbook you need the content
// as a stream
InputStream is = <open the SXSSF workbook as stream>
OutputStream os = enc.getDataStream(fs);
// copy the stream to the OutputStream
IOUtils.copy(is, os);

// Write out the encrypted version
FileOutputStream fos = new FileOutputStream("...");
fs.writeFilesystem(fos);
fos.close();

Upvotes: 3

Related Questions