How to create and edit a password protect excel sheet using Apache POI 3.14?

Question

Hey guys I am working on a Swing application that logs data to a password protected excel sheet.

My initial problem is that I haven't been able to find proper documentation on how to create the excel sheet with password protection from scratch, and I am not entirely sure if it is even supported by Apache POI version 3.14. Any insight on the matter would be greatly appreciated.

However my real problem is that assuming I already have a password protected .xlsx file (by manually setting the password from within Excel itself), I was able to access the file through WorkbookFactory.create(new FileInputStream(dataFile), "password"); but once the code has executed, the file was no longer password protected and now anyone can access it.

Here's a snippet of the code I have:

// Sheet 1
private void logSheet1(File dataFile) throws IOException, InvalidFormatException {
    Workbook workBook = WorkbookFactory.create(new FileInputStream(dataFile), "password");
    Sheet sheet1 = workBook.getSheet("Sheet 1");
    Row row = sheet1.createRow(sheet1.getLastRowNum()+1);

    // data
    for(int i=0; i<log.length; i++) {
        if(log[i] == null) log[i] = new String("No data");
        Cell cell = row.createCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(log[i]);
    }

    FileOutputStream fos = new FileOutputStream(dataFile);
    workBook.write(fos);
    fos.close();
}


// Sheet 2
private void logSheet2(File dataFile) throws IOException, InvalidFormatException {
    Workbook workBook = WorkbookFactory.create(new FileInputStream(dataFile), "password");
    Sheet sheet2 = workBook.getSheet("Sheet 2");
    Row row = sheet2.createRow(sheet2.getLastRowNum()+1);

    // data
    for(int i=0; i<log.length; i++) {
        if(log[i] == null) log[i] = new String("No data");
        Cell cell = row.createCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(log[i]);
    }

    FileOutputStream fos = new FileOutputStream(dataFile);
    workBook.write(fos);
    fos.close();
}

Answer 1

Yes, password protection in Excel is supported by Apache POI and it is very Simple to program as well.

Here is the Sample code of Protecting an Excel with Apache POI in JAVA, hope it will help you.

package excel_encryptor;

import java.io.File;
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.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//apache poi imports
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Encryption {

    public static void main(String[] args) throws IOException, InvalidFormatException, GeneralSecurityException {

        //create a new workbook
        Workbook wb = new XSSFWorkbook();

        //add a new sheet to the workbook
        Sheet sheet1 = wb.createSheet("Sheet1");

        //add 2 row to the sheet
        Row row1 = sheet1.createRow(0);
        Row row2 = sheet1.createRow(1);

        //create cells in the row
        Cell row1col1 = row1.createCell(0);
        Cell row1col2 = row1.createCell(1);

        //add data to the cells
        row1col1.setCellValue("Top Secret Data 1");
        row1col2.setCellValue("Top Secret Data 2");

        //write the excel to a file
        try {
            FileOutputStream fileOut = new FileOutputStream("D:/path/excel.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
        }

        //Add password protection and encrypt the file
        POIFSFileSystem fs = new POIFSFileSystem();
        EncryptionInfo info = new EncryptionInfo(fs, EncryptionMode.agile);
        Encryptor enc = info.getEncryptor();
        enc.confirmPassword("s3cr3t"); // s3cr3t is your password to open sheet.

        OPCPackage opc = OPCPackage.open(new File("D:/path/excel.xlsx"), PackageAccess.READ_WRITE);
        OutputStream os = enc.getDataStream(fs);
        opc.save(os);
        opc.close();

        FileOutputStream fos = new FileOutputStream("D:/path/excel.xlsx");
        fs.writeFilesystem(fos);
        fos.close();    

        System.out.println("File created!!");

    }
}