pip install pandas openpyxl xlsxwriter python-dotenv

# -*- coding: utf-8 -*-
import pandas as pd
import os
from datetime import datetime
from dotenv import load_dotenv

# 加載配置
load_dotenv()

# 路徑配置
RAW_DATA_PATH = "raw_data"
OUTPUT_PATH = "processed_reports"
if not os.path.exists(OUTPUT_PATH):
    os.mkdir(OUTPUT_PATH)

# ---------------------------
# 1. 讀取原始數(shù)據(jù)
# ---------------------------
def read_raw_data(file_path):
    """自動識別文件格式并讀取"""
    ext = os.path.splitext(file_path)[1].lower()
    if ext == ".xlsx":
        return pd.read_excel(file_path)
    elif ext == ".csv":
        return pd.read_csv(file_path)
    else:
        raise ValueError(f"不支持的文件格式：{ext}")

# ---------------------------
# 2. 自動數(shù)據(jù)清洗
# ---------------------------
def clean_data(df):
    """
    通用數(shù)據(jù)清洗函數(shù)：
    1. 去重
    2. 補全缺失值
    3. 格式轉(zhuǎn)換
    4. 異常值處理
    """
    # 復(fù)制一份，避免修改原數(shù)據(jù)
    df_clean = df.copy()
    
    # 1. 去重
    before_count = len(df_clean)
    df_clean = df_clean.drop_duplicates()
    after_count = len(df_clean)
    print(f"?? 去重：刪除了 {before_count - after_count} 條重復(fù)數(shù)據(jù)")
    
    # 2. 補全缺失值
    # 數(shù)值型列用平均值填充
    numeric_cols = df_clean.select_dtypes(include=['number']).columns
    df_clean[numeric_cols] = df_clean[numeric_cols].fillna(df_clean[numeric_cols].mean())
    # 文本型列用"未知"填充
    text_cols = df_clean.select_dtypes(include=['object']).columns
    df_clean[text_cols] = df_clean[text_cols].fillna("未知")
    print("?? 補全缺失值：已處理所有空值")
    
    # 3. 格式轉(zhuǎn)換（日期列統(tǒng)一格式）
    date_cols = [col for col in df_clean.columns if 'date' in col.lower() or '時間' in col]
    for col in date_cols:
        try:
            df_clean[col] = pd.to_datetime(df_clean[col]).dt.strftime('%Y-%m-%d')
        except:
            pass
    print("?? 格式轉(zhuǎn)換：日期列已統(tǒng)一格式")
    
    # 4. 異常值處理（簡單的3σ原則）
    for col in numeric_cols:
        mean = df_clean[col].mean()
        std = df_clean[col].std()
        lower = mean - 3 * std
        upper = mean + 3 * std
        # 異常值用中位數(shù)替換
        median = df_clean[col].median()
        df_clean[col] = df_clean[col].apply(lambda x: median if x < lower or x > upper else x)
    print("?? 異常值處理：已處理數(shù)值型列的異常值")
    
    return df_clean

# ---------------------------
# 3. 自動生成統(tǒng)計指標(biāo)
# ---------------------------
def generate_stats(df, group_col, value_col):
    """
    生成統(tǒng)計報表：
    按 group_col 分組，統(tǒng)計 value_col 的 總和、平均值、最大值、最小值
    """
    stats = df.groupby(group_col).agg(
        總和=(value_col, 'sum'),
        平均值=(value_col, 'mean'),
        最大值=(value_col, 'max'),
        最小值=(value_col, 'min'),
        數(shù)量=(value_col, 'count')
    ).reset_index()
    
    # 格式化數(shù)值（保留2位小數(shù)）
    numeric_cols = ['總和', '平均值', '最大值', '最小值']
    stats[numeric_cols] = stats[numeric_cols].round(2)
    
    print(f"?? 統(tǒng)計完成：按 {group_col} 分組，統(tǒng)計 {value_col}")
    return stats

# ---------------------------
# 4. 一鍵美化報表（Excel）
# ---------------------------
def beautify_excel(file_path, sheet_names):
    """
    美化Excel報表：
    1. 設(shè)置表頭樣式
    2. 設(shè)置列寬
    3. 設(shè)置邊框
    4. 設(shè)置對齊
    """
    from openpyxl import load_workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    
    wb = load_workbook(file_path)
    
    # 定義樣式
    header_font = Font(name='微軟雅黑', size=12, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='165DFF', end_color='165DFF', fill_type='solid')
    header_alignment = Alignment(horizontal='center', vertical='center')
    cell_font = Font(name='微軟雅黑', size=10)
    cell_alignment = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    for sheet_name in sheet_names:
        if sheet_name not in wb.sheetnames:
            continue
        ws = wb[sheet_name]
        
        # 設(shè)置表頭
        for cell in ws[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border
        
        # 設(shè)置數(shù)據(jù)行
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                cell.font = cell_font
                cell.alignment = cell_alignment
                cell.border = thin_border
        
        # 自動調(diào)整列寬
        for column in ws.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column_letter].width = adjusted_width
    
    wb.save(file_path)
    print("?? 美化完成：Excel報表已一鍵美化")

