This project is read-only.

Read XLS with Protected Book and Sheet via HSSF.EventUserModel

Apr 3, 2013 at 6:54 PM
Edited Apr 3, 2013 at 10:25 PM
Using the HSSF.EventUserModel, how can I read all Records (including CellRecords) for an XLS file with both Workbook and Worksheet protection?

Create Input Spreadsheet (in Excel 2010):
  1. Create new Blank workbook.
  2. Set value of A1 to number: 50
  3. Set value of A2 to string: fifty
  4. Set value of A3 to formula: =25*2
  5. Review (ribbon) -> Protect Sheet -> Password: pass1
  6. Review (ribbon) -> Protect Workbook -> Password: pass1
  7. File (ribbon) ->Save As... -> Save as type: Excel 97-2003 Workbook
Progress thus far:
  • The XLS file opens without a password in Excel. Therefore, you shouldn't need the password to open it in POI.
  • The XLS file opens successfully with new HSSFWorkbook(Stream fs). However, I need the efficiency of EventUserModel for my actual spreadsheet.
  • Setting NPOI.HSSF.Record.Crypto.Biff8EncryptionKey.CurrentUserPassword = "pass1"; did not work.
  • The ProcessRecord( ) function catches a PasswordRecord (link), but I can't find any documentation on how to properly handle it.
  • Perhaps, the EncryptionInfo or Decryptor classes may be of some use.
Code:<br/>
I use the following code to capture Record events. My Book1-unprotected.xls (without protection) shows all Record events (including cell values). My Book1-protected.xls displays some records and throws an exception.

I just view processedEvents in the debugger.
using System;
using System.Collections.Generic;
using System.IO;

using NPOI.HSSF.Record;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.EventUserModel;
using NPOI.POIFS;
using NPOI.POIFS.FileSystem;

namespace NPOI_small {
    class myListener : IHSSFListener {
        List<Record> processedRecords;

        private Stream fs;

        public myListener(Stream fs) {
            processedRecords = new List<Record>();
            this.fs = fs;

            HSSFEventFactory factory = new HSSFEventFactory();
            HSSFRequest request = new HSSFRequest();

            MissingRecordAwareHSSFListener mraListener;
            FormatTrackingHSSFListener fmtListener;
            EventWorkbookBuilder.SheetRecordCollectingListener recListener;
            mraListener = new MissingRecordAwareHSSFListener(this);
            fmtListener = new FormatTrackingHSSFListener(mraListener);
            recListener = new EventWorkbookBuilder.SheetRecordCollectingListener(fmtListener);
            request.AddListenerForAllRecords(recListener);

            POIFSFileSystem poifs = new POIFSFileSystem(this.fs);

            factory.ProcessWorkbookEvents(request, poifs);
        }

        public void ProcessRecord(Record record) {
            processedRecords.Add(record);
        }
    }
    class Program {
        static void Main(string[] args) {
            Stream fs = File.OpenRead(@"c:\users\me\desktop\xx\Book1-protected.xls");

            myListener testListener = new myListener(fs); // Use EventModel 
            //HSSFWorkbook book = new HSSFWorkbook(fs); // Use UserModel

            Console.Read();
        }
    }
}