Tools reference

Complete API reference for mgtx-xlwings-utils — reusable xlwings (Excel COM on Windows) + pandas helpers used at MeiraGTx. Every public name is documented below with its verbatim signature, parameters, return type, exceptions, and a short runnable example.

The public API is exactly the names exported in src/xlwings_package/__init__.py __all__: 45 names — 35 functions + 10 exception classes. Everything imports from the top-level package:

import xlwings_package as xwp
# or
from xlwings_package import paste_df_to_named_range, import_excel_table

Underscore-prefixed names (e.g. _assert_stacked_only, _check_danger_zone, _sanitize_df_for_excel) are internal and are not part of the supported surface; they appear here only where they explain observable behaviour.

Key concepts (one paragraph)

Managed tables are addressed through Excel named ranges. The package tracks each table’s live data extent with two sheet-scoped bookmarks, <name>_table_start and <name>_table_end (plates use <name>_plate_start / <name>_plate_end). These are auto-written and auto-healed.
Lanes. Managed tables form column-connected groups called lanes. Row overlap is rejected only within a lane (column-overlapping tables). Tables in disjoint column bands sharing rows are different lanes and are fully independent — side-by-side layouts in separate column bands are supported. Row overlap within a lane raises SideBySideNotSupportedError.
Danger zone. The band below a table within its lane is the danger zone. A write that would shift or destroy loose content (cells or floating shapes) there — or land a fresh header onto occupied cells — raises TableSpaceOccupiedError with a move instruction.
Benchling ctx is caller-supplied. You construct a BenchlingContext (from mgtx_benchling_wrapper) yourself and pass it via ctx=. This package never touches credentials.
Defaults to note: sanitize_formulas=True on the paste/plate writers (apostrophe-escapes =/+/-/@ text cells). Columns dropped in Benchling are always purged (cells cleared, gap closed) — there is no tombstone option.

Workbook / sheet initialize_workbook · clear_workbook · get_worksheet · create_sheet · delete_sheet

Named ranges & tables add_named_range · resolve_named_range · initialise_table_named_ranges · import_excel_table · paste_df_to_named_range · scan_named_range_table · get_named_range_results · get_value · get_list_results · check_duplicate_values

Benchling initialise_table_benchling · cleanup_stale_table_names

Plates create_plate_table · create_plate_layout · get_plate_format_presets · plate_layout_to_dataframe

Formatting & plotting colour_rows · paste_plot_to_cell · add_dropdown_list

File IO choose_save_path · read_file_to_df · save_dataframe · resolve_workbook_path

DataFrame utils strip_strings · limit_columns_to_decimals · rename_dtype_columns · check_missing_columns · check_case_sensitive_entries

UI display_message · display_message_and_exit

Exceptions XlwingsPackageError · UserCancelledError · NamedRangeNotFoundError · WorksheetNotFoundError · EmptyDataError · TableSpaceOccupiedError · ManifestNotFoundError · ExcelColumnOverflowError · ColumnOrderMismatchError · SideBySideNotSupportedError

Workbook / sheet

Source: excel_io.py. Many helpers in this package accept wb=None and fall back to a module-level default workbook set by initialize_workbook.

`initialize_workbook`

def initialize_workbook(wb):

Store wb as the module-level default workbook used by subsequent helper calls (those that accept wb=None). Returns the workbook it stored.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook to remember as the default.

Returns: the stored workbook object. Raises: —

import xlwings as xw
import xlwings_package as xwp

wb = xw.Book()
xwp.initialize_workbook(wb)        # later calls may pass wb=None

`clear_workbook`

def clear_workbook() -> None:

Clear the module-level default workbook reference (set it back to None).

Parameters: none. Returns: None. Raises: —

import xlwings_package as xwp
xwp.clear_workbook()

`get_worksheet`

def get_worksheet(wb=None, sheet_name: str = ""):

Return an xlwings Sheet from a workbook.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to the initialized default when `None`.
`sheet_name`	`str`	Name of the worksheet to retrieve (required).

Returns: xlwings Sheet. Raises: ValueError if sheet_name is empty; WorksheetNotFoundError if the sheet does not exist; ValueError if no workbook is available.

import xlwings_package as xwp
ws = xwp.get_worksheet(wb, "Results")

`create_sheet`

def create_sheet(wb, sheet_name: str):

Create and return a new sheet in the workbook.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to the default when `None`.
`sheet_name`	`str`	Name for the new sheet.

Returns: the new xlwings Sheet. Raises: ValueError if no workbook is available. (Excel raises if the name already exists / is invalid.)

import xlwings_package as xwp
ws = xwp.create_sheet(wb, "Scratch")

`delete_sheet`

def delete_sheet(wb, sheet_name: str) -> None:

Delete a worksheet from the workbook.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to the default when `None`.
`sheet_name`	`str`	Name of the sheet to delete.

Returns: None. Raises: ValueError if no workbook is available.

import xlwings_package as xwp
xwp.delete_sheet(wb, "Scratch")

See also: normal tables guide.

Named ranges & tables

Source: excel_io.py. The core read/write workflow for managed tables. Typical lifecycle: build a header → initialise_table_named_ranges → paste_df_to_named_range / import_excel_table.

`add_named_range`

def add_named_range(wb, sheet_name: str, name: str, range_object, scope: str = "sheet", replace_if_exists: bool = False):

Add a named range pointing at real cells, either sheet-scoped or workbook-scoped.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`sheet_name`	`str`	Sheet used as the scope anchor.
`name`	`str`	Name token to assign.
`range_object`	xlwings `Range`	Range whose `.address` is used.
`scope`	`str`	`"sheet"` (default) or `"workbook"`.
`replace_if_exists`	`bool`	When `True`, delete any existing same-named range first. Default `False`.

