pip install pandas openpyxl PyPDF2 pywin32  # pywin32用于Windows COM操作
# 如需連接數(shù)據(jù)庫，還需安裝 sqlalchemy pymysql 等

import pandas as pd

def read_csv_data(file_path):
    df = pd.read_csv(file_path, parse_dates=['date'])
    print(f"從CSV讀取數(shù)據(jù)，共 {len(df)} 行")
    return df

from sqlalchemy import create_engine

def read_db_data(db_url, table_name, start_date, end_date):
    engine = create_engine(db_url)
    query = f"""
        SELECT * FROM {table_name}
        WHERE date BETWEEN '{start_date}' AND '{end_date}'
    """
    df = pd.read_sql(query, engine, parse_dates=['date'])
    print(f"從數(shù)據(jù)庫讀取數(shù)據(jù)，共 {len(df)} 行")
    return df

def clean_data(df):
    # 刪除重復(fù)行
    df = df.drop_duplicates()
    
    # 處理缺失值：這里假設(shè)total_amount不能為空，若為空則填充為0
    df['total_amount'] = df['total_amount'].fillna(0)
    
    # 確保數(shù)值列類型正確
    df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce').fillna(0)
    df['unit_price'] = pd.to_numeric(df['unit_price'], errors='coerce').fillna(0)
    
    # 過濾：銷量和單價(jià)不能為負(fù)數(shù)
    df = df[(df['quantity'] >= 0) & (df['unit_price'] >= 0)]
    
    # 重新計(jì)算total_amount（防止原始數(shù)據(jù)有誤）
    df['total_amount'] = df['quantity'] * df['unit_price']
    
    return df

def analyze_data(df):
    # 按區(qū)域和門店匯總
    summary_by_store = df.groupby(['region', 'store']).agg(
        total_sales=('total_amount', 'sum'),
        order_count=('quantity', 'count'),  # 假設(shè)每條記錄為一筆訂單
        avg_order_value=('total_amount', 'mean')
    ).reset_index()
    
    # 按日期匯總每日銷售額
    daily_sales = df.groupby(df['date'].dt.date)['total_amount'].sum().reset_index()
    daily_sales.columns = ['date', 'daily_total']
    
    # 計(jì)算全局指標(biāo)
    total_sales = df['total_amount'].sum()
    total_orders = len(df)
    avg_customer_value = total_sales / total_orders if total_orders > 0 else 0
    
    metrics = {
        'total_sales': total_sales,
        'total_orders': total_orders,
        'avg_customer_value': avg_customer_value
    }
    
    return summary_by_store, daily_sales, metrics

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, NamedStyle
from openpyxl.chart import LineChart, Reference
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

def create_excel_report(df_raw, summary_df, daily_df, metrics, output_path):
    wb = Workbook()
    
    # 1. 原始數(shù)據(jù) sheet
    ws_raw = wb.active
    ws_raw.title = "Raw Data"
    # 將DataFrame逐行寫入
    for r in dataframe_to_rows(df_raw, index=False, header=True):
        ws_raw.append(r)
    
    # 2. 匯總表 sheet
    ws_summary = wb.create_sheet("Summary by Store")
    for r in dataframe_to_rows(summary_df, index=False, header=True):
        ws_summary.append(r)
    
    # 3. 每日趨勢 sheet
    ws_trend = wb.create_sheet("Daily Trend")
    for r in dataframe_to_rows(daily_df, index=False, header=True):
        ws_trend.append(r)
    
    # 保存臨時(shí)文件，后續(xù)還要進(jìn)行格式化和圖表插入
    wb.save(output_path)
    return wb  # 返回工作簿對(duì)象以便繼續(xù)操作

def format_excel(wb):
    # 定義常用樣式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'), 
        right=Side(style='thin'), 
        top=Side(style='thin'), 
        bottom=Side(style='thin')
    )
    currency_style = NamedStyle(name="currency", number_format='"￥"#,##0.00')
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        
        # 設(shè)置標(biāo)題行樣式（第一行）
        for cell in ws[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = Alignment(horizontal='center')
        
        # 調(diào)整列寬
        for col in ws.columns:
            max_length = 0
            col_letter = col[0].column_letter
            for cell in col:
                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[col_letter].width = adjusted_width
        
        # 為包含金額的列應(yīng)用貨幣格式（假設(shè)列名包含'sales'或'amount'）
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
            for cell in row:
                cell.border = thin_border
                if cell.column_letter in ['C','D']:  # 根據(jù)實(shí)際列調(diào)整
                    cell.number_format = currency_style.number_format
    
    return wb

from openpyxl.chart import LineChart, Reference

def add_chart(wb):
    ws = wb["Daily Trend"]
    # 數(shù)據(jù)范圍：假設(shè)日期在A列，銷售額在B列，從第2行開始到最后
    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row, max_col=2)
    dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
    
    chart = LineChart()
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(dates)
    chart.title = "每日銷售額趨勢"
    chart.x_axis.title = "日期"
    chart.y_axis.title = "銷售額"
    chart.style = 12  # 內(nèi)置樣式
    
    # 將圖表放在E2單元格附近
    ws.add_chart(chart, "E2")
    return wb

