XuHengJie
aa7d3761c8
重大变更： - 输入改为用户提供的工程量结算前后对比表（鸡噜工程量结算前后对比表.xlsx） - 新增输出：水浇地15～25°区域分部工程工程量汇总表 → 自动解析15个田块（T3~T72）的工程量数据 → 合计行自动汇总 - 支持批量生成全部田块的独立竣工结算资料（报验单+统计表+3份签证） - generate_all_fields 参数可控制是否全部生成 - 移除旧的单项工程结算表输入模式（v1）
674 lines
30 KiB
Python
674 lines
30 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
土地平整工程竣工结算资料（部分）生成 v2.0
|
||
从用户提供的工程量结算前后对比表中提取水浇地15～25°区域各田块数据，
|
||
生成分部工程工程量汇总表及各田块竣工结算资料套表。
|
||
"""
|
||
|
||
import sys
|
||
import json
|
||
import os
|
||
import re
|
||
|
||
try:
|
||
import openpyxl
|
||
from openpyxl.styles import (
|
||
Font, Border, Side, Alignment, PatternFill
|
||
)
|
||
from openpyxl.utils import get_column_letter
|
||
except ImportError:
|
||
print(json.dumps({"error": "openpyxl 未安装"}))
|
||
sys.exit(1)
|
||
|
||
try:
|
||
import xlrd
|
||
HAS_XLRD = True
|
||
except ImportError:
|
||
HAS_XLRD = False
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Style definitions
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
THIN = Border(left=Side('thin'), right=Side('thin'),
|
||
top=Side('thin'), bottom=Side('thin'))
|
||
TITLE_FONT = Font(name='宋体', size=16, bold=True)
|
||
HEADER_FONT = Font(name='宋体', size=10, bold=True)
|
||
NORMAL = Font(name='宋体', size=10)
|
||
BOLD = Font(name='宋体', size=10, bold=True)
|
||
SMALL = Font(name='宋体', size=9)
|
||
CA = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
||
LA = Alignment(horizontal='left', vertical='center', wrap_text=True)
|
||
HDR_FILL = PatternFill(start_color='D9E2F3', end_color='D9E2F3', fill_type='solid')
|
||
|
||
|
||
def ac(ws, r, c, v, font=None, align=None, border=None, fill=None, nf=None):
|
||
"""Set cell value + style."""
|
||
cell = ws.cell(row=r, column=c, value=v)
|
||
if font: cell.font = font
|
||
if align: cell.alignment = align
|
||
if border: cell.border = border
|
||
if fill: cell.fill = fill
|
||
if nf: cell.number_format = nf
|
||
return cell
|
||
|
||
|
||
def ms(ws, r1, r2, c1, c2, v, font=None, align=None, border=None, fill=None):
|
||
"""Merge + set."""
|
||
ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2)
|
||
return ac(ws, r1, c1, v, font=font, align=align, border=border, fill=fill)
|
||
|
||
|
||
def cw(ws, widths):
|
||
for ci, w in widths.items():
|
||
ws.column_dimensions[get_column_letter(ci)].width = w
|
||
|
||
|
||
def rh(ws, heights):
|
||
for ri, h in heights.items():
|
||
ws.row_dimensions[ri].height = h
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Input parser — 鸡噜工程量结算前后对比表.xlsx
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def parse_input(input_path):
|
||
"""
|
||
Read the 鸡噜工程量结算前后对比表.xlsx (sheet '土地平整工程').
|
||
Extract:
|
||
- project_name
|
||
- fields: [{'name':'T3', 'area':2.5638, '清理表土':7662.75, '修平田地':4807.15, '田埂修筑':86.66}, ...]
|
||
Only parses section (一) 水浇地15～25°区域.
|
||
"""
|
||
result = {'project_name': '', 'fields': []}
|
||
|
||
ext = os.path.splitext(input_path)[1].lower()
|
||
if ext == '.xls':
|
||
if not HAS_XLRD:
|
||
raise RuntimeError("需安装 xlrd 以读取 .xls 文件")
|
||
wb = xlrd.open_workbook(input_path)
|
||
ws = wb.sheet_by_index(0) # assumes first sheet
|
||
elif ext == '.xlsx':
|
||
wb = openpyxl.load_workbook(input_path, data_only=True)
|
||
# Find the right sheet
|
||
sheet_name = None
|
||
for sn in ('土地平整工程', wb.sheetnames[0]):
|
||
if sn in wb.sheetnames:
|
||
sheet_name = sn
|
||
break
|
||
if not sheet_name:
|
||
sheet_name = wb.sheetnames[0]
|
||
ws = wb[sheet_name]
|
||
else:
|
||
raise ValueError(f"不支持的文件格式: {ext}")
|
||
|
||
# Extract project name from row 2
|
||
if ext == '.xls':
|
||
raw = str(ws.cell(1, 0).value or '') # cell A2: 项目名称：xxx
|
||
else:
|
||
raw = str(ws.cell(2, 1).value or '') # cell A2: 项目名称：xxx
|
||
m = re.search(r'项目名称[：:]\s*(.+)', raw)
|
||
if m:
|
||
result['project_name'] = m.group(1).strip()
|
||
else:
|
||
result['project_name'] = raw
|
||
|
||
# Now parse each field — find section (一) 水浇地15～25°区域
|
||
# Then read subsequent rows: field blocks (number + sub-rows for 3 processes)
|
||
# Data structure in the spreadsheet:
|
||
# 编号 | 项目名称 | 单位 | 工程量(原) | 单价 | 合价 | ...
|
||
# (一) | 水浇地15～25°区域 | hm² | 40.2677 | ...
|
||
# 1 | T3 | hm² | 2.5638 | ...
|
||
# (1) | 清理表土 | m³ | 7662.75 | ...
|
||
# (2) | 修平田地 | m³ | 4807.15 | ...
|
||
# (3) | 田埂修筑 | m³ | 86.66 | ...
|
||
# 2 | T4 | hm² | ...
|
||
# ...
|
||
|
||
def cell_val(r, c):
|
||
if ext == '.xls':
|
||
v = ws.cell(r, c).value
|
||
return str(v) if v is not None else ''
|
||
else:
|
||
v = ws.cell(r, c).value
|
||
return str(v) if v is not None else ''
|
||
|
||
def cell_num(r, c, default=0.0):
|
||
if ext == '.xls':
|
||
v = ws.cell(r, c).value
|
||
else:
|
||
v = ws.cell(r, c).value
|
||
try:
|
||
return float(v)
|
||
except (ValueError, TypeError):
|
||
return default
|
||
|
||
max_row = ws.nrows if ext == '.xls' else ws.max_row
|
||
|
||
# Find start: (一) row
|
||
start_row = None
|
||
for r in range(1, max_row + 1 if ext == '.xls' else max_row + 1):
|
||
rid = cell_val(r, 0 if ext == '.xls' else 1).strip()
|
||
name = cell_val(r, 1 if ext == '.xls' else 2).strip()
|
||
if '水浇地' in name and '15' in name:
|
||
start_row = r
|
||
break
|
||
|
||
if start_row is None:
|
||
raise ValueError("未找到'水浇地15～25°区域'段落")
|
||
|
||
# Parse fields: look for numeric IDs followed by (1)/(2)/(3) process rows
|
||
current_field = None
|
||
for r in range(start_row + 1 if ext == '.xls' else start_row + 1, max_row + 1):
|
||
rid = cell_val(r, 0 if ext == '.xls' else 1).strip()
|
||
name = cell_val(r, 1 if ext == '.xls' else 2).strip()
|
||
unit = cell_val(r, 2 if ext == '.xls' else 3).strip()
|
||
|
||
# Stop at next section (二)
|
||
if '（二）' in rid or ('水田' in name and '6' in name):
|
||
break
|
||
# Skip section header row itself
|
||
if '（一）' in rid:
|
||
continue
|
||
|
||
# It's a process sub-row
|
||
if rid in ('(1)', '(2)', '(3)') and current_field:
|
||
proc_name = name
|
||
qty = cell_num(r, 3 if ext == '.xls' else 4)
|
||
if proc_name in ('清理表土', '修平田地', '田埂修筑'):
|
||
current_field[proc_name] = qty
|
||
continue
|
||
|
||
# It's a field row (number + field name)
|
||
try:
|
||
int(rid) # numeric id = field row
|
||
except ValueError:
|
||
# Not a field row, skip
|
||
continue
|
||
|
||
area = cell_num(r, 3 if ext == '.xls' else 4)
|
||
field_name_raw = name.strip()
|
||
# Extract the core field name (e.g., "T3" from "T3" or "T3田块")
|
||
field_name = field_name_raw
|
||
# Save current field
|
||
current_field = {
|
||
'name': field_name,
|
||
'area': area,
|
||
'field_display': f'{field_name}田块',
|
||
'清理表土': 0,
|
||
'修平田地': 0,
|
||
'田埂修筑': 0,
|
||
}
|
||
result['fields'].append(current_field)
|
||
|
||
return result
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Output: 分部工程工程量汇总表
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def generate_subitem_summary(wb, data, params):
|
||
"""
|
||
Generate the 分部工程工程量汇总表 (3.3.3-01.01.01.000)
|
||
filled with all fields' data.
|
||
"""
|
||
proj = params.get('project_name') or data.get('project_name', '')
|
||
contractor = params.get('contractor', '')
|
||
contract_section = params.get('contract_section', '')
|
||
fields = data.get('fields', [])
|
||
|
||
ws = wb.active
|
||
ws.title = '分部工程工程量汇总表'
|
||
|
||
cw(ws, {1: 12, 2: 10, 3: 14, 4: 14, 5: 14,
|
||
6: 8, 7: 8, 8: 8, 9: 8, 10: 8})
|
||
rh(ws, {1: 36, 2: 24, 3: 24, 4: 30, 5: 24,
|
||
6: 22, 7: 22, 8: 22, 9: 22, 10: 22,
|
||
11: 22, 12: 22, 13: 22, 14: 22, 15: 22,
|
||
16: 22, 17: 22, 18: 22, 19: 22, 20: 22,
|
||
21: 22, 22: 22, 23: 22, 24: 24, 25: 24, 26: 24})
|
||
|
||
# Col A merges for data section (rows 6-21 or so)
|
||
n_fields = len(fields)
|
||
data_start = 6 # first data row
|
||
data_end = data_start + n_fields - 1 # last data row
|
||
total_row = data_end + 1 # 合计 row
|
||
sig_start = total_row + 1 # signature rows start
|
||
|
||
# Row 1: Title
|
||
ms(ws, 1, 1, 1, 10,
|
||
'水浇地15～25°区域分部工程工程量汇总表',
|
||
font=TITLE_FONT, align=CA)
|
||
|
||
# Row 2: 项目名称
|
||
ms(ws, 2, 2, 1, 1, '项目名称', font=BOLD, align=CA)
|
||
ms(ws, 2, 2, 2, 10, proj, font=NORMAL, align=LA)
|
||
|
||
# Row 3: 承包单位
|
||
ms(ws, 3, 3, 1, 1, '承包单位名称', font=BOLD, align=CA)
|
||
ms(ws, 3, 3, 2, 8, contractor, font=NORMAL, align=LA)
|
||
ms(ws, 3, 3, 9, 10, f'合同段 {contract_section}', font=NORMAL, align=LA)
|
||
|
||
# Row 4: Table header line 1
|
||
ms(ws, 4, 5, 1, 1, '单元工程', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 5, 2, 2, '工 序 工 程 量', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
# Row 4 sub-headers
|
||
ms(ws, 4, 4, 3, 3, '工程量', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 4, 4, 4, '清理表土', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 4, 5, 5, '修平田地', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 4, 6, 6, '田埂修筑', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
# Fill remaining header cells
|
||
for c in range(7, 11):
|
||
ac(ws, 4, c, '', border=THIN, fill=HDR_FILL)
|
||
for c in range(3, 11):
|
||
ac(ws, 5, c, '', border=THIN, fill=HDR_FILL)
|
||
# Row 5 sub-header units
|
||
ac(ws, 5, 3, 'hm²', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ac(ws, 5, 4, 'm³', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ac(ws, 5, 5, 'm³', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ac(ws, 5, 6, 'm³', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
|
||
# Data rows
|
||
total_area = 0
|
||
total_cl = 0
|
||
total_xp = 0
|
||
total_tg = 0
|
||
|
||
for i, f in enumerate(fields):
|
||
r = data_start + i
|
||
fd = f.get('field_display', f['name'])
|
||
ms(ws, r, r, 1, 1, fd, font=NORMAL, align=CA, border=THIN)
|
||
ms(ws, r, r, 2, 2, '', font=NORMAL, border=THIN) # empty col B
|
||
area = f.get('area', 0)
|
||
cl = f.get('清理表土', 0)
|
||
xp = f.get('修平田地', 0)
|
||
tg = f.get('田埂修筑', 0)
|
||
ac(ws, r, 3, area if area else '', font=NORMAL, align=CA, border=THIN,
|
||
nf='#,##0.0000' if area else None)
|
||
ac(ws, r, 4, cl if cl else '', font=NORMAL, align=CA, border=THIN,
|
||
nf='#,##0.00' if cl else None)
|
||
ac(ws, r, 5, xp if xp else '', font=NORMAL, align=CA, border=THIN,
|
||
nf='#,##0.00' if xp else None)
|
||
ac(ws, r, 6, tg if tg else '', font=NORMAL, align=CA, border=THIN,
|
||
nf='#,##0.00' if tg else None)
|
||
for c in range(7, 11):
|
||
ac(ws, r, c, '', font=NORMAL, border=THIN)
|
||
total_area += area
|
||
total_cl += cl
|
||
total_xp += xp
|
||
total_tg += tg
|
||
|
||
# 合计 row
|
||
ms(ws, total_row, total_row, 1, 2, '合 计', font=BOLD, align=CA, border=THIN)
|
||
ac(ws, total_row, 3, total_area if total_area else '', font=BOLD, align=CA,
|
||
border=THIN, nf='#,##0.0000' if total_area else None)
|
||
ac(ws, total_row, 4, total_cl if total_cl else '', font=BOLD, align=CA,
|
||
border=THIN, nf='#,##0.00' if total_cl else None)
|
||
ac(ws, total_row, 5, total_xp if total_xp else '', font=BOLD, align=CA,
|
||
border=THIN, nf='#,##0.00' if total_xp else None)
|
||
ac(ws, total_row, 6, total_tg if total_tg else '', font=BOLD, align=CA,
|
||
border=THIN, nf='#,##0.00' if total_tg else None)
|
||
for c in range(7, 11):
|
||
ac(ws, total_row, c, '', font=BOLD, border=THIN)
|
||
|
||
# Signature rows
|
||
ms(ws, sig_start, sig_start, 1, 3,
|
||
'承包单位（盖章）', font=NORMAL, align=LA)
|
||
ms(ws, sig_start, sig_start, 4, 7,
|
||
'监理机构（盖章）', font=NORMAL, align=LA)
|
||
ms(ws, sig_start, sig_start, 8, 10,
|
||
'项目承担单位（盖章）', font=NORMAL, align=LA)
|
||
|
||
ms(ws, sig_start+1, sig_start+1, 1, 2,
|
||
'技术负责人：', font=NORMAL, align=LA)
|
||
ms(ws, sig_start+1, sig_start+1, 4, 5,
|
||
'监理工程师：', font=NORMAL, align=LA)
|
||
ms(ws, sig_start+1, sig_start+1, 8, 9,
|
||
'现场负责人：', font=NORMAL, align=LA)
|
||
|
||
ms(ws, sig_start+2, sig_start+2, 1, 2,
|
||
'日 期：', font=NORMAL, align=LA)
|
||
ms(ws, sig_start+2, sig_start+2, 4, 5,
|
||
'日 期：', font=NORMAL, align=LA)
|
||
ms(ws, sig_start+2, sig_start+2, 8, 9,
|
||
'日 期：', font=NORMAL, align=LA)
|
||
|
||
return fields # return for downstream use
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Output: 计量报验单 (per field)
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def generate_report(wb, data, params, field_info):
|
||
field_display = field_info.get('field_display', 'T3田块')
|
||
field_name = field_info.get('name', 'T3')
|
||
proj = params.get('project_name') or data.get('project_name', '')
|
||
contractor = params.get('contractor', '')
|
||
cs = params.get('contract_section', '')
|
||
supervision = params.get('supervision_company', '曲靖南天监理有限公司')
|
||
|
||
ws = wb.active
|
||
ws.title = '计量报验单'
|
||
cw(ws, {1: 16, 2: 16, 3: 16, 4: 14, 5: 14, 6: 12, 7: 12, 8: 10, 9: 10})
|
||
rh(ws, {1: 36, 2: 24, 3: 24, 4: 30, 5: 24, 6: 24, 7: 24, 8: 24,
|
||
9: 24, 10: 24, 11: 24, 12: 30, 13: 24, 14: 24, 15: 24})
|
||
|
||
ms(ws, 1, 1, 1, 9, f'{field_display}单元工程计量报验单', font=TITLE_FONT, align=CA)
|
||
ms(ws, 2, 2, 1, 2, '项目名称', font=BOLD, align=CA)
|
||
ms(ws, 2, 2, 3, 7, proj, font=NORMAL, align=LA)
|
||
ms(ws, 2, 2, 8, 9, f'合同段 {cs}', font=NORMAL, align=LA)
|
||
ms(ws, 3, 3, 1, 1, '致：', font=BOLD, align=CA)
|
||
ms(ws, 3, 3, 2, 9, supervision, font=NORMAL, align=LA)
|
||
ms(ws, 4, 4, 1, 4, '我方按承包合同约定，已完成了', font=NORMAL, align=LA)
|
||
ms(ws, 4, 4, 5, 7, field_display, font=NORMAL, align=CA)
|
||
ms(ws, 4, 4, 8, 9, '单元工程（工序）的施工，其', font=NORMAL, align=LA)
|
||
ms(ws, 5, 5, 1, 9, '工程质量已经检查合格，并对工程量进行了计量测量，现提交计量结果，请予以核准。', font=NORMAL, align=LA)
|
||
ms(ws, 6, 6, 1, 9, '附件：', font=BOLD, align=LA)
|
||
|
||
attachs = [('1、工序工程计量统计表', '1'), ('2、工程量测量资料', ''),
|
||
('3、工序工程计量签证表', '3')]
|
||
for i, (desc, pages) in enumerate(attachs):
|
||
r = 7 + i
|
||
ms(ws, r, r, 1, 1, '', font=NORMAL)
|
||
ms(ws, r, r, 2, 6, desc, font=NORMAL, align=LA)
|
||
if pages:
|
||
ac(ws, r, 8, float(pages), font=NORMAL, align=CA)
|
||
ac(ws, r, 9, '页', font=NORMAL, align=CA)
|
||
|
||
ms(ws, 10, 10, 1, 3, f'承包单位（盖章）： {contractor}', font=NORMAL, align=LA)
|
||
ms(ws, 10, 10, 4, 9, '', font=NORMAL)
|
||
ms(ws, 11, 11, 1, 3, '项目经理（签字）：', font=NORMAL, align=LA)
|
||
ms(ws, 11, 11, 4, 9, '', font=NORMAL)
|
||
ms(ws, 12, 12, 1, 3, '日 期：', font=NORMAL, align=LA)
|
||
ms(ws, 12, 12, 4, 9, '', font=NORMAL)
|
||
ms(ws, 13, 13, 1, 5, '监理机构审查意见：同意。', font=NORMAL, align=LA)
|
||
ms(ws, 13, 13, 6, 9, '项目承担单位审查意见：同意。', font=NORMAL, align=LA)
|
||
ms(ws, 14, 14, 1, 3, '监理工程师（签字）：', font=NORMAL, align=LA)
|
||
ms(ws, 14, 14, 6, 7, '现场负责人（签字）：', font=NORMAL, align=LA)
|
||
ms(ws, 15, 15, 1, 3, '日 期：', font=NORMAL, align=LA)
|
||
ms(ws, 15, 15, 6, 7, '日 期：', font=NORMAL, align=LA)
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Output: 工序工程量统计表 (per field)
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def generate_statistics(wb, data, params, field_info):
|
||
field_display = field_info.get('field_display', 'T3田块')
|
||
proj = params.get('project_name') or data.get('project_name', '')
|
||
contractor = params.get('contractor', '')
|
||
cs = params.get('contract_section', '')
|
||
qty = field_info
|
||
|
||
ws = wb.active
|
||
ws.title = '工序工程量统计表'
|
||
cw(ws, {1: 8, 2: 16, 3: 10, 4: 14, 5: 14, 6: 14,
|
||
7: 8, 8: 8, 9: 8, 10: 8, 11: 8, 12: 8, 13: 8, 14: 8, 15: 8})
|
||
rh(ws, {1: 36, 2: 24, 3: 24, 4: 30, 5: 24, 6: 24, 7: 24, 8: 24, 9: 24, 10: 24, 11: 24})
|
||
|
||
ms(ws, 1, 1, 1, 15, f'{field_display}单元工程工序工程量统计表', font=TITLE_FONT, align=CA)
|
||
ms(ws, 2, 2, 1, 2, '项目名称', font=BOLD, align=CA)
|
||
ms(ws, 2, 2, 3, 12, proj, font=NORMAL, align=LA)
|
||
ms(ws, 2, 2, 14, 15, f'合同段 {cs}', font=NORMAL, align=LA)
|
||
ac(ws, 2, 13, '', font=NORMAL)
|
||
|
||
ms(ws, 3, 3, 1, 2, '承包单位', font=BOLD, align=CA)
|
||
ms(ws, 3, 3, 3, 15, contractor, font=NORMAL, align=LA)
|
||
|
||
# Header rows
|
||
ms(ws, 4, 5, 1, 1, '序号', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 5, 2, 2, '工程验收段名称及部位', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 4, 3, 3, '工程量', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
ms(ws, 4, 4, 4, 6, '工 序 工 程 量', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
for c in range(7, 16):
|
||
ac(ws, 4, c, '', border=THIN, fill=HDR_FILL)
|
||
ac(ws, 5, c, '', border=THIN, fill=HDR_FILL)
|
||
ac(ws, 5, 3, '', font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
for c, lbl in [(4, '清理表土'), (5, '修平田地'), (6, '田埂修筑')]:
|
||
ac(ws, 5, c, lbl, font=HEADER_FONT, align=CA, border=THIN, fill=HDR_FILL)
|
||
|
||
# Data row
|
||
ac(ws, 6, 1, 1, font=NORMAL, align=CA, border=THIN)
|
||
ac(ws, 6, 2, field_display, font=NORMAL, align=CA, border=THIN)
|
||
ac(ws, 6, 3, '', font=NORMAL, align=CA, border=THIN)
|
||
for c, pn in [(4, '清理表土'), (5, '修平田地'), (6, '田埂修筑')]:
|
||
val = qty.get(pn, 0)
|
||
ac(ws, 6, c, val if val else '', font=NORMAL, align=CA, border=THIN,
|
||
nf='#,##0.00' if val else None)
|
||
for c in range(7, 16):
|
||
ac(ws, 6, c, '', font=NORMAL, align=CA, border=THIN)
|
||
|
||
# 合计 row
|
||
ms(ws, 7, 7, 1, 2, '合 计', font=BOLD, align=CA, border=THIN)
|
||
ac(ws, 7, 3, '', font=BOLD, align=CA, border=THIN)
|
||
for c, pn in [(4, '清理表土'), (5, '修平田地'), (6, '田埂修筑')]:
|
||
val = qty.get(pn, 0)
|
||
ac(ws, 7, c, val if val else '', font=BOLD, align=CA, border=THIN,
|
||
nf='#,##0.00' if val else None)
|
||
for c in range(7, 16):
|
||
ac(ws, 7, c, '', font=BOLD, align=CA, border=THIN)
|
||
|
||
# Signature
|
||
sigs = [('承包单位（盖章）：', '监理机构（盖章）：', '项目承担单位（盖章）：'),
|
||
('技术负责人：', '监理工程师：', '现场负责人：'),
|
||
('日 期：', '日 期：', '日 期：')]
|
||
for ri, (l1, l2, l3) in enumerate(sigs):
|
||
r = 8 + ri
|
||
ms(ws, r, r, 1, 5, l1, font=NORMAL, align=LA)
|
||
ms(ws, r, r, 6, 10, l2, font=NORMAL, align=LA)
|
||
ms(ws, r, r, 11, 15, l3, font=NORMAL, align=LA)
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Output: 工序工程量签证 (per process)
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def generate_certificate(wb, data, params, field_info, process_name):
|
||
field_display = field_info.get('field_display', 'T3田块')
|
||
proj = params.get('project_name') or data.get('project_name', '')
|
||
contractor = params.get('contractor', '')
|
||
cs = params.get('contract_section', '')
|
||
supervision = params.get('supervision_company', '曲靖南天监理有限公司')
|
||
owner = params.get('project_owner', '巧家县国土资源局')
|
||
sub_project = params.get('sub_project_name', '水浇地15～25°区域')
|
||
unit_eng = params.get('unit_engineer_name', '田块修筑')
|
||
quantity = field_info.get(process_name, 0)
|
||
|
||
ws = wb.active
|
||
ws.title = '工程量签证'
|
||
cw(ws, {1: 16, 2: 12, 3: 14, 4: 14, 5: 14,
|
||
6: 12, 7: 12, 8: 12, 9: 12, 10: 10, 11: 10, 12: 10})
|
||
rh(ws, {1: 36, 2: 24, 3: 24, 4: 24, 5: 24, 6: 24,
|
||
7: 30, 8: 30, 9: 24, 10: 24, 11: 24, 12: 24,
|
||
13: 24, 14: 24, 15: 36, 16: 36, 17: 30, 18: 24,
|
||
19: 24, 20: 24, 21: 24, 22: 24})
|
||
|
||
ms(ws, 1, 1, 1, 12, '工序（验收段）工程量签证', font=TITLE_FONT, align=CA)
|
||
ms(ws, 2, 2, 1, 2, '项目名称', font=BOLD, align=CA)
|
||
ms(ws, 2, 2, 3, 8, proj, font=NORMAL, align=LA)
|
||
ms(ws, 2, 2, 10, 12, f'合同段 {cs}', font=NORMAL, align=LA)
|
||
ac(ws, 2, 9, '', font=NORMAL)
|
||
|
||
ms(ws, 3, 3, 1, 2, '单项工程名称', font=BOLD, align=CA)
|
||
ms(ws, 3, 3, 3, 5, '土地平整工程', font=NORMAL, align=CA)
|
||
ms(ws, 3, 3, 6, 8, '分部工程名称', font=BOLD, align=CA)
|
||
ms(ws, 3, 3, 9, 12, sub_project, font=NORMAL, align=CA)
|
||
|
||
ms(ws, 4, 4, 1, 2, '单位工程名称', font=BOLD, align=CA)
|
||
ms(ws, 4, 4, 3, 5, unit_eng, font=NORMAL, align=CA)
|
||
ms(ws, 4, 4, 6, 8, '单元工程名称', font=BOLD, align=CA)
|
||
ms(ws, 4, 4, 9, 12, field_display, font=NORMAL, align=CA)
|
||
|
||
ms(ws, 5, 5, 1, 1, '致：', font=BOLD, align=LA)
|
||
ms(ws, 5, 5, 2, 12, owner, font=NORMAL, align=LA)
|
||
ms(ws, 6, 6, 1, 1, '', font=NORMAL)
|
||
ms(ws, 6, 6, 2, 12, supervision, font=NORMAL, align=LA)
|
||
|
||
ms(ws, 7, 7, 1, 4, '我方按设计要求完成', font=NORMAL, align=LA)
|
||
ms(ws, 7, 7, 5, 9, process_name, font=NORMAL, align=CA)
|
||
ms(ws, 7, 7, 10, 12, '工序验收段施工，', font=NORMAL, align=LA)
|
||
ms(ws, 8, 8, 1, 12, '经工序质量评定合格，请予以对其工程量进行核定。', font=NORMAL, align=LA)
|
||
|
||
ms(ws, 9, 9, 1, 3, '承包单位（盖章）：', font=BOLD, align=LA)
|
||
ms(ws, 9, 9, 4, 12, contractor, font=NORMAL, align=LA)
|
||
ms(ws, 10, 10, 1, 3, '项目经理（签字）：', font=NORMAL, align=LA)
|
||
ms(ws, 10, 10, 4, 12, '', font=NORMAL)
|
||
ms(ws, 11, 11, 1, 3, '日 期：', font=NORMAL, align=LA)
|
||
ms(ws, 11, 11, 4, 12, '', font=NORMAL)
|
||
|
||
ms(ws, 12, 12, 1, 12, '签证内容及图示：', font=BOLD, align=LA)
|
||
ms(ws, 13, 13, 1, 4, f'1、{field_display}', font=NORMAL, align=LA)
|
||
ms(ws, 13, 13, 5, 12, '验收段的工序工程量', font=NORMAL, align=LA)
|
||
ms(ws, 14, 14, 1, 12, '2、图示详见施工图册', font=NORMAL, align=LA)
|
||
ms(ws, 15, 15, 1, 12, '', font=NORMAL)
|
||
ms(ws, 16, 16, 1, 12, '', font=NORMAL)
|
||
|
||
ms(ws, 17, 17, 1, 12, '工程量计算：', font=BOLD, align=LA)
|
||
qty_str = f'{quantity:.2f} m³' if quantity else '0.00 m³'
|
||
ms(ws, 18, 18, 1, 4, f'1、{process_name}', font=BOLD, align=LA)
|
||
ms(ws, 18, 18, 5, 12, qty_str, font=NORMAL, align=LA)
|
||
ms(ws, 19, 19, 1, 4, '2、工程量计算表达式', font=NORMAL, align=LA)
|
||
ms(ws, 19, 19, 5, 12, '', font=NORMAL)
|
||
|
||
ms(ws, 20, 20, 1, 4, '承包单位（盖章）：', font=NORMAL, align=LA)
|
||
ms(ws, 20, 20, 5, 8, '监理机构（盖章）：', font=NORMAL, align=LA)
|
||
ms(ws, 20, 20, 9, 12, '项目承担单位（盖章）：', font=NORMAL, align=LA)
|
||
ms(ws, 21, 21, 1, 2, '技术负责人：', font=NORMAL, align=LA)
|
||
ms(ws, 21, 21, 5, 6, '监理工程师：', font=NORMAL, align=LA)
|
||
ms(ws, 21, 21, 9, 10, '现场负责人：', font=NORMAL, align=LA)
|
||
ms(ws, 22, 22, 1, 2, '日 期：', font=NORMAL, align=LA)
|
||
ms(ws, 22, 22, 5, 6, '日 期：', font=NORMAL, align=LA)
|
||
ms(ws, 22, 22, 9, 10, '日 期：', font=NORMAL, align=LA)
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
# Main
|
||
# ══════════════════════════════════════════════════════════════════════
|
||
|
||
def main():
|
||
if len(sys.argv) > 1:
|
||
p = json.loads(sys.argv[1])
|
||
elif 'PARAMS_FILE' in os.environ:
|
||
with open(os.environ['PARAMS_FILE']) as f:
|
||
p = json.load(f)
|
||
else:
|
||
p = {}
|
||
for key in ('input_table_path', 'output_dir', 'project_name',
|
||
'contractor', 'contract_section', 'supervision_company',
|
||
'project_owner', 'sub_project_name', 'unit_engineer_name',
|
||
'generate_all_fields'):
|
||
if key.upper() in os.environ:
|
||
p[key] = os.environ[key.upper()]
|
||
|
||
required = ['input_table_path', 'output_dir']
|
||
missing = [k for k in required if k not in p or not p[k]]
|
||
if missing:
|
||
print(json.dumps({
|
||
"error": f"缺少必要参数: {', '.join(missing)}",
|
||
}))
|
||
sys.exit(1)
|
||
|
||
input_path = p['input_table_path']
|
||
output_dir = p['output_dir']
|
||
os.makedirs(output_dir, exist_ok=True)
|
||
|
||
# ── 1. Parse input ─────────────────────────────────────────────
|
||
print(json.dumps({"step": "parse_input", "status": "start", "path": input_path}))
|
||
try:
|
||
data = parse_input(input_path)
|
||
except Exception as e:
|
||
print(json.dumps({"error": f"解析输入表失败: {str(e)}"}))
|
||
sys.exit(1)
|
||
|
||
fields = data.get('fields', [])
|
||
print(json.dumps({
|
||
"step": "parse_input",
|
||
"status": "ok",
|
||
"project": data.get('project_name', ''),
|
||
"field_count": len(fields),
|
||
"fields": [f['name'] for f in fields],
|
||
}))
|
||
|
||
# ── 2. Resolve params ──────────────────────────────────────────
|
||
params = {
|
||
'project_name': p.get('project_name') or data.get('project_name', ''),
|
||
'contractor': p.get('contractor', ''),
|
||
'contract_section': p.get('contract_section', ''),
|
||
'sub_project_name': p.get('sub_project_name', '水浇地15～25°区域'),
|
||
'unit_engineer_name': p.get('unit_engineer_name', '田块修筑'),
|
||
'supervision_company': p.get('supervision_company', '曲靖南天监理有限公司'),
|
||
'project_owner': p.get('project_owner', '巧家县国土资源局'),
|
||
}
|
||
|
||
generate_all = p.get('generate_all_fields', 'true').lower() in ('true', '1', 'yes')
|
||
|
||
# ── 3. Generate 分部工程工程量汇总表 ─────────────────────────────
|
||
wb0 = openpyxl.Workbook()
|
||
generate_subitem_summary(wb0, data, params)
|
||
f0 = os.path.join(output_dir, '01_水浇地15～25°区域分部工程工程量汇总表.xlsx')
|
||
wb0.save(f0)
|
||
print(json.dumps({"step": "generate", "file": f0, "status": "ok"}))
|
||
generated = [f0]
|
||
|
||
# ── 4. Generate per-field documents ─────────────────────────────
|
||
target_fields = fields if generate_all else [fields[0]] if fields else []
|
||
|
||
for field_info in target_fields:
|
||
fd = field_info.get('field_display', field_info['name'])
|
||
fn = field_info.get('name', fd)
|
||
prefix = f'02_{fn}'
|
||
|
||
# 报验单
|
||
wb1 = openpyxl.Workbook()
|
||
generate_report(wb1, data, params, field_info)
|
||
f1 = os.path.join(output_dir, f'{prefix}_{fd}单元工程计量报验单.xlsx')
|
||
wb1.save(f1)
|
||
generated.append(f1)
|
||
print(json.dumps({"step": "generate", "file": f1, "status": "ok"}))
|
||
|
||
# 统计表
|
||
wb2 = openpyxl.Workbook()
|
||
generate_statistics(wb2, data, params, field_info)
|
||
f2 = os.path.join(output_dir, f'{prefix}_{fd}单元工程工序工程量统计表.xlsx')
|
||
wb2.save(f2)
|
||
generated.append(f2)
|
||
print(json.dumps({"step": "generate", "file": f2, "status": "ok"}))
|
||
|
||
# 3x签证
|
||
for proc in ['清理表土', '修平田地', '田埂修筑']:
|
||
wb3 = openpyxl.Workbook()
|
||
generate_certificate(wb3, data, params, field_info, proc)
|
||
f3 = os.path.join(output_dir, f'{prefix}_{proc}工序工程量签证.xlsx')
|
||
wb3.save(f3)
|
||
generated.append(f3)
|
||
print(json.dumps({"step": "generate", "file": f3, "status": "ok"}))
|
||
|
||
# ── Summary ──────────────────────────────────────────────────────
|
||
total_summary = {
|
||
"清理表土": round(sum(f.get('清理表土', 0) for f in fields), 2),
|
||
"修平田地": round(sum(f.get('修平田地', 0) for f in fields), 2),
|
||
"田埂修筑": round(sum(f.get('田埂修筑', 0) for f in fields), 2),
|
||
}
|
||
|
||
result = {
|
||
"status": "success",
|
||
"output_dir": output_dir,
|
||
"files": generated,
|
||
"summary": {
|
||
"project": params['project_name'],
|
||
"contractor": params['contractor'],
|
||
"total_fields": len(fields),
|
||
"generated_fields": len(target_fields),
|
||
"total_quantities": total_summary,
|
||
}
|
||
}
|
||
print(json.dumps(result))
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|