# -*- coding: utf-8 -*-
"""
B25_批量处理Excel_zh.py
B25_BatchProcessExcel_zh.py

配套教程：B25《用 Python + openpyxl 批量处理 Excel》
用途   ：读 销售业绩表_原始.xlsx，自动算"季度合计 / 全年合计 / 完成率"，
         改样式、加汇总行、按"年度合计"降序排，存为 销售业绩表_已处理.xlsx。

依赖   ：openpyxl>=3.0
运行   ：
    python 批量处理Excel_zh.py
"""
from __future__ import annotations

import sys
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


# ---------- 1. 路径常量 ----------
INPUT  = Path("销售业绩表_原始.xlsx")
OUTPUT = Path("销售业绩表_已处理.xlsx")


# ---------- 2. 准备工作（防御性检查）----------
# 输入文件不存在 → 直接友好退出
if not INPUT.exists():
    print(f"[错误] 找不到输入文件：{INPUT}")
    print(f"        请先跑 `python 生成示例文件_zh.py` 生成测试文件。")
    sys.exit(1)

print(f"[开始] 读取：{INPUT}")

# ---------- 3. 打开 + 读数据 ----------
wb = load_workbook(INPUT)
ws = wb.active     # 默认取"上次打开时停留的那张表"

# 读表头
headers = [cell.value for cell in ws[1]]
print(f"[信息] 表头：{headers}")

# 找到几个关键列的索引（防御写法：列名变了也不慌）
def find_col(name_list, target):
    """从 name_list 里找 target 所在的列号（1-based），找不到返回 None。"""
    for i, n in enumerate(name_list, start=1):
        if n == target:
            return i
    return None

col_target = find_col(headers, "年度目标")
col_rate   = find_col(headers, "完成率")

if col_target is None or col_rate is None:
    print(f"[警告] 表头里没找到『年度目标』或『完成率』列，" \
          f"将以 0 / 0% 填充。")
    col_target = col_target or 7
    col_rate   = col_rate   or 8

# 收集数据行（保留原始顺序）
data_rows = []
for row in ws.iter_rows(min_row=2, values_only=True):
    if all(v is None for v in row):
        continue       # 跳过空行
    data_rows.append(list(row))

print(f"[信息] 读入 {len(data_rows)} 行数据")

# ---------- 4. 业务处理：算季度合计、全年合计、完成率 ----------
# 列假设：
#   0=部门, 1=负责人, 2=Q1, 3=Q2, 4=Q3, 5=Q4, 6=年度目标, 7=完成率
processed = []
for row in data_rows:
    dept, mgr, q1, q2, q3, q4, target, _old_rate = row

    # 数字防御：None / 字符串都按 0 算
    nums = [q1 or 0, q2 or 0, q3 or 0, q4 or 0]
    q_sum = sum(nums)
    y_sum = q_sum                          # 全年合计 = 四季度求和
    rate  = (y_sum / target) if target else 0.0

    processed.append([dept, mgr, q1, q2, q3, q4, target, y_sum, rate])

# 按"全年合计"降序排
processed.sort(key=lambda r: r[7], reverse=True)

# ---------- 5. 写一张新表 ----------
new_wb = Workbook()
new_ws = new_wb.active
new_ws.title = "已处理"

# 新表头（多了"全年合计"列）
new_headers = headers[:7] + ["全年合计", "完成率(重算)"]
new_ws.append(new_headers)

# 写数据
for row in processed:
    new_ws.append(row)

# 写汇总行：合计 + 平均
sum_q1  = sum((r[2] or 0) for r in processed)
sum_q2  = sum((r[3] or 0) for r in processed)
sum_q3  = sum((r[4] or 0) for r in processed)
sum_q4  = sum((r[5] or 0) for r in processed)
sum_all = sum((r[7] or 0) for r in processed)
sum_tgt = sum((r[6] or 0) for r in processed)
overall_rate = (sum_all / sum_tgt) if sum_tgt else 0.0

new_ws.append(["合计", "", sum_q1, sum_q2, sum_q3, sum_q4, sum_tgt, sum_all, overall_rate])
new_ws.append(["平均", "", sum_q1 / len(processed), sum_q2 / len(processed),
               sum_q3 / len(processed), sum_q4 / len(processed),
               sum_tgt / len(processed), sum_all / len(processed),
               overall_rate])

# ---------- 6. 美化样式 ----------
# 表头蓝底白字
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
for col_idx in range(1, len(new_headers) + 1):
    cell = new_ws.cell(row=1, column=col_idx)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 数字格式：千分位 + 百分比
for row_idx in range(2, new_ws.max_row + 1):
    for col_idx in [3, 4, 5, 6, 7, 8]:   # Q1-Q4 + 目标 + 合计
        new_ws.cell(row=row_idx, column=col_idx).number_format = "#,##0"
    new_ws.cell(row=row_idx, column=9).number_format = "0.0%"

# 汇总行：浅灰底 + 加粗
summary_fill = PatternFill(start_color="E7E6E6", end_color="E7E6E6", fill_type="solid")
summary_font = Font(bold=True)
for col_idx in range(1, len(new_headers) + 1):
    c1 = new_ws.cell(row=new_ws.max_row - 1, column=col_idx)   # 合计行
    c2 = new_ws.cell(row=new_ws.max_row,     column=col_idx)   # 平均行
    c1.fill = summary_fill
    c2.fill = summary_fill
    c1.font = summary_font
    c2.font = summary_font

# 加边框
thin = Side(border_style="thin", color="BFBFBF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in new_ws.iter_rows(min_row=1, max_row=new_ws.max_row,
                            min_col=1, max_col=len(new_headers)):
    for cell in row:
        cell.border = border

# 列宽
for col_idx in range(1, len(new_headers) + 1):
    new_ws.column_dimensions[get_column_letter(col_idx)].width = 16

# ---------- 7. 保存（关键：另存为新文件，绝不覆盖原文件）----------
new_wb.save(OUTPUT)

print()
print(f"[完成] 已生成：{OUTPUT}")
print(f"[信息] 处理 {len(processed)} 行 + 2 行汇总（合计 + 平均）")
print()
print("打开看一下吧：")
print(f"  - 原文件 {INPUT} 没动过（重要的备份原则）")
print(f"  - 新文件 {OUTPUT} 已按『全年合计』降序排好")