Returns: None. Raises: ValueError if range_object has no valid address, or scope is neither "sheet" nor "workbook"; WorksheetNotFoundError if sheet_name does not exist.

import xlwings_package as xwp
ws = wb.sheets["Results"]
xwp.add_named_range(wb, "Results", "sample_id", ws.range("B4"), scope="sheet")

`resolve_named_range`

def resolve_named_range(wb, named_range: str, scope_sheet: Optional[str] = None) -> Tuple[Any, Any]:

Resolve a named range (sheet- or workbook-scoped) and return (sheet, range_object).

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook containing the name.
`named_range`	`str`	Name to resolve.
`scope_sheet`	`str` or `None`	If given, look up the name scoped to that sheet only; otherwise workbook-scoped.

Returns: (xlwings Sheet, xlwings Range). Raises: WorksheetNotFoundError if scope_sheet is given but missing; NamedRangeNotFoundError if the name cannot be found.

import xlwings_package as xwp
sheet, rng = xwp.resolve_named_range(wb, "sample_table", scope_sheet="Results")

`initialise_table_named_ranges`

def initialise_table_named_ranges(
    wb,
    table_name_range: str,
    scope_sheet: Optional[str] = None,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
) -> dict[str, str]:

Scan a table’s header row (anchored by table_name_range) and assign a sheet-scoped named range to every header cell that lacks one. Also writes the <name>_table_start / <name>_table_end bookmarks when data rows exist below the header. Run this once before reading with import_excel_table or scan_named_range_table.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to the default when `None`.
`table_name_range`	`str`	Anchor named range covering the header row.
`scope_sheet`	`str` or `None`	Restrict name resolution to this sheet.
`include_list`	iterable of `str` or `None`	Column names / range tokens to keep. Mutually exclusive with `ignore_list`.
`ignore_list`	iterable of `str` or `None`	Column names / range tokens to drop. Mutually exclusive with `include_list`.

Returns: dict[str, str] mapping each range-name token → its display header. Raises: NamedRangeNotFoundError if the anchor can’t resolve; SideBySideNotSupportedError on a row-overlap with another managed table; ValueError if both include_list and ignore_list are passed.

import xlwings_package as xwp
mapping = xwp.initialise_table_named_ranges(wb, "sample_table", scope_sheet="Results")
# {'sample_id': 'Sample ID', 'concentration': 'Concentration', ...}

`import_excel_table`

def import_excel_table(
    wb,
    table_name_range: str,
    scope_sheet: Optional[str] = None,
    use_name_range_names: bool = True,
    check_empty_columns: bool = True,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
):

Read a managed table under a header anchor into a DataFrame, discovering data rows by scanning downward from the header. Auto-heals the _table_start / _table_end bookmarks (writes missing, grows stale; never auto-shrinks).

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`table_name_range`	`str`	Anchor named range over the header row.
`scope_sheet`	`str` or `None`	Resolve names within this sheet only.
`use_name_range_names`	`bool`	`True` (default): DataFrame columns keyed by range token; `False`: keyed by raw header text.
`check_empty_columns`	`bool`	`True` (default): raise if any column is entirely null.
`include_list`	iterable or `None`	Columns/tokens to keep. Mutually exclusive with `ignore_list`.
`ignore_list`	iterable or `None`	Columns/tokens to drop. Mutually exclusive with `include_list`.

Returns: (DataFrame, dict) — the data and the {range_token: display_header} mapping. Raises: EmptyDataError if the table is empty or an all-null column is found while check_empty_columns=True; NamedRangeNotFoundError if the anchor can’t resolve; ValueError if both selector lists are passed.

import xlwings_package as xwp
df, mapping = xwp.import_excel_table(wb, "sample_table", scope_sheet="Results")

`paste_df_to_named_range`

def paste_df_to_named_range(
    wb,
    df: pd.DataFrame | list[pd.DataFrame],
    named_range: str | list[str],
    scope_sheet: Optional[str] = None,
    sanitize_formulas: bool = True,
) -> None:

Write one or more DataFrames into managed table areas. The data is written positionally (df.values only — the index is not written) starting under the header named range. Across grows and shrinks the gap to the next stacked table below is preserved; bookmarks are updated every call and auto-healed if corrupt.

Calling modes:

Single: df is a DataFrame, named_range is a str.
Multi: df is a list of DataFrames, named_range a matching-length list of str.

Behaviour notes:

Wider DataFrame → header auto-extends (new header cells take df.columns).
Narrower DataFrame → trailing data cells cleared; the header keeps its declared width.
Sanitisation before any destructive op: ±inf → empty cell; strings over Excel’s 32,767-char limit truncated (one UserWarning); with sanitize_formulas=True (default) text cells beginning =/+/-/@ are apostrophe-escaped (CSV/formula-injection guard). Pass sanitize_formulas=False to write formula strings verbatim from a trusted source.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`df`	`DataFrame` or `list[DataFrame]`	Data to write.
`named_range`	`str` or `list[str]`	Target table header name(s).
`scope_sheet`	`str` or `None`	Resolve names within this sheet only.
`sanitize_formulas`	`bool`	Apostrophe-escape leading `=`/`+`/`-`/`@` text cells. Default `True`.

Returns: None. Raises: ValueError (mismatched list lengths); TypeError (wrong df / named_range type); EmptyDataError (zero rows); NamedRangeNotFoundError; TableSpaceOccupiedError (loose content in the danger zone); SideBySideNotSupportedError (post-paste row overlap); ColumnOrderMismatchError (df columns don’t match existing header positions); ExcelColumnOverflowError (auto-widen past column XFD).

