# -*- coding: utf-8 -*- import os import re from datetime import datetime from openpyxl import load_workbook BASE = r"c:\work\demo\dayang\mescloud" EXTRACT = os.path.join(BASE, "_extract_mes_progress_output.txt") STATUS_XLSX = r"c:\Users\j2742\Desktop\temp\大洋\实施文档\MES实施现状-260716.xlsx" PIC_DIR = r"c:\Users\j2742\Desktop\temp\大洋\pic" OUT = os.path.join(BASE, "_mes_milestone_summary.txt") CELL_RE = re.compile(r"^([A-Z]+)(\d+): (.+)$") def fmt_val(v): if v is None: return "" if isinstance(v, datetime): return v.strftime("%Y-%m-%d") return str(v).strip() def extract_xlsx(path, max_rows=200, max_cols=30): lines = [] wb = load_workbook(path, data_only=True) lines.append(f"文件: {path}") lines.append(f"工作表: {', '.join(wb.sheetnames)}") for name in wb.sheetnames: ws = wb[name] lines.append("") lines.append(f"--- 工作表: {name} (维度 {ws.dimensions}) ---") for row in ws.iter_rows(min_row=1, max_row=max_rows, min_col=1, max_col=max_cols): for cell in row: v = cell.value if v is not None and str(v).strip() != "": lines.append(f" {cell.coordinate}: {fmt_val(v)}") wb.close() return lines def parse_extract_sheet(text, sheet_name): pattern = rf"^SHEET: {re.escape(sheet_name)}\s*$" lines = text.splitlines() start = None for i, line in enumerate(lines): if re.match(pattern, line.strip()): start = i break if start is None: return [] out = [] for line in lines[start + 1:]: stripped = line.strip() if stripped.startswith("SHEET:"): break if stripped.startswith("--- Non-empty cells"): break m = CELL_RE.match(stripped) if m: out.append((m.group(1), int(m.group(2)), m.group(3))) return out def cells_to_rows(cells): rows = {} for col, row, val in cells: rows.setdefault(row, {})[col] = val return rows def list_images(root): exts = {".png", ".jpg", ".jpeg", ".gif", ".bmp", ".webp", ".ico"} found = [] for dirpath, _, filenames in os.walk(root): for fn in filenames: if os.path.splitext(fn)[1].lower() in exts: rel = os.path.relpath(os.path.join(dirpath, fn), root) found.append(rel.replace("\\", "/")) return sorted(found, key=lambda x: x.lower()) with open(EXTRACT, "r", encoding="utf-8") as f: extract_text = f.read() sections = [] sections.append("=" * 80) sections.append("六安裕洋 MES 实施里程碑与截图资源汇总") sections.append(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") sections.append("数据来源: 大洋-京宜安MES实施进度.xlsx、MES实施现状-260716.xlsx、pic/ 目录") sections.append("=" * 80) # A sections.append("") sections.append("A. 总进度 — 各周完成率（来源：总进度）") sections.append("-" * 80) zong_cells = parse_extract_sheet(extract_text, "总进度") zong_rows = cells_to_rows(zong_cells) sections.append("汇总表:") sections.append(" 实施阶段 | 开始时间 | 结束时间 | 具体任务(周) | 任务数 | 完成数 | 合格率") for r in sorted(k for k in zong_rows if k >= 3): d = zong_rows[r] sections.append( f" {d.get('A','')} | {d.get('B','')} | {d.get('C','')} | {d.get('D','')} | " f"{d.get('E','')} | {d.get('F','')} | {d.get('G','')}" ) # B sections.append("") sections.append("B. 全流程详细实施计划 — 阶段一至七（关键交付与状态）") sections.append("-" * 80) plan_cells = parse_extract_sheet(extract_text, "全流程详细实施计划") plan_rows = cells_to_rows(plan_cells) phases = [] for r in sorted(plan_rows): a = plan_rows[r].get("A", "") if a and "阶段" in a: phases.append((r, a.replace("\n", " "))) for idx, (prow, pname) in enumerate(phases): end_row = phases[idx + 1][0] if idx + 1 < len(phases) else max(plan_rows) + 1 sections.append("") sections.append(f"【{pname}】") for row in range(prow + 1, end_row): d = plan_rows.get(row, {}) sub = d.get("B", "") if sub in ("项目准备", "软件框架搭建", "系统蓝图设计", "系统开发", "系统上线", "后续对接", "交付软件", "序号"): continue task = d.get("C", "") if not task and not d.get("H"): continue seq = d.get("B", "") content = d.get("H", "") status = d.get("P", "") deliverable = d.get("J", "") parts = [] if seq: parts.append(f"{seq}. {task}" if task else f"{seq}.") else: parts.append(task) if content: parts.append(f"工作: {content}") if deliverable: parts.append(f"输出: {deliverable}") if status: parts.append(f"状态: {status}") sections.append(" " + " | ".join(p for p in parts if p)) # C sections.append("") sections.append("C. 七月份第一周 / 七月份第二周 — 任务明细") sections.append("-" * 80) WEEKLY_COLS = ("B", "C", "D", "E", "F", "G", "H", "I", "J", "K") def format_weekly_task(row_num, d): lines = [] title = d.get("B", "") or d.get("C", "") seq = d.get("A", "") head = f" 行{row_num}" if seq: head += f" / 序号{seq}" lines.append(head + ":") lines.append(f" 任务名称: {title}") if d.get("C"): lines.append(f" 工作内容: {d.get('C')}") lines.append(f" 甲方对接人: {d.get('D', '')}") lines.append(f" 乙方对接人: {d.get('E', '')}") if d.get("F") or d.get("G"): lines.append(f" 计划: {d.get('F', '')} ~ {d.get('G', '')}") lines.append(f" 状态: {d.get('H', '')}") if d.get("I"): lines.append(f" 进展说明: {d.get('I')}") if d.get("J"): lines.append(f" 备注: {d.get('J')}") k = d.get("K", "") if k and not str(k).startswith("=DISPIMG"): lines.append(f" 备注(K): {k}") return lines for sheet in ("七月份第一周", "七月份第二周"): sections.append("") sections.append(f"=== {sheet} ===") cells = parse_extract_sheet(extract_text, sheet) rows = cells_to_rows(cells) data_rows = [r for r in sorted(rows) if r >= 3 and any(rows[r].get(c) for c in WEEKLY_COLS)] for r in data_rows: sections.extend(format_weekly_task(r, rows[r])) # D sections.append("") sections.append("D. 问题清单（来源：问题清单工作表）") sections.append("-" * 80) prob_cells = parse_extract_sheet(extract_text, "问题清单") prob_rows = cells_to_rows(prob_cells) for r in sorted(prob_rows): if r < 3: continue d = prob_rows[r] if not (d.get("B") or d.get("C")): continue sections.append( f" {d.get('A', r-2)}. {d.get('B', '')} | 内容: {d.get('C', '')} | " f"对接人: {d.get('D', '')} | 后续: {d.get('E', '')} | 进度: {d.get('F', '')}" ) # E sections.append("") sections.append("E. pic/ 目录截图文件完整列表") sections.append("-" * 80) imgs = list_images(PIC_DIR) sections.append(f"共 {len(imgs)} 个图片文件:") for im in imgs: sections.append(f" {im}") # F sections.append("") sections.append("F. MES实施现状-260716.xlsx 完整内容") sections.append("-" * 80) sections.extend(extract_xlsx(STATUS_XLSX)) with open(OUT, "w", encoding="utf-8") as f: f.write("\n".join(sections) + "\n") print(f"Wrote {OUT}") print(f"Lines: {len(sections)}")