【初学Python】生成当前工作日TDLのExcel文件

'''
1.get_weekday(date_string)作用：输入日期返回对应星期几
2.get_current_month_workdays()作用：返回当前日期的年月以及对应的工作日list
3.get_last_month_workdays()作用：返回当前日期的下个月份年月以及对应的工作日list
4.make_current_tdl(path)作用：生成当前月份的TDL模板（只展示当前月份的工作日TDL）;path为绝对路径
'''
from datetime import date, timedelta, datetime
import pandas as pd


def get_weekday(date_string):
    # 将日期字符串转换为 datetime 对象
    date_object = datetime.strptime(date_string, '%Y-%m-%d')
    # 获取星期几（0代表星期一，1代表星期二，以此类推）
    weekday = date_object.weekday()
    # 将数字表示的星期转换为实际的星期名称
    weekdays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
    weekday_name = weekdays[weekday]
    return weekday_name


def get_current_month_workdays():
    '''获取当前月份的工作列表'''
    current_date = date.today()

    # 获取当前月份的第一天
    first_day_of_month = current_date.replace(day=1)
    year_month = first_day_of_month.strftime('%Y-%m')
    # 获取下个月的第一天
    first_day_of_next_month = current_date.replace(day=1, month=current_date.month % 12 + 1,
                                                   year=current_date.year + current_date.month // 12)
    # 获取当前月份的最后一天
    last_day_of_month = first_day_of_next_month - timedelta(days=1)
    workdays = pd.date_range(start=first_day_of_month, end=last_day_of_month, freq='B')
    workdayslist = []
    for wd in workdays:
        workdayslist.append(wd.strftime('%Y-%m-%d'))
    return year_month, workdayslist


def get_last_month_workdays():
    '''
    获取当前下个月份的工作列表
    :return:
    '''
    current_date = date.today()
    # 获取下个月的第一天
    first_day_of_next_month = current_date.replace(day=1, month=current_date.month % 12 + 1,
                                                   year=current_date.year + current_date.month // 12)
    year_month = first_day_of_next_month.strftime('%Y-%m')
    # 获取下下个月的第一天
    first_first_day_of_next_month = first_day_of_next_month.replace(day=1, month=first_day_of_next_month.month % 12 + 1,
                                                                    year=first_day_of_next_month.year + first_day_of_next_month.month // 12)
    # 获取下个月的最后一天
    last_day_of_month = first_first_day_of_next_month - timedelta(days=1)
    workdays = pd.date_range(start=first_day_of_next_month, end=last_day_of_month, freq='B')
    workdayslist = []
    for wd in workdays:
        workdayslist.append(wd.strftime('%Y-%m-%d'))
    return year_month, workdayslist


import os
import openpyxl
from openpyxl.styles import Font, colors, Alignment, Border, Side, numbers, PatternFill


def make_current_tdl(path):
    xlsx_year_month, workdayslist = get_current_month_workdays()
    xlsx_name = xlsx_year_month + '-龚传友-TDL.xlsx'
    xlsx_path_name = os.path.join(path, xlsx_name)
    # print(xlsx_name, xlsx_path_name)
    if not os.path.exists(xlsx_path_name):
        wb = openpyxl.workbook.Workbook()
        for workday in workdayslist:
            ws = wb.create_sheet(title=workday)
            # 设置网格线隐藏
            ws.sheet_view.showGridLines = False  # 隐藏网格线
            # 设置列宽
            ws.column_dimensions['A'].width = 1
            ws.column_dimensions['B'].width = 3.38
            ws.column_dimensions['C'].width = 4.04
            ws.column_dimensions['D'].width = 10.21
            ws.column_dimensions['E'].width = 10.21
            ws.column_dimensions['F'].width = 35
            ws.column_dimensions['G'].width = 35
            ws.column_dimensions['H'].width = 8.05
            ws.column_dimensions['I'].width = 20.21
            ws.column_dimensions['J'].width = 10.21
            ws.column_dimensions['K'].width = 10.21
            ws.column_dimensions['L'].width = 35
            ws.column_dimensions['M'].width = 35
            # 设置行高
            ws.row_dimensions[1].height = 5
            ws.row_dimensions[2].height = 29
            ws.row_dimensions[3].height = 173
            ws.row_dimensions[4].height = 25
            ws.row_dimensions[5].height = 25
            ws.row_dimensions[6].height = 84
            ws.row_dimensions[7].height = 84
            ws.row_dimensions[8].height = 84
            ws.row_dimensions[9].height = 84
            ws.row_dimensions[10].height = 80

            # 合并单元格
            ws.merge_cells('B2:M2')
            ws.merge_cells('B3:G3')
            ws.merge_cells('I3:M3')
            ws.merge_cells('B4:B5')
            ws.merge_cells('C4:C5')
            ws.merge_cells('D4:D5')
            ws.merge_cells('E4:E5')
            ws.merge_cells('F4:I4')
            ws.merge_cells('J4:K4')
            ws.merge_cells('L4:L5')
            ws.merge_cells('M4:M5')
            ws.merge_cells('B10:M10')
            ws.merge_cells('C6:C9')
            # 单元格边框样式模板--thin 细线 medium 中等线 thick 粗线
            border_stand = Border(left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'),
                                  top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'))
            # 字体样式模板
            font_biaoti = Font(name='微软雅黑', size=16, color="000000", bold=True)
            font_stand = Font(name='微软雅黑', size=10, color="000000")
            # 单元格对齐方式为水平居中和垂直居中
            alignment_stand = Alignment(horizontal='center', vertical='center', wrap_text=True)
            alignment_content = Alignment(horizontal='left', vertical='center', wrap_text=True)
            # 设置全局单元格样式
            for row in ws['B2:M10']:
                for cell in row:
                    cell.border = border_stand
                    cell.font = font_stand
                    cell.alignment = alignment_stand
            # 设置主题
            ws['B2'] = 'To Do List 复盘总结 ' + workday + ' ' + get_weekday(workday)
            ws['B2'].font = font_biaoti
            # 设置月周目标
            ws['B3'] = '''本月目标：
1、组织过程资产更新
2、信息化支持进度管理
'''
            ws['B3'].font = Font(name='微软雅黑', size=12, color="FF0000", bold=True)
            ws['B3'].alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
            ws['B3'].fill = PatternFill(fill_type='solid', start_color='FFEBCD')

            ws['I3'] = '''本周目标：
'''
            ws['I3'].font = Font(name='微软雅黑', size=12, color="FF0000", bold=True)
            ws['I3'].alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
            ws['I3'].fill = PatternFill(fill_type='solid', start_color='F5F5DC')

            # 设置标题行
            for row in ws['B4:M5']:
                for cell in row:
                    cell.font = Font(name='微软雅黑', size=12, color="000000", bold=True)
            ws['B4'] = '序号'
            ws['C4'] = '板块'
            ws['D4'] = 'From'
            ws['E4'] = 'To'
            ws['F4'] = '工作计划'
            ws['F5'] = '工作计划'
            ws['G5'] = '实施行动'
            ws['H5'] = '优先级'
            ws['I5'] = '资源协助'
            ws['J4'] = '工作总结'
            ws['J5'] = '完成情况'
            ws['K5'] = '完成比例'
            ws['L4'] = '问题分析'
            ws['M4'] = '改进策略/备注'
            # 设置内容预填写
            ws['B6'] = 1
            ws['B7'] = 2
            ws['B8'] = 3
            ws['B9'] = 4
            ws['C6'] = '工作项目'
            ws['D6'] = '08:30'
            ws['E6'] = '09:00'
            ws['F6'] = 'IT内部例会'
            ws['H6'] = 1
            ws['K6'] = 1
            ws['B10']='''今日总结：
'''
            ws['B10'].alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
            # 设置完成比例格式为百分比
            for row in ws['K6:K9']:
                for cell in row:
                    cell.number_format = numbers.FORMAT_PERCENTAGE
            # 设置序号、板块字体加粗
            for row in ws['B6:C9']:
                for cell in row:
                    cell.font = Font(name='微软雅黑', size=12, color="000000", bold=True)
            # 删除多余的行列
            if ws.max_row > 11:
                ws.delete_rows(12, ws.max_row - 11)
            if ws.max_column > 15:
                ws.delete_rows(16, ws.max_row - 15)

        wb.remove(wb['Sheet'])
        wb.save(xlsx_path_name)
        print(f'已成功创建文件{xlsx_path_name}')
    else:
        print('文件已存在')


make_current_tdl(r'D:\Work\11TDL')