Excel Automation

Excel runs the business world. Python runs Excel — if you respect the file format. One careless write can destroy a workbook.

What you'll learn

1. openpyxl read/write
2. Preserve formatting & formulas
3. pandas in/out
4. Batch across workbooks
5. xlwings / COM
6. Safe-write pattern

Remember this

• openpyxl for surgical edits (keep_vba for macros); pandas for bulk table in/out — to_excel replaces, never edits
• Batch = pipeline: new output folder, per-file logging, resumable idempotency; xlwings/COM when real Excel is required
• Safe-write ritual: backup → dry-run → approve → write new → verify → swap

Code: The safe-write pattern, implemented

from pathlib import Path
import shutil, datetime, openpyxl

SRC = Path("finance/Q1_actuals.xlsx")
OUT = Path("out/Q1_actuals_updated.xlsx")   # NEW file — never in place

# 1. BACKUP --------------------------------------------------------
ts = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")
shutil.copy(SRC, SRC.with_suffix(f".bak-{ts}.xlsx"))

# 2. DRY-RUN: compute the plan, write nothing ----------------------
wb = openpyxl.load_workbook(SRC, data_only=False)
ws = wb["Actuals"]
plan = []
for row in range(2, ws.max_row + 1):
    if ws.cell(row, 4).value == "EUR":
        plan.append((row, ws.cell(row, 5).value))
print(f"DRY-RUN: would recalc {len(plan)} EUR rows (e.g. {plan[:3]})")
# → human reviews and approves before anything is written

# 3. APPLY + WRITE NEW ---------------------------------------------
RATE = 1.08
for row, eur in plan:
    ws.cell(row, 6, round(eur * RATE, 2))
OUT.parent.mkdir(parents=True, exist_ok=True)
wb.save(OUT)

# 4. VERIFY ---------------------------------------------------------
check = openpyxl.load_workbook(OUT)
assert check.sheetnames == wb.sheetnames
formulas = sum(1 for r in check["Actuals"].iter_rows()
               for c in r if isinstance(c.value, str) and c.value.startswith("="))
print(f"OK: {len(plan)} rows updated, {formulas} formulas intact -> {OUT}")
# Only after verification: manually replace the original, backup in hand.