# ---------------------------
# 5. 自動插入圖表
# ---------------------------
def add_charts_to_excel(file_path, stats_df, sheet_name, chart_type='bar'):
    """
    在Excel中插入圖表
    """
    import xlsxwriter
    
    # 用xlsxwriter重新寫入（方便插入圖表）
    temp_path = file_path.replace('.xlsx', '_temp.xlsx')
    with pd.ExcelWriter(temp_path, engine='xlsxwriter') as writer:
        stats_df.to_excel(writer, sheet_name=sheet_name, index=False)
        
        workbook = writer.book
        worksheet = writer.sheets[sheet_name]
        
        # 創(chuàng)建圖表
        if chart_type == 'bar':
            chart = workbook.add_chart({'type': 'column'})
        elif chart_type == 'line':
            chart = workbook.add_chart({'type': 'line'})
        elif chart_type == 'pie':
            chart = workbook.add_chart({'type': 'pie'})
        else:
            chart = workbook.add_chart({'type': 'column'})
        
        # 配置圖表數(shù)據(jù)
        max_row = len(stats_df)
        chart.add_series({
            'name': [sheet_name, 0, 1],
            'categories': [sheet_name, 1, 0, max_row, 0],
            'values': [sheet_name, 1, 1, max_row, 1],
        })
        
        # 設(shè)置圖表標(biāo)題
        chart.set_title({'name': f'{stats_df.columns[0]} vs {stats_df.columns[1]}'})
        chart.set_x_axis({'name': stats_df.columns[0]})
        chart.set_y_axis({'name': stats_df.columns[1]})
        
        # 插入圖表
        worksheet.insert_chart('F2', chart)
    
    # 替換原文件
    os.replace(temp_path, file_path)
    print("?? 圖表插入完成")

# ---------------------------
# 主函數(shù)：一鍵處理數(shù)據(jù)
# ---------------------------
def run_data_processor(file_name, group_col, value_col, chart_type='bar'):
    print("?? 數(shù)據(jù)處理流水線啟動...")
    
    # 1. 讀取數(shù)據(jù)
    file_path = os.path.join(RAW_DATA_PATH, file_name)
    if not os.path.exists(file_path):
        print(f"? 文件不存在：{file_path}")
        return
    df = read_raw_data(file_path)
    print(f"? 讀取成功：共 {len(df)} 條數(shù)據(jù)")
    
    # 2. 清洗數(shù)據(jù)
    df_clean = clean_data(df)
    
    # 3. 生成統(tǒng)計
    stats = generate_stats(df_clean, group_col, value_col)
    
    # 4. 保存結(jié)果
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    output_file = os.path.join(OUTPUT_PATH, f'報表_{timestamp}.xlsx')
    
    # 寫入多個sheet
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        df_clean.to_excel(writer, sheet_name='清洗后數(shù)據(jù)', index=False)
        stats.to_excel(writer, sheet_name='統(tǒng)計報表', index=False)
    
    # 5. 插入圖表
    add_charts_to_excel(output_file, stats, '統(tǒng)計報表', chart_type)
    
    # 6. 美化報表
    beautify_excel(output_file, ['清洗后數(shù)據(jù)', '統(tǒng)計報表'])
    
    print(f"?? 全部完成！報表已保存至：{output_file}")
    return output_file

if __name__ == "__main__":
    # 示例：處理銷售數(shù)據(jù)，按"產(chǎn)品"分組，統(tǒng)計"銷售額"，插入柱狀圖
    # 你需要先在 raw_data 文件夾放一個測試文件
    # run_data_processor('銷售數(shù)據(jù).xlsx', '產(chǎn)品', '銷售額', 'bar')
    print("請在代碼中配置你的數(shù)據(jù)文件和統(tǒng)計字段后運行！")

# 可以在這里配置默認(rèn)參數(shù)
DEFAULT_GROUP_COL=產(chǎn)品
DEFAULT_VALUE_COL=銷售額
DEFAULT_CHART_TYPE=bar