openpyxl操作excel

设置单元格颜色

cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象
# 对包含指定字符的单元格进行颜色填充
cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFEE0808', end_color='FFEE0808',
                                                fill_type='solid')

设置单元格批注

cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")

详细代码如下

import openpyxl
import pandas as pd
import re
from datetime import datetime
from datetime import datetime, timedelta
from openpyxl.styles import colors, Font
import  os

# 考勤表
# excel_chidao = r"D:\飞书下载表格\导出考勤表2024-06-01.xlsx"
# 补卡
# excel_buka = r"D:\飞书下载表格\补卡2024-06-01.xlsx"
# 请假
# excel_holiday = r"D:\飞书下载表格\请假2024-06-01.xlsx"
# # 加班申请
# excel_overtime = r"D:\飞书下载表格\加班申请2024-06-01.xlsx"

# 迟到
# df_chidao = pd.read_excel(excel_chidao, skiprows=1, index_col=0)
# # 补卡
# df_buka = pd.read_excel(excel_buka, skiprows=1, index_col=0)
# df_buka_cus = pd.DataFrame(df_buka, columns=["发起人姓名", "异常日期"])
# 打卡地址预处理
def pre_daka_addr(excel_daka):
    """
    对打卡地点数据做预处理
    :param excel_daka:
    :return:
    """
    df = pd.read_excel(excel_daka, skiprows=1)
    df = pd.DataFrame(df, columns=["Unnamed: 0", "日期", "上班 1 打卡地点", "下班 1 打卡地点"])
    df = df.rename(columns={"Unnamed: 0": "姓名"})
    df["是否出差"] = ""
    df["是否出差"] = df.apply(lambda x: compute_weather_trip(x["上班 1 打卡地点"], x["下班 1 打卡地点"]), axis=1)
    return df

# 计算是否出差
def compute_weather_trip(a, b):
    """
    根据打卡地点判断是否在出差
    :param a: 上班打卡地点
    :param b: 下班打卡地点
    :return:
    """
    addr_a = a[0:6]
    addr_b = b[0:6]
    if addr_a == "-" or addr_b == "-":
        return "其他"
    elif (addr_a not in ["菁蓉国际广场", "四川省成都市"]) and (addr_b not in ["菁蓉国际广场", "四川省成都市"]):
        return "是"
    else:
        return "否"

# 出差批注
def add_chuchai_comment(df, excel_output_path):
    """
    核对出差数据
    :param df: 打卡地点预处理后的数据
    :param excel_output_path: 汇总统计表
    :return:
    """
    df = pre_daka_addr(excel_daka)
    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表
    row_index = 0  # 行索引
    for row in sheet.iter_rows(values_only=True):  # 遍历每一行
        row_index += 1  # 行索引自增, 从1开始
        cell_index = 0  # 列索引
        # 跳过第一行标题栏
        if row_index == 1:
            continue
        for cell in row:
            cell_index += 1  # 列索引自增, 从1开始
            # 跳过第一列姓名列
            if cell_index == 1:
                continue
            if not cell:  # 跳过空白
                continue
            char = dec_to_alphanumeric(cell_index)  # 列索引转字母
            pos = f"{char}{row_index}"  # 拼接行列坐标
            cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象

            row_title = sheet["A" + str(row_index)].value
            column_title = sheet[char + "1"].value
            nickname = row_title
            dates = column_title.split(" ")[0]

            if (df.query("`姓名`==@nickname and `日期`==@dates").shape[0] == 1) and (
                    df.query("`姓名`==@nickname and `日期`==@dates").values[0][4] == "是"):
                if cell_obj.value != "出差" and cell_obj.value != "休":
                    cell_obj.value = "出差"
    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件

