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.
我试图确定打开受保护工作表的最佳方法,如果可能的话。我在spreadsheetgear的文档中做了一些研究,但我还没有真正找到解决方案。
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
我遇到过两种我认为可以提供帮助的选择。 Spreadsheetgear确实有一个Unprotect(字符串密码)方法,但截至目前我无法使其工作。我让用户在本地系统上选择一个工作簿并将其上传到服务器。当我尝试打开并读取文件时,页面会中断并抛出异常
Corrupt OpenXML document.
腐败的OpenXML文档。
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.
这在未受保护的工作簿上工作正常但是当它受到保护时我得到了损坏的文档错误。理想情况下,我想要同时取消保护和打开,但我不知道如何实现这一点.OpenFromStream确实有一个重载,您可以在其中输入一个字符串作为密码,我尝试但没有运气。文档对于这些方法并不好,所以我不确定我是否正确使用它们。
3 个解决方案
#1
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).
SpreadsheetGear 2017(V8)增加了对用于Open XML(* .xlsx和* .xlsm)文件的新工作表和工作簿保护/加密选项的支持。如果您使用的是SpreadsheetGear 2012并且无法打开工作表和/或受工作簿保护的文件,请尝试下载2017年SpreadsheetGear(评估人员或许可用户的30天试用版)。
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.
您很可能遇到一个已知的限制,很快就会解决。 SpreadsheetGear 2012的工作簿和工作表保护实现基于Excel 2007(ECMA-376第1版)。但是,Microsoft已经为更高版本的Open XML(XLSX / XLSM)文件格式规范添加了许多新的工作簿保护选项以及更强大的加密形式。 SpreadsheetGear 2012尚未添加对这些新文件格式选项和加密表单的支持,因此在尝试打开这些类型的工作簿时,您可能会收到“Corrupt OpenXML文档”或“不支持的加密类型”异常,前者是您的接收。
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.
好消息是,将在今年晚些时候发布的SpreadsheetGear V8的下一个主要版本中添加Excel 2010/2013兼容的密码保护,尽管此时间表可能会下滑。
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.
在V8发布之前,一种解决方法是使用Excel 2010或Excel 2013取消保护任何工作簿和单个工作表,然后使用SpreadsheetGear本身或Excel 2007重新保护这些将使用旧保护/加密选项的工作簿/工作表。还有一些方法可以破解Windows注册表,告诉Excel 2010和Excel 2013使用Excel 2007中使用的加密选项保存工作簿(请参阅此链接),但这不是一个非常好的解决方案IMO。
#2
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.
在撰写本文时,SpreadsheetGear的V8还没有发布,所以我想我会提到我们是如何解决这个限制的。手动取消保护然后在早期的Excel版本中重新保护不是我们的选项,因为它在商业应用程序中使用。另请注意,第二种解决方法实际上不起作用。微软支持花了很多时间让他们承认这一点。
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 ;-).
事实证明,工作表保护不会加密任何内容,它只会散列密码,并且在允许访问之前依赖Excel从用户那里获取密码。 2013年,他们更改了哈希算法,这就是SpreadsheetGear尚不支持的部分。数据未加密的事实使得这种“保护”变得微不足道(只要你不介意一点点低级XML解析,实际上我做了很多;-)。
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.
每次我们使用SpreadsheetGear打开工作簿时,我们都会通过预处理器传递XML流,该预处理器简单地剥离包含哈希的元素,然后将流传递给SpreadsheetGear,然后SpreadsheetGear将工作表视为不受保护。我们跟踪哪些工作表是“未受保护的”,然后在保存工作簿之前使用SpreadsheetGear(使用旧的哈希算法)重新保护它们。请注意,这不提供“真正的”Unprotect方法,您需要调用方在允许访问之前提供正确的密码。如果这是一个要求,那么大概你也可以实现这个。这种方法有很大的性能影响,主要是因为我们必须在将整个内容传递给SpreadsheetGear之前重新压缩工作表流。对我们来说不是问题,因为在我们的典型工作簿中,重新计算沼泽的开放时间。显然,对于临时用户来说,这不是一种方法,但对于我们来说,以某种方式使其工作是不容谈判的。
#3
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...
其中一位是Tim Anderson,他回复了我发给SpreadsheetGear的支持电子邮件,说......
-
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.
SpreadsheetGear 2012的最新更新包括一个修补程序(7.4.8.100),允许打开这些“受密码保护”的表。 ProtectContents方法仍然采用密码参数,但它被删除而不使用。
-
"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."
“我们在V8版本中添加了对整体工作簿保护提供的新加密选项的支持。但是,为工作表保护添加'完全'支持(不会丢失密码)对V8来说并不是一件肯定的事情。”
#1
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).
SpreadsheetGear 2017(V8)增加了对用于Open XML(* .xlsx和* .xlsm)文件的新工作表和工作簿保护/加密选项的支持。如果您使用的是SpreadsheetGear 2012并且无法打开工作表和/或受工作簿保护的文件,请尝试下载2017年SpreadsheetGear(评估人员或许可用户的30天试用版)。
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.
您很可能遇到一个已知的限制,很快就会解决。 SpreadsheetGear 2012的工作簿和工作表保护实现基于Excel 2007(ECMA-376第1版)。但是,Microsoft已经为更高版本的Open XML(XLSX / XLSM)文件格式规范添加了许多新的工作簿保护选项以及更强大的加密形式。 SpreadsheetGear 2012尚未添加对这些新文件格式选项和加密表单的支持,因此在尝试打开这些类型的工作簿时,您可能会收到“Corrupt OpenXML文档”或“不支持的加密类型”异常,前者是您的接收。
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.
好消息是,将在今年晚些时候发布的SpreadsheetGear V8的下一个主要版本中添加Excel 2010/2013兼容的密码保护,尽管此时间表可能会下滑。
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.
在V8发布之前,一种解决方法是使用Excel 2010或Excel 2013取消保护任何工作簿和单个工作表,然后使用SpreadsheetGear本身或Excel 2007重新保护这些将使用旧保护/加密选项的工作簿/工作表。还有一些方法可以破解Windows注册表,告诉Excel 2010和Excel 2013使用Excel 2007中使用的加密选项保存工作簿(请参阅此链接),但这不是一个非常好的解决方案IMO。
#2
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.
在撰写本文时,SpreadsheetGear的V8还没有发布,所以我想我会提到我们是如何解决这个限制的。手动取消保护然后在早期的Excel版本中重新保护不是我们的选项,因为它在商业应用程序中使用。另请注意,第二种解决方法实际上不起作用。微软支持花了很多时间让他们承认这一点。
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 ;-).
事实证明,工作表保护不会加密任何内容,它只会散列密码,并且在允许访问之前依赖Excel从用户那里获取密码。 2013年,他们更改了哈希算法,这就是SpreadsheetGear尚不支持的部分。数据未加密的事实使得这种“保护”变得微不足道(只要你不介意一点点低级XML解析,实际上我做了很多;-)。
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.
每次我们使用SpreadsheetGear打开工作簿时,我们都会通过预处理器传递XML流,该预处理器简单地剥离包含哈希的元素,然后将流传递给SpreadsheetGear,然后SpreadsheetGear将工作表视为不受保护。我们跟踪哪些工作表是“未受保护的”,然后在保存工作簿之前使用SpreadsheetGear(使用旧的哈希算法)重新保护它们。请注意,这不提供“真正的”Unprotect方法,您需要调用方在允许访问之前提供正确的密码。如果这是一个要求,那么大概你也可以实现这个。这种方法有很大的性能影响,主要是因为我们必须在将整个内容传递给SpreadsheetGear之前重新压缩工作表流。对我们来说不是问题,因为在我们的典型工作簿中,重新计算沼泽的开放时间。显然,对于临时用户来说,这不是一种方法,但对于我们来说,以某种方式使其工作是不容谈判的。
#3
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...
其中一位是Tim Anderson,他回复了我发给SpreadsheetGear的支持电子邮件,说......
-
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.
SpreadsheetGear 2012的最新更新包括一个修补程序(7.4.8.100),允许打开这些“受密码保护”的表。 ProtectContents方法仍然采用密码参数,但它被删除而不使用。
-
"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."
“我们在V8版本中添加了对整体工作簿保护提供的新加密选项的支持。但是,为工作表保护添加'完全'支持(不会丢失密码)对V8来说并不是一件肯定的事情。”