_generate_mcp_tech_kjb_excel.py
4.15 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
# -*- coding: utf-8 -*-
"""一次性脚本：从 MCP 快照 JSON 生成带格式与合计行的科技部业绩 Excel。"""
import json
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
ROOT = Path(__file__).resolve().parent
DATA_JSON = ROOT / "_mcp_tech_kjb_202603_data.json"
OUT_XLSX = ROOT / "2026年3月_科技部业绩_MCP汇总.xlsx"
HEADERS = [
"门店名称",
"Cell开单业绩",
"Cell退款业绩",
"溯源系统开单业绩",
"溯源系统退款业绩",
"Slim开单业绩",
"Slim退款业绩",
]
NOTE_LINES = [
"统计口径（MCP 只读查询，与日报科技部老师落月一致）：",
"开单=lq_kd_kjbsyj INNER JOIN lq_kd_kdjlb，F_ItemCategory=科美，有效；按 kdjlb.kdrq 落在 2026-03；",
" 对 kjblsyj 按 BeautyType 分列汇总：cell/Cell、溯源系统/溯源、slim/Slim。",
"退款=lq_hytk_kjbsyj INNER JOIN lq_hytk_hytk，条件同上；按 hytk.tksj 落在 2026-03；对 kjblsyj 求和。",
"明细级取数见：mcp_tech_kjb_detail_aligned_202603.sql；老师明细 Excel：_generate_mcp_tech_kjb_detail_excel.py",
"合计行使用 Excel SUM 公式，修改明细行后会自动重算。",
]
def main():
with open(DATA_JSON, "r", encoding="utf-8") as f:
rows = json.load(f)
wb = Workbook()
ws = wb.active
ws.title = "202603科技部业绩"
thin = Side(style="thin", color="FF000000")
grid = Border(left=thin, right=thin, top=thin, bottom=thin)
header_fill = PatternFill("solid", fgColor="FF4472C4")
header_font = Font(bold=True, color="FFFFFFFF", size=11)
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
total_fill = PatternFill("solid", fgColor="FFF2F2F2")
total_font = Font(bold=True, size=11)
num_fmt = "#,##0.00"
for col, h in enumerate(HEADERS, 1):
c = ws.cell(row=1, column=col, value=h)
c.font = header_font
c.fill = header_fill
c.alignment = header_align
c.border = grid
for i, r in enumerate(rows, start=2):
ws.cell(row=i, column=1, value=r["storeName"]).border = grid
for col, key in enumerate(
["cellBilling", "cellRefund", "traceBilling", "traceRefund", "slimBilling", "slimRefund"],
start=2,
):
c = ws.cell(row=i, column=col, value=float(r[key]))
c.number_format = num_fmt
c.alignment = Alignment(horizontal="right", vertical="center")
c.border = grid
first_data = 2
last_data = first_data + len(rows) - 1
total_row = last_data + 1
ws.cell(row=total_row, column=1, value="合计").font = total_font
ws.cell(row=total_row, column=1).fill = total_fill
ws.cell(row=total_row, column=1).border = grid
ws.cell(row=total_row, column=1).alignment = Alignment(horizontal="center", vertical="center")
for col in range(2, 8):
letter = get_column_letter(col)
c = ws.cell(
row=total_row,
column=col,
value=f"=SUM({letter}{first_data}:{letter}{last_data})",
)
c.number_format = num_fmt
c.font = total_font
c.fill = total_fill
c.border = grid
c.alignment = Alignment(horizontal="right", vertical="center")
# 表区域加粗外框（合计行已含在底部）
for r in range(1, total_row + 1):
for col in range(1, 8):
ws.cell(row=r, column=col).border = grid
ws.freeze_panes = "A2"
ws.auto_filter.ref = f"A1:G{last_data}"
ws.column_dimensions["A"].width = 22
for col in range(2, 8):
ws.column_dimensions[get_column_letter(col)].width = 16
note_start = total_row + 2
note_end = note_start + len(NOTE_LINES) - 1
ws.merge_cells(start_row=note_start, start_column=1, end_row=note_end, end_column=7)
ncell = ws.cell(row=note_start, column=1, value="\n".join(NOTE_LINES))
ncell.font = Font(size=9, color="FF666666")
ncell.alignment = Alignment(wrap_text=True, vertical="top", horizontal="left")
wb.save(OUT_XLSX)
print("Saved:", OUT_XLSX)
if __name__ == "__main__":
main()