# 添加日报批注
def add_daily_comment(excel_ribao,excel_output_path):
    """
    添加日报批注
    :param df: 日报预处理后的数据
    :param excel_output_path: 汇总统计表
    :return:
    """
    df = pd.read_excel(excel_ribao)
    df = pd.DataFrame(df, columns=["姓名", "提交时间"])
    df["提交时间"] = pd.to_datetime(df["提交时间"])
    df["提交时间"] = df["提交时间"].apply(compute_daily_time)
    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表
    row_index = 0  # 行索引
    for row in sheet.iter_rows(values_only=True):  # 遍历每一行
        row_index += 1  # 行索引自增, 从1开始
        cell_index = 0  # 列索引
        # 跳过第一行标题栏
        if row_index==1:
            continue
        for cell in row:
            cell_index += 1  # 列索引自增, 从1开始
            # 跳过第一列姓名列
            if cell_index==1:
                continue
            if not cell:  # 跳过空白
                continue
            char = dec_to_alphanumeric(cell_index)  # 列索引转字母
            pos = f"{char}{row_index}"  # 拼接行列坐标
            cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象

            row_title = sheet["A" + str(row_index)].value
            column_title = sheet[char + "1"].value
            nickname=row_title
            dates=column_title.split(" ")[0]
            weekend=column_title.split(" ")[1]
            if weekend=="星期六" or weekend=="星期日":
                continue

            if cell_obj.value=="休" or cell_obj.value=="年假" or cell_obj.value=="调休假" or cell_obj.value=="病假" or cell_obj.value=="事假" or cell_obj.value=="婚假" or cell_obj.value=="产假" or cell_obj.value=="陪产假" or cell_obj.value=="丧假" or cell_obj.value=="哺乳假":
                continue
            if df.query("`姓名`==@nickname and `提交时间`==@dates").shape[0] ==0:
                old_value=cell_obj.value
                cell_obj.value=old_value+"\n"+"未提交"
                cell_obj.font=Font(color="FF0000")
    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件

# 计算日报时间
def compute_daily_time(now_time):
    """
    计算日报实际日期
    :param now_time: 下载的日报填写日期
    :return:
    """
    date = str(now_time).split(" ")[0]
    first_time = date + " " + "17:00:00"
    second_time = add_one_day(date, 1) + " " + "10:00:00"
    first_time_strp = datetime.strptime(first_time, "%Y-%m-%d %H:%M:%S")
    sencond_time_strp = datetime.strptime(second_time, "%Y-%m-%d %H:%M:%S")

    # 昨日
    yes_date = add_one_day(date, -1)
    yes_start_time = yes_date + " " + "17:00:00"
    yes_end_time = date + " " + "10:00:00"
    yes_start_time_strp = datetime.strptime(yes_start_time, "%Y-%m-%d %H:%M:%S")
    yes_end_time_strp = datetime.strptime(yes_end_time, "%Y-%m-%d %H:%M:%S")
    daily_time = ""
    # 判断是否是今日日报
    if first_time_strp < now_time < sencond_time_strp:
        daily_time = date
        return daily_time
    # 判断是否是昨日日报
    elif yes_start_time_strp < now_time < yes_end_time_strp:
        daily_time = yes_date
        return daily_time
    return "不是上月日报"

# 对天数做加减操作
def add_one_day(date_str, num):
    # 将字符串转换为 datetime 对象
    date = datetime.strptime(date_str, "%Y-%m-%d")
    # 加上一天
    next_day = date + timedelta(days=num)
    # 如果需要，将 datetime 对象转换回字符串
    next_day_str = next_day.strftime("%Y-%m-%d")
    return next_day_str

# 两个时间相减
def double_time_reduce(start, end):
    """
    两个时间相减
    :param start:
    :param end:
    :return:
    """
    # 将时间字符串解析为 datetime 对象
    time_format = "%H:%M"
    dt1 = datetime.strptime(start, time_format)
    dt2 = datetime.strptime(end, time_format)
    # 计算时间差
    time_difference = dt2 - dt1
    # 将时间差转换为小时
    hours_difference = time_difference.total_seconds() / 3600
    return hours_difference

