Online Spreadsheet Editor Development

Online Spreadsheet Editor Development

Online spreadsheet editor is the most complex class of web applications. The task includes: virtualizing large tables, formula engine, collaborative editing with conflict resolution, XLSX import/export. Full Google Sheets equivalent requires years of development; realistic goals are embedded editor for specific business case or specialized table with limited features.

UI Virtualization

A table with 100,000 rows cannot render all DOM elements simultaneously. Virtualization renders only visible rows plus small buffer:

• TanStack Virtual (formerly react-virtual) — headless hook for virtualization
• AG Grid — feature-rich commercial grid with virtualization, formulas, export
• Handsontable — close to Excel in features, MIT or commercial license

Implementing row virtualization with TanStack Virtual:

const rowVirtualizer = useVirtualizer({
  count: rows.length,
  getScrollElement: () => parentRef.current,
  estimateSize: () => 24, // row height in px
  overscan: 10,
});

return (
  <div style={{ height: rowVirtualizer.getTotalSize() }}>
    {rowVirtualizer.getVirtualItems().map(virtualRow => (
      <Row key={virtualRow.index} row={rows[virtualRow.index]}
        style={{ transform: `translateY(${virtualRow.start}px)` }} />
    ))}
  </div>
);

Formula Engine

Formula engine parses expressions like =SUM(A1:B10) * C5 + IF(D1>0, E1, 0).

HyperFormula (Handsontable team) — open-source engine with 400+ formula support, XLSX-compatible. Written in TypeScript, works in browser and Node.js:

import HyperFormula from 'hyperformula';
const hf = HyperFormula.buildFromArray([
  ['=SUM(B1:B3)', 10, 20, 30],
  [1, 2, 3],
]);
console.log(hf.getCellValue({ row: 0, col: 0, sheet: 0 })); // 6

Dependency tracking: when cell changes engine recalculates only dependent cells (lazy evaluation via dependency graph).

Collaborative Editing

OT (Operational Transformation) or CRDT for conflict resolution with simultaneous editing:

Yjs — CRDT library with shared types support. For tables: Y.Map for cells.

const ydoc = new Y.Doc();
const cells = ydoc.getMap('cells'); // key: "A1", value: cell

// Provider for sync
const provider = new WebsocketProvider('ws://server', 'spreadsheet-123', ydoc);

// Cell update
cells.set('A1', { value: 100, formula: null });

Conflict when two users change same cell simultaneously in CRDT: last update (LWW — Last Write Wins) by lamport clock timestamp.

Change History and Undo/Redo

Undo/redo via operation stack. Each change (including formulas) recorded as reversible operation. Collaborative undo is harder: undo your changes, not others'.

Data Types and Formatting

Cell can contain: string, number, date, boolean, formula. Formatting: font, background/text color, number format (%, date, currency), alignment, borders.

Number formats stored as format strings: #,##0.00, DD.MM.YYYY, 0%.

XLSX Import/Export

• SheetJS (xlsx) — full XLSX, XLS, CSV support. Reads and writes files. Works in browser (FileReader) and Node.js.
• ExcelJS — creating XLSX with formatting, images, formulas.

On XLSX import: extract cells → normalize to internal format. Formulas stored as strings, evaluated via HyperFormula.

Timeline

Specialized table (specific template, 100-1000 rows, basic formulas, no collaborative editing): 2–3 months. Full editor with virtualization, collaborative editing, XLSX import and 200+ formulas: 6–10 months.