import pandas as pd
import xlwings_package as xwp

df = pd.DataFrame({"sample_id": ["S1", "S2"], "concentration": [1.2, 3.4]})
xwp.paste_df_to_named_range(wb, df, "sample_table", scope_sheet="Results")

# Multi-table in one call
xwp.paste_df_to_named_range(wb, [df1, df2], ["table_a", "table_b"])

`scan_named_range_table`

def scan_named_range_table(
    wb,
    table_start_name: str,
    table_end_name: str,
    scope_sheet: Optional[str] = None,
    use_name_range_names: bool = False,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
):

Read a table using explicit start/end bookmark names (no header anchor). The header row is inferred as the row directly above table_start_name; the column width is found by scanning header cells rightward until the first blank (so the header must be contiguous — no blank separator column on this path). Grows table_end_name in place if data extends beyond it.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`table_start_name`	`str`	Sheet-scoped name at the first data cell (top-left of data, not header).
`table_end_name`	`str`	Sheet-scoped name at the last data cell (bottom row, any column).
`scope_sheet`	`str` or `None`	Resolve within this sheet; otherwise names must be unambiguous workbook-wide.
`use_name_range_names`	`bool`	`True`: columns keyed by range token; `False` (default): raw header text.
`include_list` / `ignore_list`	iterable or `None`	Column/token selectors (mutually exclusive).

Returns: (DataFrame, dict) — data and {range_token: display_header}. Raises: NamedRangeNotFoundError if start/end can’t resolve or are on different sheets; ValueError if both selector lists are passed.

import xlwings_package as xwp
df, mapping = xwp.scan_named_range_table(
    wb, "sample_table_start", "sample_table_end", scope_sheet="Results"
)

`get_named_range_results`

def get_named_range_results(
    wb=None,
    worksheet_name: str = "",
    named_range: str = "",
    strip: bool = True,
):

Read a single named range’s value, stripping strings by default.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`worksheet_name`	`str`	Sheet to read from.
`named_range`	`str`	Range/name to read (required).
`strip`	`bool`	When `True` (default), strip whitespace via `strip_strings`.

Returns: the cell value (whitespace-stripped when strip=True). Raises: ValueError if named_range is empty; WorksheetNotFoundError if the sheet is missing.

import xlwings_package as xwp
val = xwp.get_named_range_results(wb, "Results", "operator_name")

`get_value`

def get_value(wb, range_name: str, sheet_name: str = ""):

Read a single Excel range value from a worksheet (no stripping).

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`range_name`	`str`	Cell address or named range (e.g. `"A1"`).
`sheet_name`	`str`	Sheet to read from (required).

Returns: the raw cell value. Raises: ValueError if sheet_name is empty; WorksheetNotFoundError if the sheet is missing.

import xlwings_package as xwp
v = xwp.get_value(wb, "B2", sheet_name="Results")

`get_list_results`

def get_list_results(wb, sheet_name: str, column_name: str, start_row_name: str, end_row_name: str):

Read a contiguous vertical range into a Python list. The column comes from column_name’s column; the first/last rows come from the rows of start_row_name / end_row_name.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`sheet_name`	`str`	Sheet to read from.
`column_name`	`str`	Named range / address whose column is used.
`start_row_name`	`str`	Named range / address whose row marks the first data row.
`end_row_name`	`str`	Named range / address whose row marks the last data row.

Returns: list of cell values from start row to end row in that column. Raises: WorksheetNotFoundError if the sheet is missing.

import xlwings_package as xwp
ids = xwp.get_list_results(wb, "Results", "sample_id", "table_start", "table_end")

`check_duplicate_values`

def check_duplicate_values(mapping: dict, error_prefix: str = "Duplicate value found") -> None:

Raise if any values in mapping repeat (useful for validating {range_token: display_header} mappings).

Parameter	Type	Meaning
`mapping`	`dict`	Dict whose values must be unique.
`error_prefix`	`str`	Prefix for the error message. Default `"Duplicate value found"`.

Returns: None. Raises: ValueError listing the duplicated values.

import xlwings_package as xwp
xwp.check_duplicate_values({"a": "X", "b": "X"})   # ValueError: Duplicate value found: X

Benchling

Source: benchling_io.py. These functions resolve assay-result schemas via mgtx_benchling_wrapper’s SchemaHandler and write/refresh table headers. You supply the authenticated BenchlingContext via ctx=.

`initialise_table_benchling`

def initialise_table_benchling(
    wb,
    schema_map: "dict[str, list[str]]",
    *,
    ctx,
    mode: Literal["autopaste", "paste"] = "autopaste",
    column: "int | str | dict[str, int | str] | None" = None,
    at: "str | None" = None,
    table_gap: int = 2,
) -> list[SchemaInitResult]:

Create or refresh Benchling assay-result table headers across one or more sheets. On first run a schema’s title + header row + per-column named ranges + schema-column manifest are written; on later runs the existing table is re-synced to Benchling (display names, title, added/removed/reordered columns). New columns append at the right edge and are placed in schema order by the repack pass. Dropped columns are always purged: cells cleared, name deleted, remaining columns close up, table extent shrinks.

schema_map maps each sheet name to the ordered list of assaysch_… ids to place on it. Multiple ids under one sheet are stacked there in list order, and the same id may appear under more than one sheet.

