Python+openpyxl+tkinter自动生成指定格式Excel文件

# -*- coding: utf-8 -*-
# @Team     : Pig worm
# @Author   : BeiGu
# @Time     : 2024/3/29 10:00
# @fileName : CreExcelFile.py
# @Tool     : PyCharm
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Border, Side, colors, PatternFill, Font, Alignment
import time
import os
import tkinter
import tkinter.messagebox



def createExcelFile():
    today = time.strftime("%Y-%m-%d", time.localtime())
    name1 = "/"
    path = fileName = r"D:/路径/路径/" + today
    fileName = r"D:/路径/路径/" + today  + name1 + today + "-SKU-工作安排.xlsx"
    xlsx = openpyxl.Workbook()
    name = ["注意事项","总表","sheet1(自定义sheet页名称)","sheet2(自定义sheet页名称)","sheet3(自定义sheet页名称)","sheet4(自定义sheet页名称)","完成情况"]
    name_len = len(name)
    for i in range(name_len):
        sheets = xlsx.create_sheet(name[i])
        if name[i] == '注意事项':
            sheets.sheet_properties.tabColor = 'FF0000'
        if name[i] != "注意事项" and name[i] != "完成情况":
            sheets.append(['商品外部标识', '商品名称', '商品SKU', 'SKU商品成本 / 供货价', '云平台VIP价', '云平台原价', '云平台现价', '会员百分比'])
        if name[i] == "完成情况":
            ownWork = ['姓名','上架/选品','完成数量','问题商品','目标数量','目标完成度','日期','备注']
            assistWork = ['姓名','上架/选品','完成数量','问题商品','目标数量','目标完成度','总计数量','分配总数量','总完成度','日期','备注']
            sheets.merge_cells(range_string='E6:H6')
            sheets['E6'] = '本职工作完成情况：'
            sheets['E6'].font = Font(size=20,bold=True)
            letter = ['E','F','G','H','I','J','K','L','M','N','O']
            letter_len = len(letter)
            # 批量设置单元格宽度
            for c in range(letter_len):
                sheets.column_dimensions[letter[c]].width = 16
            # 单元格边框
            border_set = Border(left=Side(style='medium', color=colors.BLACK),
                                right=Side(style='medium', color=colors.BLACK),
                                top=Side(style='medium', color=colors.BLACK),
                                bottom=Side(style='medium', color=colors.BLACK))
            redFill = PatternFill(start_color='00CCFF', end_color='00CCFF', fill_type='solid')
            # 设置字体大小
            fontStyle = Font(size="16",bold=True)
            # 获取长度
            ownWork_len = len(ownWork)
            assistWork_len = len(assistWork)
            for i in range(ownWork_len):
                # 单元格边框
                sheets[letter[i] + '8'].border = border_set
                # 设置背景色
                sheets[letter[i] + '8'].fill = redFill
                # 设置字体大小
                sheets[letter[i] + '8'].font = fontStyle
                # 写人表头值
                sheets[letter[i] + '8'] = ownWork[i]
                # 居中显示
                sheets[letter[i] + '8'].alignment = Alignment(horizontal='center', vertical='center')
            # 插入内容
            tab_li = [5, 6, 9, 11]
            tab_value = ['自定义姓名1', '上架', '500', today]
            tab_value_two = ['自定义姓名2', '上架', '500', today]
            tab_li_len = len(tab_li)
            for j in range(tab_li_len):
                sheets.cell(row=9, column=tab_li[j], value=tab_value[j]).alignment = Alignment(horizontal='center', vertical='center')
                sheets.cell(row=10, column=tab_li[j], value=tab_value_two[j]).alignment = Alignment(horizontal='center', vertical='center')
            sheets.merge_cells(range_string='E15:H15')
            sheets['E15'] = '同事协助完成情况：'
            sheets['E15'].font = Font(size=20, bold=True)
            for s in range(assistWork_len):
                # print("单元格坐标：" + letter[i] + '17', '单元格填充内容：' + assistWork[i])
                # 单元格边框
                sheets[letter[s] + '17'].border = border_set
                # 设置背景色
                sheets[letter[s] + '17'].fill = redFill
                # 设置字体大小
                sheets[letter[s] + '17'].font = fontStyle
                # 写入表头值
                sheets[letter[s] + '17'] = assistWork[s]
                # 居中显示
                sheets[letter[s] + '17'].alignment = Alignment(horizontal='center', vertical='center')
            assistWork_value1 = ['name-1', '选品', '800', today]
            assistWork_value2 = ['name-2', '上架', '500', today]
            assistWork_value3 = ['name-3', '上架', '500', today]
            assistWork_value4 = ['name-4', '上架', '500', today]
            assistWork_value5 = ['name-5', '上架', '500', today]
            assistWork_li = [5, 6, 9, 14]
            assistWork_li_len = len(assistWork_li)
            for h in range(assistWork_li_len):
                sheets.cell(row=18, column=assistWork_li[h], value=assistWork_value1[h]).alignment = Alignment(horizontal='center',vertical='center')
                sheets.cell(row=19, column=assistWork_li[h], value=assistWork_value2[h]).alignment = Alignment(horizontal='center',vertical='center')
                sheets.cell(row=20, column=assistWork_li[h], value=assistWork_value3[h]).alignment = Alignment(horizontal='center', vertical='center')
                sheets.cell(row=21, column=assistWork_li[h], value=assistWork_value4[h]).alignment = Alignment(horizontal='center', vertical='center')
                sheets.cell(row=22, column=assistWork_li[h], value=assistWork_value5[h]).alignment = Alignment( horizontal='center', vertical='center')
    xlsx.remove_sheet(xlsx.get_sheet_by_name("Sheet"))
    window = tkinter.Tk()
    if not os.path.exists(path):
        os.makedirs(path)
        xlsx.save(fileName)
        window.withdraw()
        result = tkinter.messagebox.showinfo('1002','文件已保存在：' + str(fileName))
    elif os.path.exists(fileName):
        window.withdraw()  # 退出默认 tk 窗口
        result = tkinter.messagebox.showerror('1001', '在此目录下已存在相同路径文件')



createExcelFile()