I'm trying to determine the best way to open a protected worksheet, if even possible. I've done a little research in spreadsheetgear's documentation and I haven't really been able to track down a solution yet.
I've come across two options that I think might be able to help. Spreadsheetgear does have an Unprotect(string password) method but as of right now I cant get it to work. I have the user select a workbook on their local system and upload it to the server. When I try to open and read the file, the page breaks and throws an exception
Corrupt OpenXML document.
As of right now I open unprotected workbooks like so
byte[] file = Session["FileUpload"] as byte[];
using (MemoryStream fileStream = new MemoryStream(file))
{
IWorkbook workbook = Factory.GetWorkbookSet().Workbooks.OpenFromStream(fileStream);
.....
}
This works fine on an unprotected workbook but when its protected I get the corrupt document error. Ideally, I would want to unprotect and open at the same time but I'm not sure how to accomplish this.OpenFromStream does have an overload where you can feed it a string as a password which I tried but no luck there. The documentation isn't great for these methods so I'm not sure if I was even using them correctly.
UPDATE:
SpreadsheetGear 2017 (V8) has added support for the new worksheet and workbook protection / encryption options used for Open XML (*.xlsx and *.xlsm) files. If you are using SpreadsheetGear 2012 and having trouble opening worksheet- and/or workbook-protected files, please try downloading SpreadsheetGear 2017 (30-day trial for evaluators or Licensed users).
You are likely running into a known limitation, which will be addressed soon. SpreadsheetGear 2012's implementation of workbook and worksheet protection was based off Excel 2007 (ECMA-376 1st Edition). However, Microsoft has since added a number of new workbook protection options as well as stronger encryption forms to later editions of the Open XML (XLSX/XLSM) file format spec. SpreadsheetGear 2012 has not yet added support for these new file format options and encryption forms, so you could receive either a "Corrupt OpenXML document" or "Unsupported encryption type" exception when attempting to open these types of workbooks, the former of which you are receiving.
The good news is that Excel 2010/2013-compatible password protection will be added in the next major release of SpreadsheetGear, V8, which is slated to be released sometime later this year, though the timeline for this could slip.
Until V8 is released, one workaround would be to unprotect any workbooks and individual worksheets using Excel 2010 or Excel 2013, then use SpreadsheetGear itself or Excel 2007 to re-protect these workbook/worksheets, which will use the older protection/encryption options. There are also ways to hack the Windows Registry that would tell Excel 2010 and Excel 2013 to save workbooks out with the encryption options used in Excel 2007 (see this link), but that's not a very good solution IMO.