Excel Processing for Construction

Overview

Adapted from Anthropic's XLSX skill for construction spreadsheet workflows.

Construction Use Cases

1. Cost Estimate Template

from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.utils import get_column_letter

def create_estimate_template(output_path: str, project_name: str): """Create construction cost estimate template.""" wb = Workbook() ws = wb.active ws.title = "Cost Estimate"

# Styles
header_font = Font(bold=True, size=12)
currency_format = '$#,##0.00'
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")

# Project header
ws['A1'] = "CONSTRUCTION COST ESTIMATE"
ws['A1'].font = Font(bold=True, size=16)
ws['A2'] = f"Project: {project_name}"
ws['A3'] = "Date:"
ws['B3'] = "=TODAY()"

# Column headers (row 5)
headers = ['CSI Code', 'Description', 'Quantity', 'Unit', 'Unit Cost',
           'Labor', 'Material', 'Equipment', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=5, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.border = thin_border
    cell.alignment = Alignment(horizontal='center')

# Set column widths
widths = [12, 40, 10, 8, 12, 12, 12, 12, 14]
for i, width in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(i)].width = width

# Sample data rows with formulas
for row in range(6, 26):  # 20 empty rows
    # Total formula: Labor + Material + Equipment
    ws.cell(row=row, column=9,
            value=f"=SUM(F{row}:H{row})")

    # Apply borders
    for col in range(1, 10):
        ws.cell(row=row, column=col).border = thin_border

# Currency formatting
for col in [5, 6, 7, 8, 9]:  # Cost columns
    for row in range(6, 26):
        ws.cell(row=row, column=col).number_format = currency_format

# Subtotals section
ws['G27'] = "SUBTOTAL"
ws['I27'] = "=SUM(I6:I25)"
ws['I27'].number_format = currency_format

ws['G28'] = "Contingency (10%)"
ws['I28'] = "=I27*0.10"

ws['G29'] = "TOTAL"
ws['I29'] = "=I27+I28"
ws['I29'].font = Font(bold=True, size=14)

wb.save(output_path)
return output_path

2. Schedule Tracker

def create_schedule_tracker(output_path: str, tasks: list): """Create construction schedule tracking spreadsheet.""" wb = Workbook() ws = wb.active ws.title = "Schedule"

# Headers
headers = ['ID', 'Task', 'Start Date', 'End Date', 'Duration',
           'Progress', 'Status', 'Predecessor', 'Notes']

# Status dropdown options
from openpyxl.worksheet.datavalidation import DataValidation
status_dv = DataValidation(
    type="list",
    formula1='"Not Started,In Progress,Complete,On Hold,Delayed"',
    allow_blank=True
)
ws.add_data_validation(status_dv)

# Header row
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)

# Add tasks with formulas
for i, task in enumerate(tasks, 2):
    ws.cell(row=i, column=1, value=task.get('id', i-1))
    ws.cell(row=i, column=2, value=task.get('name', ''))
    ws.cell(row=i, column=3, value=task.get('start', ''))
    ws.cell(row=i, column=4, value=task.get('end', ''))

    # Duration formula
    ws.cell(row=i, column=5, value=f"=D{i}-C{i}")

    # Progress bar (0-100%)
    ws.cell(row=i, column=6, value=task.get('progress', 0))
    ws.cell(row=i, column=6).number_format = '0%'

    # Status with validation
    status_cell = ws.cell(row=i, column=7, value=task.get('status', 'Not Started'))
    status_dv.add(status_cell)

wb.save(output_path)
return output_path

3. RFI Log Template

def create_rfi_log(output_path: str): """Create RFI tracking log.""" wb = Workbook() ws = wb.active ws.title = "RFI Log"

headers = [
    'RFI #', 'Date Submitted', 'Subject', 'Spec Section',
    'Drawing Ref', 'Submitted By', 'Assigned To',
    'Response Due', 'Date Responded', 'Status', 'Days Open'
]

for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)

# Days Open formula (in column K)
for row in range(2, 100):
    ws.cell(row=row, column=11,
            value=f'=IF(I{row}="",TODAY()-B{row},I{row}-B{row})')

# Conditional formatting for overdue items
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

red_fill = PatternFill(bgColor="FFC7CE")
ws.conditional_formatting.add(
    'K2:K100',
    FormulaRule(formula=['K2>7'], fill=red_fill)
)

wb.save(output_path)
return output_path

4. Quantity Takeoff Processing

import pandas as pd

def process_qto_from_bim(bim_xlsx: str, output_path: str): """Process BIM export for quantity takeoff.""" # Read BIM export df = pd.read_excel(bim_xlsx)

# Group by category and type
qto = df.groupby(['Category', 'Type']).agg({
    'Volume': 'sum',
    'Area': 'sum',
    'Length': 'sum',
    'Count': 'count'
}).reset_index()

# Rename columns for clarity
qto.columns = ['Category', 'Type', 'Total Volume (m³)',
               'Total Area (m²)', 'Total Length (m)', 'Element Count']

# Create workbook with formatting
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    qto.to_excel(writer, sheet_name='QTO Summary', index=False)

    # Get workbook to apply formatting
    wb = writer.book
    ws = wb['QTO Summary']

    # Format numeric columns
    for col in ['C', 'D', 'E']:
        for row in range(2, len(qto) + 2):
            ws[f'{col}{row}'].number_format = '#,##0.00'

return output_path

Best Practices for Construction Spreadsheets

Color Coding (Industry Standard)

• Blue text: Input values (quantities, rates)

• Black text: Formulas and calculations

• Green text: Links from other sheets

• Yellow highlight: Assumptions to verify

Formula Guidelines

• Never hardcode values in formulas

• Create separate assumption cells

• Use named ranges for clarity

• Include unit checks

Error Prevention

Add error handling to formulas

formula_with_error_check = "=IFERROR(B5/C5, 0)"

Integration with DDC

Convert BIM export to estimate

from ddc_toolkit import RevitExporter, CWICRSemanticSearch

Export BIM data

exporter = RevitExporter() bim_data = exporter.read_elements("project.xlsx")

Process QTO

qto = process_qto_from_bim("project.xlsx", "qto_output.xlsx")

Match to cost database

search = CWICRSemanticSearch() for category in bim_data['Category'].unique(): prices = search.search_work_items(category) # Apply prices to QTO...

Dependencies

pip install openpyxl pandas xlrd

Resources

• Original: Anthropic XLSX Skill

• OpenPyXL Docs: https://openpyxl.readthedocs.io/