# -*- coding: utf-8 -*-
"""
B25_GenerateSample_en.py
B25_生成示例文件_en.py

Companion tutorial: B25 *Batch-Process Excel with Python + openpyxl*
Purpose    : Build a sample Excel file (SalesReport_Raw.xlsx) so you can run
             the batch-processing script immediately without manual setup.

Requires  : openpyxl>=3.0
Run        : python 生成示例文件_en.py
"""
from __future__ import annotations

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


# ---------- 1. Data (matches 数据源_sample.csv; in real projects, read from CSV) ----------
HEADERS = ["Department", "Manager", "Q1Sales", "Q2Sales", "Q3Sales",
           "Q4Sales", "AnnualTarget", "CompletionRate"]

ROWS = [
    ["Sales Team 1", "Alice Chen",  520000, 580000, 610000, 720000, 2400000, 0.97],
    ["Sales Team 2", "Bob Lee",     480000, 520000, 490000, 610000, 2000000, 1.05],
    ["Sales Team 3", "Carol Wang",  310000, 290000, 340000, 380000, 1200000, 1.10],
    ["Sales Team 4", "David Zhao",  260000, 310000, 320000, 400000, 1200000, 1.08],
    ["Sales Team 5", "Eve Qian",    180000, 220000, 260000, 310000, 1000000, 0.97],
    ["Sales Team 6", "Frank Sun",   150000, 180000, 210000, 240000,  800000, 0.97],
]

# ---------- 2. Create the workbook ----------
wb = Workbook()
ws = wb.active
ws.title = "RawData"

ws.append(HEADERS)
for row in ROWS:
    ws.append(row)

# ---------- 3. Style the header (demonstrates style APIs) ----------
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(HEADERS) + 1):
    cell = ws.cell(row=1, column=col_idx)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

for col_idx in range(1, len(HEADERS) + 1):
    ws.column_dimensions[get_column_letter(col_idx)].width = 16

# Number format demos
for row_idx in range(2, len(ROWS) + 2):
    ws.cell(row=row_idx, column=7).number_format = "#,##0"   # thousands separator
    ws.cell(row=row_idx, column=8).number_format = "0.0%"    # percentage

# ---------- 4. Save ----------
OUTPUT = "SalesReport_Raw.xlsx"
wb.save(OUTPUT)

print(f"[Done] Generated: {OUTPUT}")
print(f"[Rows] 1 header + {len(ROWS)} data = {len(ROWS) + 1} rows")
print(f"[Cols] {len(HEADERS)} columns")
print()
print("Next, run:")
print("  python 批量处理Excel_en.py")
print("See README.md section 'How to use' for the full workflow.")