xlrd - 错误"Workbook is encrypted"，Python 3.2.3

Question

我有一个简短的程序，它收集文件夹/子文件夹中所有.xls文件的列表，然后我循环浏览文件列表，打开每个xls文档（Try：book = xlrd.open_workbook（f））来查找特定信息。如果抛出异常，我将文件名写出到异常列表中。我发现我有很多文件xlrd抛出错误：

Traceback (most recent call last):
  File "<pyshell#5>", line 1, in <module>
    book = xlrd.open_workbook(f)
  File "C:Python32libsite-packagesxlrd__init__.py", line 435, in open_workbook
    ragged_rows=ragged_rows,
  File "C:Python32libsite-packagesxlrdbook.py", line 116, in open_workbook_xls
    bk.parse_globals()
  File "C:Python32libsite-packagesxlrdbook.py", line 1206, in parse_globals
    self.handle_filepass(data)
  File "C:Python32libsite-packagesxlrdbook.py", line 924, in handle_filepass
    raise XLRDError("Workbook is encrypted")
xlrd.biffh.XLRDError: Workbook is encrypted

但是我可以毫无问题地使用 Excel 打开文件。 有没有人知道为什么 xlrd 会在文件似乎没有加密时抛出加密错误？

谢谢

佛瑞德

Answer 1

我

遇到了同样的问题，正如@zindorsky在他们的评论中提到的，当文件具有受保护的工作表时，或者通过Excel使用魔术密码静默加密文件的其他原因VelvetSweatshop，可能会发生这种情况。

XLRD无法自行处理加密文件（实际上自述文件将其列为"不太可能完成"），但是最近还有另一个Python库可以解密各种MS Office文件（包括.xls文件） - msoffcrypto-tool 。

我能够使用它来成功解决问题 - 这是一个缩写（未经测试！

import xlrd
import msoffcrypto
def handle_protected_workbook(wb_filepath):
    try:
        _book = xlrd.open_workbook(wb_filepath)
    except xlrd.biffh.XLRDError, e:
        if e.message == "Workbook is encrypted":
            # Try and unencrypt workbook with magic password
            wb_msoffcrypto_file = msoffcrypto.OfficeFile(open(wb_filepath, 'rb'))
            try:
                # Yes, this is actually a thing
                # https://nakedsecurity.sophos.com/2013/04/11/password-excel-velvet-sweatshop/
                wb_msoffcrypto_file.load_key(password='VelvetSweatshop')
            except AssertionError, e:
                if e.message == "Failed to verify password":
                    # Encrypted with some other password
                    raise # or do something else
                else:
                    # Some other error occurred
                    raise
            except:
                # Some other error occurred
                raise
            else:
                # Magic Excel password worked
                assert wb_filepath.endswith('.xls')
                wb_unencrypted_filename = wb_filepath[:-(len('.xls'))] + '__unencrypted.xls'
                with tempfile.NamedTemporaryFile() as tmp_wb_unencrypted_file:
                    # Decrypt into the tempfile
                    wb_msoffcrypto_file.decrypt(tmp_wb_unencrypted_file)
                    # --- Do something with the file ---
                # return true to indicate file was touched
                return True  # or do something else
        else:
            # some other xlrd error occurred.
            return False  # or do something else
    except:
        # some non-xlrd error occurred.
        return False  # or do something else

Answer 2

基于nuclearpidgeon的答案，我创建了一个上下文管理器，可以根据需要透明地解密文件并负责清理。

用法是：

with handle_protected_workbook(filepath) as wb:
   # use wb
   ...

https://gist.github.com/terrdavis/b219e92d42dc5f9ca526aa0047d1a1d1