Schema lookup accepts only Benchling ids (assaysch_ prefix) — name-based lookup was removed.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`schema_map`	`dict[str, list[str]]`	Maps each sheet name to the ordered list of `assaysch_…` ids placed on it (stacked in list order).
`ctx`	`BenchlingContext` (keyword-only)	Caller-supplied context exposing a callable `.benchling()`.
`mode`	`Literal["autopaste", "paste"]`	`"autopaste"` (default) auto-stacks rows; `"paste"` pins to an exact cell.
`column`	`int`, Excel letter `str`, `dict[schema_id, col]`, or `None`	Autopaste column anchor. Scalar = same column for all; dict = per-schema (unlisted auto-detect); `None` auto-detects. Rejected in paste mode.
`at`	`str` or `None`	Paste anchor — top-left cell of the first table (e.g. `"C5"`); later tables stack below. Defaults to `"A1"`. Rejected in autopaste mode.
`table_gap`	`int`	Empty rows between stacked tables. Default `2`.

Returns: list[SchemaInitResult], one per schema. Each SchemaInitResult is a frozen, slotted dataclass with attributes schema_name, display_name, warehouse_name, range_name, sheet_name, action ("created" | "reinitialised"), columns (tuple[ColumnInfo, ...] — each ColumnInfo has display_name, warehouse_name, range_name), and diff (dict | None, only populated when action == "reinitialised"). Both SchemaInitResult and ColumnInfo are exported from xlwings_package. Raises: ValueError (bad mode, empty schema_map, a sheet mapped to an empty id list, column in paste mode, or at in autopaste mode); TypeError (schema_map not a dict, a sheet’s value not a list/tuple, column not an int/letter/dict/None, ctx not Benchling-compatible, or a column anchor is a bool); ImportError (mgtx-benchling-wrapper not installed); TableSpaceOccupiedError (fresh header lands on occupied cells); ExcelColumnOverflowError (placement past column XFD); NamedRangeNotFoundError (reinit can’t find the schema’s range). On a mid-reinit failure the raised exception carries a partial_diff attribute.

from mgtx_benchling_wrapper.context.benchling_context import BenchlingContext
import xlwings_package as xwp

ctx = BenchlingContext(client_id=..., client_secret=..., base_url=..., token_url=...)

# Single sheet, two schemas stacked
xwp.initialise_table_benchling(
    wb, {"Results": ["assaysch_abc123", "assaysch_def456"]}, ctx=ctx
)

# Multiple sheets
xwp.initialise_table_benchling(
    wb,
    {"Sheet1": ["assaysch_abc123"], "Sheet2": ["assaysch_def456"]},
    ctx=ctx,
)

`cleanup_stale_table_names`

def cleanup_stale_table_names(
    wb,
    table_name: str,
    *,
    sheet: Optional[str] = None,
) -> dict[str, list[str]]:

Prune stale sheet-scoped names tied to a managed Benchling table. Deletes all #REF!-broken names (named ranges left behind when a user manually deletes a column in Excel). Ownership is determined by name pattern and the schema manifest.

Parameter	Type	Meaning
`wb`	xlwings `Book` or `None`	Workbook; falls back to default when `None`.
`table_name`	`str`	Schema range name (matches the `{table_name}__schema_columns` manifest).
`sheet`	`str` or `None` (keyword-only)	Restrict to one sheet; `None` sweeps every sheet hosting the manifest.

Returns: {"removed_ref": [...]} of sheet-qualified "<sheet>!<name>" strings naming the broken names that were pruned. Raises: ManifestNotFoundError if no manifest for table_name exists on the targeted sheet(s); WorksheetNotFoundError if sheet= names a missing sheet.

import xlwings_package as xwp
result = xwp.cleanup_stale_table_names(wb, "hplc_results")
# {'removed_ref': ['Results!old_col']}

result = xwp.cleanup_stale_table_names(wb, "hplc_results", sheet="Results")
# {'removed_ref': ['Results!old_col']}

Plates

Source: plate_functions.py. Supported preset formats: 6, 12, 24, 48, 96, 384, 1536 (or custom dimensions). Typical flow: create_plate_table (fill in sample data) → create_plate_layout (visual grid) → plate_layout_to_dataframe (read back).

`create_plate_table`

def create_plate_table(
    wb: xw.Book,
    *,
    sheet_name: str = _DEFAULT_SHEET_NAME,          # "plate sheet"
    sample_rows: int | None = None,
    extra_headers: Sequence[str] | None = None,
    table_title: str = _DEFAULT_TABLE_TITLE,        # "Plate Table"
    header_named_range: str = "plate_table",
    dropdown_list_sheet_name: str = "lists",
    dropdown_list_title: str = "plate dropdown list",
    plate_format: str = "96",
    plate_rows_count: int | None = None,
    plate_cols_count: int | None = None,
    start_col: int | str = 2,
    sanitize_formulas: bool = True,
) -> tuple[pd.DataFrame, xw.Sheet]:

Create a fresh source plate table sheet: title + bold orange header row, default columns (Sample No., Sample ID, Plate Position) plus any extra_headers, a sheet-scoped named range per header, and a Plate Position dropdown driven by the chosen format. Stores plate_format_key / plate_rows_count_key / plate_cols_count_key so the layout step can detect the shape automatically.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`sheet_name`	`str`	Target sheet (default `"plate sheet"`).
`sample_rows`	`int` or `None`	Number of data rows; defaults to the format’s total well count.
`extra_headers`	sequence of `str` or `None`	Extra columns appended after the defaults.
`table_title`	`str`	Title cell text (default `"Plate Table"`).
`header_named_range`	`str`	Name for the header-row range (default `"plate_table"`).
`dropdown_list_sheet_name`	`str`	Sheet for the dropdown source list (default `"lists"`).
`dropdown_list_title`	`str`	Title for the dropdown source block.
`plate_format`	`str`	One of `6/12/24/48/96/384/1536` (default `"96"`).
`plate_rows_count` / `plate_cols_count`	`int` or `None`	Custom dims; provide both or neither.
`start_col`	`int` or Excel letter `str`	Left edge of the table (default `2`; must be `>= 1`).
`sanitize_formulas`	`bool`	Escape leading `=`/`+`/`-`/`@` in written cells. Default `True`.