# 研发部工作日出勤数据预处理
def change_excel(excel_download_path):
    """
    统计研发部工作日出勤数据---判断是否加班
    :param excel_download_path:
    :return:
    """
    # 读取下载的 excel 表格
    data_records = pd.read_excel(excel_download_path, skiprows=1)
    # 筛选研发部
    data_records = data_records[data_records["部门"] == "技术一部"]
    data_records = data_records[data_records["Unnamed: 0"] != "阿灿"]
    data_records = data_records.to_dict(orient="records")
    # 处理数据为新数据格式
    new_data_list = []
    for row_data in data_records:
        new_row_data = {"姓名": row_data["Unnamed: 0"]}
        pattern = r'^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$'  # 正则表达式，匹配一个字母
        for key, value in row_data.items():
            if bool(re.match(pattern, key[:10])):
                new_row_data[key] = value
        new_data_list.append(new_row_data)
    # print(new_data_list)
    # 统计数据
    for row_data in new_data_list:
        for key, value in row_data.items():
            if key == "姓名":  # 跳过姓名列
                continue
            # 拆分出字段
            # print(key,"===",value)
            # 正则表达式模式，用于匹配时间格式（HH:MM）
            time_pattern = r'\b(\d{2}:\d{2})\b'
            # 使用 re.findall() 提取所有匹配的时间
            arr = re.findall(time_pattern, value)
            print("date", arr)
            if len(arr) != 2:
                row_data[key] = "无"
                # continue
            # 计算出勤时间
            else:
                start = arr[0]
                end = arr[1]
                result = double_time_reduce(start, end) - 10
                row_data[key] = "加班一次" if result >= 0 else "无"
    # pd.DataFrame.from_records(new_data_list).to_excel(excel_output_path, index=False)
    df = pd.DataFrame.from_records(new_data_list)
    results = []
    for index, row in df.iterrows():
        for col in df.columns[1:]:
            if row[col] == "加班一次":
                results.append([row["姓名"], col])
    return results


# 研发部工作日加班统计
def weekday_overtime(excel_download_path, excel_output_path):
    """
    统计研发部工作日加班情况
    :param excel_download_path: 导出的考勤表
    :param excel_output_path: 输出的汇总表
    :return:
    """

    # 工作日出勤数据预处理
    dt = change_excel(excel_download_path)

    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表

    for arr in dt:
        nickname = arr[0]
        dates = arr[1]
        # dates = reverseDate(arr[2])
        row_index = 0  # 行索引
        for row in sheet.iter_rows(values_only=True):  # 遍历每一行
            row_index += 1  # 行索引自增, 从1开始
            cell_index = 0  # 列索引
            for cell in row:
                cell_index += 1  # 列索引自增, 从1开始
                if not cell:  # 跳过空白
                    continue
                char = dec_to_alphanumeric(cell_index)  # 列索引转字母
                pos = f"{char}{row_index}"  # 拼接行列坐标
                cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象

                row_title = sheet["A" + str(row_index)].value
                index_title = sheet[char + "1"].value
                if row_title == nickname and index_title == dates:
                    print(cell_obj.value)
                    # 设置单元格为加班样式
                    if (cell_obj.fill.start_color != "FF2d529f"):
                        cell_obj.fill = openpyxl.styles.PatternFill(start_color='FF2d529f', end_color='FF2d529f',
                                                                    fill_type='solid')
                    # 判断单元格内容
                    if "加班" not in cell_obj.value:
                        cell_obj.value = "加班"
                    # 添加批注
                    if not cell_obj.comment:
                        cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                    # 检查单元格是否有批注
                    if cell_obj.comment:
                        # 获取现有批注的文本
                        current_comment = cell_obj.comment.text
                        # 定义要追加的内容
                        # 防止重复添加批注
                        content = "加班一次"
                        if content not in current_comment:
                            additional_text = "\n" + content  # 换行并添加新内容
                            # 将新内容追加到现有批注文本后
                            new_comment_text = current_comment + additional_text
                            # 更新批注文本
                            cell_obj.comment.text = new_comment_text
    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件


