win32com实现MS Excel 97-2003(.xls)加密失败的技术咨询

兼容性确认 #

win32com.client通过调用Excel的COM接口，完全兼容Excel 97-2003（.xls）格式的密码保护，你的代码未针对旧版Excel的加密规则做适配，才导致设置失败。

问题根源 #

.xls和.xlsx的加密逻辑在Excel对象模型中有差异：

• 对于.xlsx文件，仅设置Workbook.Password即可生效
• 对于.xls文件，需要显式指定加密算法和选项，否则Excel可能忽略密码设置

修复后的代码 #

import win32com.client as win32
import os

# 全局变量初始化（保留原结构，建议后续优化为非全局变量）
total_excel_protected = 0
no_of_excel_file = 0
# 假设choiceList和enableEncryptionInNestedFolders为已定义变量
choiceList = ["Enable Nested", "Disable Nested"]
enableEncryptionInNestedFolders = "Enable Nested"

def password_protect_excel(file_path, password, filename):
    global total_excel_protected
    excel = None
    workbook = None

    print(f"Setting Password for - '{filename}'")

    try:
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        excel.DisplayAlerts = False

        # 打开未加密文件：无需传入密码参数
        workbook = excel.Workbooks.Open(file_path, ReadOnly=False, Editable=True)
        
        # 设置打开密码
        workbook.Password = password
        
        # 针对.xls文件设置兼容加密选项
        if file_path.lower().endswith('.xls'):
            workbook.SetPasswordEncryptionOptions(
                PasswordEncryptionProvider="Microsoft RSA SChannel Cryptographic Provider",
                PasswordEncryptionAlgorithm="RC4",
                PasswordEncryptionKeyLength=128,
                PasswordEncryptionFileProperties=True
            )
        
        # 保存并关闭工作簿
        workbook.Save()
        workbook.Close()
        excel.Quit()
        
        # 重命名文件（确保文件已释放）
        base_name, extension = os.path.splitext(file_path)
        new_file_path = base_name + "_protected" + extension
        os.rename(file_path, new_file_path)
        print(f"Renamed to {os.path.basename(new_file_path)}")

        total_excel_protected += 1
        return True

    except Exception as e:
        print(f"Error processing file: {str(e)}")
        print('Skipping this file:\n')
        return False
    finally:
        # 强制清理Excel进程，避免残留
        if workbook is not None:
            try:
                workbook.Close(SaveChanges=False)
            except:
                pass
        if excel is not None:
            excel.Quit()
            del excel

def password_protect_excels_in_folder(folder_path, password):
    global no_of_excel_file
    global total_excel_protected
    
    excel_files = [filename for filename in os.listdir(folder_path) if filename.lower().endswith(('.xlsx', '.xlsm', '.xls'))]
    
    if not excel_files:
        print(f"The folder does not contain any EXCEL files: {folder_path}")
        return
    
    if enableEncryptionInNestedFolders == choiceList[0]:
        for filename in os.listdir(folder_path):
            file_path = os.path.join(folder_path, filename)
            if os.path.isdir(file_path):
                password_protect_excels_in_folder(file_path, password)
            else:
                if filename.lower().endswith(('.xlsx', '.xlsm', '.xls')):
                    no_of_excel_file += 1
                    password_protect_excel(file_path, password, filename)
    elif enableEncryptionInNestedFolders == choiceList[1]:
        for filename in excel_files:
            file_path = os.path.join(folder_path, filename)
            no_of_excel_file += 1
            password_protect_excel(file_path, password, filename)
    
    print("==============================")
    print(f"NO. OF EXCEL FILE : {no_of_excel_file}")
    print(f"TOTAL EXCEL FILE PROTECTED : {total_excel_protected}")
    print("==============================\n")

关键修改说明 #

• .xls加密适配：调用SetPasswordEncryptionOptions指定旧版Excel支持的RC4加密算法，确保密码生效。
• 进程安全退出：用finally块强制关闭工作簿和Excel进程，避免后台残留。
• 优化打开逻辑：打开未加密文件时不传入密码参数，修正原代码的参数错误。
• 调整重命名时机：关闭工作簿后再重命名，避免文件被Excel进程占用导致失败。

注意事项 #

1. 确保系统安装了支持Excel 97-2003格式的Excel版本或兼容包。
2. 测试时使用未加密的.xls文件，避免因文件已加密触发异常。
3. 全局变量可优化为函数参数或类属性，提升代码可维护性。

内容的提问来源于stack exchange，提问作者Muskan Singh