Returns: (DataFrame, Sheet) — the table data and the sheet it was written to. Raises: ValueError (only one of the custom-dim pair given; non-positive sample_rows / dims / start_col; invalid plate_format).

import xlwings_package as xwp
df, ws = xwp.create_plate_table(wb, plate_format="96", extra_headers=["Volume (uL)"])

`create_plate_layout`

def create_plate_layout(
    wb: xw.Book,
    *,
    plate_name: str,
    plate_title: str,
    value_named_ranges: Sequence[str],
    well_value_order: Sequence[str] | None = None,
    source_table_named_range: str = "plate_table",
    source_sheet_name: str = "plate sheet",
    target_sheet_name: str = "plate layouts",
    start_cell: str = "B2",
    append_if_sheet_exists: bool = True,
    sanitize_formulas: bool = True,
) -> xw.Sheet:

Render a visual well-plate grid from the source table. Plate shape is read automatically from the source sheet’s stored metadata. Each well stacks the requested variables in well_value_order (or value_named_ranges order). Wells are filled by matching the source Plate Position column when present, else by row order. Replacing an existing plate handles format changes (clears the old footprint, inserts/deletes rows for taller/shorter plates).

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`plate_name`	`str` (keyword-only)	Plate identity; used as the named-range token.
`plate_title`	`str` (keyword-only)	Title shown above the grid.
`value_named_ranges`	sequence of `str`	Source columns (tokens or display names) to lay out.
`well_value_order`	sequence of `str` or `None`	Stacking order within each well; defaults to `value_named_ranges`.
`source_table_named_range`	`str`	Source header range (default `"plate_table"`).
`source_sheet_name`	`str`	Source sheet (default `"plate sheet"`).
`target_sheet_name`	`str`	Layout sheet (default `"plate layouts"`; created if missing).
`start_cell`	`str`	Top-left anchor for a fresh plate (default `"B2"`).
`append_if_sheet_exists`	`bool`	Stack below existing plates rather than overwrite. Default `True`.
`sanitize_formulas`	`bool`	Escape leading `=`/`+`/`-`/`@` in well values. Default `True`.

Returns: the target Sheet. Raises: ValueError (missing plate_name/plate_title; unknown variable; source row count mismatch; duplicate plate positions); SideBySideNotSupportedError on a row overlap with another managed table/plate.

import xlwings_package as xwp
xwp.create_plate_layout(
    wb,
    plate_name="plate1",
    plate_title="Plate 1 — Concentrations",
    value_named_ranges=["sample_id", "concentration"],
)

`get_plate_format_presets`

def get_plate_format_presets() -> dict[str, dict[str, str]]:

Return the built-in plate-format presets with dimension and volume metadata (keys 6/12/24/48/96/384/1536). The returned dict is a copy — safe to mutate.

Parameters: none. Returns: dict[str, dict[str, str]] — e.g. presets["96"]["rows_x_columns"] == "8 x 12". Raises: —

import xlwings_package as xwp
presets = xwp.get_plate_format_presets()
print(presets["384"]["recommended_working_volume"])   # "10-90 uL"

`plate_layout_to_dataframe`

def plate_layout_to_dataframe(
    wb: xw.Book,
    *,
    plate_name: str,
    sheet_name: str = "plate layouts",
    variables: Sequence[str] | None = None,
    rows: Sequence[str] | None = None,
    columns: Sequence[int] | None = None,
    return_format: Literal["wide", "long"] = "wide",
) -> pd.DataFrame:

Read a rendered plate layout back into a DataFrame, inferring the plate dimensions from the stored named ranges. Optional filtering by variables, plate rows, and plate columns.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`plate_name`	`str` (keyword-only)	Plate to read.
`sheet_name`	`str`	Layout sheet (default `"plate layouts"`).
`variables`	sequence of `str` or `None`	Subset of variables (display names or tokens).
`rows`	sequence of `str` or `None`	Subset of plate row labels (e.g. `["A", "B"]`).
`columns`	sequence of `int` or `None`	Subset of plate column numbers.
`return_format`	`"wide"` or `"long"`	`"wide"` (default): one row per well; `"long"`: one row per (well, variable).

Returns: pandas.DataFrame. Wide form always includes plate_name, plate_row, plate_col, plate_position plus a column per variable; long form adds variable_token, variable_display, value. Raises: ValueError (missing plate_name; bad return_format; sheet/plate ranges not found; empty rows/columns filter result).

import xlwings_package as xwp
df = xwp.plate_layout_to_dataframe(wb, plate_name="plate1", return_format="long")

See also: plates guide.

Formatting & plotting

Sources: formatting.py, plotting.py, excel_utils.py.

`colour_rows`

def colour_rows(
    wb,
    name_range: str,
    df: pd.DataFrame,
    mode: Literal["group_alternate", "equals_no", "values_match"],
    column_name: str,
    scope_sheet: Optional[str] = None,
    values_to_colour: Optional[Iterable] = None,
    colour_name: Optional[str] = None,
):

Apply row background colours to a table by column value. df must be in the same row order as the sheet.

Modes:

