发布于2020-02-25 12:27 阅读(1199) 评论(0) 点赞(8) 收藏(5)
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("已做好!")
作者:虎王168
链接:https://www.pythonheidong.com/blog/article/233236/f1755e625a329881b5f1/
来源:python黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 python黑洞网 All Rights Reserved 版权所有,并保留所有权利。 京ICP备18063182号-1
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!