""" ============================================================================= ArchiCAD 29 BIM Takeoff & Construction Estimate Exporter v6.0 ============================================================================= Firm: The Seabold Companies / S5 Construction WHAT'S NEW IN v6: - Full CSI Division 02-26 coverage on summary tab - Composite name parser: reads wall/slab/roof composite names and auto-generates individual material line items (studs, GWB, brick, insulation, sheathing, metal studs, etc.) - Doors and windows broken out by BOTH name AND size on estimate - All finish allowances: tile, base/crown molding, cabinets, closets, flooring by room type, painting by surface - Objects from model drive MEP fixture counts - Scope: delete reference panel before running (full model used) - All property names verified from debug report USAGE: python3 archicad_takeoff_v6.py python3 archicad_takeoff_v6.py --output /path/to/folder DEPENDENCIES: pip install archicad openpyxl ============================================================================= """ import sys, os, re, argparse, subprocess, math from datetime import datetime from collections import defaultdict def ensure(pkg): try: __import__(pkg) except ImportError: subprocess.check_call([sys.executable,"-m","pip","install",pkg,"-q"]) ensure("archicad"); ensure("openpyxl") from archicad import ACConnection import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter # --------------------------------------------------------------------------- # Units # --------------------------------------------------------------------------- M_TO_FT=3.28084; M2_TO_SF=10.7639; M3_TO_CY=1.30795; M3_TO_CF=35.3147 IN_PER_M=39.3701 # --------------------------------------------------------------------------- # Styles # --------------------------------------------------------------------------- DARK="1F3864"; MID="2E75B6"; LITE="D6E4F0" ALT="EBF3FB"; WHT="FFFFFF"; BLU="0000FF" GRY="F2F2F2"; YLW="FFFF00"; FNT="Arial" TS=Side(style="thin",color="BBBBBB") TB=Border(left=TS,right=TS,top=TS,bottom=TS) M_SIDE=Side(style="medium",color="2E75B6") def hc(cell,text,bg=DARK,fg=WHT,sz=10,bold=True): cell.value=text cell.font=Font(name=FNT,bold=bold,color=fg,size=sz) cell.fill=PatternFill("solid",fgColor=bg) cell.alignment=Alignment(horizontal="center",vertical="center",wrap_text=True) cell.border=TB def dc(cell,v,alt=False,fmt=None): cell.value=v cell.font=Font(name=FNT,size=9) cell.fill=PatternFill("solid",fgColor=ALT if alt else WHT) cell.alignment=Alignment( horizontal="right" if isinstance(v,(int,float)) else "left", vertical="center") cell.border=TB if fmt: cell.number_format=fmt elif isinstance(v,float): cell.number_format="#,##0.00" def ic(cell,v,fmt=None): cell.value=v cell.font=Font(name=FNT,size=9,color=BLU) cell.fill=PatternFill("solid",fgColor=ALT) cell.alignment=Alignment(horizontal="right",vertical="center") cell.border=TB if fmt: cell.number_format=fmt def af(ws,mn=8,mx=44): for col in ws.columns: best=max((len(str(c.value)) for c in col if c.value is not None),default=mn) ws.column_dimensions[get_column_letter(col[0].column)].width=max(mn,min(best+2,mx)) def pt(ws,title,sub,pn): ws.row_dimensions[1].height=26 c=ws.cell(row=1,column=1,value=title) c.font=Font(name=FNT,bold=True,size=14,color=DARK) c.alignment=Alignment(horizontal="left",vertical="center") ts=ws.cell(row=2,column=1, value=f"{sub} | Project: {pn} | {datetime.now().strftime('%Y-%m-%d %H:%M')}") ts.font=Font(name=FNT,size=8,italic=True,color="888888") def wh(ws,row,cols,bg=LITE,fg=DARK): ws.row_dimensions[row].height=28 for c,h in enumerate(cols,1): hc(ws.cell(row=row,column=c),h,bg=bg,fg=fg,sz=9) def wr(ws,start,rows,cols): r=start for i,row in enumerate(rows): alt=(i%2==0) for c,key in enumerate(cols,1): dc(ws.cell(row=r,column=c),row.get(key,''),alt) r+=1 return r def tot_line(ws,row,col,val,lbl="TOTAL"): ws.cell(row=row,column=1,value=lbl).font=Font(name=FNT,bold=True,size=9) c=ws.cell(row=row,column=col,value=round(val,2)) c.font=Font(name=FNT,bold=True,size=9,color=DARK) c.fill=PatternFill("solid",fgColor=GRY) c.number_format="#,##0.00" c.border=Border(top=M_SIDE,bottom=M_SIDE) # =========================================================================== # 1. Connection # =========================================================================== def connect(port=None): print("\n Connecting to ArchiCAD 29...") try: conn=ACConnection.connect(port) if port else ACConnection.connect() v,b,l=conn.commands.GetProductInfo() print(f" Connected: ArchiCAD {v} build {b} ({l})") return conn except Exception as e: print(f"\n ERROR: {e}"); sys.exit(1) def resolve_output(override=None): desktop=os.path.expanduser("~/Desktop") if override and os.path.isdir(override): return override cfg=os.path.join(os.path.dirname(os.path.abspath(__file__)),".archicad_export_config") if os.path.exists(cfg): p=open(cfg).read().strip() if os.path.isdir(p): return p user=input("\n Output folder [Enter=Desktop]: ").strip() return user if (user and os.path.isdir(user)) else desktop # =========================================================================== # 2. Property fetcher (verified AC29 pattern) # =========================================================================== class Props: def __init__(self,conn): self.acc=conn.commands; self.acu=conn.utilities; self._c={} def bi(self,name): if name not in self._c: try: self._c[name]=self.acu.GetBuiltInPropertyId(name) except: self._c[name]=None return self._c[name] def ud(self,group,name): k=f"_ud_{group}_{name}" if k not in self._c: try: self._c[k]=self.acu.GetUserDefinedPropertyId(group,name) except: self._c[k]=None return self._c[k] def fetch(self,elements,spec): """spec={label:pid}. Returns [{label:value}] per element.""" if not elements: return [] valid={lbl:pid for lbl,pid in spec.items() if pid is not None} if not valid: return [{} for _ in elements] labels=list(valid.keys()); pids=list(valid.values()) out=[] for start in range(0,len(elements),50): batch=elements[start:start+50] try: res=self.acc.GetPropertyValuesOfElements(batch,pids) except Exception as e: print(f" fetch error: {e}") out.extend([{} for _ in batch]); continue for er in res: row={} try: for lbl,pv in zip(labels,er.propertyValues): try: v=pv.propertyValue.value if hasattr(v,'nonLocalizedValue'): v=v.nonLocalizedValue elif hasattr(v,'displayValue'): v=v.displayValue elif hasattr(v,'value'): v=v.value if v is not None: row[lbl]=v except: pass except: pass out.append(row) return out def flt(v,d=0.): if v is None: return d try: f=float(v); return f if f==f else d except: return d def clean(s): if not s: return '' return re.sub(r'^\d+\s*\|\s*','',str(s)).strip() # =========================================================================== # 3. Composite name parser # =========================================================================== # Maps keywords found in composite names to CSI line items. # Each entry: (regex_pattern, csi_div, description, unit, unit_cost, waste, notes) # Quantities come from wall/slab measurements; these are cost multipliers. COMPOSITE_RULES = [ # FRAMING - Wood studs (r'2x4', '06','2x4 Wood Stud @ 16" OC', 'EA', 1.85, 0.15, 'Count from wall LF x (H/8) x 0.75'), (r'2x6', '06','2x6 Wood Stud @ 16" OC', 'EA', 2.45, 0.15, 'Count from wall LF x (H/8) x 0.75'), (r'2x8', '06','2x8 Wood Stud @ 16" OC', 'EA', 3.25, 0.15, 'Count from wall LF x (H/8) x 0.75'), # FRAMING - Metal studs (r'metal\s*3\.5|metal\s*3-1/2|3\.5"?\s*metal', '05','Metal Stud 3-5/8" @ 16" OC','EA', 2.10, 0.10, 'Count from wall LF x (H/8) x 0.75'), (r'metal\s*6|6"?\s*metal', '05','Metal Stud 6" @ 16" OC', 'EA', 2.75, 0.10, 'Count from wall LF x (H/8) x 0.75'), (r'metal\s*stud|mtl\s*stud|lgs|light\s*gauge', '05','Metal Stud Framing', 'SF', 5.50, 0.10, 'Per SF of wall face area'), # SHEATHING (r'osb|orient|sheath', '06','Wall Sheathing - OSB/Ply', 'SF', 4.20, 0.10, 'Per SF of wall face area'), # GYPSUM (r'5/8.*gyp|gyp.*5/8|type\s*x', '09','GWB 5/8" Type X', 'SF', 3.20, 0.10, 'Per SF of wall face area'), (r'1/2.*gyp|gyp.*1/2|gypsum|gyp\s*bd|drywall', '09','GWB 1/2" Standard', 'SF', 2.80, 0.10, 'Per SF of wall face area'), # MASONRY (r'brick', '04','Brick Face Veneer', 'SF', 32.00, 0.10, 'Per SF of exterior wall face'), (r'cmu|block|masonry', '04','CMU Block', 'SF', 22.00, 0.05, 'Per SF of wall face area'), (r'stone\s*veneer|stone\s*cladding', '04','Stone Veneer Cladding', 'SF', 45.00, 0.10, 'Per SF of wall face area'), # INSULATION (r'r-?\s*21|r21|5\.5.*insul|insul.*5\.5', '07','Batt Insulation 5.5" R-21', 'SF', 2.10, 0.05, 'Per SF of wall face area'), (r'r-?\s*13|r13|3\.5.*insul|insul.*3\.5', '07','Batt Insulation 3.5" R-13', 'SF', 1.45, 0.05, 'Per SF of wall face area'), (r'rigid.*insul|insul.*rigid|polyiso|xps|eps', '07','Rigid Insulation CI', 'SF', 2.80, 0.05, 'Per SF of wall face area'), (r'spray.*foam|foam.*insul', '07','Spray Foam Insulation', 'SF', 3.80, 0.00, 'Per SF of wall face area'), # WATERPROOFING / WRAP (r'house\s*wrap|wrb|weather\s*barrier|building\s*wrap', '07','Building Wrap / WRB', 'SF', 0.85, 0.10, 'Per SF of wall face area'), (r'water\s*proof|waterproof', '07','Waterproofing Membrane', 'SF', 8.50, 0.05, 'Per SF of wall face area'), # SIDING / CLADDING (r'siding|vinyl\s*sid|hardi|cement\s*board|fiber\s*cement', '07','Fiber Cement / Siding', 'SF', 14.00, 0.10, 'Per SF of wall face area'), (r'stucco', '07','Stucco Finish', 'SF', 18.00, 0.10, 'Per SF of wall face area'), # CONCRETE (wall type) (r'concrete|conc|cip', '03','Concrete Wall (CIP)', 'CY',1050.00, 0.05, 'Volume from model CY'), # FOUNDATION (r'foundation|found|ftg|footing', '03','Foundation Wall / Footing', 'CY', 850.00, 0.05, 'Volume from model CY'), # SLAB types (for slab composites) (r'slab.*on.*grade|sog|on\s*grade', '03','Concrete Slab on Grade', 'CY', 850.00, 0.08, 'Volume from model CY'), (r'elevated.*slab|slab.*elevated', '03','Concrete Elevated Slab', 'CY',1100.00, 0.08, 'Volume from model CY'), (r'topping\s*slab|topping', '03','Concrete Topping Slab', 'CY', 750.00, 0.05, 'Volume from model CY'), # ROOFING types (for roof composites) (r'tpo|single.*ply|membrane.*roof', '07','Roofing TPO 60mil', 'SF', 12.00, 0.10, 'Per SF of roof area'), (r'shingle|asphalt.*shingle', '07','Roofing Asphalt Shingle', 'SF', 6.50, 0.10, 'Per SF of roof area'), (r'standing\s*seam|metal.*roof|roof.*metal', '07','Roofing Standing Seam Metal','SF', 28.00, 0.10, 'Per SF of roof area'), (r'mod.*bit|modified.*bitumen', '07','Roofing Modified Bitumen', 'SF', 14.00, 0.10, 'Per SF of roof area'), # FLOOR / SLAB FINISH (r'tile|ceramic|porcelain', '09','Flooring Ceramic/Porcelain Tile','SF',16.00,0.10,'Per SF of room/slab area'), (r'hardwood|wood\s*floor', '09','Flooring Hardwood', 'SF', 14.00, 0.08,'Per SF of room/slab area'), (r'carpet', '09','Flooring Carpet', 'SY', 42.00, 0.05,'Per SY of room area'), (r'lvp|luxury.*vinyl|vinyl.*plank', '09','Flooring LVP', 'SF', 8.50, 0.05,'Per SF of room/slab area'), (r'polish.*conc|conc.*polish|concrete.*floor', '09','Flooring Polished Concrete', 'SF', 6.00, 0.00,'Per SF of slab area'), ] def parse_composite(composite_name, wall_sf=0, slab_area=0, wall_lf=0, avg_height_ft=9.0, wall_vol_cy=0, slab_vol_cy=0, wall_type='Interior'): """ Given a composite name and geometry, return list of line item dicts: {csi, description, unit, quantity, unit_cost, waste, notes, source} """ name_lc = composite_name.lower() matched = [] seen_descs = set() for pattern, csi, desc, unit, cost, waste, notes in COMPOSITE_RULES: if not re.search(pattern, name_lc, re.IGNORECASE): continue if desc in seen_descs: continue seen_descs.add(desc) # Determine quantity based on unit and context qty = 0.0 if unit == 'EA': # Stud count: LF * (12/16 studs per foot) * (height/8 ft sections) qty = round(wall_lf * 0.75 * avg_height_ft / 8, 0) elif unit == 'SF': qty = round(wall_sf, 2) elif unit == 'SY': qty = round(slab_area / 9, 2) if slab_area else round(wall_sf / 9, 2) elif unit == 'CY': qty = round(wall_vol_cy or slab_vol_cy, 3) elif unit == 'LF': qty = round(wall_lf, 2) if qty == 0: qty = 0.0 # Still include as yellow input cell matched.append({ 'csi': csi, 'description': desc, 'unit': unit, 'quantity': qty, 'unit_cost': cost, 'waste': waste, 'notes': notes, 'source': composite_name, }) return matched # =========================================================================== # 4. Extractor (same verified pattern as v5) # =========================================================================== class Extractor: def __init__(self,conn,pf): self.acc=conn.commands; self.pf=pf self.walls=[]; self.slabs=[]; self.roofs=[] self.doors=[]; self.windows=[]; self.zones=[]; self.objects=[] def run(self): print("\n Extracting elements...") for t,fn in [('Wall',self._walls),('Slab',self._slabs),('Roof',self._roofs), ('Door',self._doors),('Window',self._windows), ('Zone',self._zones),('Object',self._objects)]: try: fn() except Exception as e: print(f" [{t}] error: {e}") print(" Done.\n") def _elems(self,t): try: return self.acc.GetElementsByType(t) or [] except Exception as e: print(f" GetElementsByType({t}): {e}"); return [] def _walls(self): print(" Walls...",end=" ",flush=True) elems=self._elems('Wall') if not elems: print("none."); return spec={ 'id': self.pf.bi('General_ElementID'), 'comp': self.pf.bi('Construction_CompositeName'), 'pos': self.pf.bi('Category_Position'), 'len': self.pf.bi('Wall_ReferenceLineLength'), 'avg_len': self.pf.bi('Wall_AverageLength'), 'height': self.pf.bi('Wall_MaximumHeight'), 'thick': self.pf.bi('Wall_EndThickness'), 'surf_ci': self.pf.bi('Wall_ConditionalInsideSurfaceArea'), 'surf_co': self.pf.bi('Wall_ConditionalOutsideSurfaceArea'), 'surf_ni': self.pf.bi('Wall_NetInsideSurfaceArea'), 'surf_no': self.pf.bi('Wall_NetOutsideSurfaceArea'), 'surf_gi': self.pf.bi('Wall_GrossInsideSurfaceArea'), 'surf_go': self.pf.bi('Wall_GrossOutsideSurfaceArea'), 'vol_in': self.pf.bi('Wall_InsideSkinVolume'), 'vol_out': self.pf.bi('Wall_OutsideSkinVolume'), } vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): ei=str(v.get('id','')); comp=clean(v.get('comp','')) or ei or 'Wall' pos=str(v.get('pos','')) pl=pos.lower() if 'exterior' in pl: wt='Exterior' elif 'foundation' in pl: wt='Foundation' elif 'interior' in pl: wt='Interior' else: c2=(ei+comp).lower() if any(k in c2 for k in ['ext','exterior','facade']): wt='Exterior' elif any(k in c2 for k in ['found','ftg','grade']): wt='Foundation' elif any(k in c2 for k in ['int','interior','partition']): wt='Interior' else: wt='Unclassified' length_m=flt(v.get('len')) or flt(v.get('avg_len')) height_m=flt(v.get('height')); thick_m=flt(v.get('thick')) ins=flt(v.get('surf_ci')) or flt(v.get('surf_ni')) or flt(v.get('surf_gi')) outs=flt(v.get('surf_co')) or flt(v.get('surf_no')) or flt(v.get('surf_go')) surf=(ins+outs)/2. if (ins and outs) else max(ins,outs) vol=flt(v.get('vol_in'))+flt(v.get('vol_out')) self.walls.append({'guid':str(elem.elementId.guid),'id':ei,'composite':comp, 'type':wt,'length_m':length_m,'height_m':height_m, 'thick_m':thick_m,'surf_m2':surf,'vol_m3':vol}) print(f"{len(self.walls)}.") def _slabs(self): print(" Slabs...",end=" ",flush=True) elems=self._elems('Slab') if not elems: print("none."); return spec={'id':self.pf.bi('General_ElementID'), 'comp':self.pf.bi('Construction_CompositeName'), 'pos':self.pf.bi('Category_Position'), 'area_g':self.pf.bi('Slab_GrossTopSurfaceArea'), 'area_t':self.pf.bi('Slab_TopSurfaceArea'), 'perim':self.pf.bi('Slab_Perimeter'), 'thick':self.pf.bi('Slab_Thickness'), 'vol_n':self.pf.bi('Slab_NetVolume'), 'vol_g':self.pf.bi('Slab_GrossVolume')} vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): area=flt(v.get('area_g')) or flt(v.get('area_t')) vol=flt(v.get('vol_n')) or flt(v.get('vol_g')) comp=clean(v.get('comp','')) or str(v.get('id','')) or 'Slab' self.slabs.append({'guid':str(elem.elementId.guid),'id':str(v.get('id','')), 'composite':comp,'position':str(v.get('pos','Unknown')), 'area_m2':area,'vol_m3':vol, 'thick_m':flt(v.get('thick')),'perim_m':flt(v.get('perim'))}) print(f"{len(self.slabs)}.") def _roofs(self): print(" Roofs...",end=" ",flush=True) elems=self._elems('Roof') if not elems: print("none."); return spec={'id':self.pf.bi('General_ElementID'), 'comp':self.pf.bi('Construction_CompositeName'), 'area_n':self.pf.bi('Roof_NetTopSurfaceArea'), 'area_g':self.pf.bi('Roof_GrossTopSurfaceArea'), 'perim':self.pf.bi('Roof_Perimeter'), 'vol_n':self.pf.bi('Roof_NetVolume'), 'vol_g':self.pf.bi('Roof_GrossVolume'), 'thick':self.pf.bi('Roof_Thickness')} vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): area=flt(v.get('area_n')) or flt(v.get('area_g')) vol=flt(v.get('vol_n')) or flt(v.get('vol_g')) comp=clean(v.get('comp','')) or str(v.get('id','')) or 'Roof' self.roofs.append({'guid':str(elem.elementId.guid),'id':str(v.get('id','')), 'composite':comp,'area_m2':area,'vol_m3':vol, 'perim_m':flt(v.get('perim')),'thick_m':flt(v.get('thick'))}) print(f"{len(self.roofs)}.") def _opening(self,t,store): elems=self._elems(t) if not elems: print("none."); return spec={'id':self.pf.bi('General_ElementID'), 'lib':self.pf.bi('General_LibraryPartName'), 'pos':self.pf.bi('Category_Position'), 'area_g':self.pf.bi('WindowDoor_GrossSurfaceArea'), 'area_s':self.pf.bi('WindowDoor_SurfaceArea'), 'wh':self.pf.bi('WindowDoor_WHSize'), 'nom_w':self.pf.bi('WindowDoor_NominalRevealSideWidth'), 'nom_h':self.pf.bi('WindowDoor_NominalRevealSideHeight')} vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): name=str(v.get('lib','')) or str(v.get('id','')) or t area=flt(v.get('area_g')) or flt(v.get('area_s')) w_m=flt(v.get('nom_w')); h_m=flt(v.get('nom_h')) if w_m==0 or h_m==0: wh=str(v.get('wh','')) nums=re.findall(r"[\d.]+",wh) if len(nums)>=2: try: a,b=float(nums[0]),float(nums[1]) w_m=a*(0.3048 if a<30 else 0.0254) h_m=b*(0.3048 if b<30 else 0.0254) except: pass if area==0 and w_m>0 and h_m>0: area=w_m*h_m # Size label: convert to feet-inches def m_to_ft_in(m): if m<=0: return '?' total_in=m*IN_PER_M ft=int(total_in//12); inch=round(total_in%12) if inch==12: ft+=1; inch=0 return f"{ft}'-{inch}\"" if ft>0 else f'{inch}"' size_label=f"{m_to_ft_in(w_m)} x {m_to_ft_in(h_m)}" if (w_m>0 and h_m>0) else "size unknown" store.append({'guid':str(elem.elementId.guid),'id':str(v.get('id','')), 'name':name,'pos':str(v.get('pos','Unknown')), 'w_m':w_m,'h_m':h_m,'area_m2':area,'size_label':size_label}) def _doors(self): print(" Doors...",end=" ",flush=True) self._opening('Door',self.doors); print(f"{len(self.doors)}.") def _windows(self): print(" Windows...",end=" ",flush=True) self._opening('Window',self.windows); print(f"{len(self.windows)}.") def _zones(self): print(" Zones...",end=" ",flush=True) elems=self._elems('Zone') if not elems: print("none."); return spec={'id':self.pf.bi('General_ElementID'), 'name':self.pf.bi('Zone_ZoneName'), 'num':self.pf.bi('Zone_ZoneNumber'), 'area_c':self.pf.bi('Zone_CalculatedArea'), 'area_m':self.pf.bi('Zone_MeasuredArea'), 'area_n':self.pf.bi('Zone_NetArea'), 'perim':self.pf.bi('Zone_Perimeter'), 'wall_sa':self.pf.bi('Zone_WallsSurfaceArea'), 'int_h':self.pf.ud('SUSPENDED CEILING LEVEL (Expression)','Interior height')} vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): name=(str(v.get('name','')) or str(v.get('id','')) or 'Room').strip() area=flt(v.get('area_c')) or flt(v.get('area_m')) or flt(v.get('area_n')) self.zones.append({'guid':str(elem.elementId.guid),'id':str(v.get('id','')), 'name':name,'number':str(v.get('num','')), 'area_m2':area,'perim_m':flt(v.get('perim')), 'height_m':flt(v.get('int_h')),'wall_m2':flt(v.get('wall_sa'))}) print(f"{len(self.zones)}.") def _objects(self): print(" Objects...",end=" ",flush=True) elems=self._elems('Object') if not elems: print("none."); return spec={'id':self.pf.bi('General_ElementID'), 'lib':self.pf.bi('General_LibraryPartName'), 'pos':self.pf.bi('Category_Position')} vals=self.pf.fetch(elems,spec) for elem,v in zip(elems,vals): self.objects.append({'guid':str(elem.elementId.guid), 'id':str(v.get('id','')),'pos':str(v.get('pos','')), 'name':str(v.get('lib','')) or str(v.get('id','')) or 'Object'}) print(f"{len(self.objects)}.") # =========================================================================== # 5. Takeoff calculations # =========================================================================== class Takeoff: def __init__(self,ex:Extractor): self.e=ex def walls(self): g=defaultdict(lambda:{'ct':0,'len':0.,'hts':[],'surf':0.,'vol':0.}) for w in self.e.walls: k=(w['type'],w['composite']) g[k]['ct']+=1; g[k]['len']+=w['length_m'] g[k]['hts'].append(w['height_m']) g[k]['surf']+=w['surf_m2']; g[k]['vol']+=w['vol_m3'] rows=[] for (wt,comp),d in sorted(g.items()): ah=sum(d['hts'])/len(d['hts']) if d['hts'] else 0 lf=d['len']*M_TO_FT; sf=d['surf']*M2_TO_SF; hft=ah*M_TO_FT rows.append({'Wall Type':wt,'Wall Name':comp,'Count':d['ct'], 'Total LF':round(lf,2),'Avg Height (ft)':round(hft,2), 'Gross Wall SF':round(sf,2),'Volume (CY)':round(d['vol']*M3_TO_CY,3), 'Est. Studs 16" OC':round(lf*0.75*hft/8,0), 'Plates LF (4x)':round(lf*4,1)}) return rows def gypsum(self): rows=[] wg=defaultdict(lambda:{'ext':0.,'int':0.,'fnd':0.}) for w in self.e.walls: k=(w['type'],w['composite']); sf=w['surf_m2']*M2_TO_SF if w['type']=='Exterior': wg[k]['ext']+=sf elif w['type']=='Foundation': wg[k]['fnd']+=sf else: wg[k]['int']+=sf for (wt,comp),d in sorted(wg.items()): total=d['int']*2+d['ext']+d['fnd'] if total>0: rows.append({'Location':'Walls','Wall Type':wt,'Description':comp, 'Area SF':round(total,2),'Notes':'Int=both faces, Ext/Fnd=inside face'}) ceil=sum(s['area_m2']*M2_TO_SF for s in self.e.slabs) if ceil>0: rows.append({'Location':'Ceilings','Wall Type':'-', 'Description':'Ceiling GWB (from slab top area)', 'Area SF':round(ceil,2),'Notes':'Slab_GrossTopSurfaceArea'}) return rows def slabs(self): g=defaultdict(lambda:{'ct':0,'area':0.,'vol':0.,'thick':0.}) for s in self.e.slabs: k=(s['position'],s['composite']) g[k]['ct']+=1; g[k]['area']+=s['area_m2'] g[k]['vol']+=s['vol_m3']; g[k]['thick']+=s['thick_m'] rows=[] for (pos,comp),d in sorted(g.items()): at=d['thick']/d['ct'] if d['ct'] else 0 vol=d['vol']*M3_TO_CY if vol==0 and d['area']>0 and at>0: vol=(d['area']*at)*M3_TO_CY rows.append({'Position':pos,'Description':comp,'Count':d['ct'], 'Area SF':round(d['area']*M2_TO_SF,2), 'Avg Thickness (in)':round(at*IN_PER_M,2) if at else '', 'Volume CY':round(vol,3) if vol else '(verify)'}) return rows def roofs(self): g=defaultdict(lambda:{'ct':0,'area':0.,'vol':0.,'perim':0.}) for r in self.e.roofs: g[r['composite']]['ct']+=1; g[r['composite']]['area']+=r['area_m2'] g[r['composite']]['vol']+=r['vol_m3']; g[r['composite']]['perim']+=r['perim_m'] return [{'Description':c,'Count':d['ct'], 'Area SF':round(d['area']*M2_TO_SF,2), 'Perimeter LF':round(d['perim']*M_TO_FT,2), 'Volume CY':round(d['vol']*M3_TO_CY,3)} for c,d in sorted(g.items())] def openings(self): """Group doors/windows by name + size + position.""" rows=[] for etype,store in [('Door',self.e.doors),('Window',self.e.windows)]: g=defaultdict(lambda:{'ct':0,'area':0.,'w_m':0.,'h_m':0.}) for item in store: k=(item['name'],item['size_label'],item['pos']) g[k]['ct']+=1; g[k]['area']+=item['area_m2'] g[k]['w_m']=item['w_m']; g[k]['h_m']=item['h_m'] for (name,size,pos),d in sorted(g.items()): rows.append({'Type':etype,'Name':name,'Size':size,'Position':pos, 'Count':d['ct'],'Total SF':round(d['area']*M2_TO_SF,2)}) return rows def zones(self): return [{'Room Number':z['number'],'Room Name':z['name'], 'Area SF':round(z['area_m2']*M2_TO_SF,2), 'Perimeter LF':round(z['perim_m']*M_TO_FT,2), 'Height (ft)':round(z['height_m']*M_TO_FT,2) if z['height_m'] else '', 'Wall SA SF':round(z['wall_m2']*M2_TO_SF,2) if z['wall_m2'] else ''} for z in sorted(self.e.zones,key=lambda x:x['name'])] def objects(self): g=defaultdict(lambda:{'ct':0,'pos':set()}) for o in self.e.objects: g[o['name']]['ct']+=1; g[o['name']]['pos'].add(o['pos']) return [{'Name':n,'Count':d['ct'],'Positions':', '.join(sorted(d['pos']))} for n,d in sorted(g.items())] def composite_line_items(self, wall_rows, slab_rows, roof_rows): """ For each unique composite found in walls/slabs/roofs, parse the name and generate individual material line items for the summary. Returns list of line item dicts ready for the summary tab. """ # Aggregate geometry by composite wall_by_comp = defaultdict(lambda:{'sf':0.,'lf':0.,'hts':[],'vol_cy':0.,'type':'Interior'}) for w in self.e.walls: k=w['composite'] wall_by_comp[k]['sf']+=w['surf_m2']*M2_TO_SF wall_by_comp[k]['lf']+=w['length_m']*M_TO_FT wall_by_comp[k]['hts'].append(w['height_m']*M_TO_FT) wall_by_comp[k]['vol_cy']+=w['vol_m3']*M3_TO_CY wall_by_comp[k]['type']=w['type'] slab_by_comp = defaultdict(lambda:{'sf':0.,'vol_cy':0.}) for s in self.e.slabs: slab_by_comp[s['composite']]['sf']+=s['area_m2']*M2_TO_SF slab_by_comp[s['composite']]['vol_cy']+=s['vol_m3']*M3_TO_CY roof_by_comp = defaultdict(lambda:{'sf':0.,'vol_cy':0.}) for r in self.e.roofs: roof_by_comp[r['composite']]['sf']+=r['area_m2']*M2_TO_SF roof_by_comp[r['composite']]['vol_cy']+=r['vol_m3']*M3_TO_CY # Collect all line items, deduplicating by (csi, description) all_items = {} # (csi, desc) -> item dict (sum quantities) for comp, d in wall_by_comp.items(): ah=sum(d['hts'])/len(d['hts']) if d['hts'] else 9.0 items=parse_composite(comp,wall_sf=d['sf'],wall_lf=d['lf'], avg_height_ft=ah,wall_vol_cy=d['vol_cy'], wall_type=d['type']) for item in items: key=(item['csi'],item['description']) if key not in all_items: all_items[key]=item.copy() else: all_items[key]['quantity']+=item['quantity'] for comp, d in slab_by_comp.items(): items=parse_composite(comp,slab_area=d['sf'],slab_vol_cy=d['vol_cy']) for item in items: key=(item['csi'],item['description']) if key not in all_items: all_items[key]=item.copy() else: all_items[key]['quantity']+=item['quantity'] for comp, d in roof_by_comp.items(): items=parse_composite(comp,wall_sf=d['sf'],slab_vol_cy=d['vol_cy']) for item in items: key=(item['csi'],item['description']) if key not in all_items: all_items[key]=item.copy() else: all_items[key]['quantity']+=item['quantity'] return sorted(all_items.values(), key=lambda x:(x['csi'],x['description'])) # =========================================================================== # 6. Full CSI line item table (static + dynamic from composites) # =========================================================================== # Static line items always on the estimate regardless of model. # These are organized by CSI division. # Format: (csi_div_str, description, unit, default_qty, unit_cost, waste, notes) # default_qty=None means it will be filled from model data or left as yellow input. STATIC_LINE_ITEMS = [ # 02 - Existing Conditions ('02','Site Clearing / Grubbing', 'SY', None, 1.50, 0.05, 'Enter site area SY'), ('02','Demolition - Interior Selective', 'SF', None, 4.00, 0.00, 'Enter demolition SF'), ('02','Earthwork - Excavation', 'CY', None,18.00, 0.05, 'Enter CY from civil'), ('02','Earthwork - Backfill Compacted', 'CY', None,22.00, 0.10, 'Select fill CY'), ('02','Erosion Control - Silt Fence', 'LF', None, 3.50, 0.00, 'Perimeter LF'), # 03 - Concrete (dynamic from composites + static fallbacks) ('03','Rebar - Slab #4 @ 12" EW BW', 'Ton', None,2100.00,0.03,'Per ton, est 0.18t/CY'), ('03','Rebar - Structural', 'Ton', None,2400.00,0.03,'Per ton'), ('03','Concrete Pump Surcharge', 'CY', None, 22.00,0.00,'Pumped pours add-on'), ('03','Concrete - Footings', 'CY', None, 780.00,0.05,'Spread footings'), ('03','Concrete - Grade Beams', 'CY', None, 950.00,0.05,'Incl. forms'), ('03','Anchor Bolts / Embeds', 'LS', None, 1.00,0.00,'Allowance - enter $'), # 04 - Masonry ('04','CMU 8" Standard Block', 'SF', None, 22.00,0.05,'Mortar + labor'), ('04','CMU Filled / Reinforced', 'SF', None, 28.00,0.05,'Full grout + #5 bar'), ('04','Masonry Mortar / Grout', 'CY', None, 350.00,0.10,'Incl. in CMU unit cost'), # 05 - Metals ('05','Structural Steel - Beams / Cols', 'Ton', None,4500.00,0.02,'Fab + erect'), ('05','Metal Deck - Floor 2"', 'SF', None, 8.50,0.05,'18 ga painted'), ('05','Metal Deck - Roof 3"', 'SF', None, 7.50,0.05,'22 ga painted'), ('05','Steel Joists', 'Ton', None,3800.00,0.02,'Open web, fab + erect'), ('05','Metal Track - Top & Bottom', 'LF', None, 1.85,0.10,'For metal stud walls'), ('05','Guard Rail / Handrail - Steel', 'LF', None, 95.00,0.00,'Pipe rail painted'), # 06 - Wood & Plastics ('06','Lumber - Plates (top + bottom)', 'LF', None, 2.00,0.05,'4x total wall LF'), ('06','Lumber - Headers', 'LF', None, 8.50,0.10,'Door/window headers'), ('06','Lumber - Blocking / Misc', 'MBF', None, 850.00,0.15,'Per thousand board ft'), ('06','Plywood Roof Sheathing 5/8"', 'SF', None, 4.50,0.10,'Per SF of roof area'), ('06','Finish Carpentry - Base Molding', 'LF', None, 5.50,0.05,'Painted wood base'), ('06','Finish Carpentry - Crown Molding', 'LF', None, 9.50,0.08,'Painted wood crown'), ('06','Finish Carpentry - Door Casing', 'LF', None, 6.50,0.05,'Per side per door'), ('06','Finish Carpentry - Window Casing', 'LF', None, 6.50,0.05,'Per side per window'), ('06','Casework - Base Cabinets', 'LF', None, 280.00,0.05,'Standard grade'), ('06','Casework - Upper Cabinets', 'LF', None, 195.00,0.05,'Standard grade'), ('06','Casework - Closet Shelving', 'LF', None, 45.00,0.05,'Melamine wire shelf'), ('06','Casework - Linen / Pantry', 'EA', None, 850.00,0.00,'Allowance per unit'), ('06','Countertops - Laminate', 'LF', None, 95.00,0.05,'Post-form standard'), ('06','Countertops - Quartz / Stone', 'LF', None, 185.00,0.05,'Fabricated installed'), ('06','Rough Carpentry - Misc Blocking', 'MBF', None, 850.00,0.15,'Fire blocking misc'), # 07 - Thermal & Moisture ('07','Batt Insulation - Foundation Wall', 'SF', None, 1.45,0.05,'R-13 fiberglass'), ('07','Perimeter Sealant - Caulk', 'LF', None, 4.50,0.10,'Backer rod + sealant'), ('07','Roof Underlayment / Ice Shield', 'SF', None, 0.85,0.10,'Per SF of roof area'), # 08 - Openings (dynamic from model - door/window line items added below) ('08','Door Hardware - Passage Sets', 'EA', None, 185.00,0.00,'Per interior door'), ('08','Door Hardware - Privacy Sets', 'EA', None, 195.00,0.00,'Per bath/bed door'), ('08','Door Hardware - Entry Sets', 'EA', None, 450.00,0.00,'Per exterior door'), ('08','Door Threshold / Weatherstrip', 'EA', None, 85.00,0.00,'Per exterior door'), ('08','Window Screen', 'EA', None, 65.00,0.00,'Per operable window'), # 09 - Finishes ('09','Paint - Exterior Walls', 'SF', None, 1.85,0.05,'Primer + 2 coats'), ('09','Paint - Doors / Frames', 'EA', None, 85.00,0.00,'2 sides + frame'), ('09','Paint - Trim / Molding', 'LF', None, 1.25,0.05,'Base, crown, casing'), ('09','Flooring - VCT', 'SF', None, 5.50,0.05,'12x12 standard'), ('09','Flooring - LVP', 'SF', None, 8.50,0.05,'6mm click-lock'), ('09','Flooring - Hardwood', 'SF', None, 14.00,0.08,'3/4" solid or eng.'), ('09','Flooring - Carpet Glue-Down', 'SY', None, 42.00,0.05,'28 oz commercial'), ('09','Flooring - Ceramic / Porcelain Tile','SF', None, 16.00,0.10,'12x24 commercial'), ('09','Tile - Wall Ceramic', 'SF', None, 22.00,0.10,'10x13 field tile'), ('09','Tile - Shower / Tub Surround', 'SF', None, 28.00,0.10,'Waterproofed substrate'), ('09','Tile - Backsplash', 'SF', None, 35.00,0.10,'Kitchen / bath'), ('09','Tile Setting Material / Grout', 'SF', None, 3.50,0.10,'Incl. in tile cost'), ('09','Flooring - Polished Concrete', 'SF', None, 6.00,0.00,'Grind + 800 grit'), ('09','Acoustic Ceiling - 2x4 Grid', 'SF', None, 7.50,0.05,'Armstrong equiv.'), ('09','Epoxy Floor Coating', 'SF', None, 4.50,0.05,'Garage / utility'), # 10 - Specialties ('10','Toilet Partitions', 'EA', None,1100.00,0.00,'HDPE standard'), ('10','Fire Extinguisher + Cabinet', 'EA', None, 450.00,0.00,'10 lb ABC'), ('10','Bathroom Accessories Set', 'EA', None, 385.00,0.00,'TP, towel, mirror, etc.'), ('10','Shower Door / Enclosure', 'EA', None, 850.00,0.00,'Frameless glass'), ('10','Blinds / Window Treatment', 'EA', None, 185.00,0.00,'Per window'), ('10','Attic Access Hatch', 'EA', None, 285.00,0.00,'Insulated, pull-down'), ('10','Signage Allowance', 'LS', None, 1.00,0.00,'Enter allowance $'), # 11 - Equipment ('11','Kitchen Appliance Allowance', 'LS', None, 1.00,0.00,'Enter allowance $'), ('11','Washer / Dryer Connections', 'EA', None, 385.00,0.00,'Rough-in + connections'), # 14 - Conveying (elevators) ('14','Elevator - Hydraulic (allow)', 'EA', None, 1.00,0.00,'Enter allowance $'), # 21 - Fire Suppression ('21','Fire Sprinkler System - Commercial', 'SF', None, 6.50,0.00,'Wet pipe, NFPA 13 — commercial/institutional'), ('21','Fire Sprinkler System - Multifamily','SF', None, 4.25,0.00,'NFPA 13R — multifamily 4 stories and under'), ('21','Fire Sprinkler System - Residential','SF', None, 3.25,0.00,'NFPA 13D — one- and two-family residential'), # 22 - Plumbing ('22','Plumbing Rough-In (per fixture)', 'EA', None,1800.00,0.00,'Per fixture unit'), ('22','Fixture - Water Closet Standard', 'EA', None,1200.00,0.00,'Floor mount, incl. seat'), ('22','Fixture - Lavatory / Sink', 'EA', None, 950.00,0.00,'Drop-in or U/M'), ('22','Fixture - Tub / Shower', 'EA', None,1850.00,0.00,'Alcove or shower'), ('22','Fixture - Kitchen Sink', 'EA', None,1100.00,0.00,'SS double bowl'), ('22','Water Heater - Tank Gas', 'EA', None,2200.00,0.00,'50 gal'), ('22','Water Heater - Tankless', 'EA', None,3500.00,0.00,'Gas on-demand'), ('22','Exterior Hose Bibs', 'EA', None, 285.00,0.00,'Per bib'), ('22','Floor Drain', 'EA', None, 485.00,0.00,'Cast iron'), # 23 - HVAC ('23','HVAC - RTU per Ton', 'Ton', None,2800.00,0.00,'Rooftop packaged'), ('23','HVAC - Ductwork per SF', 'SF', None, 12.00,0.00,'Flexible + sheet metal'), ('23','HVAC - Mini-Split per Zone', 'EA', None,4200.00,0.00,'12,000 BTU'), ('23','HVAC - Exhaust Fan - Bath', 'EA', None, 385.00,0.00,'Per fan'), ('23','HVAC - Range Hood', 'EA', None, 650.00,0.00,'Ducted'), # 26 - Electrical ('26','Electrical - Service & Distribution','SF', None, 8.00,0.00,'Panel + service'), ('26','Electrical - Lighting', 'SF', None, 5.50,0.00,'LED standard'), ('26','Electrical - Power + Devices', 'SF', None, 6.00,0.00,'Duplex + GFCI'), ('26','Electrical - Fire Alarm System', 'SF', None, 3.50,0.00,'Addressable'), ('26','Electrical - Low Voltage / Data', 'SF', None, 2.50,0.00,'Cat6 + coax'), # GC / Overhead ('GC','General Conditions', 'PCT', None, 0.08,0.00,'8% of direct cost'), ('GC','Overhead & Profit', 'PCT', None, 0.10,0.00,'Contractor O&P'), ('GC','Design Contingency', 'PCT', None, 0.10,0.00,'Pre-CD allowance'), ('GC','Escalation Allowance', 'PCT', None, 0.03,0.00,'Annual escalation'), ] CSI_NAMES = { '02':'Existing Conditions / Site','03':'Concrete','04':'Masonry', '05':'Metals','06':'Wood, Plastics & Composites', '07':'Thermal & Moisture Protection','08':'Openings', '09':'Finishes','10':'Specialties','11':'Equipment', '14':'Conveying','21':'Fire Suppression','22':'Plumbing', '23':'HVAC','26':'Electrical','GC':'General Conditions & Overhead', } # =========================================================================== # 7. Excel builder # =========================================================================== class Builder: def __init__(self,pn): self.wb=openpyxl.Workbook(); self.pn=pn self.wb.remove(self.wb.active) def _pt(self,ws,title,sub): pt(ws,title,sub,self.pn) # ------------------------------------------------------------------ def summary_tab(self, wall_rows, gyp_rows, slab_rows, roof_rows, open_rows, zone_rows, composite_items): ws=self.wb.create_sheet("00 - SUMMARY",0) ws.sheet_view.showGridLines=False ws.sheet_properties.tabColor="1F3864" ws.row_dimensions[1].height=30 c=ws.cell(row=1,column=1, value=f"CONSTRUCTION ESTIMATE SUMMARY | {self.pn.upper()}") c.font=Font(name=FNT,bold=True,size=16,color=DARK) ts=ws.cell(row=2,column=1, value=f"Generated: {datetime.now().strftime('%B %d, %Y %H:%M')} " f"| For estimating purposes only.") ts.font=Font(name=FNT,size=8,italic=True,color="888888") wh(ws,4,["CSI Division","Description","Unit","Quantity", "Unit Cost","Waste Factor","Adj. Quantity","Total Cost"], bg=DARK,fg=WHT) ws.row_dimensions[4].height=28 # Pre-calculate model quantities t_lf=sum(r["Total LF"] for r in wall_rows) ext_lf=sum(r["Total LF"] for r in wall_rows if r["Wall Type"]=="Exterior") int_lf=sum(r["Total LF"] for r in wall_rows if r["Wall Type"]=="Interior") fnd_lf=sum(r["Total LF"] for r in wall_rows if r["Wall Type"]=="Foundation") g_sf=sum(r["Gross Wall SF"] for r in wall_rows) ext_sf=sum(r["Gross Wall SF"] for r in wall_rows if r["Wall Type"]=="Exterior") int_sf=sum(r["Gross Wall SF"] for r in wall_rows if r["Wall Type"]=="Interior") w_gyp=sum(r["Area SF"] for r in gyp_rows if r["Location"]=="Walls") c_gyp=sum(r["Area SF"] for r in gyp_rows if r["Location"]=="Ceilings") sl_sf=sum(r["Area SF"] for r in slab_rows if isinstance(r["Area SF"],(int,float))) rf_sf=sum(r["Area SF"] for r in roof_rows) d_ct=sum(r["Count"] for r in open_rows if r["Type"]=="Door") w_ct=sum(r["Count"] for r in open_rows if r["Type"]=="Window") w_sf=sum(r["Total SF"] for r in open_rows if r["Type"]=="Window") z_sf=sum(r["Area SF"] for r in zone_rows if isinstance(r["Area SF"],(int,float))) hts=[r["Avg Height (ft)"] for r in wall_rows if r["Avg Height (ft)"]>0] ah=sum(hts)/len(hts) if hts else 9.0 area=z_sf or sl_sf # best occupied SF # Build the full set of line items for the summary # Start with static items, inject model quantities where known static_qty_overrides = { # (csi, description): quantity ('06','Lumber - Plates (top + bottom)'): round(t_lf*4,1), ('07','Roof Underlayment / Ice Shield'): round(rf_sf,1), ('09','Paint - Exterior Walls'): round(ext_sf,2), ('21','Fire Sprinkler System - Commercial'):round(area,1), ('21','Fire Sprinkler System - Multifamily'):round(area,1), ('21','Fire Sprinkler System - Residential'):round(area,1), ('23','HVAC - Ductwork per SF'): round(area,1), ('26','Electrical - Service & Distribution'):round(area,1), ('26','Electrical - Lighting'): round(area,1), ('26','Electrical - Power + Devices'): round(area,1), ('26','Electrical - Fire Alarm System'): round(area,1), ('26','Electrical - Low Voltage / Data'): round(area,1), } # Build door/window line items from model (name+size+position) door_window_items = [] for r in open_rows: csi='08' unit='EA' if r['Type']=='Door' else 'SF' qty=float(r['Count']) if r['Type']=='Door' else r['Total SF'] # Cost by type/size name=r['Name'].lower() size=r['Size'] pos=r['Position'] if r['Type']=='Door': if 'ext' in pos.lower() or 'exterior' in name: cost=1800.00 if 'double' in name or 'dbl' in name else 1200.00 else: cost=950.00 else: # Window cost=85.00 # $/SF RO door_window_items.append({ 'csi':csi, 'description':f"{r['Type']} - {r['Name']} ({size}) [{pos}]", 'unit':unit,'quantity':qty,'unit_cost':cost, 'waste':0.00,'notes':f"{r['Count']} units", 'source':'model' }) # Combine: composite items + door/window items + static items # Group all by CSI div and write in order all_by_div = defaultdict(list) # Composite-derived items (from wall/slab/roof names) for item in composite_items: all_by_div[item['csi']].append(item) # Door/window items for item in door_window_items: all_by_div[item['csi']].append(item) # Static items (skip if same description already added by composite parser) existing_descs_by_div = {div: {i['description'] for i in items} for div, items in all_by_div.items()} for (csi,desc,unit,default_qty,cost,waste,notes) in STATIC_LINE_ITEMS: if desc in existing_descs_by_div.get(csi,set()): continue # already added by composite parser qty=static_qty_overrides.get((csi,desc), default_qty) all_by_div[csi].append({'csi':csi,'description':desc,'unit':unit, 'quantity':qty,'unit_cost':cost, 'waste':waste,'notes':notes,'source':'static'}) # Paint from GWB for wall_type in ['Walls','Ceilings']: sf_v=w_gyp if wall_type=='Walls' else c_gyp all_by_div['09'].append({ 'csi':'09','description':f"Paint - {wall_type} (2-coat)", 'unit':'SF','quantity':round(sf_v,1), 'unit_cost':1.45 if wall_type=='Walls' else 1.65, 'waste':0.05,'notes':'Primer + 2 finish coats','source':'model'}) # Base and crown molding from zone perimeters total_perim_lf=sum(r["Perimeter LF"] for r in zone_rows if isinstance(r.get("Perimeter LF"),(int,float))) if total_perim_lf>0: all_by_div['06'].append({ 'csi':'06','description':'Finish Carpentry - Base Molding', 'unit':'LF','quantity':round(total_perim_lf,1), 'unit_cost':5.50,'waste':0.05,'notes':'From room perimeters','source':'model'}) # GWB from gypsum calculations all_by_div['09'].insert(0,{'csi':'09','description':'GWB - Walls (all types)', 'unit':'SF','quantity':round(w_gyp,1), 'unit_cost':3.20,'waste':0.10, 'notes':'From wall face areas','source':'model'}) all_by_div['09'].insert(1,{'csi':'09','description':'GWB - Ceilings', 'unit':'SF','quantity':round(c_gyp,1), 'unit_cost':2.80,'waste':0.10, 'notes':'From slab top areas','source':'model'}) # Write to sheet in CSI order div_order=['02','03','04','05','06','07','08','09','10','11', '14','21','22','23','26','GC'] r=5; direct_rows=[]; cur_div="" for div in div_order: items=all_by_div.get(div,[]) if not items: continue # Division header if div!=cur_div: cur_div=div div_label=f"DIVISION {div if div=='GC' else f'0{div} 00 00'} | {CSI_NAMES.get(div,'')}" for col in range(1,9): hc(ws.cell(row=r,column=col),"",bg=MID) ws.cell(row=r,column=1,value=div_label) ws.cell(row=r,column=1).font=Font(name=FNT,bold=True,size=9,color=WHT) ws.merge_cells(start_row=r,start_column=1,end_row=r,end_column=8) ws.row_dimensions[r].height=18 r+=1 for item in items: alt=(r%2==0) fill=PatternFill("solid",fgColor=ALT if alt else WHT) csi_label=f"0{item['csi']} 00 00" if item['csi']!='GC' else 'GC' for col,val in [(1,csi_label),(2,item['description']),(3,item['unit'])]: c=ws.cell(row=r,column=col,value=val) c.font=Font(name=FNT,size=9); c.fill=fill; c.border=TB if col==3: c.alignment=Alignment(horizontal="center") qty=item.get('quantity') qc=ws.cell(row=r,column=4,value=qty if qty is not None else 0.0) if qty is None or qty==0.0: ic(qc,0.0,"#,##0.00") qc.fill=PatternFill("solid",fgColor=YLW) else: dc(qc,float(qty),alt); qc.number_format="#,##0.00" ic(ws.cell(row=r,column=5),item['unit_cost'],'"$"#,##0.00') ic(ws.cell(row=r,column=6),item['waste'],'0.0%') ql=get_column_letter(4); wl=get_column_letter(6); ul=get_column_letter(5) adj=ws.cell(row=r,column=7,value=f"={ql}{r}*(1+{wl}{r})") adj.font=Font(name=FNT,size=9); adj.number_format="#,##0.00" adj.border=TB; adj.fill=fill adj.alignment=Alignment(horizontal="right") al=get_column_letter(7) tot=ws.cell(row=r,column=8,value=f"={al}{r}*{ul}{r}") tot.font=Font(name=FNT,size=9); tot.number_format='"$"#,##0.00' tot.border=TB; tot.fill=fill tot.alignment=Alignment(horizontal="right") # GC percentage items refer to subtotal, not added to direct if div!='GC': direct_rows.append(f"H{r}") r+=1 # Subtotals r+=1; ds="+".join(direct_rows) if direct_rows else "0" def ttl(label,formula,bold=True): nonlocal r ws.row_dimensions[r].height=18 lc=ws.cell(row=r,column=7,value=label) lc.font=Font(name=FNT,bold=bold,size=9,color=DARK) lc.alignment=Alignment(horizontal="right") vc=ws.cell(row=r,column=8,value=formula) vc.font=Font(name=FNT,bold=bold,size=9,color=DARK) vc.number_format='"$"#,##0' vc.alignment=Alignment(horizontal="right") vc.border=Border(top=M_SIDE,bottom=M_SIDE) rn=r; r+=1; return rn sub =ttl("DIRECT CONSTRUCTION COST:",f"={ds}") gc =ttl("+ General Conditions (8%):",f"=H{sub}*0.08") op_ =ttl("+ Overhead & Profit (10%):",f"=(H{sub}+H{gc})*0.10") pre =ttl("SUBTOTAL BEFORE CONTINGENCY:",f"=H{sub}+H{gc}+H{op_}") cont=ttl("+ Design Contingency (10%):",f"=H{pre}*0.10") esc =ttl("+ Escalation (3%):",f"=H{pre}*0.03") ws.row_dimensions[r].height=26 for col in range(1,9): ws.cell(row=r,column=col).fill=PatternFill("solid",fgColor=DARK) gl=ws.cell(row=r,column=7,value="TOTAL PROJECT ESTIMATE:") gl.font=Font(name=FNT,bold=True,size=11,color=WHT) gl.alignment=Alignment(horizontal="right",vertical="center") gv=ws.cell(row=r,column=8,value=f"=H{pre}+H{cont}+H{esc}") gv.font=Font(name=FNT,bold=True,size=12,color=YLW) gv.number_format='"$"#,##0' gv.alignment=Alignment(horizontal="right",vertical="center") for cl,w in [("A",12),("B",52),("C",8),("D",14),("E",14),("F",12),("G",18),("H",16)]: ws.column_dimensions[cl].width=w ws.freeze_panes="A5" r+=2 for lbl,txt in [ ("Blue cells","User inputs / editable costs"), ("Yellow cells","Enter quantity - not available from model"), ("Black cells","Auto-calculated - do not edit"), ("White cells with data","Quantity pulled from BIM model"), ]: ws.cell(row=r,column=1,value=lbl).font=Font(name=FNT,bold=True,size=8) ws.cell(row=r,column=2,value=txt).font=Font(name=FNT,size=8,italic=True) r+=1 # ------------------------------------------------------------------ def ucl(self): ws=self.wb.create_sheet("01 - Unit Cost Library") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="2E75B6" self._pt(ws,"Unit Cost Library","Edit BLUE cells. Referenced by summary tab.") wh(ws,4,["CSI Div","Description","Unit","Unit Cost ($)","Waste Factor (%)","Notes"], bg=DARK,fg=WHT) items=[(i[0],i[1],i[2],i[4],i[5],i[6]) for i in STATIC_LINE_ITEMS if i[0]!='GC'] r=5 for i,(csi,desc,unit,cost,waste,notes) in enumerate(items): alt=(i%2==0); fill=PatternFill("solid",fgColor=ALT if alt else WHT) for col,val in [(1,f"0{csi} 00 00" if csi!='GC' else 'GC'),(2,desc),(3,unit)]: c=ws.cell(row=r,column=col,value=val) c.font=Font(name=FNT,size=9); c.fill=fill; c.border=TB if col==3: c.alignment=Alignment(horizontal="center") ic(ws.cell(row=r,column=4),cost,'"$"#,##0.00') ic(ws.cell(row=r,column=5),waste,'0.0%') cn=ws.cell(row=r,column=6,value=notes) cn.font=Font(name=FNT,size=9); cn.fill=fill; cn.border=TB r+=1 af(ws); ws.column_dimensions["B"].width=44 ws.column_dimensions["F"].width=30; ws.freeze_panes="A5" # ------------------------------------------------------------------ def walls_tab(self,rows): ws=self.wb.create_sheet("02 - Walls") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="70AD47" self._pt(ws,"Wall Takeoff","Grouped by wall type and composite name.") cols=["Wall Type","Wall Name","Count","Total LF","Avg Height (ft)", "Gross Wall SF","Volume (CY)",'Est. Studs 16" OC',"Plates LF (4x)"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: tot_line(ws,nr,4,sum(r["Total LF"] for r in rows)) tot_line(ws,nr,6,sum(r["Gross Wall SF"] for r in rows)) pr=nr+2 ws.cell(row=pr,column=1,value="SUMMARY BY TYPE:").font=Font(name=FNT,bold=True,size=9,color=DARK) pr+=1 for wtype in sorted(set(r["Wall Type"] for r in rows)): lf_v=sum(r["Total LF"] for r in rows if r["Wall Type"]==wtype) sf_v=sum(r["Gross Wall SF"] for r in rows if r["Wall Type"]==wtype) ct_v=sum(r["Count"] for r in rows if r["Wall Type"]==wtype) ws.cell(row=pr,column=1,value=f" {wtype}:").font=Font(name=FNT,size=9,bold=True) for col,val in [(3,ct_v),(4,round(lf_v,1)),(6,round(sf_v,1))]: c=ws.cell(row=pr,column=col,value=val); c.font=Font(name=FNT,size=9); c.number_format="#,##0.00" pr+=1 af(ws); ws.freeze_panes="A5" def gypsum_tab(self,rows): ws=self.wb.create_sheet("03 - Gypsum & Paint") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="FFC000" self._pt(ws,"Gypsum Board & Paint Takeoff","Wall and ceiling GWB SF. Paint SF = same surface.") cols=["Location","Wall Type","Description","Area SF","Notes"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: tot_line(ws,nr,4,sum(r["Area SF"] for r in rows)) pr=nr+2 ws.cell(row=pr,column=1,value="PAINT SUMMARY:").font=Font(name=FNT,bold=True,size=9,color=DARK) for loc in ["Walls","Ceilings"]: pr+=1; sf_v=sum(r["Area SF"] for r in rows if r["Location"]==loc) ws.cell(row=pr,column=1,value=f" {loc}:").font=Font(name=FNT,size=9) c=ws.cell(row=pr,column=4,value=round(sf_v,1)) c.font=Font(name=FNT,bold=True,size=9); c.number_format="#,##0.00" af(ws); ws.column_dimensions["E"].width=38; ws.freeze_panes="A5" def slabs_tab(self,rows): ws=self.wb.create_sheet("04 - Slabs") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="7F7F7F" self._pt(ws,"Slab Takeoff","Area SF and volume CY by composite type.") cols=["Position","Description","Count","Area SF","Avg Thickness (in)","Volume CY"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: tot_line(ws,nr,4,sum(r["Area SF"] for r in rows if isinstance(r["Area SF"],(int,float)))) af(ws); ws.freeze_panes="A5" def roofs_tab(self,rows): ws=self.wb.create_sheet("05 - Roofing") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="4472C4" self._pt(ws,"Roofing Takeoff","Area SF and perimeter LF.") cols=["Description","Count","Area SF","Perimeter LF","Volume CY"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: tot_line(ws,nr,3,sum(r["Area SF"] for r in rows)) af(ws); ws.freeze_panes="A5" def openings_tab(self,rows): ws=self.wb.create_sheet("06 - Openings") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="ED7D31" self._pt(ws,"Doors & Windows Takeoff","Count and area by type, name, size, and position.") cols=["Type","Name","Size","Position","Count","Total SF"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: d_ct=sum(r["Count"] for r in rows if r["Type"]=="Door") w_ct=sum(r["Count"] for r in rows if r["Type"]=="Window") for off,lbl,ct in [(0,"DOOR TOTAL:",d_ct),(1,"WINDOW TOTAL:",w_ct)]: ws.cell(row=nr+off,column=1,value=lbl).font=Font(name=FNT,bold=True,size=9) ws.cell(row=nr+off,column=4,value=ct).font=Font(name=FNT,bold=True,size=9) af(ws); ws.freeze_panes="A5" def zones_tab(self,rows): ws=self.wb.create_sheet("07 - Rooms") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="9DC3E6" self._pt(ws,"Room / Zone Schedule","Area, perimeter, and height per room.") cols=["Room Number","Room Name","Area SF","Perimeter LF","Height (ft)","Wall SA SF"] wh(ws,4,cols); nr=wr(ws,5,rows,cols) if rows: tot_line(ws,nr,3,sum(r["Area SF"] for r in rows if isinstance(r["Area SF"],(int,float)))) af(ws); ws.freeze_panes="A5" def objects_tab(self,rows): ws=self.wb.create_sheet("08 - Objects & Equipment") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="FF7C80" self._pt(ws,"Objects & Equipment","GDL library parts by name with counts.") cols=["Name","Count","Positions"] wh(ws,4,cols); wr(ws,5,rows,cols) af(ws); ws.column_dimensions["A"].width=44; ws.freeze_panes="A5" def raw_tab(self,ex): ws=self.wb.create_sheet("09 - Raw Data") ws.sheet_view.showGridLines=False; ws.sheet_properties.tabColor="BFBFBF" self._pt(ws,"Raw BIM Data","All element data in SI units. QA reference.") r=4 sections=[ ("WALLS",ex.walls,["guid","id","composite","type","length_m","height_m","thick_m","surf_m2","vol_m3"]), ("SLABS",ex.slabs,["guid","id","composite","position","area_m2","vol_m3","thick_m","perim_m"]), ("ROOFS",ex.roofs,["guid","id","composite","area_m2","vol_m3","perim_m","thick_m"]), ("DOORS",ex.doors,["guid","id","name","pos","w_m","h_m","area_m2","size_label"]), ("WINDOWS",ex.windows,["guid","id","name","pos","w_m","h_m","area_m2","size_label"]), ("ZONES",ex.zones,["guid","id","name","number","area_m2","perim_m","height_m"]), ("OBJECTS",ex.objects,["guid","id","name","pos"]), ] for sec,data,fields in sections: if not data: continue for col in range(1,len(fields)+1): hc(ws.cell(row=r,column=col),"",bg=MID) ws.cell(row=r,column=1,value=sec).font=Font(name=FNT,bold=True,size=10,color=WHT) ws.row_dimensions[r].height=18; r+=1 wh(ws,r,fields); r+=1 for i,elem in enumerate(data): alt=(i%2==0) for col,fld in enumerate(fields,1): v=elem.get(fld,''); c=ws.cell(row=r,column=col,value=v) if isinstance(v,float): c.number_format="#,##0.0000" dc(c,v,alt) r+=1 r+=1 af(ws,mx=30); ws.freeze_panes="A4" def save(self,output_dir,pn): safe="".join(c for c in pn if c.isalnum() or c in " _-").strip() or "Project" ts=datetime.now().strftime("%Y%m%d_%H%M") fp=os.path.join(output_dir,f"{safe}_Estimate_{ts}.xlsx") self.wb.save(fp); return fp # =========================================================================== # 8. Main # =========================================================================== def main(): print("="*65) print(" ArchiCAD 29 BIM Takeoff Exporter v6.0") print(" The Seabold Companies / S5 Construction") print("="*65) parser=argparse.ArgumentParser() parser.add_argument("--port",type=int,default=None) parser.add_argument("--output",type=str,default=None) args=parser.parse_args() conn=connect(args.port) pf=Props(conn) output_dir=resolve_output(args.output) print(f" Output: {output_dir}") ex=Extractor(conn,pf); ex.run() if ex.walls: w=ex.walls[0] print(" SPOT CHECK - First wall:") print(f" Composite: '{w['composite']}' Type: '{w['type']}'") print(f" Length: {round(w['length_m']*M_TO_FT,2)} ft Height: {round(w['height_m']*M_TO_FT,2)} ft") print(f" Surface: {round(w['surf_m2']*M2_TO_SF,2)} SF") parsed=parse_composite(w['composite'],wall_sf=w['surf_m2']*M2_TO_SF, wall_lf=w['length_m']*M_TO_FT,avg_height_ft=w['height_m']*M_TO_FT) print(f" Parsed {len(parsed)} material line items from composite name") print("\n Calculating quantities...") tc=Takeoff(ex) wall_r=tc.walls(); gyp_r=tc.gypsum(); slab_r=tc.slabs() roof_r=tc.roofs(); open_r=tc.openings(); zone_r=tc.zones() obj_r=tc.objects(); comp_items=tc.composite_line_items(wall_r,slab_r,roof_r) print(f" Composite parser generated {len(comp_items)} material line items") print("\n Element counts:") for lbl,lst in [("Walls",ex.walls),("Slabs",ex.slabs),("Roofs",ex.roofs), ("Doors",ex.doors),("Windows",ex.windows), ("Zones",ex.zones),("Objects",ex.objects)]: print(f" {lbl:<12} {len(lst)}") print("\n Building Excel workbook...") eb=Builder("Project") eb.summary_tab(wall_r,gyp_r,slab_r,roof_r,open_r,zone_r,comp_items) eb.ucl(); eb.walls_tab(wall_r); eb.gypsum_tab(gyp_r) eb.slabs_tab(slab_r); eb.roofs_tab(roof_r); eb.openings_tab(open_r) eb.zones_tab(zone_r); eb.objects_tab(obj_r); eb.raw_tab(ex) fp=eb.save(output_dir,"Project") print(f"\n SUCCESS: {fp}\n") return fp if __name__=="__main__": main()