2

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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Dom Bavetta
  • 104
  • 2
  • 13

3 Answers3

4

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.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
4

As of this writing, V8 of SpreadsheetGear hasn't been released, so I thought I would mention how we worked around this limitation. Manually unprotecting then re-protecting in an earlier of Excel version isn't an option for us as it's used in a commercial application. Also note that the second work-around actually doesn't work. It took many hours of arm-twisting Microsoft support to get them to admit this.

It turns out that worksheet protection doesn't encrypt any content, it only hashes the password, and relies on Excel to obtain it from the user before allowing access. In 2013, they changed the hash algorithm, and that's the piece that SpreadsheetGear doesn't support yet. The fact that the data is unencrypted makes this "protection" trivial to defeat (as long as you don't mind a little low-level XML parsing, which in fact I do very much ;-).

Every time we open a workbook with SpreadsheetGear, we pass the XML stream through a pre-processor that simply strips off the element containing the hash, and then passes the stream on to SpreadsheetGear, which then sees the worksheets as unprotected. We keep track of which worksheets were "unprotected", and then re-protect them with SpreadsheetGear (using the old hash algorithm) prior to saving the workbook. Note that this doesn't provide a "real" Unprotect method, where you would require the caller to supply the proper password before allowing access. If that was a requirement then presumably you could implement that piece as well. This approach has a big performance hit, mostly because we have to re-zip the worksheet streams prior to passing the whole thing on to SpreadsheetGear. Not an issue for us because in our typical workbooks, re-calc swamps open times. Obviously this isn't an approach for casual users, but it was non-negotiable for us to get this working somehow.

user1258221
  • 81
  • 5
3

First of all, thank you to the other two previous contributors who explained what was going on.

One of them was Tim Anderson, who replied to a support email I sent to SpreadsheetGear, saying that...

  1. the latest update to SpreadsheetGear 2012 includes a hotfix (7.4.8.100) which allows the opening of these "password-protected" sheets. The ProtectContents method still takes a password parameter, but it is dropped and not used.

  2. "We are adding support for the new encryption options provided for overall workbook protection in our V8 release. Adding 'full' support for worksheet protection (which doesn't drop the password) is not a sure thing for V8, though."

Community
  • 1
  • 1
vwfreak
  • 369
  • 2
  • 5
  • 18