"group_alternate" — alternate light-blue / white bands each time column_name changes.
"equals_no" — colour any row red where column_name == "No".
"values_match" — colour rows where column_name is in values_to_colour, using colour_name.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`name_range`	`str`	Header named range (locates sheet + columns to colour).
`df`	`DataFrame`	Table data, sheet-row-aligned.
`mode`	literal	One of the three modes above.
`column_name`	`str`	Column driving the colouring.
`scope_sheet`	`str` or `None`	Resolve `name_range` within this sheet only.
`values_to_colour`	iterable or `None`	Required for `"values_match"`.
`colour_name`	`str` or `None`	Required for `"values_match"`; a known name, CSS name (via `webcolors`), or `#RRGGBB`.

Returns: None (mutates cell colours). Raises: EmptyDataError if column_name is missing/all-null; ValueError for an unknown mode or missing mode-required args.

import xlwings_package as xwp
xwp.colour_rows(wb, "sample_table", df, mode="equals_no", column_name="Pass?")
xwp.colour_rows(
    wb, "sample_table", df, mode="values_match", column_name="status",
    values_to_colour={"FAIL"}, colour_name="#FFB6C1",
)

`paste_plot_to_cell`

def paste_plot_to_cell(
    workbook,
    df: pd.DataFrame,
    x_column: str,
    y_column: str,
    target_cell: str,
    sheet_name: str,
    *,
    title: str = "",
    x_label: str = "",
    y_label: str = "",
    width_px: int = 480,
    height_px: int = 320,
    dpi: int = 150,
    marker: str = "o",
    line: bool = True,
    best_fit: bool = True,
    r_squared: bool = True,
    log: str = "",
):

Build a matplotlib scatter plot from two DataFrame columns and paste it as a PNG image anchored at target_cell. Optional linear best-fit line and R² annotation; log scaling computes the fit on log-transformed values.

Parameter	Type	Meaning
`workbook`	xlwings `Book`	Workbook.
`df`	`DataFrame`	Data to plot.
`x_column` / `y_column`	`str`	Columns for the X / Y axes.
`target_cell`	`str`	Anchor cell for the image top-left (e.g. `"C5"`).
`sheet_name`	`str`	Target sheet.
`title` / `x_label` / `y_label`	`str` (keyword-only)	Labels; sensible defaults derived from columns.
`width_px` / `height_px` / `dpi`	`int` (keyword-only)	Image size (480×320) and resolution (150 DPI).
`marker`	`str` (keyword-only)	Point marker (default `"o"`).
`line` / `best_fit` / `r_squared`	`bool` (keyword-only)	Connect points / overlay fit / annotate R². All `True` by default.
`log`	`str` (keyword-only)	`"x"`, `"y"`, `"xy"`/`"both"`, or `""` (linear).

Returns: None (inserts the picture). Raises: WorksheetNotFoundError if sheet_name is missing; EmptyDataError if a plot column is missing or no numeric values survive coercion.

import xlwings_package as xwp
xwp.paste_plot_to_cell(
    wb, df, x_column="conc", y_column="signal",
    target_cell="H2", sheet_name="Results", best_fit=True, log="x",
)

`add_dropdown_list`

def add_dropdown_list(
    wb: xw.Book,
    *,
    list_sheet_name: str,
    title: str,
    options: Sequence[str],
    start_column: str = "A",
    start_row: int = 1,
    column_step: int = 2,
) -> tuple[xw.Sheet, xw.Range]:

Write a titled option source list to the first free column of a list sheet (scanning columns from start_column in steps of column_step). This lays out the values only — it does not attach an in-cell Data Validation dropdown; callers (e.g. create_plate_table) add Validation themselves pointing at the returned options range.

Parameter	Type	Meaning
`wb`	xlwings `Book`	Workbook.
`list_sheet_name`	`str` (keyword-only)	Sheet to write the list to (created if missing).
`title`	`str` (keyword-only)	Title cell text (required).
`options`	sequence of `str` (keyword-only)	Option values (blanks dropped).
`start_column`	`str`	Column letter to start scanning from. Default `"A"`.
`start_row`	`int`	Title row. Default `1`.
`column_step`	`int`	Column stride when scanning for free space. Default `2`.

Returns: (list_sheet, options_range) — the sheet and the value range (title row excluded). Raises: ValueError if title is empty, options has no non-empty value, or column_step <= 0.

import xlwings_package as xwp
sheet, opts = xwp.add_dropdown_list(
    wb, list_sheet_name="lists", title="Operators", options=["Alice", "Bob"]
)

See also: normal tables guide, plates guide.

File IO

Source: file_io.py. These open Tkinter dialogs / Excel alerts; user cancellation raises UserCancelledError.

`choose_save_path`

def choose_save_path(
    save_as: str = "csv",
    default_file_name: str = "output",
    title: str = "Choose where to save",
) -> str:

Open a save dialog and return the chosen absolute path.

Parameter	Type	Meaning
`save_as`	`str`	`"csv"`, `"excel"`/`"xlsx"`, or `"auto"` (user picks in dialog).
`default_file_name`	`str`	Suggested base filename.
`title`	`str`	Dialog title.

Returns: str absolute path. Raises: ValueError for an invalid save_as; UserCancelledError if the user cancels.

import xlwings_package as xwp
path = xwp.choose_save_path(save_as="xlsx", default_file_name="run42")

`read_file_to_df`

def read_file_to_df(
    sheet_name=None,
    file_type: str = "csv",
    show_pre_alert: bool = True,
    *,
    sep: str | None = None,
    header_marker: str | None = None,
) -> pd.DataFrame:

Prompt the user to select a CSV/TXT/XLSX file (optional pre-alert in the active Excel app) and return it as a DataFrame. .csv and .txt are read as delimited text; .xlsx is read with pd.read_excel.

