Wednesday, May 16, 2012

Excel Workbook Encryption from Java

UPDATE 2014/11/10: This is now possible for XML based formats like XLSX only as per http://poi.apache.org/encryption.html but if you want to support encryption in old non XML like XLS your only free option is the below. Bad news, POI nor JExcel API support Excel Workbook password protection so far.

When you think about it though if you are trying to automate the generation of password protected Excel files you will need to store somewhere the password you are using to encrypt the workbook (Granted you are supposed to store that encrypted as well) so a workaround for the issue would be to store not only the password but also an empty Excel Workbook protected with that same password. POI does allow to access a Password Protected File so you can load the protected empty workbook and do all manipulations on it. The result will be an Excel file which holds the expected encrypted content.

On the other hand POI supports Write Password Protection. So if that is what you are looking for then this code will do the trick:
package com.nestorurquiza.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class XlsUtil {
    public static void passwordProtect( InputStream is, OutputStream os, String password ) throws IOException {
        HSSFWorkbook targetWorkbook = new HSSFWorkbook(is);
        int numberOfSheets = targetWorkbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            HSSFSheet sheet = targetWorkbook.getSheetAt(i);
            sheet.protectSheet(password);
        }
        targetWorkbook.write(os);
    }
    
    public static void main(String arg[]) {
        try{
            InputStream is = (new FileInputStream("/Users/nestor/Downloads/unprotected.xls"));
            FileOutputStream os =new FileOutputStream(new File("/Users/nestor/Downloads/protected.xls"));
            passwordProtect(is, os, "testPassword");
            os.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

}

2 comments:

Todesbote said...

Hi, I think your code is the solution for my problem. But if I try to use the code I get some Errors.
java.io.IOException: Unable to read entire header; 0 bytes read; expected 512 bytes

at org.apache.poi.poifs.storage.HeaderBlock.alertShortRead(HeaderBlock.java:226)

at org.apache.poi.poifs.storage.HeaderBlock.readFirst512(HeaderBlock.java:207)

at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)

at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138)

at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:322)

at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:303)

at XlsUtil.passwordProtect(XlsUtil.java:16)

at XlsUtil.main(XlsUtil.java:29)

Do I need to use the code the package com.nestorurquiza.utils?
Where can I get it?

Can you help me please?

Nestor Urquiza said...

@Todesbote I don't have more code than this really. The code is just showing how you can protect the workbook manually and then use it later as a template to create the final workbook you really want. It would look to me like you are trying to load an invalid workbook. Note the HSSF is used for XLS while XSSF is used for XLSX workbooks. This example is for XLS workbooks but you should use this trick for XLSX provided you use the right POI API.

Followers