python excel 创建密码保护失败

Question

报错如下

代码如下

def create_project_excel(list1: [[]], list2: [[]], name: str, filenamepassword: str) -> None:
    import pythoncom
    pythoncom.CoInitialize()
    import win32com.client as win32
    excel = win32.Dispatch('Excel.Application')
    excel.Visible = False  # 可见
    wb1 = excel.Workbooks.Add()  # 创建excel
    wb1.Worksheets.Add().Name = '汇总'  # 命名初始sheet
    ws1 = wb1.Worksheets('汇总')
    ws1.Range('A1:J2').Merge()
    ws1.Range(f'A1:J{len(list1) + len(list2) + 7}').HorizontalAlignment = -4108
    ws1.Range('A3:J3').Value = [['序号', '产品名称', '品牌', '型号', '参数', '单位', '数量', '单价', '合计', '备注']]
    ws1.Range(f'A4:J{len(list1) + 3}').Value = list1
    ws1.Range(f'A{len(list1) + 6}:J{len(list1) + len(list2) + 5}').Value = list2
    ws1.Range(f'A{len(list1) + 4}:H{len(list1) + 4}').Merge()
    ws1.Range(f'A{len(list1) + 5}:J{len(list1) + 5}').Merge()
    ws1.Range(f'A{len(list1) + len(list2) + 6}:H{len(list1) + len(list2) + 6}').Merge()
    ws1.Range(f'A{len(list1) + len(list2) + 7}:H{len(list1) + len(list2) + 7}').Merge()
    ws1.Range('A1').Value = '室内工程报价模型'
    ws1.Range('A1').Font.Bold = True
    ws1.Range(f'A{len(list1) + 4}').Value = '小计'
    ws1.Range(f'A{len(list1) + 5}').Value = '以下材料为选配'
    ws1.Range(f'A{len(list1) + len(list2) + 6}').Value = '小计'
    ws1.Range(f'A{len(list1) + len(list2) + 7}').Value = '合计'
    ws1.Range(f'I{len(list1) + 4}').Value = f'=sum(I4:I{len(list1) + 3})'
    ws1.Range(f'I{len(list1) + len(list2) + 6}').Value = f'=sum(I{len(list1) + 6}:I{len(list1) + len(list2) + 5})'
    ws1.Range(f'I{len(list1) + len(list2) + 7}').Value = f'=I{len(list1) + 4}+I{len(list1) + len(list2) + 6}'
    ws1.Range(f'A1:J{len(list1) + len(list2) + 7}').Interior.ColorIndex = 23
    ws1.Range(f'A3:J{len(list1) + 3}').Interior.ColorIndex = 0
    ws1.Range(f'A{len(list1) + 6}:J{len(list1) + len(list2) + 5}').Interior.ColorIndex = 0
    ws1.Range(f"A1:J{len(list1) + len(list2) + 7}").Borders.LineStyle = 1  # 边框线段
    output_file_path = r'D:/static/media/oderexcel/' + name + '.xlsx'
    wb1.protection.sheet = True
    wb1.protection.password = filenamepassword
    wb1.protection.enable()
    wb1.SaveAs(output_file_path)  # 保存
    wb1.Close(0)  # 关闭

    def close_excel_by_force(excel):  # 关闭进程

        # Get the window's process id's
        hwnd = excel.Hwnd
        import win32process
        t, p = win32process.GetWindowThreadProcessId(hwnd)
        # Ask window nicely to close
        try:
            import win32api
            import win32con
            handle = win32api.OpenProcess(win32con.PROCESS_TERMINATE, 0, p)
            if handle:
                win32api.TerminateProcess(handle, 0)
                win32api.CloseHandle(handle)
        except:
            pass

    close_excel_by_force(excel)

测试数据如下

 must=[[1,"单元板","/","/","/","/",5,10,50,""],
          [2,"辅材","/","/","/","/",6,11,66,""],
          [3,"电源","/","/","/","/",7,12,84,""],
           [4,"控制卡","/","/","/","/",8,13,104,""],
           [5,"处理器","/","/","/","/",9,14,126,""],
          [6,"配电箱","/","/","/","/",10,15,150,""],
          [7,"结构","/","/","/","/",11,16,176,""],
          [8,"包边","/","/","/","/",12,17,204,""],
          [9,"安装调试","/","/","/","/",13,18,234,""],]
 uncertain=[[1,"箱体","/","/","/","/",5,50,250,""],
          [2,"多功能卡","/","/","/","/",6,51,306,""],
          [3,"音箱、功放","/","/","/","/",7,52,364,""],
          [4,"电脑","/","/","/","/",8,53,424,""],
          [5,"运输","/","/","/","/",9,54,486,""],
          [6,"施工平台","/","/","/","/",10,55,550,""],
          [7,"综合布线","/","/","/","/",11,56,616,""]]
name="123.xlsx"
filenamepassword='123456'

调用代码如下

p = threading.Thread(target=create_project_excel, args=(must, uncertain, filename, filenamepassword))
p.setDaemon(True)
p.start()

Answer 1

wb1.protection.sheet = True 

这个是 openpyxl 的用法吧。

这样 试试

#  wb1.protection.sheet = True
#  wb1.protection.password = filenamepassword
# wb1.protection.enable()
#  改为
ws1.Protect(filenamepassword)