# 安裝必要的庫(kù)
pip install pandas openpyxl matplotlib

import pandas as pd

def read_sales_data(file_path):
    """讀取銷售數(shù)據(jù)"""
    # 讀取多個(gè) sheet
    excel_file = pd.ExcelFile(file_path)
    
    data = {}
    for sheet_name in excel_file.sheet_names:
        data[sheet_name] = pd.read_excel(file_path, sheet_name=sheet_name)
    
    return data

# 使用示例
data = read_sales_data('sales_data.xlsx')
orders = data['訂單表']
print(orders.head())

def generate_summary(orders_df):
    """生成銷售匯總"""
    summary = {
        '總銷售額': orders_df['金額'].sum(),
        '訂單數(shù)量': len(orders_df),
        '平均訂單金額': orders_df['金額'].mean(),
        '最大訂單': orders_df['金額'].max(),
        '最小訂單': orders_df['金額'].min()
    }
    return summary

# 產(chǎn)品銷量排行
def product_ranking(orders_df):
    """產(chǎn)品銷量排行"""
    ranking = orders_df.groupby('產(chǎn)品名稱').agg({
        '數(shù)量': 'sum',
        '金額': 'sum'
    }).sort_values('金額', ascending=False)
    
    return ranking

def create_report(data, output_path):
    """生成報(bào)表文件"""
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        # 寫入?yún)R總數(shù)據(jù)
        summary_df = pd.DataFrame([generate_summary(data['訂單表'])])
        summary_df.to_excel(writer, sheet_name='匯總', index=False)
        
        # 寫入產(chǎn)品排行
        ranking = product_ranking(data['訂單表'])
        ranking.to_excel(writer, sheet_name='產(chǎn)品排行')
        
        # 寫入原始數(shù)據(jù)
        for sheet_name, df in data.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    
    print(f"報(bào)表已生成：{output_path}")

# 使用
create_report(data, '月度銷售報(bào)表.xlsx')

import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image as XLImage

def add_charts(excel_path):
    """為報(bào)表添加圖表"""
    from openpyxl import load_workbook
    
    wb = load_workbook(excel_path)
    
    # 創(chuàng)建圖表數(shù)據(jù)
    ranking = pd.read_excel(excel_path, sheet_name='產(chǎn)品排行')
    
    # 生成柱狀圖
    plt.figure(figsize=(10, 6))
    ranking['金額'].head(10).plot(kind='bar')
    plt.title('Top 10 產(chǎn)品銷售額')
    plt.tight_layout()
    plt.savefig('chart.png')
    plt.close()
    
    # 插入到 Excel
    ws = wb.create_sheet('圖表')
    img = XLImage('chart.png')
    ws.add_image(img, 'A1')
    
    wb.save(excel_path)
    print("圖表已添加")

import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as XLImage
import os
from datetime import datetime

def auto_generate_report(input_folder, output_folder):
    """自動(dòng)生成月度報(bào)表"""
    
    # 確保輸出文件夾存在
    os.makedirs(output_folder, exist_ok=True)
    
    # 獲取當(dāng)前月份
    current_month = datetime.now().strftime('%Y年%m月')
    output_file = f"{output_folder}/{current_month}銷售報(bào)表.xlsx"
    
    # 讀取所有數(shù)據(jù)文件
    all_data = []
    for file in os.listdir(input_folder):
        if file.endswith('.xlsx'):
            file_path = os.path.join(input_folder, file)
            data = read_sales_data(file_path)
            all_data.append(data['訂單表'])
    
    # 合并數(shù)據(jù)
    combined_data = pd.concat(all_data, ignore_index=True)
    
    # 生成報(bào)表
    create_report({'訂單表': combined_data}, output_file)
    
    # 添加圖表
    add_charts(output_file)
    
    print(f"? 報(bào)表生成完成：{output_file}")
    return output_file

# 每月自動(dòng)執(zhí)行
if __name__ == '__main__':
    auto_generate_report('./raw_data', './reports')

# 處理缺失值
orders_df['金額'].fillna(0, inplace=True)

# 數(shù)據(jù)類型轉(zhuǎn)換
orders_df['日期'] = pd.to_datetime(orders_df['日期'])

# 異常值處理
orders_df = orders_df[orders_df['金額'] > 0]

from openpyxl.styles import PatternFill

def highlight_top10(excel_path):
    """高亮顯示前10名"""
    wb = load_workbook(excel_path)
    ws = wb['產(chǎn)品排行']
    
    # 紅色填充
    red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
    
    # 高亮前10行
    for row in range(2, 12):
        for col in range(1, 4):
            ws.cell(row=row, column=col).fill = red_fill
    
    wb.save(excel_path)

# 添加到 crontab，每月1號(hào)凌晨執(zhí)行
0 0 1 * * /usr/bin/python3 /path/to/auto_generate_report.py