Workbook Design: Inputs, Selections, and Outputs

Workbook inputs, table selections, and published outputs and functions.

This section details how to structure Excel workbooks for Python integration: reading workbook ranges, tracking table selections, and publishing outputs and functions.

Core Marimo Rules

When writing Python in Boardflare, two Marimo rules apply: 1. Unique top-level names: Every global variable name can be defined in at most one cell. Defining the same variable name across multiple cells raises a MultipleDefinitionError. Use local functions or prefix private names with an underscore (_temp = ...) to prevent name conflicts. 2. Widgets as last expression: A UI widget (such as inputs = bf.inputs(...) or publication = bf.publish(...)) must be the last expression in a cell to render and maintain its live communication channel with the workbook host.

1. Reading Workbook Inputs (bf.inputs)

Declare workbook dependencies with bf.inputs(...):

inputs = bf.inputs(
    tax_rate="Assumptions!B2",
    orders=bf.ref("Orders", headers=True),
    picked=bf.selection("Orders"),
)
inputs
  • At most one bf.inputs per notebook: A notebook may declare at most one bf.inputs(...) widget. Calling bf.inputs(...) a second time raises RuntimeError: Only one bf.inputs(...) is allowed per notebook; use one bf.inputs(...) with all inputs. Consolidate all workbook inputs into a single call.
  • Dictionary lookup: Access materialized values with inputs["name"], never inputs.name. Attribute access conflicts with widget properties and methods.
  • Reference strings and bf.ref: A plain string such as "Sheet1!A1:C10" or "B2" is equivalent to bf.ref("B2").
  • Bare table names and headers: In Excel, a bare table name such as "Orders" resolves to the entire table including its header row. Use bf.ref("Orders", headers=True) so column names are taken from the first row and data rows form a typed pandas DataFrame.
  • Date and time types: Cells formatted as a date or date-time arrive as datetime.datetime (a date-only cell is midnight), and in a DataFrame (bf.ref(..., headers=True) or bf.selection) as a datetime64[ns] column of pd.Timestamp. Time-only cells arrive as datetime.time. Excel serial numbers are never delivered for formatted cells; a cell with a non-date number format stays a plain number. Serials appear only in template scenario set and expect values, which are written to the workbook as raw cell values. Compare dates as datetimes, for example orders["due"] < pd.Timestamp.today().normalize().
  • Reactivity: When referenced cells change in Excel, the host sends a new snapshot and Marimo automatically recalculates all dependent cells.

2. Table Selection (bf.selection)

Track the user’s active row in an Excel table using bf.selection("TableName"):

  • Single table per selection: bf.selection takes one table name.
  • DataFrame value: Reading a selection input (e.g. inputs["picked"]) returns a pandas DataFrame with the table’s headers as column names and at most one row: the row of the user’s active cell, the cell the cursor is on, even when a larger range is highlighted. Moving the cursor within that range changes the row. The row is a full record, not just an ID.
  • Empty selection: The value is an empty DataFrame with those columns, never None, when the active cell is outside the table’s body rows or columns, on another sheet, on the table’s header or total row, or on a hidden or filtered row. Handle it with a labelled default: show a fallback such as the first record, and say in the pane that the user should select a row to change it.
  • Cursor must be on a table cell: Cells beside the table select no row, so a value in an attention list or published output next to the table does not drive the pane. Select a cell inside the table’s columns.
  • Transient: The value is the user’s current grid selection, not a remembered one. It empties when the user clicks any cell outside the table or leaves the table’s sheet, and a template should assume nothing is selected when it opens.
  • Values, not positions: The DataFrame index is not aligned with the source table. Always filter or match using column values (such as picked["id"]), not row indices, so changes to the table cannot target the wrong row.
  • Hidden rows: If the active row is hidden by AutoFilter or manually hidden, the value is empty.
  • Filtering refresh: Filtering that hides the active row does not refresh the value until the user makes another selection. Do not assume the pane reflects the filter immediately.
  • Missing table: If the table does not exist in the workbook, it produces an input error (not an empty DataFrame).

Selection drives the pane, not the cells

Use a selection to decide what the notebook (task pane) shows, and do not publish selection-dependent values to cells. The selection is transient and cell contents persist, so a published value that follows the selection changes or vanishes whenever the user clicks elsewhere. Anything the user means to keep (an invoice, a mail merge) should be driven by a data column or an explicit action instead.

Patterns that fit:

  • Record detail: select a row and see it joined with related tables in the pane.
  • Lookup by key: take the active row’s key and compute a value from another table, such as the open orders for the selected customer, shown in the pane.

Nothing selected

Always handle the empty selection with a deliberate, labelled default, such as the first record (or all rows, or the top N), plus a line telling the user how to change it:

if picked.empty:
    shown = orders.head(1)
    note = mo.md("Showing the first order. Select a row in the Orders table to see its details.")
else:
    shown = picked  # the one row at the active cell
    note = mo.md("Showing the selected order.")

# Record detail: join the shown rows to a related table (match on values)
detail = shown.merge(customers, left_on="customer_id", right_on="id", suffixes=("", "_customer"))

