Spreadsheets and Documents

openpyxl (pip install openpyxl) reads and writes .xlsx files directly. Its model matches how you already think about a spreadsheet: a workbook holds one or more worksheets, and a worksheet is a grid of cells addressed by a column letter and a row number - A1, B2, C10. Loading an existing file and reading it is direct:

import openpyxl

wb = openpyxl.load_workbook("boq.xlsx", data_only=True)
ws = wb.active
print(ws["A1"].value)          # header text
print(ws.cell(row=2, column=2).value)

total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
    item, qty, rate = row[0], row[1], row[2]
    if qty is not None and rate is not None:
        total += qty * rate
print(f"BOQ total: {total}")

Two ways to reach a cell, both useful: ws["B2"] by its spreadsheet coordinate, or ws.cell(row=2, column=2) by numeric row and column - the second is what you use inside loops because the numbers can be variables. .value gets the contents. data_only=True asks openpyxl for the last-calculated result of any formula rather than the formula text - important, because openpyxl does not itself evaluate Excel formulas. iter_rows(min_row=2, values_only=True) walks the data rows (skipping the header) and hands you each row as a plain tuple, which is why totalling a BOQ collapses to a simple loop. The if ... is not None guard skips blank cells so a stray empty row does not crash the arithmetic. A real workbook often has several tabs, and openpyxl handles that too: wb.sheetnames lists them and wb["Costs"] selects one by name, so a multi-tab cost or FF&E workbook is just several worksheets you loop over the same way - pick the sheet, then read its grid.

A quick note on file types: openpyxl works with the modern .xlsx format (and .xlsm macro files), not the legacy .xls binary - if you meet an old .xls, open it once in Excel or LibreOffice and re-save as .xlsx, or reach for a different library. For genuinely huge sheets, load_workbook(..., read_only=True) streams rows instead of loading everything into memory, which stops a hundred-thousand-row export from exhausting your machine. For the schedule-sized files most design work involves, the plain load is perfectly fine.

Generating a spreadsheet is the same model in reverse: make a workbook, write cells, style them, save. You assign to .value (or use append to add a whole row at once), and formatting comes from small style objects - Font, PatternFill, Alignment - attached to cells:

import openpyxl
from openpyxl.styles import Font, PatternFill

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "BOQ"
ws.append(["Item", "Qty", "Rate", "Amount"])

rows = [("Door", 12, 4500), ("Window", 18, 3200)]
for item, qty, rate in rows:
    ws.append([item, qty, rate, qty * rate])

head = Font(bold=True, color="FFFFFF")
fill = PatternFill("solid", fgColor="306998")
for cell in ws[1]:
    cell.font = head
    cell.fill = fill
ws.column_dimensions["A"].width = 24
wb.save("boq_out.xlsx")

openpyxl.Workbook() starts a fresh file with one empty sheet, which wb.active returns. ws.append([...]) adds a row and auto-advances - far tidier than tracking row numbers by hand. The style objects are set once and reused: Font(bold=True, color="FFFFFF") (colours are hex RRGGBB strings, white here) and a solid PatternFill in the course python-blue. Looping for cell in ws[1] styles the whole header row. column_dimensions["A"].width widens a column so text is not clipped. You can also write a real Excel formula as a string - ws["D5"] = "=SUM(D2:D4)" - and Excel will calculate it when the file is opened.

Two finishing touches make a generated sheet look genuinely professional rather than scripted. Number formatting is set through a cell's number_format string: cell.number_format = '#,##0' gives thousands separators so an amount reads as 54,000 rather than 54000, and '0.0%' renders a fraction as a percentage. And ws.merge_cells("A1:D1") merges a range into one cell - perfect for a title banner spanning the top of a schedule. You can also freeze the header so it stays visible when scrolling - ws.freeze_panes = "A2" - and switch on an autofilter with ws.auto_filter.ref = ws.dimensions, both one-liners that make a long schedule genuinely usable for whoever opens it. These are exactly the touches you would apply by hand in Excel, expressed once in code and reproduced flawlessly on every run. The result is a real .xlsx a colleague can open, edit and print, produced entirely by script and identical every time.

For letters, reports and specs the deliverable is a Word document, and python-docx (install python-docx, import docx) builds .docx files from code. A document is a sequence of blocks you add in order - headings, paragraphs, tables, images - each through an add_... method:

from docx import Document
from docx.shared import Pt

doc = Document()
doc.add_heading("Site Condition Report", level=0)
doc.add_heading("1. Summary", level=1)
p = doc.add_paragraph("The existing structure is ")
p.add_run("sound").bold = True
p.add_run(" and suitable for reuse.")

