#!/usr/bin/env python3
"""
merge_excel.py —— 把一个文件夹里的 Excel/CSV 按表头对齐合并成一张总表

用法:
  python merge_excel.py 输入文件夹 -o 合并结果.xlsx
  python merge_excel.py ./10月门店 -o 10月汇总.xlsx --dedupe 订单号 --all-sheets --header-row 1

特性:
  - 列顺序不同也能按表头名自动对齐；某个文件缺的列留空
  - 自动识别 UTF-8 / GBK 编码的 CSV
  - 可按指定列去重、可标注来源文件/工作表
  - 输出 Excel 含「合并结果」「汇总」两个工作表
依赖: pip install pandas openpyxl xlrd
"""
import argparse, sys
from pathlib import Path
import pandas as pd

def read_csv(p: Path, header: int) -> pd.DataFrame:
    for enc in ("utf-8-sig", "gbk", "gb18030"):
        try:
            return pd.read_csv(p, header=header, dtype=str, encoding=enc, keep_default_na=False)
        except UnicodeDecodeError:
            continue
    raise ValueError(f"无法识别编码: {p.name}")

def load(p: Path, header: int, all_sheets: bool):
    if p.suffix.lower() == ".csv":
        yield "", read_csv(p, header)
        return
    sheets = pd.read_excel(p, sheet_name=None if all_sheets else 0, header=header, dtype=str, keep_default_na=False)
    if isinstance(sheets, pd.DataFrame):
        sheets = {"": sheets}
    yield from sheets.items()

def main():
    ap = argparse.ArgumentParser(description="Excel/CSV 多文件合并")
    ap.add_argument("folder"); ap.add_argument("-o", "--output", default="合并结果.xlsx")
    ap.add_argument("--header-row", type=int, default=1, help="表头所在行（从1开始）")
    ap.add_argument("--all-sheets", action="store_true", help="读取每个 Excel 的所有工作表")
    ap.add_argument("--dedupe", nargs="*", default=[], help="按这些列去重")
    ap.add_argument("--no-source", action="store_true", help="不加来源列")
    a = ap.parse_args()

    files = sorted(f for f in Path(a.folder).iterdir() if f.suffix.lower() in (".xlsx", ".xls", ".csv") and not f.name.startswith("~$"))
    if not files:
        sys.exit("文件夹里没有 .xlsx/.xls/.csv 文件")
    frames, summary = [], []
    for f in files:
        for sheet, df in load(f, a.header_row - 1, a.all_sheets):
            df.columns = [str(c).strip() for c in df.columns]
            df = df.apply(lambda s: s.str.strip() if pd.api.types.is_string_dtype(s) else s)
            df = df.loc[~(df == "").all(axis=1)]
            if not a.no_source:
                df.insert(0, "来源文件", f.name)
                if a.all_sheets:
                    df.insert(1, "来源工作表", sheet)
            frames.append(df); summary.append((f.name, sheet, len(df)))
            print(f"  读取 {f.name} {sheet} {len(df)} 行")
    out = pd.concat(frames, ignore_index=True, sort=False).fillna("")
    removed = 0
    if a.dedupe:
        miss = [c for c in a.dedupe if c not in out.columns]
        if miss:
            sys.exit(f"去重列不存在: {miss}，可用列: {list(out.columns)}")
        n = len(out); out = out.drop_duplicates(subset=a.dedupe, keep="first"); removed = n - len(out)
    with pd.ExcelWriter(a.output, engine="openpyxl") as w:
        out.to_excel(w, sheet_name="合并结果", index=False)
        s = pd.DataFrame(summary, columns=["来源文件", "工作表", "行数"])
        s.loc[len(s)] = ["合计（去重后）", "", len(out)]; s.loc[len(s)] = ["删除重复", "", removed]
        s.to_excel(w, sheet_name="汇总", index=False)
    print(f"完成: {len(files)} 个文件 → {len(out)} 行, {len(out.columns)} 列, 删除重复 {removed} 行 → {a.output}")

if __name__ == "__main__":
    main()