python生成工作计划

import os
from calendar import monthrange
from datetime import datetime
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill

# 定义常量
# 定义常量
TITLE_FONT = Font(name='宋体', size=20, bold=True, color="FFFFFF")
HEADER_FONT = Font(bold=True, color="000000")
CENTER_ALIGNMENT = Alignment(horizontal='center', vertical='center')
WRAP_ALIGNMENT = Alignment(wrap_text=True, horizontal='center', vertical='center')
THIN_BORDER = Border(left=Side(style='thin'), right=Side(style='thin'),
                     top=Side(style='thin'), bottom=Side(style='thin'))

# 更鲜明的配色
HEADER_FILL = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')  # 深蓝
SUB_HEADER_FILL = PatternFill(start_color='BFBFBF', end_color='BFBFBF', fill_type='solid')  # 中灰
CALENDAR_HEADER_FILL = PatternFill(start_color='ED7D31', end_color='ED7D31', fill_type='solid')  # 橙色
ROW_FILL_EVEN = PatternFill(start_color='EDEDED', end_color='EDEDED', fill_type='solid')  # 偶数行填充色


def fill_border(ws, last_day):
    # 设置表格边框
    max_row = last_day + 6
    for row in ws.iter_rows(min_row=3, max_row=last_day + 5):
        for cell in row:
            cell.border = THIN_BORDER

    # 合并辅助单元格
    ws.merge_cells(f"A1:A{max_row}")
    ws['A1'].fill = HEADER_FILL
    ws.column_dimensions['A'].width = 2
    ws.merge_cells('A2:P2')
    ws['A2'].fill = HEADER_FILL
    ws.row_dimensions[2].height = 10
    ws.merge_cells('A4:P4')
    ws['A4'].fill = HEADER_FILL
    ws.row_dimensions[4].height = 10
    ws.merge_cells(f"I2:I{max_row}")
    ws['I2'].fill = HEADER_FILL
    ws.column_dimensions['I'].width = 2
    ws.merge_cells(f"P1:P{max_row}")
    ws['P1'].fill = HEADER_FILL
    ws.column_dimensions['P'].width = 2
    ws.merge_cells(f"A{max_row}:P{max_row}")
    ws[f'A{max_row}'].fill = HEADER_FILL
    ws.row_dimensions[max_row].height = 10


def fill_plan_table(ws, year, month, last_day, start_row, start_col):
    """
    在指定工作表中填充工作计划表格。

    :param ws: 工作表对象
    :param year: 年份
    :param month: 月份
    :param last_day: 该月最后一天
    :param start_row: 表头开始行号
    :param start_col: 表头开始列号
    :return: None
    """
    # 设置表头
    plan_headers = ["序号", "日期", "工作名称", "具体内容", "进度", "状态", "备注"]
    for col, header in enumerate(plan_headers, start=start_col):
        cell = ws.cell(row=start_row, column=col, value=header)
        cell.font = HEADER_FONT
        cell.alignment = CENTER_ALIGNMENT
        cell.fill = CALENDAR_HEADER_FILL

    # 设置列宽
    column_widths = {
        0: 5,  # 序号
        1: 10,  # 日期
        2: 20,  # 工作名称
        3: 100,  # 具体内容
        4: 15,  # 进度
        5: 15,  # 状态
        6: 20  # 备注
    }

    for col_offset, width in column_widths.items():
        col_letter = ws.cell(row=start_row, column=start_col + col_offset).column_letter
        ws.column_dimensions[col_letter].width = width

    # 生成模拟数据
    work_items = []
    for i in range(1, last_day + 1):
        item = {
            "date": f"{year}-{month}-{i}",
            "name": "",
            "content": "",
            "progress": "",
            "status": "",
            "remark": ""
        }
        work_items.append(item)

    # 填充数据行
    plan_data_row = start_row + 1
    for idx, item in enumerate(work_items, start=1):
        ws.cell(row=plan_data_row, column=start_col, value=idx)
        ws.cell(row=plan_data_row, column=start_col + 1, value=item["date"])
        ws.cell(row=plan_data_row, column=start_col + 2, value=item["name"])
        ws.cell(row=plan_data_row, column=start_col + 3, value=item["content"]).alignment = WRAP_ALIGNMENT
        ws.cell(row=plan_data_row, column=start_col + 4, value=item["progress"])
        ws.cell(row=plan_data_row, column=start_col + 5, value=item["status"])
        ws.cell(row=plan_data_row, column=start_col + 6, value=item["remark"])
        plan_data_row += 1
        if idx % 2 == 0:
            for col in range(start_col, start_col + 7):
                ws.cell(row=plan_data_row, column=col).fill = ROW_FILL_EVEN


