Search code examples
apache-poixlsxwriterbonita

Wrong password provided for my excel reader accepts the generated file of the excel writer

I made two classes using apache poi that reads and writes excel files needed for a process. These excel files are encrypted with a password using the protect workbook. The encryption works and the excel reader does not accept wrong password inputs (if you manually set the password in MS excel). However, the excel files generated by the excel writer that is supposed to be encrypted with a password is not working. When the reader reads the generated excel file, it accepts any password and able to get the data inside the excel file. But when you manually open the excel file through MS excel, it is encrypted with a password.

writeExcelWithFormula {

    HSSFWorkbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Batch ACE");
    FileOutputStream fos = new FileOutputStream(fileName);
    workbook.write(fos);
    fos.close();
    encryptFile(fileName,employeeList);

encryptFile {

try {           

         fileInput = new FileInputStream(fileName);         
         bufferInput = new BufferedInputStream(fileInput);            
         poiFileSystem = new POIFSFileSystem(bufferInput);            

         Biff8EncryptionKey.setCurrentUserPassword("password");            
         HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);            
         HSSFSheet sheet = workbook.getSheetAt(0);
         sheet.setDefaultColumnWidth(25);

//create data....
         fileOut = new FileOutputStream(fileName);
                          workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), "");
         workbook.write(fileOut);

Encryption works fine manually (opening via MS excel). I use these classes in bonita bpm. I am a newbie with apache poi and bonita pls help thank you!

Solution

  • As explained on the Apache POI page on encryption support, the encryption/protection support in HSSF for .xls files is read-only. Changing or creating password-protected files is not supported, only un-protected .xls files may be generated with HSSF. If you open a protected file in HSSF, and save it out, protection will be removed.

    If you need to generate protected / encrypted Excel files with POI, then your only option is to use XSSF and .xlsx files. XSSF supports both reading and writing protected files, details of how do to that are give on the encryption support page