Create a password protected Excel file using Apache POI?

Question

I am developing a simple Java program to create an Excel file using (Apache POI) API. I am using Oracle 10g as a database and using the ojdbc14 JAR file.

I have a table called USERINFO, having three columns, namely USERNAME, PASSWORD, and NAME. Now using Apache POI, I have been able to put all the rows in an Excel file.

Since the file contains sensitive data such as username and password, I want to make it password protected. On forums, I have found how to read password protected files, but not how to create them. So how I can achieve this?

Gielgud

Answer 1

Updated: As of version 3.10 POI supports encryption as well as decryption for XLSX files. See the "Encryption Support" page on POI's website. The below is still relevant for XLS binary workbooks.

According to the "Encryption Support" page on POI's website POI supports reading encrypted XLS and XLSX files. Encrypting is not mentioned on that page, which implies that it's not supported. This is backed up by searching the POI site for "encrypt" which returns only a handful of results all of which are about decryption. I've also taken a look at the sources for their crypto implementation, which appears to only handle decryption. This isn't surprising; POI is designed for data extraction and search indexing, not for creating new spreadsheets.

As others have suggested, it's often possible to work around missing features in POI by creating a template in Excel and then using POI to populate it with data. Unfortunately that won't work for encryption because the file format of encrypted spreadsheets is radically different.

If you're willing to pay for commercial software, the latest version of ExtenXLS has full read and write support for all the encryption formats supported by Excel. Just construct an EncryptedWorkBookHandle instead of the normal WorkBookHandle. That will use the strongest possible cipher supported by an unmodified JRE, RC4 for XLS and 128-bit AES for XLSX. If you want to use 256-bit AES with OOXML and you've installed the JCE unlimited policy you can do so with the MSOfficeEncrypter class.

JExcelAPI, a popular open-source Java spreadsheet API, does not appear to support encryption at all. Aspose.Cells, a commercial offering, supports stong encryption. The documentation for Actuate's e.Spreadsheet seems to have disappeared from the 'net, so I can't tell whether it supports encryption or not.

Since none of the freely available Java spreadsheet APIs seems to support writing encrypted spreadsheets, if you're not willing to use commercial software you'll need to come up with a workaround. You could, for example, write the spreadsheet into an encrypted ZIP file. java.util.zip doesn't support encryption, but it looks like Zip4j does.

Full disclosure: I work for Extentech, the company behind ExtenXLS.

Teleplay

I've added the encryption support for xml-based files to POI recently, so it would be nice, if you update your answer, i.e. the first paragraph is out-dated. regarding the design for extraction: most POI-apis starting with reading support of a feature, but I'm not agreeing that POI is specifically designed for extraction (this was also valid back in Jan. 2012 in my point of view) – Harleyharli

Agree with kiwiwings.was mislead by this answer and checking latest docs showed there is encryption support. poi.apache.org/encryption.html Please update the answer. – Millhon

@Harleyharli I updated it, finally. FWIW you don't need to wait for the original author to update things. StackOverflow is wiki-ish, so you can always propose an edit. – Teleplay

Answer 2

Create a password protected Excel file or use an existing template and make it password protected. This will give the users a "read only" access though. Here's an example where I have an Excel file that has a password, "secret":

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

public class ProtectedExcelFile {

    public static void main(final String... args) throws Exception {

        String fname = "C:\\Documents and Settings\\sadutta\\Desktop\\sample.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("secret");
            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());
            }
        }
    }
}

The same way you should be able to write or modify the existing template that you have. After you are done, overwrite the template. If your template should be used many times, you may want to copy the template to some other location and then use the code to modify it.

Pucker

Note that while this code does create an Excel protected workbook, the resulting file is not encrypted. The password is hashed and stored in the otherwise unchanged file. It is up to the software reading the file to enforce this kind of protection. With proper encryption it is impossible to read the file without the password. – Teleplay

Though I generated excel file with password protected. its not opening via excel tool. Im able to open with Plan Maker tool. did anyone face this? – Ossieossietzky

Answer 3

I've often found with POI that to do more complex stuff, a useful approach is to create the spreadsheet in Excel with the advanced features (e.g. macros), then use POI to read the spreadsheet, populate it and write it out. POI will normally maintain the spreadsheet features and add the data.

I've not tried this for passwords, but I suspect it's worth an experiment.

See the busy developer's guide for more info.

Gorgonian

Hi Brian, i have no idea about macros or advanced features. Can you give me some link where i can get some help. I am a beginner to POI. – Gielgud

All I'm suggesting is creating the spreasheet in Excel with the capabilities you require, then use POI to read it and populate it. i.e. it's very similar to what you're doing but with an existing spreadsheet rather than a new one – Gorgonian

you have told me an optional method of doing it. What i have asked in question is just as an example. Actually in my project there are many tables , so it will be difficult if i create excel file manually for each table. – Gielgud

Downvoted why ? If you know the above approach doesn't work, then let me know and I'll delete/amend as required – Gorgonian

Answer 4

The Following Program will generate the password protected excel file in given excel path

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.security.GeneralSecurityException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class MyTest1 {
    public static void main(String[] args) {
        File file = new File("C:\\Users\\Raju\\Desktop\\workbook1.xlsx");
        try {
            file.createNewFile();
            OutputStream fileOut = new FileOutputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Venu");
            wb.write(fileOut);
            wb.close();
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try (POIFSFileSystem fs = new POIFSFileSystem()) {
            EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
            // EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile,
            // CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);
            Encryptor enc = info.getEncryptor();
            enc.confirmPassword("hello");
            // Read in an existing OOXML file and write to encrypted output stream
            // don't forget to close the output stream otherwise the padding bytes aren't
            // added
            try (OPCPackage opc = OPCPackage.open(file, PackageAccess.READ_WRITE);
                    OutputStream os = enc.getDataStream(fs)) {
                opc.save(os);
            } catch (InvalidFormatException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (GeneralSecurityException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            // Write out the encrypted version
            try (FileOutputStream fos = new FileOutputStream(file)) {
                fs.writeFilesystem(fos);
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (IOException e2) {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        }
        System.out.println("Excel file exported");
    }
}

Used maven dependency

<dependency>
 <groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>

Yes, I am aware that we can optimize so much code. At high level the program is working

Gherardo

This code is not working for me :( – Termitarium