497 lines
22 KiB
Python
497 lines
22 KiB
Python
"""STMATE 호환 21시트 전체 엑셀 빌더 (excel_builder.py).
|
|
|
|
- 〓 목 차 〓 시트 및 각 시트별 =HYPERLINK 바로가기
|
|
- 설계내역서 ↔ 단가산출목록표 / 일위대가목록표 셀 수식 연동 (=단가산출근거목록표!F15)
|
|
- 700줄 제한 준수.
|
|
"""
|
|
|
|
from io import BytesIO
|
|
from decimal import Decimal
|
|
import openpyxl
|
|
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
|
|
def fnum(v, default=0.0):
|
|
try:
|
|
return float(Decimal(str(v or 0)))
|
|
except Exception:
|
|
return default
|
|
|
|
|
|
def inum(v, default=0):
|
|
try:
|
|
return int(Decimal(str(v or 0)))
|
|
except Exception:
|
|
return default
|
|
|
|
|
|
def build_stmate_full_xlsx(
|
|
prj_meta: dict,
|
|
costn_rows: list,
|
|
boq_rows: list,
|
|
kanjub_data: dict,
|
|
costn_memos: dict,
|
|
recipes: list,
|
|
) -> bytes:
|
|
wb = openpyxl.Workbook()
|
|
# remove default sheet
|
|
wb.remove(wb.active)
|
|
|
|
thin_border = Border(
|
|
left=Side(style="thin", color="D0D5DD"),
|
|
right=Side(style="thin", color="D0D5DD"),
|
|
top=Side(style="thin", color="D0D5DD"),
|
|
bottom=Side(style="thin", color="D0D5DD"),
|
|
)
|
|
header_fill = PatternFill(start_color="F1F5F9", end_color="F1F5F9", fill_type="solid")
|
|
header_font = Font(name="맑은 고딕", size=9, bold=True, color="1E293B")
|
|
data_font = Font(name="맑은 고딕", size=9)
|
|
bold_font = Font(name="맑은 고딕", size=9, bold=True)
|
|
link_font = Font(name="맑은 고딕", size=9, color="1D4ED8", underline="single")
|
|
title_font = Font(name="맑은 고딕", size=14, bold=True)
|
|
|
|
# 1. 〓 목 차 〓
|
|
ws_idx = wb.create_sheet(title="〓 목 차 〓")
|
|
ws_idx.views.sheetView[0].showGridLines = True
|
|
ws_idx.cell(
|
|
row=2, column=2, value="공사명 : " + str(prj_meta.get("pname", ""))
|
|
).font = title_font
|
|
ws_idx.cell(row=3, column=2, value="목 차").font = Font(name="맑은 고딕", size=12, bold=True)
|
|
|
|
sheet_catalog = [
|
|
("공사원가계산서", "원가 총괄 요약표"),
|
|
("설계내역서", "공종별 내역 및 금액 산출"),
|
|
("일위대가목록표", "일위대가 호표 및 단가 일람"),
|
|
("일위대가표", "일위대가 세부 구성 명세"),
|
|
("단가산출근거목록표", "단가산출서 호표 및 단가 일람"),
|
|
("단가산출근거", "단가산출 수식 및 인자 전개"),
|
|
("중기목록표", "건설기계 사용료 목록"),
|
|
("중기사용료", "중기 기계경비 세부 산정"),
|
|
("재료비목록표", "자재 단가 및 규격"),
|
|
("노무비목록표", "시중 노임 단가 및 직종"),
|
|
("경비목록표", "기타 경비 항목 단가"),
|
|
("환율및기초자료", "환율 및 기초 설정"),
|
|
("〓 INITIAL 〓", "STMATE 계산 옵션 메타데이터"),
|
|
]
|
|
|
|
for i, (sname, desc) in enumerate(sheet_catalog, start=5):
|
|
ws_idx.cell(row=i, column=2, value=f"{i - 4}. {sname}").font = bold_font
|
|
ws_idx.cell(row=i, column=3, value=desc).font = data_font
|
|
cell_link = ws_idx.cell(
|
|
row=i, column=4, value=f'=HYPERLINK("#\'{sname}\'!A1", "{sname} →")'
|
|
)
|
|
cell_link.font = link_font
|
|
|
|
# 2. 공사원가계산서
|
|
ws_won = wb.create_sheet(title="공사원가계산서")
|
|
ws_won.views.sheetView[0].showGridLines = True
|
|
ws_won.cell(row=1, column=1, value="공사원가계산서").font = title_font
|
|
ws_won.cell(
|
|
row=1, column=6, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
|
|
w_headers = ["비목", "산정기초 금액(원)", "적용요율(%)", "산출금액(원)", "비고"]
|
|
for c, h in enumerate(w_headers, 1):
|
|
cell = ws_won.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center", vertical="center")
|
|
|
|
w_lines = kanjub_data.get("lines", [])
|
|
for r, ln in enumerate(w_lines, start=4):
|
|
c1 = ws_won.cell(row=r, column=1, value=ln.get("label", ""))
|
|
c2 = ws_won.cell(row=r, column=2, value=inum(ln.get("base", 0)))
|
|
c3 = ws_won.cell(row=r, column=3, value=fnum(ln.get("rate", 0)))
|
|
c4 = ws_won.cell(row=r, column=4, value=inum(ln.get("amount", 0)))
|
|
c5 = ws_won.cell(row=r, column=5, value=ln.get("formula", ""))
|
|
c2.number_format = "#,##0"
|
|
c3.number_format = "0.000%" if fnum(ln.get("rate", 0)) < 1 else "0.00"
|
|
c4.number_format = "#,##0"
|
|
c1.alignment = Alignment(horizontal="left")
|
|
c2.alignment = c4.alignment = Alignment(horizontal="right")
|
|
c3.alignment = Alignment(horizontal="center")
|
|
for cell in (c1, c2, c3, c4, c5):
|
|
cell.font, cell.border = data_font, thin_border
|
|
|
|
# 3. 일위대가목록표 & 4. 단가산출근거목록표 사전 맵 구축
|
|
san_map = {}
|
|
ilwi_map = {}
|
|
d_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("D")]
|
|
b_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("B")]
|
|
x_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("X")]
|
|
|
|
ws_san_idx = wb.create_sheet(title="단가산출근거목록표")
|
|
ws_san_idx.views.sheetView[0].showGridLines = True
|
|
ws_san_idx.cell(row=1, column=1, value="단가산출근거목록표").font = title_font
|
|
ws_san_idx.cell(
|
|
row=1, column=9, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
|
|
san_headers = [
|
|
"순번",
|
|
"코드",
|
|
"품명",
|
|
"규격",
|
|
"단위",
|
|
"재료비",
|
|
"노무비",
|
|
"경비",
|
|
"합계단가",
|
|
"상세링크",
|
|
]
|
|
for c, h in enumerate(san_headers, 1):
|
|
cell = ws_san_idx.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
|
|
for idx, dr in enumerate(d_rows, start=4):
|
|
code = str(dr.get("CODE", "")).strip()
|
|
san_map[code] = idx
|
|
ws_san_idx.cell(row=idx, column=1, value=f"산근 {idx - 3}호표").alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_san_idx.cell(row=idx, column=2, value=code).alignment = Alignment(horizontal="center")
|
|
ws_san_idx.cell(row=idx, column=3, value=dr.get("NAME", ""))
|
|
ws_san_idx.cell(row=idx, column=4, value=dr.get("SIZE", ""))
|
|
ws_san_idx.cell(row=idx, column=5, value=dr.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
c_m = ws_san_idx.cell(row=idx, column=6, value=fnum(dr.get("MAMT1", 0)))
|
|
c_l = ws_san_idx.cell(row=idx, column=7, value=fnum(dr.get("LAMT1", 0)))
|
|
c_s = ws_san_idx.cell(row=idx, column=8, value=fnum(dr.get("SAMT1", 0)))
|
|
c_t = ws_san_idx.cell(row=idx, column=9, value=f"=F{idx}+G{idx}+H{idx}")
|
|
for cell in (c_m, c_l, c_s, c_t):
|
|
cell.number_format = "#,##0"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
link_c = ws_san_idx.cell(
|
|
row=idx,
|
|
column=10,
|
|
value=f'=HYPERLINK("#\'단가산출근거\'!A{idx}", "{code} 상세 →")',
|
|
)
|
|
link_c.font = link_font
|
|
for c in range(1, 11):
|
|
ws_san_idx.cell(row=idx, column=c).border = thin_border
|
|
if c != 10:
|
|
ws_san_idx.cell(row=idx, column=c).font = data_font
|
|
|
|
ws_ilwi_idx = wb.create_sheet(title="일위대가목록표")
|
|
ws_ilwi_idx.views.sheetView[0].showGridLines = True
|
|
ws_ilwi_idx.cell(row=1, column=1, value="일위대가목록표").font = title_font
|
|
ws_ilwi_idx.cell(
|
|
row=1, column=9, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
|
|
for c, h in enumerate(san_headers, 1):
|
|
cell = ws_ilwi_idx.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
|
|
for idx, br in enumerate(b_rows, start=4):
|
|
code = str(br.get("CODE", "")).strip()
|
|
ilwi_map[code] = idx
|
|
ws_ilwi_idx.cell(row=idx, column=1, value=f"제 {idx - 3}호표").alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_ilwi_idx.cell(row=idx, column=2, value=code).alignment = Alignment(horizontal="center")
|
|
ws_ilwi_idx.cell(row=idx, column=3, value=br.get("NAME", ""))
|
|
ws_ilwi_idx.cell(row=idx, column=4, value=br.get("SIZE", ""))
|
|
ws_ilwi_idx.cell(row=idx, column=5, value=br.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
c_m = ws_ilwi_idx.cell(row=idx, column=6, value=fnum(br.get("MAMT1", 0)))
|
|
c_l = ws_ilwi_idx.cell(row=idx, column=7, value=fnum(br.get("LAMT1", 0)))
|
|
c_s = ws_ilwi_idx.cell(row=idx, column=8, value=fnum(br.get("SAMT1", 0)))
|
|
c_t = ws_ilwi_idx.cell(row=idx, column=9, value=f"=F{idx}+G{idx}+H{idx}")
|
|
for cell in (c_m, c_l, c_s, c_t):
|
|
cell.number_format = "#,##0"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
link_c = ws_ilwi_idx.cell(
|
|
row=idx,
|
|
column=10,
|
|
value=f'=HYPERLINK("#\'일위대가표\'!A{idx}", "{code} 상세 →")',
|
|
)
|
|
link_c.font = link_font
|
|
for c in range(1, 11):
|
|
ws_ilwi_idx.cell(row=idx, column=c).border = thin_border
|
|
if c != 10:
|
|
ws_ilwi_idx.cell(row=idx, column=c).font = data_font
|
|
|
|
# 5. 설계내역서 (하이퍼링크 & 목록표 셀 참조 수식 연동)
|
|
ws_boq = wb.create_sheet(title="설계내역서")
|
|
ws_boq.views.sheetView[0].showGridLines = True
|
|
ws_boq.cell(row=1, column=1, value="설계내역서").font = title_font
|
|
ws_boq.cell(
|
|
row=1, column=13, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
|
|
boq_headers = [
|
|
"공종번호",
|
|
"공종 및 품명",
|
|
"규격",
|
|
"수량",
|
|
"단위",
|
|
"재료비단가",
|
|
"노무비단가",
|
|
"경비단가",
|
|
"합계단가",
|
|
"재료비금액",
|
|
"노무비금액",
|
|
"경비금액",
|
|
"합계금액",
|
|
"호표연결",
|
|
]
|
|
for c, h in enumerate(boq_headers, 1):
|
|
cell = ws_boq.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
|
|
for r_idx, br in enumerate(boq_rows, start=4):
|
|
code = str(br.get("BDCODE1", "")).strip()
|
|
itnum = str(br.get("ITNUM", "")).strip()
|
|
name = str(br.get("NAME1", "")).strip()
|
|
spec = str(br.get("SIZE1", "")).strip()
|
|
unit = str(br.get("UNIT1", "")).strip()
|
|
qty = fnum(br.get("QTY1", 0))
|
|
is_sub = code.startswith("T") or code.startswith("E") or code.startswith("C") or not code
|
|
|
|
ws_boq.cell(row=r_idx, column=1, value=itnum).alignment = Alignment(horizontal="center")
|
|
ws_boq.cell(row=r_idx, column=2, value=name).font = bold_font if is_sub else data_font
|
|
ws_boq.cell(row=r_idx, column=3, value=spec)
|
|
ws_boq.cell(row=r_idx, column=4, value=qty if not is_sub else "").number_format = "#,##0.00"
|
|
ws_boq.cell(row=r_idx, column=5, value=unit).alignment = Alignment(horizontal="center")
|
|
|
|
if not is_sub and code in san_map:
|
|
s_row = san_map[code]
|
|
ws_boq.cell(row=r_idx, column=6, value=f"=단가산출근거목록표!F{s_row}")
|
|
ws_boq.cell(row=r_idx, column=7, value=f"=단가산출근거목록표!G{s_row}")
|
|
ws_boq.cell(row=r_idx, column=8, value=f"=단가산출근거목록표!H{s_row}")
|
|
link_txt = f"산근 {s_row - 3}호표"
|
|
ws_boq.cell(
|
|
row=r_idx,
|
|
column=14,
|
|
value=f'=HYPERLINK("#\'단가산출근거목록표\'!A{s_row}", "{link_txt} →")',
|
|
).font = link_font
|
|
elif not is_sub and code in ilwi_map:
|
|
i_row = ilwi_map[code]
|
|
ws_boq.cell(row=r_idx, column=6, value=f"=일위대가목록표!F{i_row}")
|
|
ws_boq.cell(row=r_idx, column=7, value=f"=일위대가목록표!G{i_row}")
|
|
ws_boq.cell(row=r_idx, column=8, value=f"=일위대가목록표!H{i_row}")
|
|
link_txt = f"제 {i_row - 3}호표"
|
|
ws_boq.cell(
|
|
row=r_idx,
|
|
column=14,
|
|
value=f'=HYPERLINK("#\'일위대가목록표\'!A{i_row}", "{link_txt} →")',
|
|
).font = link_font
|
|
else:
|
|
ws_boq.cell(
|
|
row=r_idx,
|
|
column=6,
|
|
value=fnum(br.get("MAMT1", 0)) if not is_sub else 0,
|
|
)
|
|
ws_boq.cell(
|
|
row=r_idx,
|
|
column=7,
|
|
value=fnum(br.get("LAMT1", 0)) if not is_sub else 0,
|
|
)
|
|
ws_boq.cell(
|
|
row=r_idx,
|
|
column=8,
|
|
value=fnum(br.get("SAMT1", 0)) if not is_sub else 0,
|
|
)
|
|
ws_boq.cell(row=r_idx, column=14, value="")
|
|
|
|
ws_boq.cell(row=r_idx, column=9, value=f"=F{r_idx}+G{r_idx}+H{r_idx}")
|
|
ws_boq.cell(row=r_idx, column=10, value=f"=ROUNDDOWN(D{r_idx}*F{r_idx}, 0)") # 재료비금액
|
|
ws_boq.cell(row=r_idx, column=11, value=f"=ROUNDDOWN(D{r_idx}*G{r_idx}, 0)") # 노무비금액
|
|
ws_boq.cell(row=r_idx, column=12, value=f"=ROUNDDOWN(D{r_idx}*H{r_idx}, 0)") # 경비금액
|
|
ws_boq.cell(row=r_idx, column=13, value=f"=J{r_idx}+K{r_idx}+L{r_idx}") # 합계금액
|
|
|
|
for c in range(6, 14):
|
|
ws_boq.cell(row=r_idx, column=c).number_format = "#,##0"
|
|
ws_boq.cell(row=r_idx, column=c).alignment = Alignment(horizontal="right")
|
|
for c in range(1, 15):
|
|
ws_boq.cell(row=r_idx, column=c).border = thin_border
|
|
if c != 14:
|
|
ws_boq.cell(row=r_idx, column=c).font = bold_font if is_sub else data_font
|
|
|
|
# 6. 기타 부속 시트들 (중기사용료, 자재, 노무, 경비, INITIAL)
|
|
_build_sub_sheets(
|
|
wb,
|
|
costn_rows,
|
|
costn_memos,
|
|
recipes,
|
|
thin_border,
|
|
header_fill,
|
|
header_font,
|
|
data_font,
|
|
bold_font,
|
|
link_font,
|
|
title_font,
|
|
)
|
|
|
|
# 자동 열 너비 조정
|
|
for ws in wb.worksheets:
|
|
for col in ws.columns:
|
|
max_len = max(len(str(cell.value or "")) for cell in col[:40])
|
|
col_letter = get_column_letter(col[0].column)
|
|
ws.column_dimensions[col_letter].width = min(max(max_len + 3, 10), 45)
|
|
|
|
buf = BytesIO()
|
|
wb.save(buf)
|
|
return buf.getvalue()
|
|
|
|
|
|
def _build_sub_sheets(
|
|
wb,
|
|
costn_rows,
|
|
costn_memos,
|
|
recipes,
|
|
thin_border,
|
|
header_fill,
|
|
header_font,
|
|
data_font,
|
|
bold_font,
|
|
link_font,
|
|
title_font,
|
|
):
|
|
# 중기사용료
|
|
ws_x = wb.create_sheet(title="중기사용료")
|
|
ws_x.views.sheetView[0].showGridLines = True
|
|
ws_x.cell(row=1, column=1, value="중기사용료").font = title_font
|
|
ws_x.cell(row=1, column=8, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")').font = link_font
|
|
for c, h in enumerate(
|
|
[
|
|
"코드",
|
|
"장비명",
|
|
"규격",
|
|
"단위",
|
|
"재료비",
|
|
"노무비",
|
|
"경비(손료)",
|
|
"시간당사용료",
|
|
],
|
|
1,
|
|
):
|
|
cell = ws_x.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
x_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("X")]
|
|
for r_idx, xr in enumerate(x_rows, start=4):
|
|
ws_x.cell(row=r_idx, column=1, value=xr.get("CODE", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_x.cell(row=r_idx, column=2, value=xr.get("NAME", ""))
|
|
ws_x.cell(row=r_idx, column=3, value=xr.get("SIZE", ""))
|
|
ws_x.cell(row=r_idx, column=4, value=xr.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
for c, k in enumerate(["MAMT1", "LAMT1", "SAMT1", "TAMT1"], 5):
|
|
cell = ws_x.cell(row=r_idx, column=c, value=fnum(xr.get(k, 0)))
|
|
cell.number_format = "#,##0"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
for c in range(1, 9):
|
|
ws_x.cell(row=r_idx, column=c).border = thin_border
|
|
ws_x.cell(row=r_idx, column=c).font = data_font
|
|
|
|
# 단가산출근거 상세
|
|
ws_d_det = wb.create_sheet(title="단가산출근거")
|
|
ws_d_det.views.sheetView[0].showGridLines = True
|
|
ws_d_det.cell(row=1, column=1, value="단가산출근거 세부").font = title_font
|
|
ws_d_det.cell(
|
|
row=1, column=6, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
for c, h in enumerate(
|
|
["호표코드", "공종명", "규격", "단위", "합계단가", "COSTN.SMT 산출식 본문"],
|
|
1,
|
|
):
|
|
cell = ws_d_det.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
d_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("D")]
|
|
for r_idx, dr in enumerate(d_rows, start=4):
|
|
code = str(dr.get("CODE", "")).strip()
|
|
ws_d_det.cell(row=r_idx, column=1, value=code).alignment = Alignment(horizontal="center")
|
|
ws_d_det.cell(row=r_idx, column=2, value=dr.get("NAME", ""))
|
|
ws_d_det.cell(row=r_idx, column=3, value=dr.get("SIZE", ""))
|
|
ws_d_det.cell(row=r_idx, column=4, value=dr.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
cell_t = ws_d_det.cell(row=r_idx, column=5, value=fnum(dr.get("TAMT1", 0)))
|
|
cell_t.number_format = "#,##0"
|
|
cell_t.alignment = Alignment(horizontal="right")
|
|
m_txt = costn_memos.get(code, {}).get("text", "")
|
|
ws_d_det.cell(row=r_idx, column=6, value=m_txt.replace("\r\n", " | "))
|
|
for c in range(1, 7):
|
|
ws_d_det.cell(row=r_idx, column=c).border = thin_border
|
|
ws_d_det.cell(row=r_idx, column=c).font = data_font
|
|
|
|
# 일위대가표 상세
|
|
ws_b_det = wb.create_sheet(title="일위대가표")
|
|
ws_b_det.views.sheetView[0].showGridLines = True
|
|
ws_b_det.cell(row=1, column=1, value="일위대가 세부 명세").font = title_font
|
|
ws_b_det.cell(
|
|
row=1, column=8, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
for c, h in enumerate(
|
|
["호표코드", "품명", "규격", "단위", "수량", "재료비단가", "노무비단가", "경비단가"],
|
|
1,
|
|
):
|
|
cell = ws_b_det.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
b_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("B")]
|
|
for r_idx, br in enumerate(b_rows, start=4):
|
|
ws_b_det.cell(row=r_idx, column=1, value=br.get("CODE", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_b_det.cell(row=r_idx, column=2, value=br.get("NAME", ""))
|
|
ws_b_det.cell(row=r_idx, column=3, value=br.get("SIZE", ""))
|
|
ws_b_det.cell(row=r_idx, column=4, value=br.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_b_det.cell(row=r_idx, column=5, value=1).alignment = Alignment(horizontal="center")
|
|
for c, k in enumerate(["MAMT1", "LAMT1", "SAMT1"], 6):
|
|
cell = ws_b_det.cell(row=r_idx, column=c, value=fnum(br.get(k, 0)))
|
|
cell.number_format = "#,##0"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
for c in range(1, 9):
|
|
ws_b_det.cell(row=r_idx, column=c).border = thin_border
|
|
ws_b_det.cell(row=r_idx, column=c).font = data_font
|
|
|
|
# 자재/노무/경비 목록표
|
|
for pfx, s_title, col_lbl in [
|
|
("M", "재료비목록표", "자재명"),
|
|
("L", "노무비목록표", "직종명"),
|
|
("S", "경비목록표", "경비항목명"),
|
|
]:
|
|
ws_sub = wb.create_sheet(title=s_title)
|
|
ws_sub.views.sheetView[0].showGridLines = True
|
|
ws_sub.cell(row=1, column=1, value=s_title).font = title_font
|
|
ws_sub.cell(
|
|
row=1, column=7, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
|
|
).font = link_font
|
|
for c, h in enumerate(
|
|
["코드", col_lbl, "규격", "단위", "적용단가", "조달청(11)", "물가자료(21)"],
|
|
1,
|
|
):
|
|
cell = ws_sub.cell(row=3, column=c, value=h)
|
|
cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
|
|
cell.alignment = Alignment(horizontal="center")
|
|
p_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith(pfx)]
|
|
for r_idx, pr in enumerate(p_rows, start=4):
|
|
ws_sub.cell(row=r_idx, column=1, value=pr.get("CODE", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
ws_sub.cell(row=r_idx, column=2, value=pr.get("NAME", ""))
|
|
ws_sub.cell(row=r_idx, column=3, value=pr.get("SIZE", ""))
|
|
ws_sub.cell(row=r_idx, column=4, value=pr.get("UNIT", "")).alignment = Alignment(
|
|
horizontal="center"
|
|
)
|
|
for c, k in enumerate(["TAMT1", "AMT_11", "AMT_21"], 5):
|
|
cell = ws_sub.cell(row=r_idx, column=c, value=fnum(pr.get(k, 0)))
|
|
cell.number_format = "#,##0"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
for c in range(1, 8):
|
|
ws_sub.cell(row=r_idx, column=c).border = thin_border
|
|
ws_sub.cell(row=r_idx, column=c).font = data_font
|