# 研发部非工作日加班批注
def add_jiaban_comment(excel_overtime, excel_output_path):
    """
    添加非工作日加班批注
    :param excel_overtime:
    :param excel_output_path:
    :return:
    """
    # 加班申请预处理
    df = pd.read_excel(excel_overtime, skiprows=1)
    df = df[df["申请状态"] == "已同意"]
    df = df[df["发起人部门"] == "研发中心/技术一部"]
    df=df[df["发起人姓名"] !="阿灿"]
    df_cus = pd.DataFrame(df, columns=["发起人姓名", "申请状态", "开始时间", "结束时间", "时长", "发起人部门"])
    df_cus['开始时间'] = df_cus['开始时间'].str.split(' ').str[0]
    df_cus['结束时间'] = df_cus['结束时间'].str.split(' ').str[0]

    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表

    dt = df_cus.values
    for arr in dt:
        nickname = arr[0]
        dates = reverseDate(arr[2])
        # 加班时长
        overtime=str(arr[4])+"小时"
        start_date=arr[2]
        end_date=arr[3]


        row_index = 0  # 行索引
        for row in sheet.iter_rows(values_only=True):  # 遍历每一行
            row_index += 1  # 行索引自增, 从1开始
            cell_index = 0  # 列索引
            for cell in row:
                cell_index += 1  # 列索引自增, 从1开始
                if not cell:  # 跳过空白
                    continue
                char = dec_to_alphanumeric(cell_index)  # 列索引转字母
                pos = f"{char}{row_index}"  # 拼接行列坐标
                cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象

                row_title = sheet["A" + str(row_index)].value
                index_title = sheet[char + "1"].value
                if row_title == nickname and index_title == dates:
                    print(cell_obj.value)
                    # 设置单元格为加班样式
                    if(cell_obj.fill.start_color != "FF2d529f"):
                        cell_obj.fill = openpyxl.styles.PatternFill(start_color='FF2d529f', end_color='FF2d529f',
                                                                fill_type='solid')
                    # 判断周日是否也加班
                    if start_date !=end_date:
                        char_new = dec_to_alphanumeric(cell_index+1)  # 列索引转字母
                        pos_new = f"{char_new}{row_index}"  # 拼接行列坐标
                        cell_obj_new = sheet[pos_new]  # 根据行列坐标获取 cell 单元格对象
                        if (cell_obj_new.fill.start_color != "FF2d529f"):
                            cell_obj_new.fill = openpyxl.styles.PatternFill(start_color='FF2d529f', end_color='FF2d529f',
                                                                        fill_type='solid')
                        if "加班" not in cell_obj_new.value:
                            cell_obj_new.value = "加班"


                    # 判断单元格内容
                    if "加班" not in cell_obj.value :
                            cell_obj.value = "加班"
                    # 添加批注
                    if not cell_obj.comment:
                        cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                    # 检查单元格是否有批注
                    if cell_obj.comment:
                        # 获取现有批注的文本
                        current_comment = cell_obj.comment.text
                        # 定义要追加的内容
                        # 防止重复添加批注
                        content=overtime
                        if content not in current_comment:
                            additional_text = "\n" + content  # 换行并添加新内容
                            # 将新内容追加到现有批注文本后
                            new_comment_text = current_comment + additional_text
                            # 更新批注文本
                            cell_obj.comment.text = new_comment_text
    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件

# 事假
def add_holidays_comment(excel):
    df = pd.read_excel(excel, skiprows=1)
    df = df[df["假期类型"] == "事假"]
    df_cus = pd.DataFrame(df, columns=["发起人姓名", "假期类型", "开始时间", "结束时间", "时长"])
    df_cus['开始时间'] = df_cus['开始时间'].str.split(' ').str[0]
    return df_cus


def add_buka_comment(excel_buka, excel):
    """
    添加补卡批注
    :param excel_buka:
    :param excel: 汇总表
    :return:
    """
    # 补卡数据预处理
    df_buka = pd.read_excel(excel_buka, skiprows=1, index_col=0)
    df_buka_cus = pd.DataFrame(df_buka, columns=["发起人姓名", "异常日期", "异常记录"])
    df_buka_cus['异常记录'] = df_buka_cus['异常记录'].str.extract(r'(应上班|应下班)', expand=False)
    print(df_buka_cus)

    workbook = openpyxl.load_workbook(excel)
    sheet = workbook.active  # 选择工作表

    dt = df_buka_cus.values
    for arr in dt:
        print(arr)
        nickname = arr[0]
        dates = reverseDate(arr[1])
        if type(arr[2])==float:
            continue
        work_type = arr[2].replace("应", "")

        row_index = 0  # 行索引
        for row in sheet.iter_rows(values_only=True):  # 遍历每一行
            row_index += 1  # 行索引自增, 从1开始
            cell_index = 0  # 列索引
            for cell in row:
                cell_index += 1  # 列索引自增, 从1开始
                if not cell:  # 跳过空白
                    continue
                char = dec_to_alphanumeric(cell_index)  # 列索引转字母
                pos = f"{char}{row_index}"  # 拼接行列坐标
                cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象

                row_title = sheet["A" + str(row_index)].value
                index_title = sheet[char + "1"].value
                if row_title == nickname and index_title == dates:
                    print(cell_obj.value)
                    # 设置单元格为缺卡样式
                    if (cell_obj.fill.start_color != "FFCCFFFF"):
                        cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                                                                    fill_type='solid')
                        print(cell_obj.value)
                    # 判断单元格内容
                    if ("上班缺卡" in cell_obj.value) or ("下班缺卡" in cell_obj.value):
                        if (work_type + "缺卡") not in cell_obj.value:
                            cell_obj.value = cell_obj.value + work_type + "缺卡"

                    else:
                        cell_obj.value = ""
                        cell_obj.value = work_type + "缺卡"

                    # 添加批注
                    if not cell_obj.comment:
                        cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                                                                    fill_type='solid')
                        cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                    # 检查单元格是否有批注
                    if cell_obj.comment:
                        # 获取现有批注的文本
                        current_comment = cell_obj.comment.text
                        # 定义要追加的内容
                        # 防止重复添加批注
                        content = work_type + "已补卡"
                        if content not in current_comment:
                            additional_text = "\n" + content  # 换行并添加新内容
                            # 将新内容追加到现有批注文本后
                            new_comment_text = current_comment + additional_text
                            # 更新批注文本
                            cell_obj.comment.text = new_comment_text
    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件


