Stacked tables & reinitialise — how the engine behaves

1. The stacked-only contract safe

Table X (rows 2–4) sits above neighbour Y (rows 6–8) with a one-row gap between them. Both occupy columns A–C — the columns overlap completely. That is fine: the only thing the engine forbids is row overlap.

The stack

A

B

C

2

3

S1

B1

10start

4

S2

B1

20end

(gap — empty, safe to grow into)

6

well

conc

od

7

A1

5

0.2

8

A2

5

0.3

Why it is accepted

The invariant test compares row intervals only. X spans rows [2,4]; Y spans rows [6,8]. Their intersection is empty, so the layout is accepted — even though every column is shared.

Column overlap is fine; row overlap is not. Each managed table is tracked by sheet-scoped names: a header anchor, per-column tokens, and the _table_start / _table_end bookmarks that record the live data extent. The bookmark prefix is the table's identity.

If X and Y shared even one row, a resize of X would tear Y in half — so that layout is rejected up front with SideBySideNotSupportedError (scenario 5).

2. Row grow — neighbour pushed down, gap preserved safe

X holds 2 data rows; you paste a 4-row DataFrame. The engine opens 2 row slots below X's old bottom and the neighbour Y slides down as a whole unit. The one-row gap is mechanically preserved.

Before — n_old = 2

A

B

C

2

3

S1

B1

10

4

S2

B1

20end

(gap)

6

well

conc

od

7

A1

5

0.2

8

A2

5

0.3

→

After — n_new = 4, delta = +2

A

B

C

2

3

D1

4

D2

5

D3

6

D4

D4end

(gap — preserved)

8

well

conc

od

9

A1

5

0.2

10

A2

5

0.3

clear → shift → write. ClearContents on X's own data cells, then Range.Insert(Shift=xlShiftDown) for delta = +2 rows below old_end across the cluster band, then write df.values and rewrite the bookmarks. The shift only ever touches rows below X's old bottom — so Y moves intact, by the same delta as X's new bottom. Gap in, gap out.

3. Row shrink — neighbour pulled up, gap preserved safe

X holds 3 data rows; you paste a 1-row DataFrame. The engine deletes the trailing rows and the neighbour Y is pulled up by the same amount. The gap between them is unchanged.

Before — n_old = 3

A

B

C

2

3

S1

B1

10

4

S2

B1

20

5

S3

B2

30end

(gap)

7

well

conc

od

8

A1

5

0.2

9

A2

5

0.3

→

After — n_new = 1, delta = −2

A

B

C

2

3

D1

D1end

4

—

5

—

(gap — preserved)

7

well

conc

od

8

A1

5

0.2

9

A2

5

0.3

The trailing rows are cleared, then Range.Delete(Shift=xlShiftUp) closes |delta| = 2 rows below the new data. Y shifts up by 2 (rows 7–9 → 5–7, redrawn here at their post-shift positions). Under the stacked-only invariant a shrink is unconditionally safe — no other managed table shares X's rows, so nothing side-by-side could be destroyed.

4. Column widen — header auto-extends, no column insert safe

The next pull returns a DataFrame one column wider than the header (4 cols vs 3). The engine extends the header rightward into column D and writes the new column straight down. No Insert columns is needed — the stacked-only invariant guarantees nobody else owns column D at these rows.

Before — header width 3

A

B

C

D

2

3

S1

B1

10

4

S2

B1

20end

(gap)

6

well

conc

od

→

After — header width 4

A

B

C

D

2

qc

3

D1

4

D2

D2end

(gap — preserved)

6

well

conc

od

