使用python语言写一个自动自动计算每月奖金的脚本,直接生成excel格式

import pandas as pd
from datetime import datetime, timedelta
import calendar

定义基本参数

base_bonus = 500
year = 2024

创建一个空的DataFrame来存储数据

columns = [‘Date’, ‘Day of Week’, ‘Is Sunday’, ‘Worked’, ‘Daily Bonus’, ‘Cumulative Bonus’]
data = []

生成数据

for month in range(1, 13):
# 获取该月的所有日期
start_date = datetime(year, month, 1)
end_date = datetime(year, month, calendar.monthrange(year, month)[1])

# 计算该月的总天数和周日数量
total_days = (end_date - start_date).days + 1
sundays = sum(1 for i in range(total_days) if (start_date + timedelta(days=i)).weekday() == 6)

# 计算实际工作天数
actual_working_days = total_days - sundays

# 计算平均日奖金
average_daily_bonus = base_bonus / actual_working_days

cumulative_bonus = 0  # 初始化累计奖金为0

for i in range(total_days):
    current_date = start_date + timedelta(days=i)
    is_sunday = current_date.weekday() == 6
    worked = not is_sunday  # 默认情况下，周日不工作，其余时间工作
    
    daily_bonus = average_daily_bonus if worked else 0
    cumulative_bonus += daily_bonus if worked else 0
    
    data.append([current_date.strftime('%Y-%m-%d'), current_date.strftime('%A'), is_sunday, worked, daily_bonus, cumulative_bonus])

将数据转换为DataFrame

df = pd.DataFrame(data, columns=columns)

保存为Excel文件

output_file = ‘daily_cumulative_bonus_report_2024.xlsx’
with pd.ExcelWriter(output_file, engine=‘openpyxl’) as writer:
df.to_excel(writer, index=False, sheet_name=‘Bonus Report’)
workbook = writer.book
worksheet = writer.sheets[‘Bonus Report’]

# 添加公式
for row in range(2, len(df) + 2):
    date_cell = f'A{row}'
    worked_cell = f'D{row}'
    daily_bonus_cell = f'E{row}'
    cumulative_bonus_cell = f'F{row}'
    
    # 累计奖金计算
    if row == 2 or df.loc[row-3, 'Date'].split('-')[1] != df.loc[row-2, 'Date'].split('-')[1]:
        worksheet[cumulative_bonus_cell] = f'=IF({worked_cell}, {daily_bonus_cell}, 0)'
    else:
        previous_cumulative_bonus_cell = f'F{row-1}'
        worksheet[cumulative_bonus_cell] = f'=IF({worked_cell}, {previous_cumulative_bonus_cell}+{daily_bonus_cell}, {previous_cumulative_bonus_cell})'

print(f"报表已生成并保存为 {output_file}")