def converDate(date_str):
    """
    转化日期格式
    2024-06-10 星期一  =====>2024年06月10日
    :return:
    """
    # 使用正则表达式提取日期部分
    date_part = re.findall(r'\d{4}-\d{2}-\d{2}', date_str)[0]
    # 将日期字符串转换为datetime对象
    date_obj = datetime.strptime(date_part, '%Y-%m-%d')
    # 格式化日期为所需的格式
    formatted_date = date_obj.strftime('%Y年%m月%d日')
    return formatted_date


def reverseDate(date_str):
    """
    转化日期格式
     2024年06月10日=====>2024-06-10 星期一
    :param date_str:
    :return:
    """
    # 将日期字符串转换为datetime对象
    date_obj = datetime.strptime(date_str, '%Y年%m月%d日')

    # 格式化日期为所需的格式
    formatted_date = date_obj.strftime('%Y-%m-%d')
    # 获取星期几，注意%A返回的是英文星期几
    week_day = date_obj.strftime('%A')
    # 将星期几转换为中文
    week_days = {
        'Monday': '星期一',
        'Tuesday': '星期二',
        'Wednesday': '星期三',
        'Thursday': '星期四',
        'Friday': '星期五',
        'Saturday': '星期六',
        'Sunday': '星期日'
    }
    week_day_chinese = week_days[week_day]
    # 拼接星期几到日期字符串
    formatted_date_with_weekday = f"{formatted_date} {week_day_chinese}"
    return formatted_date_with_weekday


def dec_to_alphanumeric(number):
    """
    数字转字母:
    1 - A; 2 - B; 4 - C; 4 - D
    :param number: 需要被转换的数字
    :return: 转换后的字母
    # dec_to_alphanumeric(1)  return A
    """
    base26 = []
    while number > 0:
        number, remainder = divmod(number, 26)
        if remainder == 0:
            base26.append('Z')
            number -= 1
        else:
            base26.append(chr(remainder - 1 + 65))
    base26 = base26[::-1]
    return ''.join(base26)



