"""경쟁사 산근 엑셀 읽기 — 블록 · 같은 책 단가표(PLAN 793-1 · 읽기만). STmate(.xlsx) = 단가산출근거 시트 G 열 「BDCOD|D…」 머리 · POSS~POSE 몸(숨은 G 열 원문) · 꼬리(계 · 계약단가 · 총계) + 목록표 단가(노무비 · 재료비 · 경비 = E 열 · 숨은 G 열 `COD` · 중기 · 단가산출근거 = E~H 열 계 · 노 · 재 · 경 · 숨은 J 열 메타). EST(.xls · .xlsx) = 단가산출 시트 블록 글(`M02_Hopyo_ReadEst.read`) + 노임단가 · 자재단가(적용단가) · 기계경비총괄표 · 견적단가 · 단가산출총괄표(적용 칸 · 합이 안 맞으면 당초 칸). """ from __future__ import annotations import io import re import warnings from pathlib import Path from typing import Any from M02_MasterTemplete import M02_Hopyo_ReadEst as read_est STM_SHEETS = ( "단가산출근거", "단가산출근거목록표", "노무비목록표", "재료비목록표", "경비목록표", "중기목록표", ) EST_SHEETS = ("단가산출총괄표", "단가산출", "기계경비총괄표", "자재단가", "노임단가", "견적단가") _X7, _X5 = "_x0007_", "_x0005_" _HEAD = re.compile(r"BDCOD\|([A-Z]\d+).*?POSS\|(\d+).*?POSE\|(\d+)") def sheets(name: str, data: bytes) -> dict[str, list[list[Any]]]: """책 바이트 → {시트: 줄} (두 꼴 시트만).""" keep = STM_SHEETS + EST_SHEETS if data[:4] == b"PK\x03\x04" or name.lower().endswith(".xlsx"): import openpyxl with warnings.catch_warnings(): # 인쇄 영역 이름 경고 warnings.simplefilter("ignore") wb = openpyxl.load_workbook(io.BytesIO(data), read_only=True, data_only=True) try: return { ws.title.strip(): [list(r) for r in ws.iter_rows(values_only=True)] for ws in wb.worksheets if ws.title.strip() in keep } finally: wb.close() import xlrd wb = xlrd.open_workbook(file_contents=data, logfile=io.StringIO()) return { sh.name.strip(): [sh.row_values(r) for r in range(sh.nrows)] for sh in wb.sheets() if sh.name.strip() in keep } def kind_of(book: dict[str, list]) -> str | None: if "단가산출근거" in book: return "STmate" return "EST" if "단가산출" in book else None # ── STmate ───────────────────────────────────────────────────────────── def _col(row: list[Any], letter: str) -> Any: i = ord(letter) - 65 return row[i] if i < len(row) else None def meta(text: Any) -> dict[str, str]: """숨은 메타 칸 「COD|L00003 ␅ UNT|L% …」 → dict.""" s = str(text or "").replace(_X7, "").replace(_X5, "\x05") out = {} for part in s.split("\x05"): part = part.strip("`") if "|" in part: k, v = part.split("|", 1) out[k] = v return out _BODY = "BCDEFGHIJKLMNO" def stmate_blocks(book: dict[str, list]) -> list[dict[str, Any]]: """→ [{코드, 이름, 행, 머리 {A..F}, 몸 [{행, B, G, C~F …}], 꼬리 [{행, A, C~F, H}]}].""" rows = book["단가산출근거"] out = [] for i, r in enumerate(rows): gv = _col(r, "G") if not (isinstance(gv, str) and "BDCOD|" in gv): continue m = _HEAD.search(gv) code, ps, pe = m.group(1), int(m.group(2)), int(m.group(3)) body = [] for rn in range(ps, pe + 1): rr = rows[rn - 1] one = {k: _col(rr, k) for k in _BODY} one["G"] = str(one["G"] or "").replace(_X7, "") body.append({"행": rn, **one}) tail = [] for rn in range(pe + 1, min(pe + 40, len(rows) + 1)): rr = rows[rn - 1] g2 = _col(rr, "G") if isinstance(g2, str) and "BDCOD|" in g2: break a = str(_col(rr, "A") or "").replace(" ", "") if a and isinstance(_col(rr, "C"), (int, float)): tail.append({"행": rn, "A": a, **{k: _col(rr, k) for k in "CDEFH"}}) head = {k: _col(r, k) for k in "ABCDEF"} name = _col(rows[i + 1], "B") if i + 1 < len(rows) else None out.append( {"코드": code, "이름": name, "행": i + 1, "머리": head, "몸": body, "꼬리": tail} ) return out def stmate_prices(book: dict[str, list]) -> dict[str, dict[str, Any]]: """코드 → {T, L, M, S, meta, 이름, 규격, 단위}.""" prices: dict[str, dict[str, Any]] = {} for sheet, comp in (("노무비목록표", "L"), ("재료비목록표", "M"), ("경비목록표", "S")): for r in book.get(sheet, [])[3:]: m = meta(_col(r, "G")) if isinstance(_col(r, "G"), str) else {} v = _col(r, "E") if m.get("COD") and isinstance(v, (int, float)): one = {c: (v if c == comp else 0) for c in "LMS"} prices[m["COD"]] = {"T": v, **one, "meta": m} | _names(r) for sheet in ("중기목록표", "단가산출근거목록표"): for r in book.get(sheet, [])[3:]: m = meta(_col(r, "J")) if isinstance(_col(r, "J"), str) else {} if m.get("COD") and isinstance(_col(r, "E"), (int, float)): one = {"T": _col(r, "E"), "L": _col(r, "F") or 0, "M": _col(r, "G") or 0} prices[m["COD"]] = {**one, "S": _col(r, "H") or 0, "meta": m} | _names(r) return prices def _names(r: list[Any]) -> dict[str, Any]: return {"이름": _col(r, "B"), "규격": _col(r, "C"), "단위": _col(r, "D")} # ── EST ──────────────────────────────────────────────────────────────── def flat(s: Any) -> str: return re.sub(r"\s+", "", str(s or "")) def _hcol(rows: list[list[Any]], word: str, start: int = 2) -> int | None: for r in rows[start : start + 2]: for j, v in enumerate(r): if flat(v) == word: return j return None def _n(v: Any) -> float | None: if isinstance(v, (int, float)): return float(v) try: return float(str(v).replace(",", "").strip()) except ValueError: return None def applied_how(r: list[Any]) -> tuple[dict[str, float], str | None]: """총괄표 줄 → ({T, L, M, S}, 쓴 칸 표지) — 적용 칸(5~8) 합이 맞으면 적용 · 아니면 당초(25~28). 표지 = 당초를 쓴 경우(까닭) · 적용 ≠ 당초인 경우(차이) · 그 밖 None(PLAN 797-1a).""" a = {k: _n(r[j]) for k, j in (("T", 5), ("L", 6), ("M", 7), ("S", 8))} first = {k: _n(r[j]) for k, j in (("T", 25), ("L", 26), ("M", 27), ("S", 28))} names = {"T": "계", "L": "노무비", "M": "재료비", "S": "경비"} gap = " · ".join( f"{names[k]} 적용 {a[k] or 0:g} · 당초 {first[k]:g}" for k in "TLMS" if a["T"] and first[k] is not None and abs((a[k] or 0) - first[k]) >= 0.5 ) if ( a["T"] and all(a[k] is not None for k in "LMS") and abs(a["L"] + a["M"] + a["S"] - a["T"]) < 0.5 and (a["L"] or a["M"] or a["S"]) ): return a, (f"적용 칸 사용 — 당초와 다름 ({gap})" if gap else None) got = {k: (v or 0.0) for k, v in first.items()} if not got["T"]: return got, None # 쓸 값이 없음 — 표지할 것 없음 why = "적용 칸 합이 안 맞음" if a["T"] else "적용 칸 비어 있음" return got, f"당초 칸 사용 — {why}" + (f" ({gap})" if gap else "") def applied(r: list[Any]) -> dict[str, float]: return applied_how(r)[0] def est_prices(book: dict[str, list]) -> tuple[list[dict[str, Any]], dict[int, dict[str, Any]]]: """→ (단가 목록 [{종류, 이름, 규격, 비목, 값}], 산근 총괄 {번: {T, L, M, S, 이름, 규격}}).""" out: list[dict[str, Any]] = [] lab = book.get("노임단가", []) c = _hcol(lab, "적용단가") for r in lab[3:]: if c is not None and len(r) > c and r[2] and isinstance(r[c], (int, float)): out.append( {"종류": "노임", "이름": flat(r[2]), "규격": "", "비목": "L", "값": float(r[c])} ) mat = book.get("자재단가", []) c = _hcol(mat, "적용단가") for r in mat[4:]: if c is not None and len(r) > c and r[2] and isinstance(r[c], (int, float)): out.append( { "종류": "자재", "이름": flat(r[2]), "규격": flat(r[3]), "비목": "M", "값": float(r[c]), } ) for sheet, kind in (("기계경비총괄표", "기계"), ("견적단가", "견적")): for r in book.get(sheet, [])[3:]: if len(r) > 28 and r[2]: for k, v in applied(r).items(): if isinstance(v, (int, float)) and (v or k != "T"): one = {"종류": kind, "이름": flat(r[2]), "규격": flat(r[3])} out.append({**one, "비목": k, "값": float(v or 0)}) san: dict[int, dict[str, Any]] = {} for r in book.get("단가산출총괄표", [])[3:]: if len(r) > 28 and str(r[1]).strip().isdigit(): n = int(str(r[1]).strip()) got, note = applied_how(r) san[n] = {k: float(v or 0) for k, v in got.items()} san[n] |= {"이름": flat(r[2]), "규격": flat(r[3]), "칸": note} for k in "TLMS": if san[n][k] or k != "T": one = {"종류": "산근", "이름": f"단산{n}", "규격": ""} out.append({**one, "비목": k, "값": san[n][k]}) return out, san def est_blocks(book: dict[str, list]) -> list[dict[str, Any]]: """→ [{번, 이름, 단위, 글 [줄], 글행 [행]}].""" blocks = read_est.read(book.get(read_est.SHEET)) keep = ("번", "이름", "단위", "글", "글행") return [{k: b[k] for k in keep} for b in blocks.values()] def load(path: str | Path) -> dict[str, Any]: """파일 → {꼴, 블록, 단가(STmate dict | EST 목록), 총괄(EST)}.""" path = Path(path) book = sheets(path.name, path.read_bytes()) kind = kind_of(book) if kind == "STmate": return {"꼴": kind, "블록": stmate_blocks(book), "단가": stmate_prices(book)} if kind == "EST": prices, san = est_prices(book) return {"꼴": kind, "블록": est_blocks(book), "단가": prices, "총괄": san} raise ValueError("STmate · EST 산근 시트 없음")