# 生产系统增补报价单生成脚本（三工段扩展，单价 1200 元/人天） import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter RATE = 1200 OUT = "output/生产系统/生产系统增补报价单.xlsx" FONT = "Microsoft YaHei" DARK = "1F4E79" LIGHT = "DDEBF7" GREEN = "C6E0B4" thin = Side(style="thin", color="BFBFBF") border = Border(left=thin, right=thin, top=thin, bottom=thin) # 明细数据：(类别, 工作项, 人天) items = [ ("报表系统", "岗位操作记录报表（净化/水处理/二洗，替代手工记录）", 6), ("报表系统", "多工段数据统计分析（班次/日/月汇总、工段对比）", 4), ("报表系统", "AI操作记录报表（介入次数、调整参数、采纳情况、调整前后指标对比）", 5), ("报表系统", "现有日报表、趋势、合格率分析扩展到水处理、二洗工段", 4), ("批次管理", "新增工段采集点位接入与历史数据补录（水处理、二洗）", 3), ("批次管理", "跨工段批次关联（净化→水处理→二洗物料流转追溯）", 5), ("批次管理", "水处理工段批次配置与过程数据记录（复用框架，仅算差异）", 4), ("批次管理", "二洗工段批次配置与过程数据记录（复用框架，仅算差异）", 4), ("批次管理", "各工段批次统计（产量、合格率、生产周期）", 4), ("批次管理", "原料、设备、人员基础资料管理（复用）", 0), ("AI模型（三工段）", "水处理工段数据清洗与特征提取", 4), ("AI模型（三工段）", "水处理工段指标预测模型训练与调优", 7), ("AI模型（三工段）", "水处理工段工艺-指标关联模型与反向寻优适配", 5), ("AI模型（三工段）", "水处理工段DCS控制接入（点位、安全约束、方案生成）", 4), ("AI模型（三工段）", "水处理工段训练/推理数据集构建", 2), ("AI模型（三工段）", "二洗工段数据清洗与特征提取", 4), ("AI模型（三工段）", "二洗工段指标预测模型训练与调优", 7), ("AI模型（三工段）", "二洗工段工艺-指标关联模型与反向寻优适配", 5), ("AI模型（三工段）", "二洗工段DCS控制接入（点位、安全约束、方案生成）", 4), ("AI模型（三工段）", "二洗工段训练/推理数据集构建", 2), ("AI模型（三工段）", "成本/能耗指标纳入优化目标（多目标寻优）", 5), ("AI模型（三工段）", "三工段模型持续训练与优化", 3), ("测试与交付", "新增工段联调与UAT", 8), ] # 汇总行：(名称, 范围说明)，区间由类别在明细中的行号推出 summary_rows = [ ("报表系统", "岗位操作记录、多工段统计分析、AI操作记录报表"), ("批次管理", "跨工段批次关联、新增工段批次记录与统计"), ("AI模型（三工段）", "水处理、二洗工段模型训练调优、反向寻优、DCS接入，成本目标优化"), ("测试与交付", "新增工段联调与UAT"), ] wb = openpyxl.Workbook() # ---------- 报价明细 ---------- ws = wb.active ws.title = "报价明细" ws.merge_cells("A1:E1") c = ws["A1"] c.value = "生产系统增补报价单" c.font = Font(name=FONT, size=15, bold=True, color="FFFFFF") c.fill = PatternFill("solid", fgColor=DARK) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.row_dimensions[1].height = 34 headers = ["序号", "类别", "功能模块 / 工作项", "人天", "金额（元）"] for i, h in enumerate(headers, 1): c = ws.cell(row=3, column=i, value=h) c.font = Font(name=FONT, size=11, bold=True, color="FFFFFF") c.fill = PatternFill("solid", fgColor=DARK) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) c.border = border ws.row_dimensions[3].height = 24 cat_ranges = {} # 类别 -> (起始行, 结束行) row = 4 for idx, (cat, name, days) in enumerate(items, 1): ws.cell(row=row, column=1, value=idx) ws.cell(row=row, column=2, value=cat) ws.cell(row=row, column=3, value=name) ws.cell(row=row, column=4, value=days) ws.cell(row=row, column=5, value=f"=D{row}*{RATE}") for col in range(1, 6): c = ws.cell(row=row, column=col) c.font = Font(name=FONT, size=11) c.border = border if col == 3: c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) elif col in (4, 5): c.alignment = Alignment(horizontal="right", vertical="center", wrap_text=True) else: c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) if cat not in cat_ranges: cat_ranges[cat] = [row, row] cat_ranges[cat][1] = row row += 1 total_row = row # 类别列合并 + 填色 for cat, (r1, r2) in cat_ranges.items(): if r2 > r1: ws.merge_cells(start_row=r1, start_column=2, end_row=r2, end_column=2) a = ws.cell(row=r1, column=2) a.fill = PatternFill("solid", fgColor=LIGHT) for r in range(r1, r2 + 1): ws.cell(row=r, column=2).fill = PatternFill("solid", fgColor=LIGHT) ws.merge_cells(start_row=total_row, start_column=1, end_row=total_row, end_column=3) ws.cell(row=total_row, column=1, value="合计") ws.cell(row=total_row, column=4, value=f"=SUM(D4:D{total_row-1})") ws.cell(row=total_row, column=5, value=f"=SUM(E4:E{total_row-1})") for col in range(1, 6): c = ws.cell(row=total_row, column=col) c.font = Font(name=FONT, size=11, bold=True) c.fill = PatternFill("solid", fgColor=GREEN) c.border = border c.alignment = Alignment( horizontal="right" if col in (4, 5) else "center", vertical="center" ) for col, w in zip("ABCDE", [6, 16, 56, 9, 15]): ws.column_dimensions[col].width = w # ---------- 报价汇总 ---------- ws2 = wb.create_sheet("报价汇总", 0) ws2.merge_cells("A1:E1") c = ws2["A1"] c.value = "生产系统增补报价汇总" c.font = Font(name=FONT, size=15, bold=True, color="FFFFFF") c.fill = PatternFill("solid", fgColor=DARK) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws2.row_dimensions[1].height = 34 headers2 = ["序号", "功能 / 模块", "范围说明", "人天", "金额（元）"] for i, h in enumerate(headers2, 1): c = ws2.cell(row=3, column=i, value=h) c.font = Font(name=FONT, size=11, bold=True, color="FFFFFF") c.fill = PatternFill("solid", fgColor=DARK) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) c.border = border ws2.row_dimensions[3].height = 24 r = 4 for i, (name, scope) in enumerate(summary_rows, 1): r1, r2 = cat_ranges[name] ws2.cell(row=r, column=1, value=i) ws2.cell(row=r, column=2, value=name) ws2.cell(row=r, column=3, value=scope) ws2.cell(row=r, column=4, value=f"=SUM(报价明细!D{r1}:D{r2})") ws2.cell(row=r, column=5, value=f"=SUM(报价明细!E{r1}:E{r2})") for col in range(1, 6): c = ws2.cell(row=r, column=col) c.font = Font(name=FONT, size=11) c.border = border if col == 3: c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) elif col in (4, 5): c.alignment = Alignment(horizontal="right", vertical="center", wrap_text=True) else: c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) r += 1 sum_total = r ws2.merge_cells(start_row=sum_total, start_column=1, end_row=sum_total, end_column=3) ws2.cell(row=sum_total, column=1, value="合计") ws2.cell(row=sum_total, column=4, value=f"=SUM(D4:D{sum_total-1})") ws2.cell(row=sum_total, column=5, value=f"=SUM(E4:E{sum_total-1})") for col in range(1, 6): c = ws2.cell(row=sum_total, column=col) c.font = Font(name=FONT, size=11, bold=True) c.fill = PatternFill("solid", fgColor=GREEN) c.border = border c.alignment = Alignment( horizontal="right" if col in (4, 5) else "center", vertical="center" ) for col, w in zip("ABCDE", [6, 18, 50, 10, 16]): ws2.column_dimensions[col].width = w wb.save(OUT) print("saved:", OUT, "| 明细行数:", len(items), "| 合计人天:", sum(d for _, _, d in items))