def add_comment(excel_download_path,excel_output_path,excel_buka):
    """
    添加迟到 缺卡 事假批注
    :param excel_download_path: 下载的考勤表
    :param excel_output_path: 生成的模板表
    :return:
    """
    df_buka = pd.read_excel(excel_buka, skiprows=1, index_col=0)
    df_buka_cus = pd.DataFrame(df_buka, columns=["发起人姓名", "异常日期"])
    df_chidao = pd.read_excel(excel_download_path, skiprows=1, index_col=0)
    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表
    row_index = 0  # 行索引
    for row in sheet.iter_rows(values_only=True):  # 遍历每一行
        row_index += 1  # 行索引自增, 从1开始
        cell_index = 0  # 列索引
        for cell in row:
            cell_index += 1  # 列索引自增, 从1开始
            if not cell:  # 跳过空白
                continue
            char = dec_to_alphanumeric(cell_index)  # 列索引转字母
            pos = f"{char}{row_index}"  # 拼接行列坐标
            cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象
            # 对包含指定字符的单元格进行颜色填充
            if "迟到" in cell:
                # 获取姓名
                nickname = sheet["A" + str(row_index)].value
                # 获取日期
                dates = sheet[char + str(1)].value
                # cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                # 查询迟到时间
                cell_value = df_chidao.loc[nickname, dates]
                matches = re.findall(r'\d+分钟', cell_value)
                result = matches[0]
                matches = re.findall(r"\d+", result)
                result = int(matches[0]) - 30
                content = "迟到" + str(result) + "分钟"
                # 检查单元格是否有批注
                if cell_obj.comment:
                    # 获取现有批注的文本
                    current_comment = cell_obj.comment.text
                    # 定义要追加的内容
                    # 防止重复添加批注
                    if content not in current_comment:
                        additional_text = "\n" + content  # 换行并添加新内容
                        # 将新内容追加到现有批注文本后
                        new_comment_text = current_comment + additional_text
                        # 更新批注文本
                        cell_obj.comment.text = new_comment_text
            if "缺卡" in cell:
                # cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                #                                             fill_type='solid')
                # cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                # 获取姓名
                nickname = sheet["A" + str(row_index)].value
                # 获取日期
                dates = sheet[char + str(1)].value
                dates = converDate(dates)

                # 查询补卡信息
                arr = df_buka_cus.query("`发起人姓名`==@nickname")["异常日期"].values
                if dates in arr:
                    # 检查单元格是否有批注
                    if not cell_obj.comment:
                        cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                                                                    fill_type='solid')
                        cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
                    if cell_obj.comment:
                        # 获取现有批注的文本
                        current_comment = cell_obj.comment.text
                        # 定义要追加的内容
                        # 防止重复添加批注
                        content = "已补卡"
                        if content not in current_comment:
                            additional_text = "\n" + content  # 换行并添加新内容
                            # 将新内容追加到现有批注文本后
                            new_comment_text = current_comment + additional_text
                            # 更新批注文本
                            cell_obj.comment.text = new_comment_text

            # if "早退" in cell:
            #     cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
            #                                                 fill_type='solid')
            #     cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
            if "事假" in cell:
                # 获取姓名
                nickname = sheet["A" + str(row_index)].value
                # 获取日期
                dates = sheet[char + str(1)].value
                dates = converDate(dates)
                # 查询事假时长
                df_holiday = add_holidays_comment(excel_holiday)
                df_holiday_cus = df_holiday.query("`发起人姓名`==@nickname and `开始时间`==@dates")
                # 获取时长
                duration = df_holiday_cus["时长"].values[0]
                if duration:
                    if int(duration <= 4):
                        content = "事假半天"
                        # 检查单元格是否有批注
                        if cell_obj.comment:
                            # 获取现有批注的文本
                            current_comment = cell_obj.comment.text
                            # 定义要追加的内容
                            # 防止重复添加批注
                            if content not in current_comment:
                                additional_text = "\n" + content  # 换行并添加新内容
                                # 将新内容追加到现有批注文本后
                                new_comment_text = current_comment + additional_text
                                # 更新批注文本
                                cell_obj.comment.text = new_comment_text

            # if "出差" in cell:
            #     cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFCCFFFF', end_color='FFCCFFFF',
            #                                                 fill_type='solid')
            #     cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")

    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件

