#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""重绘“呈总经理”文档中的三张图（2026 统一为 1–10 月），并替换 docx 内嵌图片。"""
import os
import shutil
import zipfile
from collections import defaultdict
import openpyxl
from PIL import Image
from playwright.sync_api import sync_playwright
BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DOC = os.environ.get('DOCX') or (BASE + '/销售分析/西安佰奥莱博_近三年经营简报_呈总经理_20261021.docx')
P = {'2024': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2024全年订单.xlsx',
'2025': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx',
'2026': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'}
N = '西安佰奥莱博生物科技有限公司'
BLUE = '#1F6FC5'
ORANGE = '#F5A04A'
monthly = defaultdict(float)
yearly = defaultdict(float)
def load(y, p):
wb = openpyxl.load_workbook(p, data_only=True)
ws = wb['Sheet1'] if y != '2026' else wb.active
it = ws.iter_rows(values_only=True)
H = [str(x).strip() if x is not None else '' for x in next(it)]
i = {h: j for j, h in enumerate(H)}
for r in it:
if str(r[i['客户']] or '') != N:
continue
if str(r[i['单据类型']] or '') != '标准销售订单' or str(r[i['是否赠品']] or '') == '是':
continue
a = float(r[i['销售数量']] or 0) * float(r[i['含税单价']] or 0)
mon = str(r[i['合同月份']] if '合同月份' in i else r[i['合同日期']])[:7]
monthly[mon] += a
yearly[y] += a
for y, p in P.items():
load(y, p)
Y = {k: v / 1e4 for k, v in yearly.items()}
M26 = [monthly.get('2026-%02d' % m, 0) / 1e4 for m in range(1, 11)]
M25 = [monthly.get('2025-%02d' % m, 0) / 1e4 for m in range(1, 13)]
YOY = (Y['2026'] / sum(M25[:10]) - 1) * 100
G25 = (Y['2025'] / Y['2024'] - 1) * 100
CSS = """
*{margin:0;padding:0;box-sizing:border-box}
body{background:#fff;font-family:"PingFang SC","Microsoft YaHei",sans-serif;color:#5A6773}
.wrap{position:relative;background:#fff}
.title{color:#2E75B6;font-weight:500}
"""
def html1():
bars = [('2024年', Y['2024'], '', BLUE), ('2025年', Y['2025'], '+%.1f%%' % G25, BLUE),
('2026年1-10月', Y['2026'], '%.1f%%' % YOY, ORANGE)]
x0, y0, h = 130, 360, 280 # plot origin(left, bottom), height
def bar(i, label, val, delta, color):
bw = 170
cx = 210 + i * 300
hh = val / 120 * h
top = y0 - hh
txt = ('
%.2f
'
% (cx - bw // 2, top - 34, bw, val))
d = ''
if delta:
dcol = '#FFFFFF' if color == BLUE else '#FFFFFF'
d = ('
'
% (cx - bw // 2, y0 + 14, bw, label))
return txt + b + d + lb
grid = ''
for k in range(5):
v = 30 * k
yy = y0 - v / 120 * h
grid += (''
'
%d
'
% (x0, yy, yy - 11, v))
return ('
'
'
近三年销售额（价税合计，万元）
'
'%s%s%s
' % (CSS, grid, ''.join(bar(i, *b) for i, b in enumerate(bars)), ''))
def html2():
W, Hh, L, B = 980, 400, 75, 330
maxv, ph = 30, 260
pts25 = []
pts26 = []
for i in range(12):
x = L + 55 + i * 72
y = B - M25[i] / maxv * ph
pts25.append((x, y, M25[i]))
for i in range(10):
x = L + 55 + i * 72
y = B - M26[i] / maxv * ph
pts26.append((x, y, M26[i]))
poly = lambda pts: ' '.join('%d,%d' % (x, y) for x, y, _ in pts)
grid = ''
for v in (0, 8, 15, 22, 30):
yy = B - v / maxv * ph
grid += (''
'
%d
'
% (L + 40, yy, yy - 10, v))
xs = ''.join('
%d月
'
% (L + 30 + i * 72, B + 14, i + 1) for i in range(12))
dots = ''.join('' % (x - 4, y - 4, c)
for (x, y, _), c in [(p, '#A8C7E8') for p in pts25] + [(p, ORANGE) for p in pts26])
return ('
'
'
月度销售额对比（万元）：2025 全年 vs 2026 年 1-10 月
'
'
━━ 2025年
'
'
━━ 2026年
'
'%s%s'
''
'%s%s
' % (CSS, 110, 210, ORANGE, grid, xs, poly(pts25), poly(pts26), ORANGE, dots, ''))
def html3():
rows = [('2024年', 6.8, 2.72), ('2025年', 57.1, 62.64), ('2026年1-10月', 53.5, 24.77)]
out = ''
for i, (lab, pct, amt) in enumerate(rows):
top = 100 + i * 62
wide = 470
fill = wide * pct / 100
out += ('
%s
'
''
''
'
%.1f%% （%.2f 万元）
'
% (top + 4, lab, top, wide, top, max(6, fill), BLUE if i else '#A8C7E8',
205 + max(6, fill) + 12, top + 6, pct, amt))
return ('
'
'
FMD 直扩系列占客户销售额比重（%%）
'
'%s
' % (CSS, out))
OUT = '/tmp/charts'
os.makedirs(OUT, exist_ok=True)
specs = [('image2.png', html1(), 980, 440), ('image3.png', html2(), 980, 400), ('image4.png', html3(), 980, 300)]
with sync_playwright() as pw:
b = pw.chromium.launch(channel='chrome')
for name, html, w, h in specs:
hp = os.path.join(OUT, name.replace('.png', '.html'))
open(hp, 'w', encoding='utf-8').write(html)
pg = b.new_page(viewport={'width': w, 'height': h}, device_scale_factor=2)
pg.goto('file://' + hp)
pg.wait_for_timeout(300)
pg.locator('.wrap').screenshot(path=os.path.join(OUT, name))
pg.close()
im = Image.open(os.path.join(OUT, name))
im.resize((w, h), Image.LANCZOS).save(os.path.join(OUT, name))
b.close()
# 替换 docx 内嵌图片
tmp = '/tmp/doc_replace'
shutil.rmtree(tmp, ignore_errors=True)
os.makedirs(tmp)
with zipfile.ZipFile(DOC) as z:
names = z.namelist()
z.extractall(tmp)
for name in ('image2.png', 'image3.png', 'image4.png'):
shutil.copy(os.path.join(OUT, name), os.path.join(tmp, 'word/media', name))
new = DOC + '.new'
with zipfile.ZipFile(new, 'w', zipfile.ZIP_DEFLATED) as z:
for n in names:
z.write(os.path.join(tmp, n), n)
os.replace(new, DOC)
print('charts rebuilt & docx updated')
print('Y', {k: round(v, 2) for k, v in Y.items()}, 'YOY %.1f%% G25 %.1f%%' % (YOY, G25))
print('M26', [round(x, 2) for x in M26])