1 The Problem
We want a tiny spreadsheet: a grid of cells where you can put numbers or formulas like =A1+B2, and the program calculates the results — including formulas that reference other cells. It teaches evaluating expressions and resolving references between cells, a small taste of how spreadsheets and interpreters work.
Where this shows up: spreadsheet engines, calculators, rule engines, template systems, any tool where users write expressions that get evaluated. Understanding references and evaluation demystifies tools you use daily.
2 How to Think About It
Think about how a formula gets its answer, before any code:
The plan — in plain English
1. Cells are stored by name (A1, B2) mapping to their content. → 2. A plain number is just its value. → 3. A formula (starting with =) needs evaluating: find the cell references in it, replace each with that cell’s value, then compute. → 4. Show the grid of results.
3 The Build — explained part by part
Here is a complete mini-spreadsheet. Each part is explained below.
Pythonspreadsheet.py
import re
def evaluate(cell, cells):
# A cell is either a plain number or a formula starting with "=".
content = cells.get(cell, "")
if not content.startswith("="):
return float(content) if content else 0.0
expression = content[1:] # drop the "="
# Replace each cell reference (like A1) with that cell's value.
def replace_ref(match):
return str(evaluate(match.group(), cells))
resolved = re.sub(r"[A-Z]+\d+", replace_ref, expression)
# Evaluate the now-numeric expression safely.
return eval(resolved, {"__builtins__": {}})
if __name__ == "__main__":
cells = {"A1": "10", "A2": "20", "A3": "=A1+A2"}
print("A3 =", evaluate("A3", cells)) # 30.0
What each part does — in plain words
evaluate(cell, cells) — work out a cell’s value. The whole thing is recursive: a formula’s references are themselves evaluated the same way.if not content.startswith("="): — a plain value: just turn the text into a number (or 0 if empty).
content[1:] — for a formula, drop the leading
= to get the expression.re.sub(r"[A-Z]+\d+", replace_ref, expression) — the clever step: find every cell reference (a pattern like
A1 — letters then digits) and replace it with that cell’s evaluated value. replace_ref calls evaluate again, which is the recursion.eval(resolved, {"__builtins__": {}}) — once all references are numbers, evaluate the arithmetic. Passing an empty builtins dictionary is a basic safety measure so the expression cannot call dangerous functions.
Common mistakes — and how to avoid them
✗ Using
eval on raw user input with full builtins — a serious security hole.✓ Restrict it with
{"__builtins__": {}}, or better, write a small parser.✗ Not handling references recursively — a formula pointing to another formula breaks.
✓ Call
evaluate for each reference so chains resolve.✗ A regex that misses multi-letter columns like AA1.
✓ Use
[A-Z]+\d+ so multi-letter columns match.4 Test & Prove Each Part
We test plain values and formulas, including ones that reference other cells.
A plain number cell returns its value
A simple formula is calculated
A formula referencing other cells resolves them
Pythontest_spreadsheet.py
import re
def evaluate(cell, cells):
content = cells.get(cell, "")
if not content.startswith("="):
return float(content) if content else 0.0
expression = content[1:]
def replace_ref(match):
return str(evaluate(match.group(), cells))
resolved = re.sub(r"[A-Z]+\d+", replace_ref, expression)
return eval(resolved, {"__builtins__": {}})
def test_plain_number():
"""A plain number cell returns its value."""
assert evaluate("A1", {"A1": "42"}) == 42.0
def test_simple_formula():
"""A formula with literals is computed."""
assert evaluate("A1", {"A1": "=2+3"}) == 5.0
def test_cell_references():
"""A formula referencing cells resolves them."""
cells = {"A1": "10", "A2": "20", "A3": "=A1+A2"}
assert evaluate("A3", cells) == 30.0
Run with pytest -v. The cell-references test is the real proof: it shows a formula correctly pulling values from other cells. That recursion is the heart of any spreadsheet engine.
5 The Interface
SETcell = value or formulaA1 = 10, A3 = =A1+A2
EVALevaluate(cell)the computed result
Example
A1=10, A2=20, A3==A1+A2
evaluate("A3") -> 30.06 Run It & Automate It
Run it locally
python3 spreadsheet.pySee
=A1+A2 evaluate to 30. Add more cells and formulas to experiment.Jenkins runs the tests automatically — each line explained below.
What you should see when it works
Terminala real run
A3 = 30.0If it breaks — how to fix it
🚨
NameError during eval.A reference was not replaced — check your regex matches all cell names.
🚨 Infinite recursion.
Two cells reference each other in a loop. Real spreadsheets detect these cycles; add a guard if needed.
GroovyJenkinsfile
// Jenkinsfile — automated tests on every change.
pipeline {
agent any
stages {
stage('Get the code') { steps { checkout scm } } // download the code
stage('Install') { steps { sh 'python3 -m venv venv && . venv/bin/activate && pip install pytest' } } // test tool
stage('Test') { steps { sh '. venv/bin/activate && pytest -v' } } // run every test
}
post {
success { echo 'All tests passed.' }
failure { echo 'A test failed — look above.' }
}
}
🎯 Try this next — make it yours
- Functions. Support
=SUM(A1:A3). (Teaches: ranges and functions.) - Cycle detection. Catch cells that reference each other. (Teaches: graph cycles.)
- A real parser. Replace
evalwith your own expression parser. (Teaches: parsing — see the Tier 5 interpreter.)
What you learned
You learned to evaluate expressions and resolve references between cells using recursion and pattern-matching — the core idea behind spreadsheets and a first step toward interpreters. Related: re, Functions & Scope.