用Python给运营同事做了个Excel自动化工具，从此她再也不加班了

pandas==1.5.3  # 数据处理
openpyxl==3.1.2  # Excel读写和格式设置
matplotlib==3.7.1  # 生成图表
pyinstaller==5.13.0  # 打包成exe
tkinter  # 做个简单的选文件界面，Python自带不用装

# 列名映射字典，不管原始列叫啥，统一重命名
column_mapping = {
    '订单编号': 'order_id',
    '订单金额': 'amount',
    '实付金额': 'amount',
    '买家实付（元）': 'amount',
    '支付时间': 'pay_time',
    '下单时间': 'pay_time',
    '商品品类': 'category',
    '品类名称': 'category'
}

def read_and_clean(file_path):
    df = pd.read_excel(file_path)
    # 只保留我们需要的列，顺便重命名
    df = df.rename(columns=column_mapping)
    df = df[['order_id', 'amount', 'pay_time', 'category']]
    return df

# 转换Excel日期序列号
df['pay_time'] = pd.to_datetime(df['pay_time'], unit='D', origin='1899-12-30')
# 过滤掉退款单和测试单
df = df[df['amount'] > 0]
df = df[~df['order_id'].str.contains('测试|退款', na=False)]

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']  # 解决中文显示
plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题

# 画周度销量趋势图
weekly_sales = df.groupby('week')['amount'].sum()
plt.figure(figsize=(10, 4))
plt.plot(weekly_sales.index, weekly_sales.values, marker='o', color='#2E86C1')
plt.title('近8周销售额趋势', fontsize=12)
plt.xlabel('周次', fontsize=10)
plt.ylabel('销售额（元）', fontsize=10)
plt.grid(alpha=0.3)
plt.savefig('trend.png', dpi=100, bbox_inches='tight')

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.drawing.image import Image
import datetime

def generate_report(input_files, output_path):
    # 1. 读取并合并所有文件
    all_dfs = []
    for f in input_files:
        all_dfs.append(read_and_clean(f))
    df = pd.concat(all_dfs, ignore_index=True)
    
    # 2. 数据计算
    df['pay_time'] = pd.to_datetime(df['pay_time'], unit='D', origin='1899-12-30')
    df['week'] = df['pay_time'].dt.isocalendar().week
    # 本周和上周数据
    current_week = datetime.date.today().isocalendar().week
    last_week = current_week - 1
    
    current_data = df[df['week'] == current_week].groupby('category').agg(
        销量=('order_id', 'count'),
        销售额=('amount', 'sum'),
        客单价=('amount', 'mean')
    ).reset_index()
    last_data = df[df['week'] == last_week].groupby('category')['销售额'].sum().reset_index()
    last_data.columns = ['category', '上周销售额']
    
    # 合并计算环比
    result = pd.merge(current_data, last_data, on='category', how='left')
    result['环比增长'] = (result['销售额'] - result['上周销售额']) / result['上周销售额']
    result['客单价'] = result['客单价'].round(2)
    result['环比增长'] = result['环比增长'].apply(lambda x: f"{x*100:.1f}%")
    
    # 3. 写入Excel并设置格式
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        result.to_excel(writer, sheet_name='周度汇总', index=False)
        # 插入趋势图
        wb = writer.book
        ws = wb['周度汇总']
        img = Image('trend.png')
        ws.add_image(img, 'A10')
        
        # 设置表头格式（灰色背景、加粗、居中）
        header_fill = PatternFill(start_color='D9D9D9', end_color='D9D9D9', fill_type='solid')
        thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                           top=Side(style='thin'), bottom=Side(style='thin'))
        for cell in ws[1]:
            cell.font = Font(bold=True)
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal='center')
        # 给所有单元格加边框
        for row in ws.iter_rows(min_row=1, max_row=len(result)+1, max_col=5):
            for cell in row:
                cell.border = thin_border
                cell.alignment = Alignment(horizontal='center')

pyinstaller -F -w -i icon.ico main.py