198 lines
8.8 KiB
Python
198 lines
8.8 KiB
Python
# 生产系统开发报价单生成脚本（单价 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 = [
|
||
("公共底座", "项目初始化+模块接入+前端脚手架+CI/CD", 3),
|
||
("公共底座", "登录+用户权限管理", 3),
|
||
("公共底座", "文件服务+Excel/报表导出", 3),
|
||
("公共底座", "OPC/Modbus数据采集服务（协议接入+点位配置+断线重连+采集入库）", 8),
|
||
("公共底座", "DCS历史站历史数据接入", 3),
|
||
("报表系统", "自定义生产日报表（模板配置+自动生成）", 6),
|
||
("报表系统", "生产过程数据查询、统计", 4),
|
||
("报表系统", "多批次数据对比分析", 4),
|
||
("报表系统", "趋势、异常、合格率分析", 5),
|
||
("批次管理", "Batch批次管理（定义/启停/列表查询）", 6),
|
||
("批次管理", "每釜过程数据记录（温度/压力/流量/液位曲线）", 6),
|
||
("批次管理", "原料、设备、人员、生产时间基础资料管理", 5),
|
||
("批次管理", "阶段性及最终物料指标录入与批次关联", 4),
|
||
("批次管理", "批次数据查询与追溯", 4),
|
||
("批次管理", "AI训练/推理数据集构建（抽取/清洗/版本管理）", 5),
|
||
("AI指标预测", "生产数据清洗与特征提取", 6),
|
||
("AI指标预测", "纯度预测模型建立与训练（单设备基准模型）", 8),
|
||
("AI指标预测", "多设备模型复制框架（按设备独立训练与管理）", 4),
|
||
("AI指标预测", "实时预测服务与结果展示", 5),
|
||
("AI指标预测", "预测值与实际检测值对比", 3),
|
||
("AI指标预测", "模型持续训练与优化", 5),
|
||
("工艺反向优化", "生产工艺-物料指标关联模型", 5),
|
||
("工艺反向优化", "根据目标指标反向计算工艺参数（寻优算法）", 8),
|
||
("工艺反向优化", "原料配比优化（原液添加量建议）", 5),
|
||
("工艺反向优化", "最佳工艺方案生成与展示", 4),
|
||
("工艺反向优化", "AI工艺调整建议", 3),
|
||
("工艺反向优化", "实际执行结果记录与模型迭代", 3),
|
||
("DCS智能控制", "AI模型/Agent服务接入与调度", 6),
|
||
("DCS智能控制", "DCS实时数据分析与调整方案生成", 5),
|
||
("DCS智能控制", "参数上下限与安全约束控制", 4),
|
||
("DCS智能控制", "半自动控制（人工确认后经OPC下发指令）", 6),
|
||
("DCS智能控制", "控制执行记录与追溯", 2),
|
||
("测试与交付", "联调+自测+Bug修复", 14),
|
||
("测试与交付", "系统测试/UAT", 9),
|
||
("测试与交付", "部署上线", 4),
|
||
("项目管理", "项目管理+需求+文档+沟通", 16),
|
||
]
|
||
|
||
# 汇总行：(名称, 范围说明)，区间由类别在明细中的行号推出
|
||
summary_rows = [
|
||
("公共底座", "脚手架、权限、OPC/Modbus采集、DCS历史数据接入"),
|
||
("报表系统", "生产日报表、查询统计、对比与合格率分析"),
|
||
("批次管理", "批次与过程数据记录、指标关联、追溯、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, 14, 52, 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, 16, 46, 10, 16]):
|
||
ws2.column_dimensions[col].width = w
|
||
|
||
wb.save(OUT)
|
||
print("saved:", OUT, "| 明细行数:", len(items), "| 合计人天:", sum(d for _, _, d in items))
|