from __future__ import annotations import csv from copy import copy from pathlib import Path from openpyxl import Workbook, load_workbook from openpyxl.formatting.rule import FormulaRule from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.worksheet.table import Table, TableStyleInfo ROOT = Path(__file__).resolve().parent OUT = ROOT / "output" / "智慧食堂3SPU产品能力与项目应用体系_20260904.xlsx" FONT = "微软雅黑" SHEETS = [ ("01-功能主表", "capability-master.csv", "三条 SPU 的四端功能模块、细功能点、项目应用与证据状态", "CapabilityTable"), ("02-项目应用关系", "project-application-map.csv", "项目到功能、设备和特殊应用的长表映射；一行是一条可追溯关系", "ProjectMapTable"), ("03-设备特殊集成", "device-integration.csv", "标准设备族、第三方硬件、项目特配及升级为标准能力的门槛", "DeviceTable"), ("04-标准产品建设", "standard-product-build.csv", "跨三条 SPU 的合规、知识产权、部署、安装、质量和交付底座", "StandardBuildTable"), ("05-每周工作归类", "weekly-classification.csv", "每周先归类事实，再决定是否升级功能主表或标准产品建设", "WeeklyTable"), ("06-来源与口径", "source-index.csv", "来源、读取状态、证据等级和可使用边界", "SourceTable"), ] NAVY = "16324F" BLUE = "1E5A88" TEAL = "1F7A8C" SKY = "DCECF6" PALE = "F4F8FB" WHITE = "FFFFFF" GREEN = "DFF1E3" YELLOW = "FFF1C7" RED = "F8D7DA" GRAY = "E9EEF2" thin = Side(style="thin", color="C9D4DD") def read_csv(name: str) -> tuple[list[str], list[list[str]]]: with (ROOT / name).open("r", encoding="utf-8-sig", newline="") as f: rows = list(csv.reader(f)) if not rows or not rows[0]: raise ValueError(f"empty source: {name}") width = len(rows[0]) for idx, row in enumerate(rows[1:], start=2): if len(row) != width: raise ValueError(f"{name}:{idx} expected {width} columns, got {len(row)}") return rows[0], rows[1:] def apply_font(ws) -> None: for row in ws.iter_rows(): for cell in row: font = copy(cell.font) font.name = FONT cell.font = font def style_data_sheet(ws, title: str, subtitle: str, headers: list[str], rows: list[list[str]], table_name: str) -> None: width = len(headers) ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=width) ws.cell(1, 1, title).font = Font(name=FONT, size=18, bold=True, color=WHITE) ws.cell(1, 1).fill = PatternFill("solid", fgColor=NAVY) ws.cell(1, 1).alignment = Alignment(horizontal="left", vertical="center") ws.row_dimensions[1].height = 34 ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=width) ws.cell(2, 1, subtitle).font = Font(name=FONT, size=10, color="40566A") ws.cell(2, 1).fill = PatternFill("solid", fgColor=SKY) ws.cell(2, 1).alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[2].height = 28 ws.merge_cells(start_row=3, start_column=1, end_row=3, end_column=width) ws.cell(3, 1, "维护口径：标准定义只写一次；项目差异进入关系表；已合入代码或 release 不自动等于生产验收。") ws.cell(3, 1).font = Font(name=FONT, size=10, italic=True, color="5F6F7E") ws.cell(3, 1).fill = PatternFill("solid", fgColor=PALE) for col, value in enumerate(headers, start=1): cell = ws.cell(4, col, value) cell.font = Font(name=FONT, size=11, bold=True, color=WHITE) cell.fill = PatternFill("solid", fgColor=BLUE) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) ws.row_dimensions[4].height = 32 for r_idx, row in enumerate(rows, start=5): for c_idx, value in enumerate(row, start=1): cell = ws.cell(r_idx, c_idx, value) cell.font = Font(name=FONT, size=10, color="203040") cell.alignment = Alignment(vertical="top", wrap_text=True) cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) if r_idx % 2 == 0: cell.fill = PatternFill("solid", fgColor=PALE) ws.row_dimensions[r_idx].height = 42 if rows: ref = f"A4:{ws.cell(4 + len(rows), width).coordinate}" table = Table(displayName=table_name, ref=ref) table.tableStyleInfo = TableStyleInfo( name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=False, showColumnStripes=False, ) ws.add_table(table) ws.freeze_panes = "A5" ws.sheet_view.showGridLines = False ws.auto_filter.ref = f"A4:{ws.cell(4 + len(rows), width).coordinate}" ws.page_setup.orientation = "landscape" ws.page_setup.fitToWidth = 1 ws.sheet_properties.pageSetUpPr.fitToPage = True ws.print_title_rows = "1:4" ws.auto_filter.ref = f"A4:{ws.cell(4 + len(rows), width).coordinate}" for c_idx, header in enumerate(headers, start=1): values = [header] + [row[c_idx - 1] for row in rows] max_len = max(len(str(v)) for v in values) if any(k in header for k in ["描述", "说明", "特殊", "待补", "下一", "证据/", "来源"]): ws.column_dimensions[ws.cell(4, c_idx).column_letter].width = min(max(max_len * 0.9, 24), 52) elif any(k in header for k in ["SPU", "模块", "建设域", "设备", "项目名称"]): ws.column_dimensions[ws.cell(4, c_idx).column_letter].width = min(max(max_len * 0.9, 16), 30) else: ws.column_dimensions[ws.cell(4, c_idx).column_letter].width = min(max(max_len * 0.9, 12), 24) data_end = max(5, 4 + len(rows)) status_cols = [idx + 1 for idx, h in enumerate(headers) if any(k in h for k in ["状态", "成熟度", "判断", "结论"])] for col in status_cols: letter = ws.cell(4, col).column_letter rng = f"{letter}5:{letter}{data_end}" ws.conditional_formatting.add(rng, FormulaRule(formula=[f'OR(ISNUMBER(SEARCH("成熟",{letter}5)),ISNUMBER(SEARCH("完成",{letter}5)),ISNUMBER(SEARCH("上线",{letter}5)))'], fill=PatternFill("solid", fgColor=GREEN))) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'OR(ISNUMBER(SEARCH("待",{letter}5)),ISNUMBER(SEARCH("中",{letter}5)),ISNUMBER(SEARCH("候选",{letter}5)))'], fill=PatternFill("solid", fgColor=YELLOW))) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'OR(ISNUMBER(SEARCH("阻断",{letter}5)),ISNUMBER(SEARCH("BLOCKED",{letter}5)))'], fill=PatternFill("solid", fgColor=RED))) def build_cover(wb: Workbook, counts: dict[str, int]) -> None: ws = wb.active ws.title = "00-使用说明" ws.sheet_view.showGridLines = False ws.merge_cells("A1:H2") ws["A1"] = "智慧食堂 3 SPU 产品能力与项目应用体系" ws["A1"].font = Font(name=FONT, size=22, bold=True, color=WHITE) ws["A1"].fill = PatternFill("solid", fgColor=NAVY) ws["A1"].alignment = Alignment(horizontal="left", vertical="center") for row in ws["A1:H2"]: for cell in row: cell.fill = PatternFill("solid", fgColor=NAVY) ws.merge_cells("A3:H3") ws["A3"] = "统一结构：SPU → PC/移动/大屏/设备 → 功能模块 → 功能点 → 项目应用 → 特殊应用 → 每周变化" ws["A3"].font = Font(name=FONT, size=11, color="40566A") ws["A3"].fill = PatternFill("solid", fgColor=SKY) cards = [ ("A5", "功能点", counts["01-功能主表"]), ("C5", "项目关系", counts["02-项目应用关系"]), ("E5", "设备/集成", counts["03-设备特殊集成"]), ("G5", "标准建设项", counts["04-标准产品建设"]), ] for anchor, label, value in cards: col = ws[anchor].column ws.merge_cells(start_row=5, start_column=col, end_row=5, end_column=col + 1) ws.merge_cells(start_row=6, start_column=col, end_row=7, end_column=col + 1) ws.cell(5, col, label).font = Font(name=FONT, size=10, bold=True, color=WHITE) ws.cell(5, col).fill = PatternFill("solid", fgColor=TEAL) ws.cell(5, col).alignment = Alignment(horizontal="center") ws.cell(6, col, value).font = Font(name=FONT, size=24, bold=True, color=NAVY) ws.cell(6, col).alignment = Alignment(horizontal="center", vertical="center") for row in ws.iter_rows(min_row=5, max_row=7, min_col=col, max_col=col + 1): for cell in row: cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) ws["A9"] = "三条 SPU" ws["A9"].font = Font(name=FONT, size=14, bold=True, color=NAVY) spu_rows = [ ("前厅营养结算与健康管理", "完成支付、膳食采集、营养分析和健康反馈", "餐前准备 → 餐中结算 → 餐后反馈 → 运营复盘"), ("智慧食安监管", "把后厨风险变成预警、处置、整改、追溯闭环", "人员准入 → 加工监管 → 留样检测 → 预警整改 → 公示追溯"), ("智慧进销存与后厨运营", "打通菜单、采购、验收、库存、成本与供应商", "排菜计划 → 需求采购 → 验收入库 → 出库盘点 → 成本复盘"), ] headers = ["SPU", "核心问题", "业务闭环"] for idx, h in enumerate(headers, start=1): c = ws.cell(10, idx, h) c.font = Font(name=FONT, bold=True, color=WHITE) c.fill = PatternFill("solid", fgColor=BLUE) c.alignment = Alignment(horizontal="center") for r, row in enumerate(spu_rows, start=11): for c, value in enumerate(row, start=1): ws.cell(r, c, value) ws.cell(r, c).alignment = Alignment(wrap_text=True, vertical="top") ws.cell(r, c).border = Border(left=thin, right=thin, top=thin, bottom=thin) if r % 2 == 1: ws.cell(r, c).fill = PatternFill("solid", fgColor=PALE) ws["A15"] = "怎么维护" ws["A15"].font = Font(name=FONT, size=14, bold=True, color=NAVY) rules = [ "1. 每周先在“05-每周工作归类”登记新增、修复、上线、集成、合规和部署事项。", "2. 已形成复用对象、接口契约、自动化测试和验收证据后，项目特配才升级为标准能力。", "3. 业务功能只放四端；等保、软著、私有化部署、一键安装等放“04-标准产品建设”。", "4. A=用户/现场/生产确认；B=代码或受控资料；C=方案/计划；D=待补证。", "5. 三全支付宝付款码：用户确认本周上线，release 合并已核验；流水线与现场签字仍待补。", ] for r, value in enumerate(rules, start=16): ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=8) ws.cell(r, 1, value) ws.cell(r, 1).alignment = Alignment(wrap_text=True, vertical="center") ws.cell(r, 1).fill = PatternFill("solid", fgColor=PALE if r % 2 == 0 else WHITE) ws.row_dimensions[r].height = 25 ws["A23"] = "工作表" ws["A23"].font = Font(name=FONT, size=14, bold=True, color=NAVY) for i, (name, _, subtitle, _) in enumerate(SHEETS, start=24): ws.cell(i, 1, name) ws.cell(i, 1).font = Font(name=FONT, bold=True, color=BLUE) ws.cell(i, 2, subtitle) ws.merge_cells(start_row=i, start_column=2, end_row=i, end_column=8) ws.cell(i, 2).alignment = Alignment(wrap_text=True) widths = {"A": 26, "B": 24, "C": 18, "D": 18, "E": 20, "F": 20, "G": 18, "H": 18} for col, value in widths.items(): ws.column_dimensions[col].width = value ws.freeze_panes = "A4" apply_font(ws) def main() -> None: sources = {} for sheet, filename, subtitle, table_name in SHEETS: headers, rows = read_csv(filename) sources[sheet] = (headers, rows, subtitle, table_name) OUT.parent.mkdir(parents=True, exist_ok=True) wb = Workbook() wb.properties.title = "智慧食堂3SPU产品能力与项目应用体系" wb.properties.subject = "三SPU四端功能、项目应用、特殊设备集成与标准产品建设" wb.properties.creator = "CPT 产品开发部" build_cover(wb, {name: len(data[1]) for name, data in sources.items()}) for sheet, (headers, rows, subtitle, table_name) in sources.items(): ws = wb.create_sheet(sheet) style_data_sheet(ws, sheet, subtitle, headers, rows, table_name) apply_font(ws) wb.active = 0 wb.save(OUT) check = load_workbook(OUT, data_only=False) expected = ["00-使用说明"] + [s[0] for s in SHEETS] if check.sheetnames != expected: raise ValueError(f"sheet mismatch: {check.sheetnames}") for name, (_, rows, _, _) in sources.items(): if check[name].max_row != len(rows) + 4: raise ValueError(f"row mismatch: {name}") print(OUT) if __name__ == "__main__": main()