Updating and encrypting existing xlsx file with Apache POI

Question

I have existing xlsx spreadsheet. I am using Apache POI 3.17 to read it, add some entries and save as password protected spreadsheet in new file. After I run the program, new file is password protected, but I don't see new entries, only the ones which existed before. Here is simplified version of the program which opens empty spreadsheet, writes new cell and saves in new file with password. When I open file in Excel 2010 using password I still see empty spreadsheet. Any help will be appreciated. Thanks

public static void main(String[] args) {

  try {
    POIFSFileSystem fs = new POIFSFileSystem();

    EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
    Encryptor enc = info.getEncryptor();
    enc.confirmPassword("passw");


    File is = new File("./empty.xlsx");
    OPCPackage opc = OPCPackage.open(is, PackageAccess.READ_WRITE);

    Workbook wb = WorkbookFactory.create(opc);

    Sheet sheet  = wb.getSheetAt(0);
    Row row = sheet.createRow(1);
    Cell cell = row.createCell(1);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("CRYPT");

    OutputStream encos = enc.getDataStream(fs);
    opc.save(encos);
    opc.close();

    OutputStream fos = new FileOutputStream(new File("./f.xlsx"));
    fs.writeFilesystem(fos);
    fos.close();
  }
  catch (Exception ex) {
    System.out.println(ex.getMessage());
    ex.printStackTrace();
  }
}

@kiwiwings: Your code fails using apache poi version 3.17 while closing encos: Exception in thread "main" org.apache.poi.EncryptedDocumentException: java.io.FileNotFoundException: /tmp/poifiles/encrypted_package1071788535456667764crypt (...) at org.apache.poi.poifs.crypt.standard.StandardEncryptor$StandardCipherOutputStream.processPOIFSWriterEvent(StandardEncryptor.java:201) ... at org.apache.poi.poifs.crypt.standard.StandardEncryptor$StandardCipherOutputStream.close(StandardEncryptor.java:169) — Axel Richter
@AxelRichter: you are right, I've just tested the code on the trunk and I've changed the closing habbit because of this issue. I need to make it more clear in the release notes. — kiwiwings
@kiwiwings: OK. And since you are using a File in Workbook wb = WorkbookFactory.create(new File("empty.xlsx"));, the empty.xlsx is updated too while wb.write updates the OPCPackage. So after that empty.xlsx contains the new value in B2 too and is not more "empty" ;-). As said in my answer, it lacks a possibility for programmmatically influencing the committing process between the XSSFWorkbook and it's OPCPackage. — Axel Richter
I've fixed the closing issue via #60713 ... I'll fix the unwanted parallel writing via #59287 — kiwiwings

Axel Richter Axel Richter · Accepted Answer · 2018-06-13T06:15:06

The problem here is the discrepancy in committing changes between the XSSFWorkbook and it's OPCPackage. Changes in the XSSFWorkbook will be committed to the OPCPackage only while XSSFWorkbook.write. So if you do not (or cannot) write out the XSSFWorkbook, the OPCPackage stays unchanged.

On other hand, if you write out the XSSFWorkbook, it always commits changes to the OPCPackage from which the workbook was created. So if that was an OPCPackage created from an File, then always this file is updated before the workbook will possibly be written into another file. This is annoying too.

So in my opinion it lacks a possibility for programmmatically influencing the committing process between the XSSFWorkbook and it's OPCPackage.

But the main problem with your code is that you are writing the OPCPackage, which is not updated, to the Encryptor's data stream. Instead you should writing the Workbook, which you have updated.

So for example:

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.poifs.crypt.*;
import org.apache.poi.ss.usermodel.*;

import java.io.*;

class ExcelUpdateAndEncrypt {

 public static void main(String[] args) throws Exception {

  POIFSFileSystem fs = new POIFSFileSystem();

  EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
  Encryptor enc = info.getEncryptor();
  enc.confirmPassword("passw");

  FileInputStream is = new FileInputStream("./empty.xlsx");
  Workbook wb = WorkbookFactory.create(is);

  Sheet sheet  = wb.getSheetAt(0);
  Row row = sheet.createRow(1);
  Cell cell = row.createCell(1);
  cell.setCellValue("CRYPT");

  OutputStream encos = enc.getDataStream(fs);
  wb.write(encos); 
  wb.close();

  OutputStream os = new FileOutputStream(new File("./f.xlsx"));
  fs.writeFilesystem(os);
  os.close();

 }
}

Updating and encrypting existing xlsx file with Apache POI

1 Answers