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 processedRecords; private Stream fs;
public myListener(Stream fs) { processedRecords = new List();
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(); } }}
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Read-XLS-with-Protected-Book-and-Sheet-via-HSSF-EventUserModel-tp5712485.html
Sent from the POI - User mailing list archive at Nabble.com.