def fill_calendar(ws, year, month, start_row, start_col):
    """
    在指定工作表中填充日历信息。

    :param ws: 工作表对象
    :param year: 年份
    :param month: 月份
    :param start_row: 开始行号
    :param start_col: 开始列号
    """
    # 日历标题
    calendar_header = ["一", "二", "三", "四", "五", "六", "日"]
    for col, header in enumerate(calendar_header, start=start_col):
        cell = ws.cell(row=start_row, column=col, value=header)
        cell.font = HEADER_FONT
        cell.alignment = CENTER_ALIGNMENT
        cell.fill = CALENDAR_HEADER_FILL

    # 填充日历日期
    first_day = datetime(year, month, 1)
    last_day = monthrange(year, month)[1]
    start_cell_col = first_day.weekday() + start_col

    current_row = start_row + 1
    current_col = start_cell_col

    for day in range(1, last_day + 1):
        ws.cell(row=current_row, column=current_col, value=day)
        cell = ws.cell(row=current_row, column=current_col)
        cell.alignment = CENTER_ALIGNMENT
        cell.border = THIN_BORDER
        current_col += 1

        # 如果到达一周末尾（假设每周7天）
        if current_col > start_col + 6:
            current_col = start_col
            current_row += 1


def create_month_sheet(wb, year, month):
    ws = wb.create_sheet(title=f"{month}月")

    # 大标题
    ws.merge_cells('B1:P1')
    ws['B1'] = "工作计划表—月度规划"
    ws['B1'].font = TITLE_FONT
    ws['B1'].alignment = CENTER_ALIGNMENT
    ws['B1'].fill = HEADER_FILL

    # 工作进度相关设置
    ws.merge_cells('B3:C3')
    ws['B3'] = "工作进度："
    ws.merge_cells('D3:H3')
    ws.merge_cells('J3:K3')
    ws['J3'] = "本月工作安排数："
    ws.merge_cells('M3:N3')
    ws['M3'] = "已完成："
    ws['B3'].fill = SUB_HEADER_FILL
    ws['J3'].fill = SUB_HEADER_FILL
    ws['M3'].fill = SUB_HEADER_FILL

    start_row = 5
    start_col = 10

    # 填充工作计划表格
    last_day = monthrange(year, month)[1]
    fill_plan_table(ws, year=year, month=month, last_day=last_day, start_row=start_row, start_col=2)
    # 填充日历
    fill_calendar(ws, year=year, month=month, start_row=start_row, start_col=start_col)
    fill_border(ws, last_day=last_day)
    return ws


# 创建工作簿和工作表
wb = Workbook()
# 删除默认的 sheet
wb.remove(wb.active)

# 生成 12 个月的 sheet
for m in range(1, 13):
    create_month_sheet(wb, year=2025, month=m)

# 创建目标目录（如果不存在）
path = "../../output/cale"
try:
    os.makedirs(path, exist_ok=True)
    # 保存文件到指定路径
    filename = "2025年工作计划.xlsx"
    wb.save(os.path.join(path, filename))
except OSError as e:
    print(f"创建目录或保存文件时发生错误: {e}")