mo.vstack([note, mo.ui.table(detail, selection=None)])

Pane-first layout

A pane-first template has one selectable table on its sheet and everything else in the pane:

  • No input or settings cells outside the table. Do not put as-of dates, thresholds or other settings in cells beside or above the table. They crowd the first screen and are easy to overwrite.
  • Settings are constants in the notebook’s rules section, for example LOW_STOCK = 10. For a date that should follow the clock, use a one-liner: TODAY = pd.Timestamp.today().normalize().

Pane recipe

Build the pane as one mo.vstack in the last expression of a cell:

  • Table options: for a read-only display table use mo.ui.table(df.reset_index(drop=True), selection=None, show_data_types=False, show_column_summaries=False, show_download=False). Each option is a mo.ui.table parameter in marimo 0.23.15. selection=None removes the row checkboxes, and the others remove the type row, the summary charts and the download button.
  • Escape $ in mo.md: a pair of $ characters renders as LaTeX. Write \$ for a literal dollar sign, for example mo.md(f"Total: \\${total:,.2f}") (the backslash is doubled inside a normal string, or use a raw string).
  • Re-indexing NaN trap: after reset_index(drop=True) or any filter, a Series from another frame aligns on index labels, so shown["x"] = other["x"] fills NaN where labels differ. Assign values, not Series: shown["x"] = other["x"].to_numpy(), or use merge on a key column.
if picked.empty:
    shown, note = orders.head(1), "Showing the first order. Select a row to change it."
else:
    shown, note = picked, "Showing the selected order."
view = shown[["id", "customer_id", "due", "total"]].reset_index(drop=True)
mo.vstack([
    mo.md(note),
    mo.md(f"**Total:** \\${view['total'].sum():,.2f}"),
    mo.ui.table(view, selection=None, show_data_types=False,
                show_column_summaries=False, show_download=False),
])

3. Publishing Outputs and Functions (bf.publish)

Publish calculated values and Python functions to Excel formulas using bf.publish(...):

# Fixed 10-row block: pad short results with "" so the spill covers the same 10 rows
summary = summary_df.head(10).reset_index(drop=True).reindex(range(10), fill_value="")

bf.publish(
    outputs={"summary": summary},
    functions={"discount": calculate_discount},
)
  • Worksheet outputs: Consumed in Excel cells via =BF.OUTPUT("summary"). Scalars return in a single cell; DataFrames, Series, and 2D arrays spill dynamically into adjacent cells.
  • Spill area: A DataFrame, Series, or 2D array spills into the cells below and to the right of its anchor. Those cells must stay clear, or Excel shows #SPILL!. To keep that area fixed, pad short results with "" up to the rows and columns you leave clear.
  • Worksheet functions: Invoked in Excel formulas via =BF.FUNCTION("discount", A1, B1). Functions accept positional arguments with optional trailing defaults, optional *args, and keyword-only arguments that have defaults.
  • Formula consumers: Inspect publication.consumers to see active worksheet formulas subscribed to each published output and function. Accessing publication.value raises RuntimeError.

4. Complete Example Notebook

The following complete Marimo notebook reads Orders and Customers tables, shows the selected order joined to its customer in the pane (falling back to the first order when nothing is selected), and publishes selection-independent results to Excel formulas:

import marimo

__generated_with = "0.23.15"
app = marimo.App()


@app.cell
def _():
    import boardflare as bf
    import marimo as mo
    return bf, mo


@app.cell
def _(bf):
    # At most one bf.inputs per notebook; widget must be the cell's last expression
    inputs = bf.inputs(
        orders=bf.ref("Orders", headers=True),
        customers=bf.ref("Customers", headers=True),
        picked=bf.selection("Orders"),
    )
    inputs
    return (inputs,)


@app.cell
def _(inputs, mo):
    orders = inputs["orders"]
    customers = inputs["customers"]
    picked = inputs["picked"]

    # Selection drives the pane only; no active row gets a labelled default
    if picked.empty:
        shown = orders.head(1)
        note = mo.md("Showing the first order. Select a row in the Orders table to see its details.")
    else:
        shown = picked
        note = mo.md("Showing the selected order.")

    # Record detail: join the selected row to the related table by value
    detail = shown.merge(customers, left_on="customer_id", right_on="id", suffixes=("", "_customer"))
    mo.vstack([note, mo.ui.table(detail, selection=None)])
    return (orders,)


@app.cell
def _(bf, orders):
    # Published values come from the whole table, so they do not depend on the selection
    total_revenue = float(orders["amount"].sum())

    def get_order_revenue(order_id: int) -> float:
        matches = orders[orders["id"] == order_id]
        return float(matches["amount"].iloc[0]) if not matches.empty else 0.0

    # Publish output for =BF.OUTPUT("total_revenue") and function for =BF.FUNCTION("order_revenue", A2)
    publication = bf.publish(
        outputs={"total_revenue": total_revenue},
        functions={"order_revenue": get_order_revenue},
    )
    publication
    return (publication,)


if __name__ == "__main__":
    app.run()