Python xlrd: xlrd.biffh.XLRDError: Workbook is encrypted

error¶

Error when use xlrd in Python to read xlsx file

error: xlrd.biffh.XLRDError: Workbook is encrypted

Reason¶

The Excel file is encrypted. The manual method is to revoke the protection one by one, and the batch method is to use the Python package msoffcrypto to decrypt Office files

Software installation: 

pip install msoffcrypto-tool

GitHub URL: https://github.com/nolze/msoffcrypto-tool

The code for batch decryption of Excel files is as follows:

import msoffcrypto
import xlrd
import xlsxwriter
import os

def decrypt_xlsx_files():
    input_path = "E:\\Data\\org\\"
    output_path = "E:\\Data\\decrypted\\"
    for file in os.listdir(input_path):
        if file.endswith('.xls'):
            data = msoffcrypto.OfficeFile(open(os.path.join(input_path, file), 'rb'))
            data.load_key(password='VelvetSweatshop')  # The default password is'VelvetSweatshop'
            data.decrypt(open(os.path.join(output_path, file), 'wb'))  # Output without password protection
            print('finished' + file)

The password of the general office encrypted file is VelvetSweatshop. There is a more robust file reading method in the reference website. The above code just introduces the idea.