mgtx-xlwings-utils — Documentation

Reusable xlwings (Excel COM automation on Windows) + pandas helpers for Excel automation workflows at MeiraGTx, including a Benchling → Excel round-trip. This is the documentation hub: start here, then jump to the guide for your scenario.

pip install mgtx-xlwings-utils            # base
pip install "mgtx-xlwings-utils[benchling]"   # + Benchling integration
pip install -e ".[dev]"                      # local development

Python requirement: >=3.10.11,<4.0. Excel automation requires a live Excel install on Windows.

---

Pick your workflow

The package supports four scenarios. Each guide is full lifecycle — not just initialisation, but pasting data in, reading it back out, updating, formatting, and the errors you’ll hit along the way.

Guide	Use it when you…	Key functions
Normal named-range tables	Build a table by hand in Excel and drive it from Python — no Benchling	initialise_table_named_ranges, paste_df_to_named_range, import_excel_table, scan_named_range_table
One Benchling table	Pull a single Benchling result schema onto a sheet and write results back	initialise_table_benchling, paste_df_to_named_range, cleanup_stale_table_names
Many Benchling tables (multi-sheet)	Lay out several Benchling schemas across multiple sheets from a dict of ids	initialise_table_benchling (dict mode + loops)
Plates	Build 6/12/24/48/96/384/1536-well or custom plate layouts with named-range round-trips	create_plate_table, create_plate_layout, plate_layout_to_dataframe

---

Visual walkthroughs (colour)

Interactive colour Excel-grid diagrams — best viewed on the docs site or opened in a browser (the markdown guides also embed always-available text grids):

• 🎨 Normal named-range tables
• 🎨 One Benchling table
• 🎨 Many Benchling tables
• 🎨 Plates
• 🎨 Stacked tables & reinitialise — the engine (7 toggleable scenarios)

---

Understand the engine

Stacked tables & Benchling reinitialise — concepts deep-dive

The definitive explanation of how the layout engine behaves, with before/after Excel-grid diagrams:

• The managed-table model — named ranges, _table_start/_table_end bookmarks, the Benchling schema anchor + manifest.
• The stacked-only invariant — why tables must have disjoint row ranges (column overlap is fine), and when SideBySideNotSupportedError fires.
• The danger zone and safe placement (TableSpaceOccupiedError).
• The paste shift mechanism (clear → shift → write) and how the gap to a stacked neighbour is preserved across grows and shrinks.
• Bookmark auto-heal and cleanup_stale_table_names.
• Centerpiece: start from two stacked Benchling tables, reinitialise the upper one with more columns, and watch it auto-widen while the neighbour below is protected/shifted.

---

Reference & operational

• Full tools reference — every public function and exception (45 names) with verbatim signatures, parameters, return types, raised exceptions, and examples.
• Credentials — how to construct the BenchlingContext (ctx) the Benchling functions expect (credential handling lives in mgtx-benchling-wrapper, not here).
• Common patterns — config cells, dropdowns, sheet management, plotting, file IO.

---

Quickstart

import xlwings as xw
from xlwings_package import initialize_workbook, initialise_table_benchling

wb = xw.Book.caller()
initialize_workbook(wb)

# Pull a Benchling schema and lay it out on a sheet.
results = initialise_table_benchling(
    wb,
    {"Assay Results": ["assaysch_abc123"]},
    ctx=my_benchling_context,   # see credentials.md
)

See the single-Benchling guide for the full seven-step pattern (init → read → transform → write → colour → save).

---

Two defaults worth knowing

• Formula-injection guard is on by default. All cell writes apostrophe-escape text values that start with =, +, -, or @ so external data can’t smuggle in a live Excel formula. Numbers and numeric-looking strings are untouched. Opt out with sanitize_formulas=False.
• Benchling reinit always purges dropped columns. A column removed from the Benchling schema is cleared outright, the gap is closed, and the table extent shrinks. There is no soft-mark option.

---

Doc map

docs/
├── index.md                     ← you are here
├── guides/
│   ├── normal-tables.md         ← manual named-range tables
│   ├── benchling-single.md      ← one Benchling table, start to finish
│   ├── benchling-multiple.md    ← many Benchling tables across sheets
│   └── plates.md                ← plate layouts
├── concepts/
│   └── stacked-tables-and-reinit.md   ← how the engine behaves
├── reference/
│   └── tools.md                 ← full API reference (45 names)
├── credentials.md               ← building the Benchling ctx
└── common-patterns.md           ← config cells, dropdowns, IO