Finish the widget-in-cell catalog with the two deferred controls, each a
plain value plus a render flag (same convention as checkbox/slider/Markdown).
- Dropdown: CellStyle.choices (a serialized, pipe-escaped list) makes a
cell cycle through its choices on click. The toolbar "Drop" button parses
the selected cell's value as a "Low|Med|High" list into choices (undoable
SetChoices) and selects the first entry; a dropdown renders its value
with a trailing ▾ affordance. dropdown.rs holds next_choice /
parse_choice_list.
- Button: CellStyle.button makes a cell a button whose value is a
TARGET[+N] action spec (A1 ref + optional signed step, default +1).
Clicking increments the target cell's numeric value through set_cell
(undoable, recalculates dependents) — the spreadsheet-native counterpart
of the reference's "+10" boost. button.rs holds parse_button_spec /
button_step / format_step_value; the cell renders as a raised box with
the spec centred. Toolbar "Btn" toggles the flag.
Both flags ride in the render cache so cached cells stay styled, and both
commands route through WorkbookCommand (apply + apply_command + dirty
marking). Choices serialize after the markdown/slider/button flags; older
files default to off/empty.
Engine: 491 lib tests (+4) + integration. UI controllers: 142 tests (+8,
button 5 + dropdown 4). Coverage: engine 96.59%, ui-controllers 99.45%
(floors 96); button.rs and dropdown.rs at 100%.
Close the last sort gap: the spreadsheet grid's header sort now cycles
ascending → descending → off like the datagrid reference, and reports how
long each sort took.
Engine: SpreadsheetData::sort_rows remembers a RowSort { ascending,
key_col, restore } where restore[view_row] is the row the data originally
lived on, composed across sort chains. New unsort_rows applies that map to
restore the pre-sort order (no-op without a sort, clears undo, recalculates
moved formulas); sort_state() reports the active (column, direction).
Workbook gains unsort_active_sheet and active_sort_state. The sort order is
transient — reset by deserialize, never serialized.
UI: sort_state::next_sort_state replaces the 2-state next_sort_direction
with the asc→desc→off cycle, and the workspace HeaderClicked handler times
the sort with std::time::Instant, drives unsort_active_sheet on "off", and
appends the report ("sorted 1000 rows by B descending in N ms") to the
status bar via a cached sort_status field.
Engine: 487 lib tests (+6) + integration. UI controllers: 134 tests.
Coverage: engine 96.66%, ui-controllers 99.42% (floors 96).
Complete the widget-in-cell gap the same value/style-convention way as the
checkbox: two more cell kinds, both plain data with a render flag.
Engine:
- CellStyle gains `markdown` and `slider` bool flags, serialized as two
trailing columns on the CELL line (older files default them off), plus
WorkbookCommand::{SetMarkdown, SetSlider} routed through apply and
apply_command via mutate_cell — undoable like every style mutation.
UI:
- markdown.rs (measured): a flat inline parser splitting a cell value into
Regular/Bold/Italic/Code runs (`**bold**`, `*italic*`, `` `code` ``);
unclosed markers and empty spans stay literal/dropped. The grid draws
each run with the bold or regular resource (code tinted like formulas).
- slider.rs (measured): 0-100 fraction/value mapping (rounded to whole
steps) and track/fill/handle geometry. A slider cell draws the control
instead of text, and a press/drag sets the value through set_cell — one
undo step per drag (reverse-order ChangeSet application restores the
pre-drag value).
- Toolbar "Md" and "Slider" buttons toggle the flags on the selection;
the render cache carries the two flags so cached cells stay styled.
Dropdown and button cells are intentionally not included: a dropdown
needs a per-cell choices list and a button needs an action semantic that
a spreadsheet does not have — both would require a CellKind in the data
model rather than a render flag.
Engine: 481 lib tests (+2) + integration. UI controllers: 133 tests (+13,
markdown 10 + slider 3). Coverage: engine 96.62%, ui-controllers 99.42%
(floors 96); markdown.rs and slider.rs at 100%.
Close the engine half of the virtualization gap (report section A). Add a
GridDataSource trait — row/col counts, per-cell display text, sort, and a
column label — implemented by SpreadsheetData (the real store, delegating
to get_display_value/sort_rows) and by a new VirtualSheet.
VirtualSheet derives every cell procedurally from a hashed (row, col), so
1,000,000 rows × 1,000 columns (one billion cells) cost one struct and no
stored data. Its rendered text and numeric sort key come from the same
hash, so sorting by the key sorts what the user sees; sort_rows permutes a
Vec<u32> row index (O(n log n) over the index, not the data) with a
deterministic tie-break. Column labels mirror the reference (# / Name /
City / Balance / Score / Active, then ·N repeats).
Engine: 478 lib tests (+12) + integration. Coverage: data_source.rs 100%,
engine total 96.67% (floor 96).
Close gap #10 (sparkline cells) from the datagrid gap analysis, split as
usual into testable headless logic plus thin grid glue:
- Engine: a `SPARKLINE(range)` function resolves its single range argument
to a flat numeric series and returns a new `Value::Sparkline(Vec<f64>)`.
The variant is not a scalar — arithmetic on it is a `#VALUE:` error, and
the aggregation/lookup helpers skip it — so no code path invents a number
from a chart. `SpreadsheetData::apply_formula_result` stores the series
on `CellData.sparkline` (derived state, like spills: never serialized,
re-derived on recalculation) and clears it when the formula stops
returning a sparkline. Dependency tracking is inherited from the range
reference, so editing a cell in the source range re-derives the bars.
- UI: a new measured `sparkline` module computes the bar rectangles (bars
rise from the series minimum, tinted up/down by last-vs-first trend,
gap-shrunk for narrow cells) — the same geometry as the reference
`Sparkline` widget, but testable headlessly. The grid draws the bars with
a dedicated `draw_spark` resource (depth 0.4) when a cell carries a
sparkline and skips the text path; the series is cached in
`CellRenderState` alongside the display text. The in-cell editor keeps
its text on top by suppressing the bars while editing.
Engine: 467 lib tests + integration (483 total, +6 sparkline tests).
UI controllers: 90 tests (+5). Coverage: engine 96.60%, ui-controllers
99.15% (floors 96); sparkline.rs at 100%.
Wire up the three missing header interactions from the datagrid gap
analysis, split as usual into testable headless logic plus thin grid
glue:
- Selection kinds: SelectionController gains GridSelectKind
(Cells/Rows/Cols/All). Header clicks select a whole column / row, the
corner (or Ctrl+A) selects the sheet, and bounds() expands the
row/column/all kinds to the sheet's full extent so the fill, copy,
delete, format and autofill paths handle every kind without branching.
- Header-click sort: clicking a column header toggles the sort direction
(same column flips asc/desc, a new column starts ascending) and
re-sorts the active sheet; the sorted header shows an ▲/▼ glyph. The
toggle lives in the new measured sort_state module; the grid holds the
(col, asc) state and the workspace applies Workbook::sort_active_sheet.
- Column reorder: dragging a header past the tap threshold moves the
column, with a drop-indicator line at the insertion index (computed by
the new, tested GridMetrics::col_insert_at). Engine addition
SpreadsheetData::move_column(from, to) remaps cells and column-width
overrides, rebuilds the dependency graph, recalculates, and clears undo
history; Workbook::move_active_column propagates to cross-sheet readers
via the new WorkbookCommand::MoveColumn.
Selection overlay drawing is clipped to the cell area so a full
row/column/sheet selection border no longer paints over the headers and
the surrounding workspace.
Engine: 461 lib tests + integration (477 total). UI controllers: 85
tests. Coverage: engine 96.66% and ui-controllers 99.11% (floors 96).
Tranche 3 of the gap analysis: the datagrid example sorts a million rows
on a header click. This is the engine-side sort that makes that possible
(the UI header wiring and the procedural data provider are later
tranches).
`SpreadsheetData::sort_rows(ascending, key_col)`:
- Sorts every row by the values in one column. Numbers sort before text
(case-insensitively), and empty cells always sort last — in both
directions, unlike a naive `reverse` which would float blanks to the
top on a descending sort.
- Stable: equal keys keep their original order via a row-index tie-break.
- Formulas move with their rows and recalculate against the sorted
positions, matching Excel's reference-by-position semantics.
- Undo history is cleared (a sort is a destructive bulk reorder); row
heights, column widths and named ranges stay positional, as in Excel.
`Workbook::sort_active_sheet(ascending, key_col)`:
- Sorts the active sheet and recalculates cross-sheet dependents so
readers on other sheets see the sorted values.
Tests: 6 (ascending/descending, numbers-vs-text-vs-empty ordering, stable
ties, formula recalculation, non-undoability, workbook-level sort with
cross-sheet propagation). Engine unit tests 447 -> 453. Engine coverage
96.61% (floor 96).
Tranche 1 of the gap analysis against the Makepad `work`-branch
datagrid example: the two formula-engine features it has that we
lacked.
`^` exponentiation operator:
- New `Caret` token, `BinOp::Pow` (precedence 5), and a right-associative
`parse_pow` between multiplicative and unary in the parser.
- Unary minus binds tighter than `^`, so `-3^2` is `(-3)^2 = 9` — Excel's
precedence, and the exact assertion the datagrid reference pins.
- `2*3^2` = 18 (power over multiply) and `2^3^2` = 512 (right-assoc).
- Evaluated in `apply_binop`, so it broadcasts element-wise over arrays
like every other operator.
General-format thousands separators:
- `format_number` stays comma-free — computed values and criteria must
round-trip through `parse_cell_computed_value` and `parse::<f64>`.
- New `format_number_display` (plus a `group_thousands` helper) groups
the integer part at the display boundary only: `apply_number_format`'s
General arm now shows `1,000,000` while the raw/edit value stays
`1000000`. Non-numeric text passes through untouched.
Tests: `^` precedence/associativity/evaluation, display grouping, and an
end-to-end check that comma display does not break the recalc fast path.
Engine unit tests 441 -> 447; UI lib tests still pass. Engine coverage
96.58% (floor 96).
Completes the first-class-function story started with LAMBDA-as-an-
argument.
Postfix application:
- A new `Expr::Apply { func, args }` node lets a callable expression be
invoked directly: `LAMBDA(x, x*2)(3)` and curried `LAMBDA(x, LAMBDA(y,
x+y))(1)(2)`. The parser gained a `parse_postfix` loop, so `f(...)(...)`
chains bind tightest, after the primary expression.
- Arguments are bound with `resolve_bound_arg`: a range or array binds as
an array (so `LAMBDA(x, SUM(x))(A1:A4)` aggregates), a scalar binds as
a scalar.
- Applying a non-callable is `#VALUE!`; an argument-count mismatch is
reported. Postfix arguments still register their cell dependencies.
LET:
- `LET(name1, value1, [name2, value2, ...], body)` binds names to values
sequentially — a later value may reference an earlier name — and
evaluates the body with the names in scope, reusing the LAMBDA
substitution machinery. A range value binds as an array, so
`LET(s, A1:A4, SUM(s))` aggregates the whole range.
- Duplicate names are rejected in both LET and LAMBDA, matching Excel;
an unbound name in the body is `#NAME?`.
Tests: 6 unit tests (postfix application, currying, LET binding/range/
error shapes, duplicate-parameter rejection, dependency tracking) + an
end-to-end test proving LET and postfix application recalculate through
the dependency graph. Engine unit tests 434 -> 441. Engine coverage
96.55% (floor 96).
Closes the four limits documented when the spill model landed.
LAMBDA (was: GROUPBY/PIVOTBY only took an aggregate name):
- `LAMBDA(params..., body)` builds a `Value::Callable` without evaluating
its body. A new `Expr::BoundValue` node splices a bound argument into
the body AST when the callable is applied, so the ordinary evaluator
runs the body.
- `GROUPBY` and `PIVOTBY` accept either a named aggregate (`SUM`, ...)
or a `LAMBDA`, applied per value column / per pivot bucket. This
unlocks arbitrary aggregations (`LAMBDA(x, MAX(x)-MIN(x))`).
FILTER include + full arithmetic broadcasting:
- `FILTER` now accepts a same-shape include: matching cells are kept and
non-matching positions become `#N/A`, element-wise, like Excel.
- Unary operators broadcast over an array (`-A1#`, `-FILTER(...)`),
completing the operator-level arithmetic alongside the existing binary
broadcast.
Spill formatting:
- `SpillRange` records the anchor's `NumberFormat`, and derived cells are
formatted at display time through a shared `apply_number_format`
(extracted from `write_display_value`). Raw values stay numeric, so
`SUM(A1#)` still evaluates correctly.
#SPILL! blocking:
- A spill that would overwrite an existing cell — or another spill,
flowing or blocked — reports `#SPILL!` in the anchor instead of
clobbering data. The would-be range is remembered in `blocked_spills`,
and clearing the blocking cell retries the spill automatically.
Tests: 7 new unit tests (LAMBDA in GROUPBY/PIVOTBY, lambda errors,
same-shape FILTER, unary broadcast) + 2 end-to-end tests (NumberFormat
inheritance and #SPILL! block-then-retry). Engine unit tests 427 -> 434;
UI lib tests still pass. Engine coverage 96.41% (floor 96).
The engine stored every formula result as one display string per cell.
This adds the dynamic-array architecture: a formula can return a grid,
which "spills" into the cells below/right of its anchor.
Value model:
- `Value::Array(Vec<Vec<Value>>)` — a grid result. Scalars coerce to
one-cell grids where the shape matters; `to_f64`/`to_bool` refuse an
array, `to_display_string` shows the top-left, and `resolve_arg`
flattens an array argument so `SUM(FILTER(...))` aggregates its cells.
- Element-wise broadcasting: `resolve_array2d` maps a binary operator
over a grid when one side is a scalar (or both grids share a shape),
so `FILTER(A1:A4, C1:C4 > 15)` builds the boolean include the way
Excel does. The binary-op logic was factored into `apply_binop`.
Parser and AST:
- `#` is now the spill operator: `A1#` and `Sheet2!A1#` parse as
`SpillRef` / `SheetSpillRef`, evaluate to the anchored array (so
`=A1#` re-spills), flatten in aggregates, and track their anchor in
the dependency graph (intra- and cross-sheet).
Functions (dynamic arrays):
- FILTER(array, include, [if_empty]) — keep rows (column include) or
columns (row include); `#N/A` on no match unless `if_empty`.
- GROUPBY(row_fields, values, function, [field_headers]) — group rows
by field tuples and aggregate each value column (SUM/AVERAGE/COUNT/
MAX/MIN/MEDIAN by name, ETA-reduced-LAMBDA form).
- PIVOTBY(row_fields, col_fields, values, function) — a 2D pivot with
the aggregate name in the top-left corner.
Spill storage (data.rs):
- `SpillRange` + `spills` map on `SpreadsheetData`: derived cells read
back through `get_display_value`/`get_raw`/`get_edit_value`, are not
blank, and are read-only — `set_cell`/`put_cell`/`remove_cell`/
`mutate_cell` refuse to touch them (the UI blocks via `is_spilled`).
- Recalc builds the spill from the `Array` result (`apply_formula_result`)
and drops stale spills when a formula becomes scalar, is removed, or
cycles. Spills are derived state, never serialized — the anchor
formula persists and re-derives on load.
- Cross-sheet spills read through `get_sheet_spill_values`.
Tests: 21 new units (FILTER/GROUPBY/PIVOTBY shapes, broadcasting,
Value::Array methods, spill parsing) + 5 end-to-end tests (spill
display, read-only cells, `A1#` aggregation and re-spill, stale-spill
clearing, cross-sheet spill). Engine unit tests 405 -> 427; UI lib tests
still pass. Engine coverage 96.66% (floor 96).
Full GROUPBY/PIVOTBY need a dynamic-array "spill" model (a formula
returning a grid), which this single-cell engine deliberately does not
have. These are the single-cell building blocks that do the same work.
Multi-criteria conditional aggregates (the "filter then aggregate" pivot
core), resolved positionally so criteria ranges stay aligned:
- SUMIFS(sum_range, criteria_range1, criteria1, ...)
- AVERAGEIFS(avg_range, criteria_range1, criteria1, ...) (#DIV/0! on no match)
- COUNTIFS(criteria_range1, criteria1, ...)
- MAXIFS / MINIFS (0 on no match, like Excel)
Mismatched range sizes are #VALUE!, not a silent misalignment.
Chart statistics (over the flattened numeric arguments):
- MEDIAN, MODE (ties keep the smallest value), and the sample/population
STDEV / STDEVP / VAR / VARP. Sample forms divide by n-1 (#DIV/0! for a
single value), population by n.
Pivot/ranking helpers:
- SUMPRODUCT(array1, [array2], ...) — the element-wise dot product;
text counts as zero, errors propagate, mismatched sizes are #VALUE!.
- LARGE / SMALL(array, k) — k-th largest/smallest; k out of range is the
new #NUM! error.
- RANK(value, array, [order]) — descending by default, ascending on any
nonzero order, tied values share a rank (RANK.EQ).
`FormulaError` gains `NumError` (`#NUM!`) for out-of-domain numeric
arguments, rounding out the error surface after `Na` in the lookup
tranche; it round-trips through display/parse and propagates from cached
values.
Tests: 10 unit tests (multi-criteria aggregation, statistics, dot
products, top-N/ranking, argument/size errors) + an end-to-end test
proving the dependency graph tracks every range and recalculates the
pivot formulas when a source cell is edited. Engine unit tests 395 ->
405. Engine coverage 97.34% (floor 96).
Completes the cross-sheet reference feature with `Sheet!Name`, the one
shape the previous tranche explicitly left out.
Parser and AST:
- `Sheet2!Total` and `'My Sheet'!Sales` parse as a new
`Expr::SheetNamedRange { sheet, name }` node. In
`parse_sheet_qualified_ref`, an identifier after `!` that is not a
valid cell reference is treated as a named range; a cell reference
wins when both readings are possible (`Sheet2!A1`), matching Excel.
Evaluation:
- `EvalContext` gains `get_sheet_named_range` (default `None`).
`evaluate` resolves a `SheetNamedRange` to its range and returns the
first cell in expression context, while `resolve_arg` expands it to
every cell for aggregates — so `Data!Total` reads one cell and
`SUM(Data!Total)` sums the whole range. An unknown name on a real
sheet is `#NAME?`, and `DataEvalContext` looks the range up on the
sibling sheet case-insensitively (or on the sheet itself, through
the intra-sheet path).
Dependencies:
- A named-range reference is a coarse sheet-level dependency, because
its bounds live on the target sheet. `SpreadsheetData` tracks
`cross_sheet_named_refs` (cell -> sheet names), rebuilt by
`rebuild_dependency_graphs` / `update_dependency_graph` alongside the
cell-level `cross_sheet_refs`, and the workbook folds both into its
sheet-index dependency map. Editing a cell inside the named range
therefore recalculates the reading sheet.
Tests: parser/evaluator units (incl. quoted names, cell-vs-named
precedence, dependency extraction) and workbook end-to-end (SUM over a
cross-sheet named range with propagation on edit, quoted names, and the
#NAME? case). Engine unit tests 390 -> 395; UI lib tests still pass.
Engine coverage 97.56% (floor 96).
The engine's dependency graph is per-sheet by design, so this was the
structural change: a formula can now read a cell on another sheet, and
edits to the source sheet recalculate the readers.
Parser and AST:
- New tokens: `Bang` (`!`) and `SheetName` (`'My Sheet'`, with `''` as
an escaped quote). `!=` still tokenizes as not-equal.
- New AST nodes: `SheetCellRef { sheet, cell }` and
`SheetRange { sheet, range }`, parsed from `Sheet2!A1`,
`Sheet2!A1:B2`, `'My Sheet'!B3`, before the cell-ref fallback (a bare
`Sheet2` would otherwise parse as a bogus cell reference).
- `evaluate` and `resolve_arg` resolve them through two new `EvalContext`
hooks, `get_sheet_cell_value` / `get_sheet_range_values`, which default
to `#REF!` in contexts without sibling sheets.
Evaluation:
- `DataEvalContext` gains a sibling-sheet view plus a shared cross-sheet
cycle guard keyed by `(sheet, row, col)`. Reading a sibling routes to a
child context pointed at that sibling; re-entering the same cell on the
same sheet mid-evaluation reports `#CYCLE!`, and a reference back to the
sheet being recalculated is caught through the guard.
- `recalculate_all_with` / `evaluate_formula_with` accept the sibling
view; the plain per-sheet entry points are unchanged.
Recalculation and dependencies:
- `SpreadsheetData` tracks `cross_sheet_refs` per cell (rebuilt by
`rebuild_dependency_graphs` / `update_dependency_graph`), and the
workbook flattens it into sheet-index edges. Editing a sheet
recalculates — with sibling access — the transitive closure of sheets
that read it, plus the sheet itself (its own formulas may read other
sheets). Single-sheet edits with no cross-sheet references keep the
fast incremental path.
- `evaluate_all` clears computed values across sheets first, then runs
one extra pass per sheet, so acyclic chains propagate and mutual
cross-sheet cycles terminate with `#CYCLE!`.
- Cross-sheet formulas survive save/load: deserialization runs a
sibling-aware pass, and a missing sheet renders `#REF!`.
Tests: parser/evaluator units (incl. quoted names, dependency
extraction) and workbook end-to-end (read + propagation, a three-sheet
chain, quoted names, missing sheet, save/load round trip, cycle
termination). Engine unit tests 382 -> 390; UI lib tests still pass.
Engine coverage 97.55% (floor 96).
The formula engine could compute over ranges but could not look a
value up in one. This adds the Excel lookup family, plus the #N/A error
they need to report "not found".
New `FormulaError::Na`:
- Renders as `#N/A`, parses back in `from_display`, and — a bonus fix —
the recalc fast path now propagates a stored `#N/A` as an error
instead of turning it into text.
Functions (5):
- MATCH(lookup_value, lookup_array, [match_type]) — 1-based position.
Type 0 exact (case-insensitive text), 1 largest ≤ lookup, -1 smallest
≥ lookup.
- INDEX(array, row_num, [col_num]) — cell at a 1-based position; a
single index walks the array flat in row-major order. Out of range
is #REF!.
- VLOOKUP / HLOOKUP — exact or approximate (approximate takes the
largest first-column/first-row value ≤ lookup, the sorted-table
convention), col/row index out of range is #REF!.
- XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode]) — match modes 0 exact, -1 next-smaller,
1 next-larger, 2 wildcard (`*`/`?`, case-insensitive); search modes
1 first-to-last and -1 last-to-first; the `if_not_found` fallback is
evaluated lazily, only when nothing matches. Binary search modes are
rejected with a clear error rather than silently mishandled.
Lookups index ranges by position, so they keep their arguments as AST
nodes (a new `range_from_arg` resolves Range and NamedRange expressions)
instead of flattening through the aggregate path.
Tests: 9 new unit tests (exact/approximate/wildcard/search-direction/
error arms) + an end-to-end test proving the dependency graph tracks the
lookup table and recalculates dependents when a table cell is edited.
Engine unit tests 373 -> 382.
The formula engine had no notion of dates. This adds an Excel-style
date/time model and the functions to work with it.
Date model (new `dates` module):
- A date/time is a single f64: the integer part is a day serial, the
fraction is the time of day. Serial 25569 = 1970-01-01, so every
modern date agrees with Excel exactly. The calendar is the proleptic
Gregorian (Howard Hinnant's days_from_civil/civil_from_days), so
Excel's phantom 1900-02-29 (serial 60) reads back as 1900-02-28 and
serial 61 = 1900-03-01 — documented rather than reproduced.
- Serial <-> calendar conversion, day-of-week, leap-year and
days-in-month helpers, date/time string parsing, EDATE/EOMONTH,
DATEDIF(Y/M/D), and ISO formatting.
Functions (17):
- DATE, DATEVALUE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIME,
TIMEVALUE, WEEKDAY (return types 1/2/3), DAYS, EDATE, EOMONTH,
DATEDIF — with Excel's month/day rollover in DATE, day clamping in
EDATE, and #VALUE! for malformed strings and unknown units.
- TODAY and NOW read a wall clock. The engine stays deterministic:
`EvalContext` gains a default-none `now_serial` hook, and
`SpreadsheetData` carries an optional `now_serial` (never
serialized). Without a clock they report "requires a wall clock";
`SpreadsheetData::set_system_now` / `Workbook::set_system_now` supply
the system clock, and the UI workspace model wires it on creation.
Display:
- `NumberFormat::Date` and `NumberFormat::DateTime` render a serial as
`YYYY-MM-DD` / `YYYY-MM-DD HH:MM:SS` through write_display_value,
with codes that round-trip through the existing serialization.
Tests: 18 new unit tests (calendar round-trips across centuries,
known serials, leap-year rules, parsing, weekday schemes, EDATE/EOMONTH
clamping, DATEDIF, ISO formatting) + 6 evaluator tests + an end-to-end
test covering format rendering and recalc through the dependency graph.
Engine unit tests 352 -> 373. UI lib tests still 62 pass with the new
clock wiring.
The formula engine could count, sum, compare and test conditions, but
had almost no way to work with text (only CONCAT/LEN/UPPER/LOWER) and
no way to ask about a value's type. This adds both.
Text functions (operate on the display form of their arguments):
- TRIM — collapse internal space runs and drop leading/trailing spaces
- LEFT / RIGHT — first/last n characters (default 1)
- MID — n characters from a 1-based start
- SUBSTITUTE — replace all occurrences, or just the nth instance
- FIND / SEARCH — 1-based position, case-sensitive vs case-insensitive,
#VALUE! when not found or start is out of range
- REPT — repeat n times, capped at Excel's 32767-character result
- PROPER — title-case each word
Info functions (never propagate their argument's error, like Excel —
ISERROR reports it, the others treat it as FALSE):
- ISNUMBER / ISTEXT / ISNONTEXT / ISLOGICAL / ISERROR
- ISBLANK — TRUE only for an absent cell. This needs the distinction
between "missing" and "holds 0", so EvalContext gains a
`cell_is_blank` method; DataEvalContext overrides it with a direct
cells lookup (an absent cell reads as Number(0.0) through
get_cell_value, but is blank, whereas a real 0 is not).
Dependencies flow through the existing AST walk, so a TRIM/LEFT/etc.
reference is tracked and recalculates when its source cell is edited —
pinned by an end-to-end test through SpreadsheetData.
Engine unit tests 345 -> 352.
The formula engine could sum, average and do single-cell math, but had
no way to combine conditions or aggregate selectively — no `=IF(AND(...))`,
no `=SUMIF(...)`. This closes that gap with seven Excel-compatible
functions.
Logical (evaluated lazily, like IF, so they short-circuit):
- `AND(a, b, ...)` — TRUE iff every argument coerces to TRUE; ranges
flatten to their cells. Stops at the first FALSE, so `AND(FALSE, 1/0)`
is FALSE rather than `#DIV/0!`.
- `OR(a, b, ...)` — TRUE iff any argument is TRUE, short-circuiting on
the first TRUE.
- `NOT(x)` — logical negation of the single argument.
- `IFERROR(value, fallback)` — `value` unless it errors, in which case
the fallback is evaluated and returned (lazily).
Conditional aggregates (criteria-matched by position):
- `COUNTIF(range, criteria)` — count of matching cells.
- `SUMIF(range, criteria[, sum_range])` — sum of `sum_range` (or
`range`) cells whose position matches; text in the summed region is
skipped, errors propagate.
- `AVERAGEIF(range, criteria[, average_range])` — mean of the matched
cells, `#DIV/0!` when nothing matches.
Criteria accept numbers, comparison operators (`>5`, `>=5`, `<5`, `<=5`,
`<>5`, `=5`), case-insensitive text (`"apple"`, `=apple`, `<>apple`),
and cell references holding any of those. Wildcards are not supported.
Dependencies flow through the existing AST walk, so a SUMIF's range and
criteria cell are tracked by the dependency graph and the formula
recalculates when an input is edited — pinned by an end-to-end test
through SpreadsheetData.
Engine unit tests 331 -> 343.
The last reachable lines in data.rs, all in the persistence layer and
one style command:
- serialize/deserialize round-trip for named ranges (the NAME block
line), text colour, and an unknown block tag (ignored for forward
compatibility), plus a malformed NAME line inserting nothing.
- The legacy CSV deserializer's `|B` bold and `|F` formula metadata
segments.
- apply_command with BorderTarget::None clearing all four edges (the
"remove borders" action), the complement of the per-edge arm the
existing test covers.
- A large-range self-reference reporting CycleDetected (pins the fix
in the previous commit).
data.rs 97.91% -> 99.49%; engine total 98.50% -> 99.13%. Unit tests
325 -> 331.
Deliberately uncovered, as documented: the B17 invariant's
debug_assert/#ERROR! patch (only reachable on a dep-graph bug), the
topological-sort underflow guard, the let-else continue after a cell
disappears mid-iteration, and the `}` attribution regions after
unconditional returns.
The large-range fast path in DataEvalContext::get_range_values (used
for ranges over 64 cells) handled a re-entrant formula cell — a
reference cycle — by silently pushing 0.0. The small-range path in
get_cell_value reports FormulaError::CycleDetected for the same
situation. So a cyclic formula inside a large range contributed zero
to the sum instead of surfacing #CYCLE!.
The inconsistency is invisible in normal recalculation: the topological
sort in recalculate_incremental detects cycles before any evaluation,
so the branch only fires through the legacy recursive get_cell_value
API. The fix makes the two range paths agree.
Three files, each the weakest reachable logic left after the workbook_api
tranche.
autofill.rs 92.71% -> 96.26%:
- A single chrono value is still a series (delta defaults to 1).
- A chrono sequence whose delta changes mid-run is not a series.
- A repeated chrono value has delta 0 and is rejected rather than
producing an infinite fill.
- A backward chrono sequence wraps the delta modulo the list length
(Feb, Jan -> delta 11).
- get_series_value on Series::None returns None.
- match_case with an empty value returns an empty string.
undo.rs 98.34% -> 100%:
- Redo of a deletion (a Change::SetCell whose `new` is None, the shape
recorded by set_cell("") and remove_cell) removes the cell and its
dependency-graph edges; undoing it restores both. This is the mirror
of the existing create/undo test and was the one uncovered arm in
Change::apply_redo.
workbook.rs 99.35% -> 99.68%:
- detect_workbook_version on a legacy `#MP_SHEET_V2` payload reports
version 1.
Engine total 98.24% -> 98.50% (floor 96). Unit tests 318 -> 325.
Deliberately uncovered, as before: panic-arm canaries in positive
tests, the `}` after an unconditional return in detect_series, and the
test-harness save/restore cleanup branches in persistence.rs (their
inverse runs depending on pre-existing state — the CI-normal path).
The two weakest files left after the formula2 tranche.
workbook_api.rs 88.80% -> 99.58%:
- with_sheets_active with an empty sheet list falls back to a fresh
single-sheet workbook.
- set_active_sheet with an out-of-range index is a no-op.
- remove_sheet: removing a sheet before the active one shifts the index
down; removing the active one (or one after it) clamps to the new end.
- apply(): the style arms the earlier tests did not touch — italic,
underline, number format, background — and both SetBorders shapes
(per-edge target writes each requested edge, BorderTarget::None clears
all four).
- Workbook::default() is a fresh single-sheet workbook.
- save()/load() round-trip through the default path, a legacy payload
still loads as a one-sheet workbook, and a non-workbook payload yields
None. The disk test takes a new crate::test_disk lock so it cannot
race the persistence module's disk test under parallel `cargo test`;
that pre-existing test now takes the same lock.
util.rs 90.91% -> 100%:
- write_col_letters matches col_letters across one-, two- and three-
letter columns and reuses the caller's buffer without stale tails.
- adjust_formula_refs: a digit-bearing function name (LOG10) is not a
cell ref; an 8-letter column overflowing u32 and a 30-digit row
overflowing i64 are copied verbatim through the overflow fallbacks.
Engine total 96.54% -> 98.24% (floor 96). Unit tests 308 -> 318;
9 integration tests unchanged.
Deliberately uncovered: the mutex-poison recovery closure in the
test_disk lock (unreachable unless a test panics while holding it), and
the disk test's restore-to-clean branch (runs only when no save file
pre-exists — the CI-normal path; its inverse is the branch that runs
otherwise). Both match the existing persistence test's save/restore
convention.
`Workbook::load()` carried a fallback — if the saved payload was not a
V2 workbook but "looked legacy", migrate it — plus the two private
helpers behind it, `is_legacy_workbook_payload` and
`migrate_legacy_workbook`.
The branch is unreachable. `deserialize_workbook` already treats both
legacy V1 formats (single-sheet `#MP_SHEET_V2` and the older CSV) as
`WorkbookFormat::V1Legacy` and returns a migrated one-sheet workbook,
so by the time `load()` sees a legacy payload the `if let` above the
fallback has already returned `Some`. The two conditions are exact
complements — any string `deserialize_workbook` rejects is also rejected
by `is_legacy_workbook_payload` — so the migrate branch and both helpers
are dead code, confirmed by direct probe against `detect_workbook_format`.
Behavior is unchanged: the legacy migration still happens, inside
`deserialize_workbook`, which the existing
`legacy_v1_migration_clears_undo_history` test already pins.
The branches the corrected report named, now that every test binary is
measured:
- Tokenizer: a lone `!` is a parse error; string escapes (`\n`, `\t`,
`\\`); a double-dot number (`1.2.3`) is rejected after the tokenizer
breaks on the second dot.
- FormulaError: `InvalidRef` and `RuntimeError` display strings; the
`Display` delegation; `from_display` round-trip for `#REF!...`.
- BinOp precedence table, pinned so a shared precedence cannot pass
silently.
- Value conversions: `to_f64` / `to_bool` / `to_display_string` /
`is_error` propagate and render `Value::Error`.
- Evaluator: boolean literals; unary `+` (built directly — the parser
collapses `+x` to `x`, so the `UnaryOp::Pos` arm only runs on a
hand-built AST); single-cell named range; single- and multi-cell
range expressions; `SUM(Undefined)` -> UnknownName through
`resolve_arg`; `values_equal` text (case-insensitive), boolean,
error and mixed-type arms.
- evaluate_call: `SQRT(-4)` -> `#DIV/0!`; `LOG10`, `SIN`, `COS`, `TAN`.
- evaluate_if: wrong argument count -> `#VALUE:`.
formula2.rs 90.11% -> 99.05% lines; engine total 94.38% -> 96.54%.
Unit tests 289 -> 308; 9 integration tests unchanged.
Deliberately left uncovered: the `invalid number` map_err closure in
the tokenizer (an f64 parse of a digit/dot string cannot fail) and the
`_ => panic!(...)` arms of existing positive match tests.
`parse_inner` is a byte-for-byte duplicate of the earlier iteration of
`CellRef::parse` that carried `#[allow(dead_code)]` since a refactor,
and no caller in the repository references it. It never ran, so its 60
lines could only ever drag the coverage report down without guarding
anything. `parse` remains the single entry point for cell-reference
grammar.
The coverage report was reading one object file. Cargo builds each
integration test into its own executable, so measuring only the lib-test
binary discarded everything `tests/` exercised.
That is not a rounding error. `persistence.rs` reported 41.77% with 14 of
its 17 functions apparently never called, while `tests/sync_flow.rs` was
calling `save_spreadsheet_state` and `load_saved_spreadsheet_state` on
every run and passing. The functions were covered; the report was reading
the wrong object. Fixing it alone moved persistence.rs to 72.15% and the
engine total 90.09% -> 90.57% without a single new test.
This is the third defect of its kind in this script — the ignore regex
that excluded the sources being measured, the awk matcher that never
fired, and now the single-object report. All three had the same
signature: a confident number that was measuring less than it claimed.
`--all-targets` for the UI crate too, so a future `tests/` file is
measured the day it is added rather than silently skipped. Doing that
immediately surfaced `spreadsheet-ui/tests/ui.rs`, which had **never
compiled**: the crate did not enable `makepad-widgets`' `test` feature,
so `makepad_widgets::makepad_test` did not resolve. `cargo test --lib`
never built it and nothing reported the breakage. The manifest now
enables the feature, matching `pdf-makepad`, and the two tests are
`#[ignore]`d with the same documented reason as `pdf-makepad`'s — the
fork has no headless Linux backend. Compiled on every run, so they
cannot rot further while appearing to be coverage.
Then the branches the corrected report named:
- `undo.rs` 86.11% -> 98.34%. Resize undo/redo, both directions. The
`None` arms are the substance: a column with no width override must
have its key *removed* on undo, not have a default written into it.
Writing a default looks identical until the default changes, at which
point every previously-resized-then-undone column stops following it.
- `persistence.rs` -> 93.70%. The legacy `current.sheet.csv` fallback,
including that a whitespace-only current file must not shadow a real
legacy one; `save_spreadsheet_state_as` writing where it says it does;
and a rejected filename writing nothing at all.
- `style.rs` 92.19% -> 100%. Format and alignment codes round-trip, and
the codes are distinct — a shared code passes a round-trip test while
making two formats indistinguishable on disk.
- `model.rs` 84.87% -> 90.99%. Undo/redo intents, from_parts/into_parts,
the active-sheet accessors agreeing with each other, and the disk
round trip (`#[ignore]`d: it writes the shared generated/ file).
Verified by mutation, seven injected defects, each confirmed red:
undo None-arm writes a default 6 fail
two number formats share a code 1 fail
save_as ignores its validation 1 fail
legacy fallback removed 2 fail
Undo intent wired to redo() 1 fail
from_parts drops the active index 1 fail
active_sheet_data_mut hits sheet 0 1 fail
Two of those changed the tests rather than merely passing:
- `save_as ignores its validation` really does write `../escape.tsv`
into the crate root, and the file survives the failing run — so every
later run failed on the previous run's debris rather than on the
current code. The test now removes any leftover before asserting.
- `undo_and_redo_intents_reach_the_workbook` failed on first run because
`apply()` does not call `begin_recording` and `apply_batch()` does, so
there was nothing to undo. That asymmetry is the trap pinned by the
engine's `only_set_cell_records_its_own_undo_step`; it now has a test
on the model side too, since a caller reaching for `apply` and then
offering an undo button gets a button that does nothing.
Also fixed a pre-existing clippy **error** in `util.rs` — `approx_constant`
on a literal `3.14` in a test that has nothing to do with PI. Confirmed
pre-existing by reproducing on a stashed tree. It denies the whole crate,
so no clippy gate could be added while it stood.
Engine 269 -> 280 tests, 90.09% -> 91.58%; floor 90 -> 91.
UI 17 -> 23 tests, 94.55% -> 95.70%; floor 94 -> 95.
Targets the uncovered branches the coverage report named, around the
formula dependency graph and incremental recalculation:
- Formula replacement/removal edge cases: set_cell("") removes the
cell and its edges, formula->value and formula->formula rewiring
drop stale dependency edges.
- No-op removal paths: set_cell("") on a missing cell records nothing.
- Dependency-graph cleanup after formula deletion: remove_cell on a
formula cell, and update_dependency_graph's defensive path when a
dependent has no dependents entry.
- Affected-cell recalculation error paths: non-formula cells inside a
cycle-affected set keep their raw value; the ="" empty-result
regression; the recursive eval slow path (cached AST, parse
fallback, and CycleDetected); parse_cell_computed_value arms; parse
errors propagating through a dependent and through a large range.
Also covers named-range unary expansion (=-Total), the >64-cell range
fast path, non-numeric number-format fallbacks, and the demo_q3 /
demo_roi constructors.
Engine line coverage: data.rs 91.17% -> 97.90%; engine total
90.09% -> 93.24%. Unit tests 260 -> 279 (19 new); 9 integration tests
unchanged.
`recalculate_incremental` treated "empty computed_value on a changed
formula cell" as proof that the topological sort dropped the cell, and
debug_asserted on it. But a formula may legitimately evaluate to the
empty string (`=""`, `=IF(FALSE, "x", "")`), leaving computed_value
empty through no fault of the dep-graph walk. In a debug build that
edit panicked the engine.
The invariant now checks what it actually meant to check: whether the
topological sort *visited* every changed formula cell, using the
`visited` set built from `sorted`. Emptiness is no longer used as the
error signal, so legitimate empty-string results flow through (the
display falls back to the raw formula text, as already documented for
empty computed values).
Nine tests against `workbook_api.rs`, the weakest file in the engine at
77.80%. Now 88.80%; engine total 88.97% -> 90.09%, tests 259 -> 260.
One of them pins a trap rather than a bug. `set_cell` calls
`begin_recording()` before `apply()`; the other eight public mutators
(`remove_cell`, `put_cell`, `set_col_width`, `set_row_height`,
`toggle_bold`, `set_number_format`, `set_alignment`, `set_bg_color`) do
not. Calling one of those directly and then `undo()` reverts the
*previous* recorded edit, not the one just made.
Nothing ships broken: every `spreadsheet-ui` call site takes its own
`data.snapshot()` first, checked one by one in `grid.rs`. But the
asymmetry is invisible at the call site and the next caller will not know
to snapshot. `only_set_cell_records_its_own_undo_step` states the current
contract so a change to it is a deliberate decision rather than an
accident.
