#!/usr/bin/env python3 # -*- coding: utf-8 -*- """在v7末尾插入2026科研新客户开发数量与浙广TOP20课题组页。""" import os, json, re from pptx import Presentation from pptx.util import Inches, Pt from pptx.dml.color import RGBColor from pptx.enum.text import PP_ALIGN, MSO_ANCHOR from pptx.enum.shapes import MSO_SHAPE from pptx.oxml.ns import qn from lxml import etree ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' SRC = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v7.pptx') DATA = os.path.join(ROOT, '数据/2026科研新客户_浙广TOP20_20261108.json') LOGO = '/Users/liuxinyuan/work/公司资料/logo_transparent.png' NAVY = RGBColor(0x0B, 0x2A, 0x4A) BLUE = RGBColor(0x1F, 0x6F, 0xEB) PALE = RGBColor(0xF5, 0xF8, 0xFC) GREY = RGBColor(0x5B, 0x6B, 0x7F) MIDGREY = RGBColor(0x8A, 0x99, 0xAA) RED = RGBColor(0xC0, 0x39, 0x2B) GREEN = RGBColor(0x1E, 0x84, 0x49) WHITE = RGBColor(0xFF, 0xFF, 0xFF) def set_run(run, size=8, color=NAVY, bold=False): run.font.size = Pt(size); run.font.bold = bold run.font.color.rgb = color; run.font.name = 'Times New Roman' rPr = run._r.get_or_add_rPr() ea = rPr.find(qn('a:ea')) if ea is None: ea = etree.SubElement(rPr, qn('a:ea')) ea.set('typeface', '宋体') cs = rPr.find(qn('a:cs')) if cs is None: cs = etree.SubElement(rPr, qn('a:cs')) cs.set('typeface', '宋体') def add_text(slide, x, y, w, h, text, size=9, color=NAVY, bold=False, align=PP_ALIGN.LEFT, anchor=MSO_ANCHOR.TOP, line_spacing=1.06): box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h)) tf = box.text_frame; tf.word_wrap = True tf.margin_left = tf.margin_right = tf.margin_top = tf.margin_bottom = 0 tf.vertical_anchor = anchor for i, line in enumerate(str(text).split('\n')): p = tf.paragraphs[0] if i == 0 else tf.add_paragraph() p.alignment = align; p.line_spacing = line_spacing r = p.add_run(); r.text = line; set_run(r, size, color, bold) return box def add_rect(slide, x, y, w, h, fill, line=None): shp = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, Inches(x), Inches(y), Inches(w), Inches(h)) shp.fill.solid(); shp.fill.fore_color.rgb = fill if line: shp.line.color.rgb = line; shp.line.width = Pt(0.75) else: shp.line.fill.background() def add_header(s, title, subtitle): add_rect(s, 0, 0, 13.333, 0.06, BLUE) if os.path.exists(LOGO): s.shapes.add_picture(LOGO, Inches(0.48), Inches(0.22), height=Inches(0.34)) add_text(s, 0.48, 0.57, 12.2, 0.42, title, size=18, color=NAVY, bold=True) add_text(s, 0.48, 1.01, 12.3, 0.28, subtitle, size=8.5, color=GREY) def add_footer(s, note): add_rect(s, 0.48, 7.03, 12.35, 0.012, RGBColor(0xD9, 0xE2, 0xEE)) add_text(s, 0.48, 7.10, 9.8, 0.22, note, size=6.8, color=MIDGREY) add_text(s, 10.55, 7.10, 2.25, 0.22, '守护生命健康，缔造美好生活', size=7.5, color=BLUE, bold=True, align=PP_ALIGN.RIGHT) def add_card(s, x, y, w, h, label, value, color): add_rect(s, x, y, w, h, PALE, RGBColor(0xDD, 0xE7, 0xF2)) add_text(s, x + 0.16, y + 0.10, w - 0.3, 0.2, label, size=7.8, color=GREY) add_text(s, x + 0.16, y + 0.31, w - 0.3, 0.32, value, size=18, color=color, bold=True) def add_table(s, x, y, w, headers, rows, widths, row_h=0.36, font=8.0, header_font=7.6, align_center=(1, 2, 3), bold_first=True): nrows, ncols = len(rows) + 1, len(headers) table = s.shapes.add_table(nrows, ncols, Inches(x), Inches(y), Inches(w), Inches(0.32 + len(rows) * row_h)).table table.first_row = True; table.horz_banding = False for j, wd in enumerate(widths): table.columns[j].width = Inches(wd) table.rows[0].height = Inches(0.32) for i in range(1, nrows): table.rows[i].height = Inches(row_h) for j, htext in enumerate(headers): cell = table.cell(0, j) cell.fill.solid(); cell.fill.fore_color.rgb = NAVY cell.margin_left = cell.margin_right = Inches(0.04) cell.margin_top = cell.margin_bottom = Inches(0.01) cell.vertical_anchor = MSO_ANCHOR.MIDDLE p = cell.text_frame.paragraphs[0] p.alignment = PP_ALIGN.CENTER if j in align_center else PP_ALIGN.LEFT r = p.add_run(); r.text = htext; set_run(r, header_font, WHITE, True) for i, row in enumerate(rows, start=1): fill = PALE if i % 2 == 0 else WHITE for j, val in enumerate(row): cell = table.cell(i, j) cell.fill.solid(); cell.fill.fore_color.rgb = fill cell.margin_left = cell.margin_right = Inches(0.04) cell.margin_top = cell.margin_bottom = Inches(0.01) cell.vertical_anchor = MSO_ANCHOR.MIDDLE p = cell.text_frame.paragraphs[0] p.alignment = PP_ALIGN.CENTER if j in align_center else PP_ALIGN.LEFT p.line_spacing = 1.0 r = p.add_run(); r.text = str(val) set_run(r, font, NAVY, bold_first and j == 0) return table def trim(t, n=62): t = re.sub(r'\s+', ' ', str(t or '')).strip() if len(t) <= n: return t cut = t[:n] for p in '。；，、': k = cut.rfind(p) if k >= n * 0.55: return cut[:k] + '…' return cut + '…' def short_product(name): if not name: return '' s = name.replace('AcuGenix™ ', '').replace(' (Dye Plus)', '').replace(' (Universal ROX)', '') s = s.replace('Master Mix', 'Mix').replace('All-in-One ', '').replace('cDNA Synthesis', 'cDNA') return s data = json.load(open(DATA, encoding='utf-8')) prs = Presentation(SRC) blank = prs.slide_layouts[1] new_ids = [] def new_slide(title, subtitle): s = prs.slides.add_slide(blank) for sh in list(s.shapes): if sh.is_placeholder: sh._element.getparent().remove(sh._element) add_header(s, title, subtitle) new_ids.append(prs.slides._sldIdLst[-1]) return s # ---------- summary ---------- s = new_slide('2026科研新客户开发：浙江 / 广东', '生命科学部 KPI 口径：新课题组首次发出测试单算开发成功｜2026年1-10月｜单位：个') add_card(s, 0.48, 1.38, 2.85, 0.82, '2026年1-10月合计', '525', BLUE) add_card(s, 3.55, 1.38, 2.85, 0.82, '浙江', '229', NAVY) add_card(s, 6.62, 1.38, 2.85, 0.82, '广东', '296', NAVY) add_card(s, 9.69, 1.38, 2.85, 0.82, '10月新增', '19', GREEN) months = [f'2026-{m:02d}' for m in range(1, 11)] month_names = [f'{m}月' for m in range(1, 11)] rows = [] for m, name in zip(months, month_names): v = data['monthly'][m] rows.append([name, str(v['浙江']), str(v['广东']), str(v['合计'])]) rows.append(['合计', str(data['counts']['浙江']), str(data['counts']['广东']), str(data['counts']['合计'])]) t = add_table(s, 0.48, 2.42, 7.4, ['月份', '浙江', '广东', '合计'], rows, [1.6, 1.4, 1.4, 1.4], row_h=0.37, font=9.0, header_font=8.4, align_center=(1, 2, 3)) for c in range(4): t.cell(len(rows), c).fill.solid(); t.cell(len(rows), c).fill.fore_color.rgb = RGBColor(0xE8, 0xF1, 0xFC) # right panel add_rect(s, 8.18, 2.42, 4.35, 3.92, PALE, RGBColor(0xDD, 0xE7, 0xF2)) add_text(s, 8.42, 2.62, 3.9, 0.28, '区域结构', size=10.5, color=NAVY, bold=True) add_text(s, 8.42, 3.02, 3.85, 1.65, f'浙江 229，占 43.6%\n广东 296，占 56.4%\n\n3月为全年峰值：浙江 61、广东 74\n10月新增回落：浙江 6、广东 13', size=9.2, color=NAVY, line_spacing=1.35) add_text(s, 8.42, 4.85, 3.85, 1.15, '口径说明：\n• 新课题组首次测试单即计入开发成功\n• 同一课题组只计1次\n• 学校/医院不作为客户单位', size=7.4, color=GREY, line_spacing=1.3) add_footer(s, '数据源：数据/ls_ketizu_purchase.json（首次订单 first_any，截至2026-10-31）；客户单位为PI课题组；KPI定义见 数据/kpi_scheme.json（ls_mgr）。') # ---------- top20 pages ---------- for reg in ['浙江', '广东']: rows = data['top20'][reg] for part in (0, 1): chunk = rows[part * 10:(part + 1) * 10] s = new_slide(f'{reg}｜2026科研新客户TOP20课题组（{part + 1}/2）', '按2026累计销售额排序｜主销货号取该课题组销售额第一的货号｜项目背景取自飞书活动摘要') table_rows = [] for r in chunk: table_rows.append([ str(r['rank']), r['name'], r['sales'], f"{r['amt_p26'] / 10000:.2f}", f"{r['sku']}\n{short_product(r['sku_name'])}", trim(r['background'], 58), ]) add_table(s, 0.48, 1.82, 12.35, ['#', '课题组', '销售员', '2026销售\n(万元)', '主销货号 / 品名', '项目背景概述（活动摘要）'], table_rows, [0.42, 1.72, 0.78, 1.02, 2.58, 5.83], row_h=0.46, font=7.2, header_font=7.4, align_center=(0, 2, 3), bold_first=False) add_footer(s, '口径：新客户=2026年首次出现测试单/订单的PI课题组；销售额为该课题组2026年累计含税金额；活动摘要覆盖2026-07至11，未匹配到的标“待补”。') # insert before old summary (P26, index 25) lst = prs.slides._sldIdLst for offset, new_id in enumerate(new_ids): lst.remove(new_id) lst.insert(25 + offset, new_id) prs.save(SRC) print('saved', SRC, 'slides', len(prs.slides))