The header named range is extended from A–C to A–D; the new cell takes its display name from df.columns; _table_end moves to column D. Y lives in different rows, so it is completely untouched. (If the rightmost new column would pass Excel's last column XFD = 16,384, ExcelColumnOverflowError fires first.) A later narrow clears trailing cells but keeps the header width, so re-widening needs no header change.

5. Row overlap / side-by-side refused

A second managed table N is placed at rows 2–4 in columns D–F — sharing X's rows. The row intervals intersect, so this is rejected at init and at paste time, before any destructive operation.

Layout — rows shared

A

B

C

D

E

F

2

N_h

3

x1

n1

4

x2

n2

Error raised

SideBySideNotSupportedError — X rows [2,4] ∩ N rows [2,4] = [2,4] ≠ ∅ within the same lane (X and N share column D). Within a lane, managed tables must have pairwise disjoint row ranges. Move N to disjoint columns (making it a separate lane) or stack it vertically.

The engine resizes a table by inserting or deleting whole worksheet rows across a column band. A full-row shift moves every cell in those columns; a table sharing X's rows would be torn — its left columns shifted, its right columns not. So the layout is forbidden rather than carrying the complexity of per-table smart placement.

The check is forward-looking: it compares X's post-operation rows against every neighbour's projected rows, and fires at initialise_table_named_ranges, initialise_table_benchling, paste_df_to_named_range, and create_plate_layout.

6. Danger zone — loose content below the table refused

A managed table owns the band directly below it, in its cluster columns, indefinitely — that is where its data grows. A loose note dropped into that band (B6, no protecting named range) blocks a grow.

Attempt: paste 4 rows into X

A

B

C

2

3

S1

B1

10

4

S2

B1

20end

▼ danger zone — owned by table X (cols A–C)

5

6

NOTE: QA

Error raised

TableSpaceOccupiedError — Loose content detected at B6 below table 'X' in cluster cols 1..3. Repasting would shift or destroy it. The danger-zone scan runs before any cell is cleared or shifted, so a rejection leaves the sheet untouched.

The danger zone spans the full column extent of the transitive column-overlap cluster (a BFS closure), and the scan and the row-shift use the same band — so anything that would be shifted is also inspected first. That is the safety guarantee.

Three safe places for loose content: above the title row; in columns outside the cluster band; or under its own named range (the scan exempts any cell already covered by a name). A stacked managed neighbour is not loose content — it shifts as a unit (scenario 2). The same TableSpaceOccupiedError also guards the claim zone when a widen would overwrite cells in the new columns.

★ 7. Reinitialise with MORE columns — the payoff centerpiece

Two stacked Benchling tables: assay_x (cols A–C) above assay_y (cols A–C), with a 2-row gap and live data in both. Upstream, the Assay X schema gains a qc field at the end. You re-run initialise_table_benchling exactly as the first time; it detects the existing anchor and routes to _reinit. The header auto-widens in place, named ranges rebind, the manifest updates — and assay_y below is protected because nothing shifts vertically.

Before — assay_x = [sample, batch, result]

A

B

C

D

2

3

S1

B1

10

4

S2

B1

20end

(gap — 2 rows)

7

well

conc

od

8

A1

5

0.2

9

A2

5

0.3

manifest: assay_x__schema_columns = "sample,batch,result"

→

After — assay_x = [sample, batch, result, qc]

A

B

C

D

2

qcanchor

3

S1

B1

10

(empty)

4

S2

B1

20

(empty)end

(gap — preserved)

7

well

conc

od

8

A1

5

0.2

9

A2

5

0.3

manifest now: assay_x__schema_columns = "sample,batch,result,qc"

What _reinit does for the end-appended qc

Locate the table → re-sync display names + title from the schema → diff the new schema against the manifest (manual ranges a dev added in the band are excluded — only schema-owned columns are touched) → place qc at end_col + 1 (col D) after _check_band_empty confirms D2 is free → extend the anchor to $A$2:$D$2, re-merge the title across A–D, move assay_x_table_end to col D → rewrite the manifest. Existing sample/batch/result data is preserved cell-for-cell; only qc starts empty.

Why the neighbour is protected: an end-append widen never inserts rows. The qc header lands at D2 and its column extends straight down into column D, which nobody else owns at those rows. assay_y stays exactly at rows 7–10 and the 2-row gap is untouched. (A column added in the middle of the schema instead does a row-scoped Range.Insert(Shift=xlShiftToRight) confined to the table's own rows — so the neighbour below is still not displaced.)

Re-pasting after the schema change is positional

After reinit, the sheet header order is [sample, batch, result, qc]. A repaste writes df.values positionally, so the DataFrame's columns must line up. If they are reordered — or a column was inserted/dropped in the middle — the write would silently misalign, so it raises ColumnOrderMismatchError before any destructive op. Fix: reorder the DataFrame, or (if Benchling itself reordered) re-run initialise_table_benchling to rebind the order, then paste. Columns past the current header are handled by the auto-widen path instead.

The mirror case — Benchling DROPS a column

A dropped schema column is purged outright: every cell from header to _table_end is cleared, its named range is deleted, the remaining columns close up (repack), and the table extent shrinks to the new rightmost column. This operates on columns, not rows, so the stacked neighbour below is untouched, and tables in other lanes are unaffected. There is no soft-mark option. sanitize_formulas=True is also the default on the paste path.