def create_summary_sheet(wb, metrics):
    ws = wb.create_sheet("Summary", 0)  # 插入到第一個(gè)位置
    
    # 寫入標(biāo)題
    ws['A1'] = "銷售周報(bào)總結(jié)"
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:C1')
    
    # 寫入核心指標(biāo)
    row = 3
    ws[f'A{row}'] = "總銷售額："
    ws[f'B{row}'] = metrics['total_sales']
    ws[f'B{row}'].number_format = '"￥"#,##0.00'
    row += 1
    ws[f'A{row}'] = "總訂單數(shù)："
    ws[f'B{row}'] = metrics['total_orders']
    row += 1
    ws[f'A{row}'] = "客單價(jià)："
    ws[f'B{row}'] = metrics['avg_customer_value']
    ws[f'B{row}'].number_format = '"￥"#,##0.00'
    
    # 添加結(jié)論性文字
    row += 2
    ws[f'A{row}'] = "結(jié)論："
    row += 1
    conclusion = f"上周總銷售額為￥{metrics['total_sales']:,.0f}，共完成{metrics['total_orders']}筆訂單，客單價(jià)為￥{metrics['avg_customer_value']:.2f}。"
    ws[f'A{row}'] = conclusion
    ws.merge_cells(f'A{row}:C{row}')
    
    # 調(diào)整列寬
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 15
    
    return wb

import win32com.client
import os

def export_sheet_to_pdf(excel_path, sheet_name, pdf_path):
    """
    使用Excel COM對(duì)象將指定sheet導(dǎo)出為PDF
    """
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False  # 后臺(tái)運(yùn)行
    
    try:
        wb = excel.Workbooks.Open(os.path.abspath(excel_path))
        ws = wb.Sheets(sheet_name)
        
        # 導(dǎo)出為PDF
        ws.ExportAsFixedFormat(0, pdf_path)  # 0代表xlTypePDF
        print(f"已導(dǎo)出 {sheet_name} 到 {pdf_path}")
    except Exception as e:
        print(f"導(dǎo)出PDF失敗: {e}")
        raise
    finally:
        wb.Close(SaveChanges=False)
        excel.Quit()

import PyPDF2

def encrypt_pdf(input_pdf, output_pdf, password):
    with open(input_pdf, 'rb') as file:
        pdf_reader = PyPDF2.PdfReader(file)
        pdf_writer = PyPDF2.PdfWriter()
        
        for page_num in range(len(pdf_reader.pages)):
            pdf_writer.add_page(pdf_reader.pages[page_num])
        
        pdf_writer.encrypt(password)
        
        with open(output_pdf, 'wb') as out_file:
            pdf_writer.write(out_file)
    print(f"PDF已加密，保存至 {output_pdf}")

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import os

def send_email(sender, password, receiver, subject, body, attachments):
    # 創(chuàng)建郵件對(duì)象
    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = receiver
    msg['Subject'] = subject
    
    # 添加正文
    msg.attach(MIMEText(body, 'plain', 'utf-8'))
    
    # 添加附件
    for file_path in attachments:
        with open(file_path, 'rb') as attachment:
            part = MIMEBase('application', 'octet-stream')
            part.set_payload(attachment.read())
            encoders.encode_base64(part)
            part.add_header(
                'Content-Disposition',
                f'attachment; filename= {os.path.basename(file_path)}'
            )
            msg.attach(part)
    
    # 發(fā)送郵件（以QQ郵箱為例）
    try:
        server = smtplib.SMTP_SSL('smtp.qq.com', 465)
        server.login(sender, password)
        server.send_message(msg)
        server.quit()
        print("郵件發(fā)送成功")
    except Exception as e:
        print(f"郵件發(fā)送失敗: {e}")

def generate_report(csv_path, excel_output, pdf_output, email_config):
    # 1. 讀取數(shù)據(jù)
    df_raw = read_csv_data(csv_path)
    
    # 2. 清洗與分析
    df_clean = clean_data(df_raw)
    summary_df, daily_df, metrics = analyze_data(df_clean)
    
    # 3. 生成Excel（先保存基本數(shù)據(jù)）
    wb = create_excel_report(df_clean, summary_df, daily_df, metrics, excel_output)
    wb = format_excel(wb)
    wb = add_chart(wb)
    wb = create_summary_sheet(wb, metrics)
    wb.save(excel_output)
    print(f"Excel報(bào)表已生成: {excel_output}")
    
    # 4. 將Summary sheet導(dǎo)出為PDF
    export_sheet_to_pdf(excel_output, "Summary", pdf_output)
    
    # 可選：用PyPDF2加密PDF
    # pdf_encrypted = pdf_output.replace('.pdf', '_encrypted.pdf')
    # encrypt_pdf(pdf_output, pdf_encrypted, 'weekly123')
    
    # 5. 發(fā)送郵件
    send_email(
        sender=email_config['sender'],
        password=email_config['password'],
        receiver=email_config['receiver'],
        subject=email_config['subject'],
        body=email_config['body'],
        attachments=[excel_output, pdf_output]  # 同時(shí)發(fā)送Excel和PDF
    )
    
    print("報(bào)表生成及發(fā)送流程全部完成！")

if __name__ == "__main__":
    csv_file = "data/sales_data.csv"
    excel_file = "output/sales_report.xlsx"
    pdf_file = "output/summary.pdf"
    
    email_cfg = {
        'sender': 'your_email@qq.com',
        'password': 'your_authorization_code',  # QQ郵箱授權(quán)碼
        'receiver': 'boss@company.com',
        'subject': '上周銷售報(bào)表',
        'body': '您好，附件是上周銷售報(bào)表，請(qǐng)查收。\n\n數(shù)據(jù)分析團(tuán)隊(duì)'
    }
    
    generate_report(csv_file, excel_file, pdf_file, email_cfg)