I have a short program that collects a list of all .xls files in folder/sub-folders and then I loop through the file list, opening each xls document (Try: book = xlrd.open_workbook(f) ) to look for specific information. If an exception is thrown, I write out the filename to an exception list. What I am finding is that I have a LOT of files that xlrd throws the error :
Traceback (most recent call last): File "", line 1, in book = xlrd.open_workbook(f) File "C:\Python32\lib\site-packages\xlrd\__init__.py", line 435, in open_workbookragged_rows=ragged_rows, File "C:\Python32\lib\site-packages\xlrd\book.py", line 116, in open_workbook_xlsbk.parse_globals() File "C:\Python32\lib\site-packages\xlrd\book.py", line 1206, in parse_globalsself.handle_filepass(data) File "C:\Python32\lib\site-packages\xlrd\book.py", line 924, in handle_filepassraise XLRDError("Workbook is encrypted")xlrd.biffh.XLRDError: Workbook is encryptedBut I can go and open the files with Excel with no problems. Does anyone have an idea as to why xlrd would throw an encrypted error when it doesn't appear that the files are encrypted?
Thanks,
Fred
回答1:I ran into this same problem, and as @zindorsky has mentioned in their comment, this can happen when the file has a protected sheet - or via some other reason where Excel has silently encrypted the file with the magic password VelvetSweatshop.
XLRD can't handle files with encryption on its own (in fact the README lists it as 'unlikely to be done'), but there is another recent Python library that can unencrypt a variety of MS Office files (including .xls files) - msoffcrypto-tool .
I was able to use it to workaround the issue successfully - here's an abbreviated (and untested!) snippet version of the code
import xlrdimport msoffcryptodef 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 passwordwb_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 passwordraise # or do something elseelse:# Some other error occurredraiseexcept:# Some other error occurredraiseelse:# Magic Excel password workedassert wb_filepath.endswith('.xls')wb_unencrypted_filename = wb_filepath[:-(len('.xls'))] + '__unencrypted.xls'with tempfile.NamedTemporaryFile() as tmp_wb_unencrypted_file:# Decrypt into the tempfilewb_msoffcrypto_file.decrypt(tmp_wb_unencrypted_file)# --- Do something with the file ---# return true to indicate file was touchedreturn True # or do something elseelse:# some other xlrd error occurred.return False # or do something elseexcept:# some non-xlrd error occurred.return False # or do something else来源：https://stackoverflow.com/questions/22789951/xlrd-error-workbook-is-encrypted-python-3-2-3