import pandas as pd
from pathlib import Path

def excel_clean_merge(folder_path, output_name):
    all_data = []
    for file in Path(folder_path).glob('*.xlsx'):
        df = pd.read_excel(file, skiprows=2)  # 跳過標題行
        df['Source'] = file.name
        all_data.append(df)

    merged = pd.concat(all_data, ignore_index=True)
    # 關鍵清洗步驟
    merged.dropna(subset=['OrderID'], inplace=True)
    merged['Amount'] = merged['Amount'].astype(float)
    # 保存處理結果
    merged.to_excel(f"{output_name}.xlsx", index=False)

# 使用示例
excel_clean_merge('/data/reports', 'Q1_Consolidated_Report')

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import csv

def send_bulk_emails(config_file, template_file):
    # 讀取配置文件
    with open(config_file) as f:
        reader = csv.DictReader(f)
        contacts = list(reader)

    # 加載模板
    with open(template_file) as f:
        template = f.read()

    server = smtplib.SMTP('smtp.office365.com', 587)
    server.starttls()
    server.login('your_email@domain.com', 'secure_password')

    for contact in contacts:
        msg = MIMEMultipart()
        personalized = template.format(**contact)
        msg.attach(MIMEText(personalized, 'html'))
        msg['Subject'] = f"重要通知：{contact['name']}的專屬更新"
        server.sendmail('noreply@domain.com', contact['email'], msg.as_string())

    server.quit()

# 使用前需配置SMTP服務和聯系人CSV
# send_bulk_emails('contacts.csv', 'template.html')

import PyPDF2
import re

def extract_pdf_data(file_path):
    result = []
    with open(file_path, 'rb') as pdf_file:
        reader = PyPDF2.PdfReader(pdf_file)
        for page in reader.pages:
            text = page.extract_text()
            # 提取發票關鍵信息
            inv_pattern = r'Invoice No:\s*(\w+)'
            amount_pattern = r'Total Due:\s*(\$\d+\.\d{2})'
            if match := re.search(inv_pattern, text):
                invoice_num = match.group(1)
            if match := re.search(amount_pattern, text):
                amount = match.group(1)
            result.append((invoice_num, amount))
    return result

# 批量處理示例
for pdf in Path().glob('invoices/*.pdf'):
    print(extract_pdf_data(str(pdf)))

from openpyxl import load_workbook
from datetime import datetime

def auto_daily_report(data_file):
    wb = load_workbook(data_file)
    ws = wb.active
    today = datetime.now().strftime("%Y-%m-%d")

    # 動態獲取最新數據區域
    max_row = ws.max_row
    daily_sales = ws.cell(row=max_row, column=3).value

    report = f"""
    ╭──────────────────────────╮
    │ 每日銷售報告 ({today})    │
    ├──────────────────────────┤
    │ 當日銷售額：${daily_sales} │
    ╰──────────────────────────╯
    """
    # 寫入日志文件
    with open(f'daily_report_{today}.txt', 'w') as f:
        f.write(report)

    return report

print(auto_daily_report('sales_database.xlsx'))

import boto3
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler

class SyncHandler(FileSystemEventHandler):
    def on_modified(self, event):
        s3 = boto3.client('s3')
        s3.upload_file(event.src_path, 'my-bucket', f'backups/{event.src_path}')

observer = Observer()
observer.schedule(SyncHandler(), path='/critical_docs', recursive=True)
observer.start()  # 后臺運行

import win32com.client
import pandas as pd

def schedule_meetings(participants_file):
    outlook = win32com.client.Dispatch('Outlook.Application')
    df = pd.read_excel(participants_file)

    for _, row in df.iterrows():
        appt = outlook.CreateItem(1)  # 1=olAppointmentItem
        appt.Subject = "季度業務復盤會議"
        appt.Start = row['PreferredTime'] + " 14:00"
        appt.Duration = 60
        appt.Location = "Teams會議"
        appt.MeetingStatus = 1# olMeeting
        appt.Recipients.Add(row['Email'])
        appt.Save()

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def run_daily_tasks():
    excel_clean_merge()
    generate_reports()
    send_notifications()

with DAG('office_auto', 
         start_date=datetime(2023, 1, 1),
         schedule_interval='@daily') as dag:

    main_task = PythonOperator(
        task_id='daily_workflow',
        python_callable=run_daily_tasks
    )