---
jupyter:
  description: 'Design tables in planex2d: a constraint sketch writes each variant of a part as a row of a CSV or Excel
    file, every dimension a column, and reads the rows back to update the sketch. A plate with a hole in three sizes,
    checked against its exact area.'
---

# Design tables in a spreadsheet

A design table lists the variants of a part, one row per variant and one column per dimension, as CAD systems keep
them in a spreadsheet. A constraint sketch writes such a table and reads it back:

| Call | What it does |
|---|---|
| `sk.AddToSpreadsheet(path, name=, values=)` | Appends the current solution as a row, with its name, every dimension by id, and further values such as an area |
| `sk.ReadSpreadsheet(path)` | Returns the rows as the sketch's driving dimensions, `{name: {dimension: value}}`, ready for `sk.Update(row)` |

A missing or empty file gets a header row first, and every later row must have the same columns. A CSV file is
written with Python's csv module, with commas between the values and a decimal point. An Excel file, `.xlsx`, works
the same way and needs the optional package openpyxl.

```python
import math
from pathlib import Path

import planex as px
```

```python nbsphinx="hidden"
from docs_figures import Row
```

## Writing the variants

The example is the plate from [Constraints and dimensions](kinds.md), $w$ = 50 by $h$ = 30 with one corner rounded to
a radius $r$ of 10 and a hole of diameter $d$ = 10, placed 20 from the left side and 15 from the bottom. Its area is
$w h - (1 - \pi/4)\,r^2 - \pi d^2/4$.

```python
plate = px.Sketch(id="plate")
plate.Move(plate.origin, px.Coincident)
plate.Line(37, 2.5, px.Horizontal, id="bottom")
plate.Arc(9, 95, px.Tangent, id="fillet")
plate.Line(-1, 17, px.Vertical, px.Tangent, id="right")
plate.Line(-46, 3, px.Perpendicular, id="top")
plate.Close(px.Perpendicular, id="left")
plate.Constrain("fillet", px.Radius, 10, id="r")
plate.Constrain("bottom.start", "right", px.Distance, 50, id="width")
plate.Constrain("bottom", "top", px.Distance, 30, id="height")
plate.Circle((20, 15), 5, id="hole")
plate.Constrain("hole", px.Diameter, 10, id="d")
plate.Constrain("hole.center", "bottom", px.Distance, 15, id="y_hole")
plate.Constrain("hole.center", "left", px.Distance, 20, id="x_hole")
print(plate.status, plate.dof, "degrees of freedom, dimensions", sorted(plate.dimensions))
```

Three sizes go into the file plates.csv, each with its area as a further column. `AddToSpreadsheet` appends to a file
that exists, so the example starts with an empty one:

```python
sizes = {"P50": {"width": 50, "height": 30, "r": 10, "d": 10, "x_hole": 20, "y_hole": 15},
         "P70": {"width": 70, "height": 40, "r": 12, "d": 14, "x_hole": 25, "y_hole": 20},
         "P90": {"width": 90, "height": 50, "r": 15, "d": 18, "x_hole": 30, "y_hole": 25}}
Path("plates.csv").unlink(missing_ok=True)
for name, size in sizes.items():
    plate.Update(size)
    plate.AddToSpreadsheet("plates.csv", name=name, values={"area": plate.Face().mass})
print(Path("plates.csv").read_text())
```

## Reading them back

`ReadSpreadsheet` returns every row by its name, with the driving dimensions only. The area is no dimension of the
sketch, so it stays out. `Update` with a row turns the sketch into that variant, and each variant's area matches the
formula (Figure 1):

```python
def area(row):
    """the plate's area from its dimensions"""
    return row["width"] * row["height"] - (1 - math.pi / 4) * row["r"] ** 2 - math.pi * row["d"] ** 2 / 4


rows = plate.ReadSpreadsheet("plates.csv")
print(rows["P70"])
variants = {}
for name, row in rows.items():
    variants[name] = plate.Copy().Update(row)
    print(name, "area", variants[name].Face().mass, "exact", area(row))
```

```python nbsphinx="hide-input"
Row(*[(variant, name, {"constraints": True}) for name, variant in variants.items()],
    caption="Three variants of the plate from one design table")
```

`sk.Animate(rows)` plays the same rows as an animation. See [Animating a sketch](animating.md) for details.