table = doc.add_table(rows=1, cols=2)
table.style = "Light Grid Accent 1"
table.rows[0].cells[0].text = "Element"
table.rows[0].cells[1].text = "Condition"
for element, condition in [("Roof", "Good"), ("Walls", "Fair")]:
    cells = table.add_row().cells
    cells[0].text = element
    cells[1].text = condition

doc.save("report.docx")

The structure mirrors how you would build the document by hand, top to bottom. add_heading(..., level=0) is the title; level=1 and level=2 are sub-headings that inherit the template's styles. A paragraph is made of runs - stretches of text with the same formatting - which is why bolding one word means splitting it into its own add_run(...) and setting .bold = True. Tables are built by adding a header row, then add_row() per data row and filling cells[i].text - a perfect fit for a schedule or a register. You can also drop in images and page breaks: doc.add_picture("logo.png", width=Inches(2)) (with Inches imported from docx.shared) inserts an image at a set width, and doc.add_page_break() starts a fresh page - enough to assemble a presentable multi-page report entirely from code. doc.save(...) writes a real .docx that opens in Word or Google Docs with live, editable styles.

A word on styles pays off here. Rather than hand-setting fonts and sizes on every paragraph, lean on Word's named styles: add_heading and add_paragraph(..., style="List Bullet") apply the document's built-in styles, so if you start from a branded template the output inherits the studio's typography automatically. That is the difference between a document that merely contains the right words and one that looks like it belongs to your practice - and it costs nothing extra, because the styling lives in the template rather than in your code.

The transformative move is not generating one document but templating: designing a layout once, then pouring many rows of data through it to produce a consistent document per project, per unit, or per room. The pattern is to read structured data (a spreadsheet or a list of dictionaries) and, in a loop, build one document per record - the template-plus-data-equals-document flow of the figure:

from docx import Document

clients = [
    {"name": "Ms Rao", "project": "Villa 12", "fee": 350000},
    {"name": "Mr Iyer", "project": "Flat 4B", "fee": 180000},
]
for c in clients:
    doc = Document("letter_template.docx")
    for para in doc.paragraphs:
        para.text = para.text.replace("{name}", c["name"])
        para.text = para.text.replace("{project}", c["project"])
        para.text = para.text.replace("{fee}", f"{c['fee']:,}")
    safe = c["name"].replace(" ", "_")
    doc.save(f"letter_{safe}.docx")

Here Document("letter_template.docx") opens a Word file you designed by hand - correct fonts, logo, layout - containing placeholders like {name} and {fee}. The loop opens a fresh copy per client, replaces each placeholder with that client's data, and saves a uniquely named file. f"{c['fee']:,}" formats the number with thousands separators (350,000). In minutes you produce a folder of finished, on-brand letters that are impossible to make inconsistently. The same idea reads rows from a BOQ spreadsheet with openpyxl and emits a per-package report with python-docx - the two libraries chained. That chaining is where automation stops being a party trick and becomes real leverage: structured data in, finished, consistent, house-style deliverables out. A caveat to keep you honest: the simple find-and-replace above works when a placeholder sits within a single run; Word sometimes splits text across runs, so for heavier templating a dedicated engine (such as docxtpl) is more robust. Start simple, reach for the engine when the placeholders start misbehaving.

None of this lives in isolation. The data you pour into a spreadsheet or a document usually starts somewhere else - a CSV export, a pandas DataFrame from Module 4, a folder you catalogued with last lesson's file tools, or (next lesson) a live API. The everyday toolkit is at its most powerful when chained: read a messy CSV, clean and total it in pandas, write a styled .xlsx with openpyxl, and emit a templated Word summary with python-docx - a single script that turns raw data into several polished, consistent deliverables at once.

import csv
from docx import Document

with open("rooms.csv", newline="") as f:
    rooms = list(csv.DictReader(f))

doc = Document()
doc.add_heading("Area Statement", level=0)
total = 0.0
for r in rooms:
    area = float(r["area"])
    total += area
    doc.add_paragraph(f"{r['name']}: {area:.1f} sqm")
doc.add_paragraph(f"Total: {total:.1f} sqm")
doc.save("area_statement.docx")

Here csv.DictReader (from Module 3) reads each row as a dictionary keyed by the header, and the single loop both totals the areas and writes one line per room straight into the document - raw table in, finished statement out. Swap the source for an openpyxl read of a BOQ sheet, or a pandas DataFrame, and the shape is unchanged.

A closing note on effort keeps you honest. If a deliverable is genuinely one-off, a script can be slower than simply typing it - the leverage of document automation appears when the same report goes out every month, or when fifty near-identical letters or per-room specs must be produced and kept consistent. For a single unique document, open Word; for a repeating or fan-out deliverable, template it. And keep a human in the loop: generated documents should be reviewed before they leave the office, because a script will just as happily produce fifty perfect copies as fifty copies of the same mistake.