Open an xls file using xlrd even if it has protected sheets.

xlrd_protected.py

# based on: https://stackoverflow.com/a/52290873/9560908

import contextlib
import tempfile

import msoffcrypto
import xlrd


@contextlib.contextmanager
def handle_protected_workbook(wb_filepath):
    try:
        with xlrd.open_workbook(wb_filepath) as wb:
            yield wb
    except xlrd.biffh.XLRDError as e:
        if str(e) != "Workbook is encrypted":
            raise
        # Try and unencrypt workbook with magic password
        wb_path = Path(wb_filepath)
        with wb_path.open("rb") as fp:
            wb_msoffcrypto_file = msoffcrypto.OfficeFile(fp)
            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 Exception as e:
                raise Exception('Unable to read file "{}"'.format(wb_path)) from e
                
            # Magic Excel password worked
            with tempfile.NamedTemporaryFile(delete=False) as tmp_wb_unencrypted_file:
                # Decrypt into the tempfile
                wb_msoffcrypto_file.decrypt(tmp_wb_unencrypted_file)
                decrypted_path = Path(tmp_wb_unencrypted_file.name)
            try:
                with xlrd.open_workbook(str(decrypted_path)) as wb:
                    yield wb
            finally:
                decrypted_path.unlink()