#!/usr/bin/env python3 # -*- coding: utf-8 -*- """生成 10月W4 周会纪要预填版（赵云浩 / 刘子研） 骨架：工具/大客户部周会纪要_20261026_10月W4.xlsx 数据：本周周报（10/19-10/25）、上周周报（10/12-10/18）、9月W4 纪要 输出：工具/大客户部周会纪要_20261026_10月W4_预填.xlsx """ import os import re import openpyxl from openpyxl.styles import Alignment, Font DOC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents' ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' SKEL = os.path.join(ROOT, '工具', '大客户部周会纪要_20261026_10月W4.xlsx') OUT = os.path.join(ROOT, '工具', '大客户部周会纪要_20261026_10月W4_预填.xlsx') F_ZHA_W3 = os.path.join(DOC, 'doc_64942601ff3e_销售工程师周报-赵云浩（2026年10月W3）.xlsx') F_ZHA_W2 = os.path.join(DOC, 'doc_0cc815d0c9b2_销售工程师周报-赵云浩（2026年10月W2）.xlsx') F_LIU_W3 = os.path.join(DOC, 'doc_8fe61b1a4b99_销售工程师周报-刘子研（2026年10月25日).xlsx') F_LIU_W2 = os.path.join(DOC, 'doc_e9130588c9f6_销售工程师周报-刘子研（2026年10月18日).xlsx') F_SEP = os.path.join(DOC, 'doc_169de7994949_大客户部周会纪要(20260928).xlsx') def txt(v): return '' if v is None else str(v).strip() def norm(s): s = re.sub(r'(湖南|广州|北京|郑州|苏州|杭州|宁波|上海|深圳|西安|武汉)', '', txt(s)) return re.sub(r'\s+', '', s) def hit(a, b): a, b = norm(a), norm(b) return bool(a and b and (a in b or b in a)) def tokens(s): """中文按 2-gram 切，避免“客诉跟进”与“手足口客诉”一个字都不重合""" s = txt(s) out = set() for run in re.findall(r'[\u4e00-\u9fa5]{2,}', s): for i in range(len(run) - 1): out.add(run[i:i + 2]) return out def parse_report(path, done_col): ws = openpyxl.load_workbook(path, data_only=True).active sec1, sec2, in2 = [], [], False for r in range(3, ws.max_row + 1): a = txt(ws.cell(r, 1).value) if a.startswith('二、'): in2 = True continue if a.startswith('三、'): break c, d = txt(ws.cell(r, 3).value), txt(ws.cell(r, 4).value) if not c or c in ('客户名称', '客户'): continue if in2: sec2.append({'cust': c, 'proj': d, 'next': txt(ws.cell(r, 5).value)}) else: sec1.append({'cust': c, 'proj': d, 'plan': txt(ws.cell(r, 5).value), 'done': txt(ws.cell(r, done_col).value)}) return sec1, sec2 def sep_history(): ws = openpyxl.load_workbook(F_SEP, data_only=True)['9月W4'] out = [] for r in range(8, ws.max_row + 1): c = txt(ws.cell(r, 2).value) if not c or c in ('客户名称', '客户'): continue out.append({'cust': c, 'prog': txt(ws.cell(r, 10).value), 'next': txt(ws.cell(r, 11).value)}) return out def pick(rows, cust, key): for x in rows: if hit(cust, x['cust']): return x.get(key, '') return '' def attach_next(rows, sec2, who): """下周计划贴到本人该客户下第一条还没计划的行；对不上就单列一行""" for item in sec2: multi = any(sep in item['cust'] for sep in ('/', '、', '，')) cands = [] if multi else [r for r in rows if r['who'] == who and hit(r['cust'], item['cust'])] free = [r for r in cands if not r['next']] if free: tk = tokens(item['proj']) free.sort(key=lambda r: -len(tk & tokens(r['proj']))) free[0]['next'] = item['next'] else: rows.append({'who': who, 'cust': item['cust'], 'proj': item['proj'], 'plan': '', 'done': '（下周计划单列，本周完成情况未单列）', 'sep_prog': '', 'sep_next': '', 'w2_done': '', 'w2_next': '', 'next': item['next']}) def main(): sep = sep_history() zha1, zha2 = parse_report(F_ZHA_W3, 14) zha1w2, zha2w2 = parse_report(F_ZHA_W2, 13) liu1, liu2 = parse_report(F_LIU_W3, 7) liu1w2, liu2w2 = parse_report(F_LIU_W2, 7) rows = [] for who, s1, s1w2, s2w2 in (('赵云浩', zha1, zha1w2, zha2w2), ('刘子研', liu1, liu1w2, liu2w2)): for x in s1: rows.append({'who': who, 'cust': x['cust'], 'proj': x['proj'], 'plan': x['plan'], 'done': x['done'], 'sep_prog': pick(sep, x['cust'], 'prog'), 'sep_next': pick(sep, x['cust'], 'next'), 'w2_done': pick(s1w2, x['cust'], 'done'), 'w2_next': pick(s2w2, x['cust'], 'next'), 'next': ''}) attach_next(rows, zha2, '赵云浩') attach_next(rows, liu2, '刘子研') # 保证同一个人所有行连续（补行会追加到末尾） rows.sort(key=lambda r: 0 if r['who'] == '赵云浩' else 1) wb = openpyxl.load_workbook(SKEL) ws = wb['10月W4'] for m in list(ws.merged_cells.ranges): if m.min_row >= 8: ws.unmerge_cells(str(m)) heads = ['责任人', '客户名称', '项目名称', '上周工作计划', '本周完成情况', '9月W4进度', '9月W4下周计划', '10月W2进度', '10月W2下周计划', '备注/联系人', '下周工作计划'] for i, h in enumerate(heads, 1): c = ws.cell(7, i, h) c.font = Font(bold=True) c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) for i in range(12, 16): ws.cell(7, i, None) r0 = 8 for i, x in enumerate(rows): vals = [x['who'] if i == 0 or rows[i - 1]['who'] != x['who'] else None, x['cust'], x['proj'], x['plan'], x['done'], x['sep_prog'], x['sep_next'], x['w2_done'], x['w2_next'], '', x['next']] vals = ['' if isinstance(v, str) and v.startswith('=DISPIMG') else v for v in vals] for j, v in enumerate(vals, 1): c = ws.cell(r0 + i, j, v) c.alignment = Alignment(vertical='top', wrap_text=True) ws.row_dimensions[r0 + i].height = 90 start = r0 for i in range(1, len(rows) + 1): if i == len(rows) or rows[i]['who'] != rows[i - 1]['who']: if i + r0 - 1 > start: ws.merge_cells(start_row=start, start_column=1, end_row=i + r0 - 1, end_column=1) start = i + r0 for col, w in {'A': 10, 'B': 14, 'C': 30, 'D': 34, 'E': 52, 'F': 40, 'G': 26, 'H': 40, 'I': 24, 'J': 12, 'K': 30}.items(): ws.column_dimensions[col].width = w ws['C5'] = ('本周重点（2026-10-19—10-25）：\n' '1、圣湘：脓毒症报证项目三批中试试剂已发出（FM2181-4-NA-020303），等待客户验证；呼吸道冻干 8 色超快速项目完成线下对接。\n' '2、万孚：FE20-002902 新酶客户反馈优于对照，待二次验证；HBV/HIV 冻干与 UNG 版本继续推进。\n' '3、安图：E10-000725 首单采购后仍在测试阶段，本月内出结论。\n' '4、明日达：829 体系去掉 RT 酶后液体性能提升，冻干后仍不如对照，加速稳定性待解。\n' '5、刘子研线：天隆 ZJ4 单管不稳定、丹威 LAMP 冻干全预混优化、迪安麻疹四款冻干球、新产业数字 PCR 4X buffer 结果良好。') ws['C5'].alignment = Alignment(vertical='top', wrap_text=True) ws.row_dimensions[5].height = 120 wb.save(OUT) print('rows', len(rows), '→', OUT) for x in rows: print(' ', x['who'], x['cust'], '|', x['proj'][:22].replace('\n', ' '), '| 下周:', x['next'][:26].replace('\n', ' ')) if __name__ == '__main__': main()