Sheetwrite 0.5.0

Sheetwrite 0.5.0 adds an optional full formula engine, makes collaboration recover without a reload, lets an app give engine memory back to the browser, and makes large documents faster to load, edit, and undo. All seven packages release as 0.5.0. Read the breaking changes first: most apps already call initSheetwrite() and need no change.

Highlights

• Full formula engine. @sheetwrite/formulas adds grouping, pivots, LAMBDA, statistics, regression, finance, regex, and reshaping functions.
• Spill references and array constants in both engines: =SUM(A1#) and ={1,2;3,4}.
• Large undo works. Undo of a very large clear is one history step, sent as one compressed operation or one atomic batch.
• Conflict recovery without a reload. SyncCoordinator.recoverConflict() recovers a cell-level conflict on the live Grid.
• Engine memory goes back to the browser. releaseEngineMemory() moves the live stores to a fresh engine instance: after a million-row sheet is cleared to ten rows, WebAssembly memory drops from 173 MiB to 38 MiB.
• String storage stays bounded. Text that edits overwrite or clear is reclaimed and reused instead of piling up for the life of the document.
• Faster. Loading 100,000 to 1,000,000 rows is 26–35% faster, XLSX import 38% faster, and CSV import 52% faster.

New

Full formula engine

@sheetwrite/formulas is the same Rust engine as @sheetwrite/wasm, built with the analysis families. It has the same API and loaders. Select it one time, before you create a Grid:

import { initSheetwrite } from "@sheetwrite/core";
import * as formulas from "@sheetwrite/formulas";

await initSheetwrite(undefined, formulas);

It adds:

• grouping and pivot tables: GROUPBY, PIVOTBY, PERCENTOF;
• LAMBDA and its helpers: MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY, ISOMITTED;
• 36 statistical distribution functions, descriptive statistics, and ranking;
• regression: LINEST, LOGEST, TREND, GROWTH, FORECAST.LINEAR;
• finance and working-day functions, including NETWORKDAYS.INTL;
• text: TEXTSPLIT, TEXTBEFORE, TEXTAFTER, and REGEXTEST, REGEXEXTRACT, REGEXREPLACE;
• reshaping: VSTACK, HSTACK, WRAPROWS, TOCOL, EXPAND, SORTBY;
• database functions and matrices: MMULT, MINVERSE, MDETERM, MUNIT.

The default engine does not change: its function set and binary stay the same, and formula assist suggests only the functions of the selected engine.

• Analysis formulas guide: one worked task per family.
• Formula function contract: every function and its build.
• Formula analysis showcase: thirteen panels recalculate live over 20,000 orders.

Formulas in both engines

• Spill references. =SUM(A1#) refers to the current spill of A1 and follows it when it changes size. XLSX files store it as _xlfn.ANCHORARRAY. See Spill references.
• Array constants. ={1,2;3,4} spills two rows and two columns, and array constants work as function arguments. See Array constants.
• Array arithmetic. Operators apply to each array element. Scalars and single rows or columns expand to match the other operand.

Large undo and atomic batches

Undo of a very large clear now works and stays one history step. The Grid sends the restore as one compressed restoreBlock operation. If that still needs more than one server version, it uses an atomic batch through the new optional PersistenceAdapter.commitBatch: the server applies all versions or none, and peers apply the whole batch in one transaction. An undo above every limit changes nothing and emits mutation-rejected, and older edits stay undoable. See Undo and redo and Large undo and atomic batches.

Conflict recovery without a reload

After a base-version conflict, call sync.recoverConflict(). When the pending work and the server versions it did not see change different cells, and neither side inserts, deletes, or moves rows or columns, it applies the missed versions to the live Grid, moves the pending queue onto the server head, and keeps undo history. In every other case it returns reload-required with the reason and changes nothing, so the host reloads as before. See Conflict and snapshot reload.

Release engine memory

A WebAssembly instance never gives memory back, so after a large import or clear the page used to keep its peak for its whole life. Call releaseEngineMemory() when the app is idle. It waits for running transactions and page loads, then moves every live store into a fresh engine instance and lets the browser free the old one. Values, formulas and their cached results, spills, loaded datasource pages, unsaved edits, and undo history all move. If any store cannot move, nothing changes.

Release never makes memory larger, and the pause grows with the live data. See Release retained engine memory.

Other new APIs

Choose the layer you use:

Breaking changes

Changed

• String storage is reclaimed. Text that edits overwrite or clear was kept for the life of the document. After a commit that leaves at least 256 KiB of dead text, and more dead text than live text, the engine rebuilds its string pool and reuses the freed space. History keeps its own text, so undo is not affected. To shrink WebAssembly memory itself, use releaseEngineMemory().
• One owner for formula results. The engine stores calculated values, spill output, and calculated errors apart from the cells you write. A write always wins over a calculated value, as before; spill reads and writes next to a spill are faster.
• Fill and Replace all send one operation. They used to send one operation per cell, so the maxOperations transaction limit (10,000 by default) rejected a fill or replace over more than 10,000 cells. They now send one range operation, as paste already did, and only the maxEncodedBytes limit applies. Events, undo, and sync results are the same.
• Resize previews. Row and column resize show a preview while you drag and record one undoable change on release, instead of changing the document on every step.
• Pointer gestures end with their context. A drag ends when the sheet, view, structure, or read-only state changes, and a second pointer cannot take over a drag.
• Selection before edit. Selection changes are reported before edit-begin.
• Negative currency. The minus sign comes before a currency prefix (-$246), as in Excel.
• Smaller packages. Published packages no longer include source maps, and the React, Vue, and Svelte adapters declare only CSS files as side effects, so bundlers can remove unused JavaScript.
• New dependency. @sheetwrite/core depends on fflate 0.8.3 for the restore codec.

Fixed

Formulas

• A spill that reads another spill shows its current values. Spills that read each other show #CYCLE!.
• With a windowed datasource, a visible formula that reads an off-screen column no longer stays #LOADING!.
• With a windowed datasource, a visible formula that reads a frozen row or a merged cell no longer shows a loading marker or a stale value, and the cells visible formulas read stay loaded while they are on screen.
• Exports, getRange, and find no longer unload cells that are on screen.
• FILTER works in debug builds when its input is smaller than its size bound.
• Decoded lookup ranges are released when recalculation ends.

Collaboration and undo

• rebaseDocumentOperations no longer puts an edit on the wrong row when pending work holds more than one operation and one side inserted or deleted rows. An edit to a row the server deleted now returns a conflict instead of being written onto another row.
• Undo after a collaborator inserts or deletes rows or columns restores the cell you edited, instead of writing the old value onto another row.
• An undo above the transaction limits now emits mutation-rejected and leaves older edits undoable, instead of failing silently every time.
• MemoryPersistenceAdapter works before the engine is initialized, and a failed commit, including one member of an atomic batch, leaves the document unchanged.

Editing and display

• In the Svelte adapter, a live option such as readOnly changed while the Grid starts is applied before ready fires. In every adapter, a ref or onReady callback that throws now retires the new Grid, as React already did.
• Every editor commits to the cell it opened on, through sorting and filtering.
• A rejected fill leaves the selection unchanged.
• Paste across hidden columns writes only the visible columns, and one undo reverts it.
• The toolbar, find bar, and context menu follow a theme change, and the toolbar colour swatches show the theme's real colours.
• Row numbers no longer paint into the header corner while you scroll.
• Sheet tabs no longer scroll the page, and the sheet menu stays open when an unrelated part of the page scrolls.
• A search highlight or note on a partly visible top row is no longer dropped.

Performance

These medians come from alternating runs on the same machine. Times vary by device and workload. The performance page has the full measurements and package sizes.

Faster paths

Bars show the time saved compared with 0.4.0. A full bar is 100%. The lighter end shows the reported range. The Memory group shows bytes saved.

Slower paths

These paths took longer in 0.5.0. The bars show measured times, not speed ratios.

Column sums use aggregate. They were about 2× slower in 0.5.0. 0.5.1 made them about 2× faster again, but the 100,000-row case can still be slower than 0.4.0. See #110. The spill case was 6–27% slower. See #109.

Upgrade checklist

1. EveryoneRequired

   Initialize the engine, then update every package together

   Make sure you await initSheetwrite() before you create a Grid or store. Install 0.5.0 for each @sheetwrite/* package that you use.

2. If you call CellStore.setBlock from @sheetwrite/wasmBreaking

   Use the packed block writers

   Use setBlockPacked (row-major) or setColumnBlockPacked (column-major). They take all string cells as one UTF-8 buffer plus byte offsets.

3. If you run a persistence or collaboration serverRequired

   Accept restoreBlock before 0.5.0 clients connect

   Optional: implement commitBatch for undo that needs more than one version. See Large undo and atomic batches.

4. If you cache raw string IDs from @sheetwrite/wasmRequired

   Drop the cache when the string pool changes

   Clear cached IDs when stringPoolEpoch changes.

5. If you use presentation: "data-grid" with filler columnsBehavior change

   Set minColumns

   Data-grid presentation no longer adds filler columns by itself.

6. If you want the analysis functionsOptional

   Install the full formula engine

   Install @sheetwrite/formulas and select it before you create a Grid.

7. If you hold large documents for a long timeOptional

   Release engine memory when idle

   Call releaseEngineMemory() after a large clear or import.

8. If you recover from sync conflictsOptional

   Recover in place before you reload

   Call sync.recoverConflict() before your reload path. See Conflict and snapshot reload.

9. If you check dependency licenses or lockfilesFor information

   Expect fflate 0.8.3

   @sheetwrite/core now depends on fflate. Your package manager installs it; you do not add it yourself.

The full list of changes is in each package's CHANGELOG.md.