Parameter	Type	Meaning
`sheet_name`	`str`, `list`, or `None`	Excel sheet selector; `None` reads the first sheet (keeps the `-> DataFrame` contract).
`file_type`	`str`	`"csv"` (lists `.csv` and `.txt`), `"excel"`, or `"all"`.
`show_pre_alert`	`bool`	Show an OK/Cancel alert before the picker. Default `True`.
`sep`	`str` or `None`	Delimiter for `.csv`/`.txt`. Defaults to a comma for `.csv` and a tab for `.txt`.
`header_marker`	`str` or `None`	Skip a metadata preamble: lines are skipped until the first whose first field equals this marker, which is then read as the header. `.csv`/`.txt` only.

Returns: pandas.DataFrame. Raises: ValueError for an invalid file_type, a wrong selected extension, an unreadable file, a header_marker that is not found, or header_marker combined with a multi-character sep; UserCancelledError if the user cancels the alert or picks no file.

import xlwings_package as xwp
df = xwp.read_file_to_df(file_type="all")
df = xwp.read_file_to_df(file_type="csv", header_marker="Sample")  # skip a .txt preamble

`save_dataframe`

def save_dataframe(
    df: pd.DataFrame,
    save_path: str | None = None,
    save_as: str = "auto",
    default_file_name: str = "output",
    show_dialog: bool = True,
    dialog_message: str = "Sequence saved to selected directory.",
) -> str:

Save a DataFrame to CSV/XLSX — prompting for the path when save_path is None — and optionally show a post-save dialog (with Open file / Open folder).

Parameter	Type	Meaning
`df`	`DataFrame`	Data to save.
`save_path`	`str` or `None`	Full output path; `None` opens a save dialog.
`save_as`	`str`	`"csv"`, `"excel"`/`"xlsx"`, or `"auto"` (infer from extension / dialog).
`default_file_name`	`str`	Suggested name when a dialog is shown.
`show_dialog`	`bool`	Show the post-save dialog (set `False` for batch). Default `True`.
`dialog_message`	`str`	Message shown in the dialog.

Returns: str — the absolute path written. Raises: ValueError for an invalid save_as or unresolved extension; UserCancelledError if a save dialog is cancelled.

import xlwings_package as xwp
out = xwp.save_dataframe(df, save_path=r"C:\data\run42.xlsx", show_dialog=False)

`resolve_workbook_path`

def resolve_workbook_path(path_or_folder: str) -> str:

Resolve a workbook path from either a direct .xlsm/.xlsx file path or a folder containing exactly one workbook.

Parameter	Type	Meaning
`path_or_folder`	`str`	A workbook file path, or a folder holding exactly one workbook.

Returns: str absolute workbook path. Raises: ValueError if the path doesn’t exist, is the wrong file type, the folder has zero workbooks, or the folder has more than one.

import xlwings_package as xwp
path = xwp.resolve_workbook_path(r"C:\runs\today")   # single workbook in folder

See also: common patterns.

DataFrame utils

Source: dataframe_utils.py. Pure pandas helpers (no Excel COM).

`strip_strings`

def strip_strings(value):

Strip whitespace across common container/string types. Strings are stripped; lists/dicts have their string members stripped and empty-string entries dropped; DataFrames have every string cell stripped; anything else is returned as-is.

Parameter	Type	Meaning
`value`	`str`, `list`, `dict`, `DataFrame`, or any	Value to clean.

Returns: the cleaned value (same type for str/list/dict/DataFrame). Raises: —

import xlwings_package as xwp
xwp.strip_strings(["  a ", "", "b "])     # ['a', 'b']

`limit_columns_to_decimals`

def limit_columns_to_decimals(df: pd.DataFrame, columns: Iterable[str], decimals: int = 3) -> pd.DataFrame:

Round numeric values in the selected columns to a fixed precision (booleans and non-numeric values are left untouched). Mutates and returns df.

Parameter	Type	Meaning
`df`	`DataFrame`	Input frame.
`columns`	iterable of `str`	Columns to round.
`decimals`	`int`	Decimal places. Default `3`.

Returns: the DataFrame (same object, columns rounded). Raises: EmptyDataError if a named column is absent.

import xlwings_package as xwp
df = xwp.limit_columns_to_decimals(df, ["concentration"], decimals=2)

`rename_dtype_columns`

def rename_dtype_columns(
    df: pd.DataFrame,
    org_name: list[str],
    new_name: list[str],
    desired_dtype: list,
) -> pd.DataFrame:

Rename selected columns and cast each to the corresponding dtype. The three lists are zipped positionally and must be equal length. Returns a copy.

Parameter	Type	Meaning
`df`	`DataFrame`	Input frame.
`org_name`	`list[str]`	Existing column names.
`new_name`	`list[str]`	New names (aligned to `org_name`).
`desired_dtype`	`list`	Target dtypes (aligned to `new_name`).

Returns: a new DataFrame with renamed, recast columns. Raises: ValueError if the lists differ in length; EmptyDataError if any org_name column is missing.

import xlwings_package as xwp
df = xwp.rename_dtype_columns(df, ["Conc"], ["concentration"], [float])

`check_missing_columns`

def check_missing_columns(df: pd.DataFrame, required_columns: Iterable[str]) -> pd.DataFrame:

Validate that df contains every required column. Returns df unchanged on success.

Parameter	Type	Meaning
`df`	`DataFrame`	Frame to validate.
`required_columns`	iterable of `str`	Columns that must be present.

Returns: the same DataFrame. Raises: EmptyDataError listing any missing columns.

import xlwings_package as xwp
xwp.check_missing_columns(df, ["sample_id", "concentration"])

`check_case_sensitive_entries`

