Formulas, links and merges

Add live calculations, hyperlinks and joined cells

Three small features that share a habit: each takes a list of tuples rather than dictionaries.

Formulas

formulas · list[tuple[int, int, str, str | None]]

(row, col, formula, cached_value).

formulas=[
    (2, 3, "=SUM(A2:C2)", None),
    (5, 3, "=AVERAGE(D2:D4)", "45.5"),
    (6, 3, '=IF(D5>50,"High","Low")', None),
]

Cached values

The fourth element is what the cell displays before Excel recalculates. None leaves it empty until someone opens the file.

Supply a cached value

When the formula reaches outside the workbook, takes real time to compute, or when something might read the file without recalculating, such as a preview pane, another library, or an automated pipeline.

Leave it None

For ordinary local formulas, and always for volatile functions such as NOW() or RAND() where a cached value would be stale on arrival.

Jetxl doesn’t check a cached value against the formula. Supply a wrong one and the sheet shows it until the first recalculation, then silently changes. Only cache values you actually computed.

Hyperlinks

hyperlinks · list[tuple[int, int, str, str | None]]

(row, col, url, display_text). A None display shows the raw URL.

hyperlinks=[
    (2, 0, "https://example.com", "Visit Example"),
    (3, 0, "https://example.org", None),
    (4, 2, "mailto:[e-mail]", "Email us"),
]

mailto: works, and so do the other schemes Excel understands. A display text of None is fine for short URLs and unreadable for long ones.

Merged cells

merge_cells · list[tuple[int, int, int, int]]

(start_row, start_col, end_row, end_col).

merge_cells=[
    (1, 0, 1, 3),   # A1:D1 — a title across four columns
    (2, 0, 5, 0),   # A2:A5 — a label down four rows
]

A merge keeps only the top-left value and discards the rest. Merged cells also break sorting and filtering over the range, so keep them in title blocks and out of your data.