sheetengine

A headless TypeScript spreadsheet calculation engine for Node, browsers, workers, and frontend apps.

import { createWorkbook, isSpreadsheetError } from "sheetengine";

const wb = createWorkbook();

wb.addSheet("Sheet1");
wb.setCell("Sheet1", "A1", 10);
wb.setCell("Sheet1", "A2", 20);
wb.setCell("Sheet1", "A3", "=A1+A2");

console.log(wb.getValue("Sheet1", "A3")); // 30

wb.setCell("Sheet1", "A1", 100);
console.log(wb.getValue("Sheet1", "A3")); // 120

const value = wb.getValue("Sheet1", "A3");
if (isSpreadsheetError(value)) {
  console.error(value.code, value.message);
}

API

• createWorkbook()
• workbook.addSheet(name)
• workbook.removeSheet(name)
• workbook.listSheets()
• workbook.hasSheet(name)
• workbook.setCell(sheetName, address, input)
• workbook.getCell(sheetName, address)
• workbook.listCells(sheetName)
• workbook.getFormula(sheetName, address)
• workbook.getDependencies(sheetName, address)
• workbook.getDependents(sheetName, address)
• workbook.getValue(sheetName, address)
• workbook.clearCell(sheetName, address)
• workbook.batch(fn)
• workbook.transaction(fn, options?)
• workbook.beginTransaction(options?)
• workbook.commitTransaction()
• workbook.rollbackTransaction()
• workbook.subscribe(listener)
• workbook.getVersion()
• workbook.undo()
• workbook.redo()
• workbook.canUndo()
• workbook.canRedo()
• workbook.clearHistory()
• workbook.registerFunction(name, implementation)
• workbook.toJSON()

Workbook methods throw normal JavaScript errors for invalid API usage, such as missing sheets or invalid addresses. Formula evaluation errors are returned as typed spreadsheet error values.

getValue() returns numbers, strings, booleans, null, or typed spreadsheet error objects:

type SpreadsheetError = {
  type: "error";
  code: "#DIV/0!" | "#REF!" | "#VALUE!" | "#NAME?" | "#CYCLE!";
  message: string;
};

Formula Support

MVP formulas support:

• Numbers, double-quoted strings, TRUE, FALSE
• Cell references such as A1
• Ranges such as A1:A10
• Sheet-qualified references such as Sheet1!A1 and 'My Sheet'!A1
• Operators: +, -, *, /, ^, &, =, <>, <, <=, >, >=
• Functions: SUM, AVG, MIN, MAX, COUNT, IF
• Custom functions through registerFunction

Custom Functions

wb.registerFunction("DOUBLE", (args) => {
  const first = Array.isArray(args[0]) ? args[0][0] : args[0];
  return typeof first === "number" ? first * 2 : null;
});

wb.setCell("Sheet1", "B1", "=DOUBLE(A1)");

Custom functions receive scalar values or arrays for ranges. Return a literal value or a typed spreadsheet error.

Workbook JSON

Serialize sparse workbook inputs with toJSON() and hydrate with createWorkbook(data):

const data = wb.toJSON();
const restored = createWorkbook(data);

The JSON format stores sheet names and cell inputs only. Parsed formulas, cached values, dependency maps, dirty state, and registered custom functions are not serialized. Register custom functions again after hydration.

Inspection

wb.listSheets(); // ["Sheet1"]
wb.listCells("Sheet1");
wb.getFormula("Sheet1", "A3"); // "A1+A2"
wb.getDependencies("Sheet1", "A3");
wb.getDependents("Sheet1", "A1");

Dependency inspection returns public { sheetName, address } references, not internal graph keys.

Batch Updates

Use batch() to defer recalculation until several updates finish:

wb.batch(() => {
  wb.setCell("Sheet1", "A1", 100);
  wb.setCell("Sheet1", "A2", 200);
});

batch() is kept for compatibility. It is silent and non-history: it does not emit events and does not add a new undo entry for the batched edits.

Transactions

Use transaction() when a frontend workflow should be treated as one user action:

wb.transaction(
  () => {
    wb.setCell("Sheet1", "A1", 100);
    wb.setCell("Sheet1", "A2", 200);
  },
  { label: "Update inputs", source: "sidebar" }
);

Transactions defer recalculation and event delivery until the outermost commit. If the callback throws, all changes made inside the transaction are rolled back and the original error is rethrown.

Manual transactions are also available:

wb.beginTransaction({ label: "Paste" });
try {
  wb.setCell("Sheet1", "A1", 1);
  wb.setCell("Sheet1", "A2", 2);
  wb.commitTransaction();
} catch (error) {
  wb.rollbackTransaction();
  throw error;
}

Nested transactions are supported. Inner commits merge into the outer transaction; only the outer commit emits an event and history entry.

Transaction Options

type TransactionOptions = {
  label?: string;
  source?: string;
  history?: boolean;
  emit?: boolean;
};

• label is a human-readable action label, useful for undo menus.
• source identifies where the change came from, such as "formula-bar" or "paste".
• history: false prevents the transaction from being undoable.
• emit: false prevents subscriber events and version increments.

Events

Subscribe to committed workbook changes:

const unsubscribe = wb.subscribe((event) => {
  console.log(event.version);
  console.log(event.cellChanges);
  console.log(event.recalculatedCells);
});

unsubscribe();

Each emitted event represents one committed transaction. Non-transaction mutations are automatically wrapped as single-operation transactions.

type WorkbookEvent = {
  version: number;
  label?: string;
  source?: string;
  sheetChanges: SheetChange[];
  cellChanges: CellChange[];
  recalculatedCells: CellReference[];
};

cellChanges contains directly edited cells with previous and next snapshots where available. recalculatedCells contains formula cells that were recalculated because dependencies changed. Listener errors are reported after the workbook state has already committed; they do not revert the transaction.

Use getVersion() as a cheap signal for frontend state subscriptions. It increments once per emitted transaction.

Undo and Redo

Undo and redo operate on committed history entries:

if (wb.canUndo()) wb.undo();
if (wb.canRedo()) wb.redo();
wb.clearHistory();

Undo/redo supports sheet additions, sheet removals, cell edits, and cell clears. Undo and redo emit normal workbook events and update the version. Custom function registration is not undoable.

The default history limit is 100 committed user-visible transactions. Transactions created with { history: false } are skipped.

Frontend Integration Pattern

A UI can subscribe once, then update only affected views:

const unsubscribe = wb.subscribe((event) => {
  for (const change of event.cellChanges) {
    rerenderCell(change.sheetName, change.address);
  }

  for (const ref of event.recalculatedCells) {
    rerenderCell(ref.sheetName, ref.address);
  }
});

This avoids diffing toJSON() after every edit. Use toJSON() for persistence and message passing, not for routine UI updates.

Serialization Boundaries

toJSON() serializes workbook structure and cell inputs only:

• Sheet names
• Sparse cell addresses
• Cell inputs

It intentionally does not serialize:

• Cached values
• Parsed formula ASTs
• Dependency maps
• Dirty state
• Event listeners
• Undo/redo history
• Registered custom functions

After hydration, formulas are recalculated from inputs. Register custom functions again before reading formulas that depend on them.

Publish

npm ci
npm test
npm run typecheck
npm run build
npm pack --dry-run
npm publish --access public --otp 123456

If the unscoped sheetengine package name is unavailable, publish under a scope such as @dragonpeti53/sheetengine by changing the name field in package.json.

Development

npm install
npm test
npm run typecheck
npm run build

This package intentionally does not include UI, XLSX import/export, formatting, charts, pivot tables, array formulas, or full Excel compatibility.