准备工作:xlsx文件,sheet页设置好统一格式
list1 = ['财务部', '市场部', '工程部', '采购部', '技术部', '办公室', '人力资源部', '安全部', '其他']
wb = load_workbook('test.xlsx')
sheet = wb[wb.sheetnames[0]]
year = datetime.now().year
sheet['A1'].value = '测试数据2024'.format(year)
source_sheet = wb['汇总']
all_list = []
for s in list1:
# 创建一个新的sheet页,并复制内容和格式
new_sheet = wb.copy_worksheet(source_sheet)
# 可以修改新sheet的名字
new_sheet.title = s
new_sheet['A1'].value = '{}{}测试数据'.format(s, year)
for sheet in wb.sheetnames:
# 处理每个sheet页,写入对应数据
# .......
ws = wb[sheet]
start_row = 3 # 跳过表头
for row_data in all_list:
for col, value in enumerate(row_data.values(), start=1):
coordinate = ws.cell(row=start_row, column=col, value=value).coordinate # 写入数据,获取单元格的坐标
ws.row_dimensions[start_row].height = 25 # 设置行高
ws[coordinate].border = thin_border # 设置边框
ws[coordinate].alignment = Alignment(vertical='center', horizontal='center') # 设置单元格居中对齐
start_row += 1
wb.save('example999.xlsx')