title: Read XLS with Protected Book and Sheet via HSSF.EventUserModel
body: <p><strong>END GOAL:</strong> Efficiently (in one pass) read all <code>CellRecords</code> on a huge (30,000+ row), protected <code>Worksheet</code>.<br/></p>
  
  <p><strong>Problem:</strong>
  Using the <code>HSSF.EventUserModel</code>, how can I read all <code>Record</code>s (including <code>CellRecords</code>)  for an XLS file with both Workbook and Worksheet protection?</p>
  
  <p>Create Input Spreadsheet (in Excel 2010):</p>
  
  <ol>
  <li>Create new Blank workbook.</li>
  <li>Set value of A1 to number: 50</li>
  <li>Set value of A2 to string: fifty</li>
  <li>Set value of A3 to formula: =25*2</li>
  <li>Review (ribbon) -> Protect Sheet -> Password: pass1</li>
  <li>Review (ribbon) -> Protect Workbook -> Password: pass1</li>
  <li>File (ribbon) ->Save As... -> Save as type: Excel 97-2003 Workbook</li>
  </ol>
  
  <p>Progress thus far:</p>
  
  <ul>
  <li>The XLS file opens without a password in Excel.  Therefore, you shouldn't need the password to open it in POI.</li>
  <li>The XLS file opens successfully with <code>new HSSFWorkbook(Stream fs)</code>.  However, I need the efficiency of <code>EventUserModel</code> for my actual spreadsheet.</li>
  <li>Setting <code>NPOI.HSSF.Record.Crypto.Biff8EncryptionKey.CurrentUserPassword = "pass1";</code> did not work.</li>
  <li>The <code>ProcessRecord( )</code> function catches a <code>PasswordRecord</code>, but I can't find any documentation on how to properly handle it.</li>
  <li>Perhaps, the <code>EncryptionInfo</code> or <code>Decryptor</code> classes may be of some use.</li>
  </ul>
  
  <p>Note:<br/>
  I'm using NPOI.  However, I can translate any java examples to C#.</p>
  
  <p>Code:<br/>
  I use the following code to capture <code>Record</code> events.  My <code>Book1-unprotected.xls</code> (without protection) shows all <code>Record</code> events (including cell values).  My <code>Book1-protected.xls</code> displays some records and throws an exception.</p>
  
  <p>I just view <code>processedEvents</code> in the debugger.</p>
  
  <pre><code>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&lt;Record&gt; processedRecords;
  
          private Stream fs;
  
          public myListener(Stream fs) {
              processedRecords = new List&lt;Record&gt;();
              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();
          }
      }
  }
  </code></pre>
  
  <p><strong>UPDATE</strong> (for Juan Mellado)<strong>:</strong>
  Below is the exception.  My best guess right now (in the answer by Victor Petrykin) is that the <code>HSSFEventFactory</code> uses <code>RecordInputStream</code> which cannot natively decrypt protected records.  Upon receiving the exception, <code>processedRecords</code> contains 22 records including the following potentially significant ones:</p>
  
  <ul>
  <li>processedRecords[5] is a <code>WriteAccessRecord</code> with a garbled (probably encrypted) value for <code>.name</code></li>
  <li>processedRecords[22] is a <code>RefreshAllRecord</code> and is the last <code>Record</code> in the list</li>
  </ul>
  
  <p>Exception:</p>
  
  <pre><code>NPOI.Util.RecordFormatException was unhandled
    HResult=-2146233088
    Message=Unable to construct record instance
    Source=NPOI
    StackTrace:
         at NPOI.HSSF.Record.RecordFactory.ReflectionConstructorRecordCreator.Create(RecordInputStream in1)
         at NPOI.HSSF.Record.RecordFactory.CreateSingleRecord(RecordInputStream in1)
         at NPOI.HSSF.Record.RecordFactory.CreateRecord(RecordInputStream in1)
         at NPOI.HSSF.EventUserModel.HSSFRecordStream.GetNextRecord()
         at NPOI.HSSF.EventUserModel.HSSFRecordStream.NextRecord()
         at NPOI.HSSF.EventUserModel.HSSFEventFactory.GenericProcessEvents(HSSFRequest req, RecordInputStream in1)
         at NPOI.HSSF.EventUserModel.HSSFEventFactory.ProcessEvents(HSSFRequest req, Stream in1)
         at NPOI.HSSF.EventUserModel.HSSFEventFactory.ProcessWorkbookEvents(HSSFRequest req, POIFSFileSystem fs)
         at NPOI_small.myListener..ctor(Stream fs) in c:\Users\me\Documents\Visual Studio 2012\Projects\myTest\NPOI_small\Program.cs:line 35
         at NPOI_small.Program.Main(String[] args) in c:\Users\me\Documents\Visual Studio 2012\Projects\myTest\NPOI_small\Program.cs:line 80
         at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
         at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.ThreadHelper.ThreadStart()
    InnerException: NPOI.Util.RecordFormatException
         HResult=-2146233088
         Message=Expected to find a ContinueRecord in order to read remaining 137 of 144 chars
         Source=NPOI
         StackTrace:
              at NPOI.HSSF.Record.RecordInputStream.ReadStringCommon(Int32 requestedLength, Boolean pIsCompressedEncoding)
              at NPOI.HSSF.Record.RecordInputStream.ReadUnicodeLEString(Int32 requestedLength)
              at NPOI.HSSF.Record.FontRecord..ctor(RecordInputStream in1)
  </code></pre>