def generate_basic_excel(excel_download_path, excel_output_path):
    # 读取下载的 excel 表格
    data_records = pd.read_excel(excel_download_path, skiprows=1).to_dict(orient="records")

    # 处理数据为新数据格式
    new_data_list = []
    for row_data in data_records:
        new_row_data = {"姓名": row_data["Unnamed: 0"]}
        pattern = r'^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$'  # 正则表达式，匹配一个字母
        for key, value in row_data.items():
            if bool(re.match(pattern, key[:10])):
                new_row_data[key] = value
        new_data_list.append(new_row_data)

    # 统计数据
    for row_data in new_data_list:
        for key, value in row_data.items():
            if key == "姓名":  # 跳过姓名列
                continue
            # 拆分出字段
            part_1 = value.split(";")[0]
            morning = part_1.split(",")[0]
            evening = part_1.split(",")[1] if len(part_1.split(",")) == 2 else ""
            part_2 = value.split(";")[1] if len(value.split(";")) == 2 else ""

            if "休息" in value:
                row_data[key] = "休"
                continue
            elif "-" == value:
                row_data[key] = ""
                continue
            elif "病假" in part_2:
                row_data[key] = "病假"
                continue
            elif "事假" in part_2:
                row_data[key] = "事假"
                continue
            elif "年假" in part_2:
                row_data[key] = "年假"
                continue
            elif "调休假" in part_2:
                row_data[key] = "调休假"
                continue

            elif "缺卡" in part_1 or "迟到" in part_1 or "早退" in part_1:
                row_data[key] = ""
                if "迟到" in morning:
                    row_data[key] += "迟到,"
                if "缺卡" in morning:
                    row_data[key] += "上班缺卡,"
                if "缺卡" in evening:
                    row_data[key] += "下班缺卡,"
                if "早退" in evening:
                    row_data[key] += "早退,"
                continue
            elif "出差" in part_2:
                row_data[key] = "出差"
                continue
            elif "正常" or "外勤" or "入职日" in morning and "正常" or "外勤" or "入职日" in evening:
                row_data[key] = "打卡"
                continue

    pd.DataFrame.from_records(new_data_list).to_excel(excel_output_path, index=False)
    # 加载已有的Excel文件, 进行颜色填充和批注添加
    workbook = openpyxl.load_workbook(excel_output_path)
    sheet = workbook.active  # 选择工作表

    row_index = 0  # 行索引
    for row in sheet.iter_rows(values_only=True):  # 遍历每一行
        row_index += 1  # 行索引自增, 从1开始
        cell_index = 0  # 列索引
        for cell in row:
            cell_index += 1  # 列索引自增, 从1开始
            if not cell:  # 跳过空白
                continue
            char = dec_to_alphanumeric(cell_index)  # 列索引转字母
            pos = f"{char}{row_index}"  # 拼接行列坐标
            cell_obj = sheet[pos]  # 根据行列坐标获取 cell 单元格对象
            # 对包含指定字符的单元格进行颜色填充
            if "迟到" in cell:
                cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFEE0808', end_color='FFEE0808',
                                                            fill_type='solid')
                cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
            if "缺卡" in cell:
                cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                                                            fill_type='solid')
                cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
            if "早退" in cell:
                cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
                                                            fill_type='solid')
                cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
            if "假" in cell:
                cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFF4AF85', end_color='FFF4AF85',
                                                            fill_type='solid')
                cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")
            if "出差" in cell:
                cell_obj.fill = openpyxl.styles.PatternFill(start_color='FFCCFFFF', end_color='FFCCFFFF',
                                                            fill_type='solid')
                cell_obj.comment = openpyxl.comments.Comment("批注", "Comment Author")

    # 保存并关闭
    workbook.save(excel_output_path)  # 保存文件
    workbook.close()  # 关闭文件


if __name__ == "__main__":
    # excel_download_path = r'D:\飞书考勤统计\20240601-20240630导出考勤.xlsx'
    # 加载已有的Excel文件, 进行颜色填充和批注添加
    # excel_output_path = r"D:\飞书下载表格\20240601-20240630汇总统计.xlsx"
    excel_download_path=r"D:\飞书下载表格\导出考勤表2024-07-01.xlsx"
    excel_output_path = r"D:\飞书下载表格\汇总统计.xlsx"
    # 补卡
    excel_buka = r"D:\飞书下载表格\补卡2024-07-01.xlsx"
    # 请假
    excel_holiday = r"D:\飞书下载表格\请假2024-07-01.xlsx"
    # 加班申请
    excel_overtime = r"D:\飞书下载表格\加班申请2024-07-01.xlsx"
    # 日报
    excel_ribao = r"D:\飞书下载表格\日报2024-07-01.xlsx"
    # 打卡地点
    excel_daka = r"D:\飞书下载表格\打卡地点2024-07-01.xlsx"
    # 生成基本表
    generate_basic_excel(excel_download_path,excel_output_path)
    # 添加出差批注
    if os.path.exists(excel_daka):
        df=pre_daka_addr(excel_daka)
        add_chuchai_comment(df, excel_output_path)
    # 添加迟到，缺卡，事假批注
    if os.path.exists(excel_buka):
        add_comment(excel_download_path, excel_output_path,excel_buka)
        add_buka_comment(excel_buka, excel_output_path)

    # 工作日加班
    # weekday_overtime(excel_download_path, excel_output_path)
    # # 非工作日加班
    # if os.path.exists(excel_overtime):
    #     add_jiaban_comment(excel_overtime, excel_output_path)
    # # 添加日报批注
    # add_daily_comment(excel_ribao, excel_output_path)