[prev in list] [next in list] [prev in thread] [next in thread] 

List:       poi-user
Subject:    (REVISED) Read XLS with Protected Book and Sheet via HSSF.EventUserModel
From:       Steven Dziegiel <sd2working () gmail ! com>
Date:       2013-04-04 15:00:39
Message-ID: CAKQybthciyMj2+QVHHwnw-OMYfrDrQ3w60+PDP8oxVOZax1=8w () mail ! gmail ! com
[Download RAW message or body]

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):
 - Create new Blank workbook.
 - Set value of A1 to number: 50
 - Set value of A2 to string: fifty
 - Set value of A3 to formula: =25*2
 - Review (ribbon) -> Protect Sheet -> Password: pass1
 - Review (ribbon) -> Protect Workbook -> Password: pass1
 - 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.


Note:
I'm using NPOI. However, I can translate any java examples to C#.
Also, there seems to be much more POI support here than on the NPOI
forum.

Code:
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
        }
    }
}

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]