openpyxl、win32com操作excel读写，操作复选框

openpyxl无法操作复选框，利用win32com先操作复选框保存后，再赋值其他数据。代码如下：

import openpyxl
import os,time
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill,Border, Side, Alignment, Font
from win32com.client import gencache
from openpyxl.styles.colors import RED,YELLOW, BLUE, BLACK,WHITE

#自动设置列宽
def liekuan(ws):
    for col in ws.columns:
        max_length = 0
        column = col[0].column # Get the column name
        for cell in col:
            if cell.coordinate in ws.merged_cells: # not check merge_cells
                continue
            try: # Necessary to avoid error on empty cells
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2) * 2
        ws.column_dimensions[column].width = adjusted_width    
#居中
alignment=Alignment(horizontal='center',vertical='center')
#边
thin = Side(border_style="thin",color=BLACK)
#边框
border = Border(top=thin, left=thin, right=thin, bottom=thin)
#字体
row_title_font = Font(name='黑体', size=16, bold=True, color=BLACK)
#单元格填充背景颜色
row_title_fill = PatternFill(fill_type='solid',fgColor=YELLOW)
#内容字体
content_font = Font(name='宋体', size=14, bold=False,color=BLACK)
#单元格填充背景颜色
content_fill = PatternFill(fill_type='solid',fgColor=WHITE)
#标题字体
title_font = Font(name="宋体", bold=True, size=24,color=BLACK)
############################################################
#打开基础数据模板
wf=openpyxl.load_workbook("muban.xlsx",keep_vba=True)
sheet1=wf.worksheets[0]
i=0
k=2
for row in range(2,sheet1.max_row+1):
    i=i+1
    print("第"+str(i)+"行数据")
    excel = gencache.EnsureDispatch('Excel.Application')
    #禁用事件
    #excel.EnableEvents = False
    #禁止弹窗
    excel.DisplayAlerts = False
    wbb = excel.Workbooks.Open(r"D:\python\ok.xlsx")
    #工作表用Worksheets('Sheet1')方法
    wss = wbb.Worksheets('Sheet1')
    dibao=sheet1['G'+str(row)].value
    pinkun=sheet1['H'+str(row)].value
    zhuf=sheet1['I'+str(row)].value
    shui=sheet1['J'+str(row)].value
    if sheet1['G'+str(row)].value=="是" or sheet1['H'+str(row)].value=="是" or sheet1['I'+str(row)].value=="是" or sheet1['J'+str(row)].value=="是":   
##########################################################复选框赋值
        print('Shape count: %s' % len(wss.Shapes))
        for shape in wss.Shapes:
            if shape.Type == 8: # form control
                if 'Check Box' in shape.Name:
                    if dibao=="是":
                        if shape.AlternativeText=="低保户":
                            shape.ControlFormat.Value=1
                    if pinkun=="是":
                        if shape.AlternativeText=="贫困户":
                            shape.ControlFormat.Value=1
                    if zhuf=="是":
                        if shape.AlternativeText=="安全住房":
                            shape.ControlFormat.Value=1
                    if shui=="是":
                        if shape.AlternativeText=="有自来水":
                            shape.ControlFormat.Value=1
                    print('%s: %s' % (shape.AlternativeText, shape.ControlFormat.Value))
        wbb.Save()
        excel.Quit() 
        time.sleep(3)
##########################################################
        #打开写入复选框后的模板
        wb=openpyxl.load_workbook("ok.xlsx",keep_vba=True)
        ws=wb.worksheets[0]
        ws.cell(row=k+1,column=1).value = sheet1['A'+str(row)].value
        ws.cell(row=k+1,column=2).value = sheet1['B'+str(row)].value
        ws.cell(row=k+1,column=3).value = sheet1['C'+str(row)].value
        ws.cell(row=k+1,column=4).value = sheet1['D'+str(row)].value
        ws.cell(row=k+1,column=5).value = sheet1['E'+str(row)].value
        ws.cell(row=k+1,column=6).value = sheet1['F'+str(row)].value
        # 标题合并居中
        ws.merge_cells("A1:D1")
        top_left_cell = ws['A1']
        top_left_cell.value = "20XXxxxxxx统计表"
        top_left_cell.font = title_font
        #每行样式
        for row in ws.rows:
            for cell in row:
                cell.alignment = alignment
                if cell.row == 1:
                    continue
                elif cell.row == 2:
                    cell.border = border
                    cell.font = row_title_font
                    cell.fill = row_title_fill
                else:
                    cell.border = border
                    cell.font = content_font
                    cell.fill = content_fill
#设置列宽函数
        liekuan(ws)
        wb.save('{}_test.xlsx'.format(i))
        xl_app = gencache.EnsureDispatch("Excel.Application")
        #重新设置新的空模板，由ok2另存为空模板
        xl_wk = excel.Workbooks.Open(r"D:\python\ok2.xlsx")
        xl_wk.SaveAs("D:\python\ok.xlsx")
        xl_app.Quit() 

print("已做好！")