import os
import glob
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

# 1. 定義文件夾路徑
folder_path = "sales_reports"

# 2. 使用 glob 匹配文件夾下所有的 .xlsx 文件
# 假設(shè)當(dāng)前目錄下有 sales_reports 文件夾
file_paths = glob.glob(os.path.join(folder_path, "*.xlsx"))

# 用于存放每個讀取出來的 DataFrame
df_list = []

print("開始批量讀取文件...")
for file in file_paths:
    print(f"正在讀取: {file}")
    # 讀取 Excel
    temp_df = pd.read_excel(file)
    
    # 【小技巧】可以從文件名中提取城市名，作為新的一列加上去
    # 比如從 "sales_reports\北京_202310.xlsx" 中提取 "北京"
    basename = os.path.basename(file) # 獲取文件名: 北京_202310.xlsx
    city_name = basename.split("_")[0] # 以下劃線分割，取第一部分: 北京
    temp_df["文件來源城市"] = city_name 
    
    df_list.append(temp_df)

# 3. 使用 concat 將所有數(shù)據(jù)上下拼接合并
master_df = pd.concat(df_list, ignore_index=True)
print(f"合并完成，共計 {len(master_df)} 條數(shù)據(jù)。")

# 1. 數(shù)據(jù)清洗：剔除銷售額為空的行
master_df.dropna(subset=["銷售額"], inplace=True)

# 2. 統(tǒng)計分析：按“文件來源城市”分組，計算總銷售額和訂單數(shù)
summary_df = master_df.groupby("文件來源城市").agg({
    "銷售額": "sum",
    "訂單號": "count" # 假設(shè)原始表有訂單號這一列，計數(shù)即可代表訂單數(shù)量
}).reset_index()

summary_df.rename(columns={"訂單號": "訂單總數(shù)", "銷售額": "總銷售額"}, inplace=True)

output_file = "月度匯總報告.xlsx"

# 1. pandas 寫入數(shù)據(jù)
with pd.ExcelWriter(output_file) as writer:
    summary_df.to_excel(writer, sheet_name="城市匯總", index=False)
    master_df.to_excel(writer, sheet_name="所有明細", index=False)

# 2. openpyxl 修改格式
wb = load_workbook(output_file)
ws_summary = wb["城市匯總"]

# 定義樣式：加粗，黃色背景
header_font = Font(bold=True)
header_fill = PatternFill(fill_type="solid", start_color="FFFF00")

# 遍歷第一行（表頭），設(shè)置樣式
for cell in ws_summary[1]:
    cell.font = header_font
    cell.fill = header_fill

# 調(diào)整列寬使數(shù)據(jù)顯示更完整
ws_summary.column_dimensions['A'].width = 15
ws_summary.column_dimensions['B'].width = 15
ws_summary.column_dimensions['C'].width = 15

# 保存最終結(jié)果
wb.save(output_file)
print("自動化報表生成完畢，格式已美化！")

import pandas as pd
from pathlib import Path
folder_path = "./sales_reports"
# 用 Path.glob 批量獲取所有 xlsx 文件
files = list(Path(folder_path).glob("*.xlsx"))
# 收集所有 DataFrame 到列表
dataframes = []
for file in files:
    df = pd.read_excel(file)
    # 【可選】從文件名提取標(biāo)識列，如月份/城市
    df["來源文件"] = file.stem   # 提取文件名不含擴展名
    dataframes.append(df)
# 一次性垂直合并，性能遠優(yōu)于逐行 append
merged = pd.concat(dataframes, ignore_index=True, sort=False)
# 保存結(jié)果
merged.to_excel("合并結(jié)果.xlsx", index=False)

import pandas as pd
from pathlib import Path
from tqdm import tqdm   # pip install tqdm

def merge_excels(folder_path, output_name="merged_output.xlsx"):
    print(f"正在掃描: {folder_path}")
    files = list(Path(folder_path).rglob("*.xlsx"))  # 遞歸查找子文件夾中的文件
    if not files:
        print("未找到 Excel 文件")
        return

    print(f"發(fā)現(xiàn) {len(files)} 個文件")
    all_data = []
    error_files = []

    for file in tqdm(files, desc="處理進度"):
        try:
            df = pd.read_excel(file, dtype=str)       # 統(tǒng)一先按字符串讀取，避免類型沖突
            all_data.append(df)
        except Exception as e:
            error_files.append((file.name, str(e)))  # 記錄錯誤文件

    if not all_data:
        print("沒有成功讀取任何文件")
        return

    # 一次性合并
    merged = pd.concat(all_data, ignore_index=True, sort=False)
    merged.to_excel(output_name, index=False)

    print(f"合并完成！共 {len(merged)} 行。")
    if error_files:
        print(f"警告：{len(error_files)} 個文件讀取失敗")

import pandas as pd

file_path = "同一個工作簿.xlsx"
sheets = pd.read_excel(file_path, sheet_name=None)   # sheet_name=None 讀取所有 sheet

all_dfs = []
for sheet_name, df in sheets.items():
    all_dfs.append(df)

result = pd.concat(all_dfs, ignore_index=True)
result.to_excel("合并后.xlsx", index=False)

# 左表：明細表，右表：城市分類映射表
merged_df = pd.merge(sales_detail, city_mapping, on="城市", how="left")

# 1. 分組匯總
summary = merged.groupby("城市")["銷售額"].agg(["sum", "mean", "count"]).reset_index()

# 2. 數(shù)據(jù)透視表
pivot = pd.pivot_table(merged, values="銷售額", index="城市", columns="季度", aggfunc="sum", fill_value=0)

with pd.ExcelWriter("報表.xlsx") as writer:
    merged.to_excel(writer, sheet_name="明細", index=False)
    summary.to_excel(writer, sheet_name="匯總", index=False)

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("報表.xlsx")
ws = wb["匯總"]   # 或 "明細"

# 設(shè)置表頭樣式：加粗 + 黃色背景
header_font = Font(bold=True)
header_fill = PatternFill(fill_type="solid", start_color="FFFF00")  # 黃色

for cell in ws[1]:   # 第一行是表頭
    cell.font = header_font
    cell.fill = header_fill

# 調(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, 30)
    ws.column_dimensions[col_letter].width = adjusted_width

wb.save("報表_美化.xlsx")