tools-only 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/