def check_case_sensitive_entries(
    df: pd.DataFrame,
    column_name: str,
    expected_value: str,
    display_name: str | None = None,
) -> pd.DataFrame:

Require an exact, case-sensitive value in one column. Adds a "{name}?" flag column ("Yes"/"No") and raises if any value matches case-insensitively but not exactly. Returns a copy.

Parameter	Type	Meaning
`df`	`DataFrame`	Input frame.
`column_name`	`str`	Column to check.
`expected_value`	`str`	The exact value required.
`display_name`	`str` or `None`	Label for the flag column; defaults to `expected_value`.

Returns: a new DataFrame with the flag column added. Raises: EmptyDataError if column_name is absent; ValueError if a case-mismatched entry is found.

import xlwings_package as xwp
df = xwp.check_case_sensitive_entries(df, "gene", "GFP")   # rejects "gfp"/"Gfp"

See also: common patterns.

UI

Source: ui.py. Thin wrappers over the active Excel app’s alert.

`display_message`

def display_message(message: str) -> None:

Show a message via the active Excel app alert.

Parameter	Type	Meaning
`message`	`str`	Text to display.

Returns: None. Raises: RuntimeError if there is no active Excel app.

import xlwings_package as xwp
xwp.display_message("Import complete.")

`display_message_and_exit`

def display_message_and_exit(message: str, exit_code: int = 1) -> None:

Show a message, then terminate via raise SystemExit(exit_code).

Parameter	Type	Meaning
`message`	`str`	Text to display.
`exit_code`	`int`	Exit code. Default `1`.

Returns: does not return normally. Raises: SystemExit (always); RuntimeError if there is no active Excel app.

import xlwings_package as xwp
xwp.display_message_and_exit("Validation failed — aborting.", exit_code=2)

Exceptions

Source: exceptions.py. All custom errors derive from XlwingsPackageError, so a single except XlwingsPackageError: catches everything this package raises. (Several functions additionally raise built-in ValueError / TypeError for caller-side input validation — see each function above.)

`XlwingsPackageError`

class XlwingsPackageError(Exception):

Base class for all package errors. Base: Exception. Catch this to handle any error raised by the package.

import xlwings_package as xwp
try:
    xwp.import_excel_table(wb, "missing_table")
except xwp.XlwingsPackageError as exc:
    print("package error:", exc)

`UserCancelledError`

class UserCancelledError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when the user cancels an interactive operation — the pre-alert or file/save dialog in read_file_to_df, choose_save_path, save_dataframe.

`NamedRangeNotFoundError`

class NamedRangeNotFoundError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when a named range cannot be resolved (by resolve_named_range and the table read/paste functions), or when a schema’s range is not found during a Benchling reinit.

`WorksheetNotFoundError`

class WorksheetNotFoundError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when a worksheet doesn’t exist — e.g. get_worksheet, paste_plot_to_cell, or a sheet= argument to cleanup_stale_table_names.

`EmptyDataError`

class EmptyDataError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when required data is missing or empty — an empty table or all-null column in import_excel_table, a zero-row DataFrame in paste_df_to_named_range, missing columns in the DataFrame utils, or missing/empty plot columns.

`TableSpaceOccupiedError`

class TableSpaceOccupiedError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when a write would shift or destroy non-table content — loose cells or floating shapes in the danger zone below a managed table (lane band), or cells a fresh Benchling header would land on. The exception message names the colliding object and provides a move instruction. (A DataFrame wider than the declared header is a ValueError, not this — that’s caller-side input validation.)

`ManifestNotFoundError`

class ManifestNotFoundError(XlwingsPackageError):

Base: XlwingsPackageError. Raised by cleanup_stale_table_names when the table has no {table_name}__schema_columns manifest on the targeted sheet(s) — i.e. it was never initialised by initialise_table_benchling (or the manifest name was deleted manually).

`ExcelColumnOverflowError`

class ExcelColumnOverflowError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when a placement would land a header column past Excel’s last column (XFD = 16,384) — auto-widening in paste_df_to_named_range, or a fresh schema placement in initialise_table_benchling.

`ColumnOrderMismatchError`

class ColumnOrderMismatchError(XlwingsPackageError):

Base: XlwingsPackageError. Raised by paste_df_to_named_range when df.columns don’t match the existing header positions (reordered / inserted / dropped middle column), which would silently misalign the positional write. Fires before any destructive op; matches against either the per-column range token or the header display value. Fix by reordering the DataFrame or re-initialising the table.

`SideBySideNotSupportedError`

class SideBySideNotSupportedError(XlwingsPackageError):

Base: XlwingsPackageError. Raised when a managed table/plate on the sheet has a row range overlapping the operating table’s rows within the same lane (column-overlapping tables). Tables in disjoint column bands are in separate lanes and are never checked against each other. Fires at init (initialise_table_named_ranges, create_plate_layout) and at paste (paste_df_to_named_range) before any destructive operation.

See also: stacked tables & re-init concept.

Tools reference

Key concepts (one paragraph)

Table of contents

Workbook / sheet

initialize_workbook

clear_workbook

get_worksheet

create_sheet

delete_sheet

Named ranges & tables

add_named_range

resolve_named_range

initialise_table_named_ranges

import_excel_table

paste_df_to_named_range

scan_named_range_table

get_named_range_results

get_value

get_list_results

check_duplicate_values

Benchling

initialise_table_benchling

cleanup_stale_table_names

Plates

create_plate_table

create_plate_layout

get_plate_format_presets

plate_layout_to_dataframe

Formatting & plotting

colour_rows

paste_plot_to_cell

add_dropdown_list

File IO

choose_save_path

read_file_to_df

save_dataframe

resolve_workbook_path