# Spreadsheet Editor

Links: [Home](../index.md) | [Workflow Document](../../Workflow_SpreadSheet.md)

An **SGApps.IO product**: an Excel-like spreadsheet editor built entirely from scratch, in-house, as an ApplicationPrototype module. No third-party code on any path — the formula engine, rendering, charts, and XLSX / ODS / CSV import and export are all our own code (the export archive is packed with the in-house [Packer](../archiver/packer.md)). Proprietary, source-available — see `modules/editors/spreadsheet/LICENSE`.

## Features at a Glance

| Category | Highlights |
|---|---|
| **Cell Editing** | Inline editing, formula bar, bold/italic/underline, fonts, colors, alignment, wrap, merge, borders, number formats |
| **Formulas** | 130+ built-in functions (SUM, VLOOKUP, IF, IRR, TEXTJOIN, ...), own tokenizer / recursive-descent parser / AST evaluator, dependency-ordered recalculation with `#CIRC!` detection, function autocomplete and a Function Wizard |
| **Data** | Sort (multi-level), auto-filter, find & replace, auto-fill series, paste special, data validation |
| **Charts** | Bar, line, pie, area, scatter — inline SVG, draggable, resizable |
| **Sheets** | Multi-sheet, rename, duplicate, reorder, color-code tabs, cross-sheet formulas |
| **History** | Undo/redo (100 steps by default, `undoSize` option), version snapshots with restore |
| **Import/Export** | Import XLSX, ODS, CSV; export XLSX, ODS, CSV (own OpenXML / ODF writers, packed with the in-house `archiver/packer`); PDF through the browser's print dialog |
| **View** | Zoom (25%–400%), freeze panes, split view, full-screen, show/hide gridlines |

---

## Loading the Module

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var sheet = Spreadsheet({
        container: document.getElementById('spreadsheet'),
        rows: 1000,
        cols: 26,
        showToolbar: true,
        showFormulaBar: true,
        showSheetTabs: true
    });

    sheet.whenLoaded().then(function () {
        console.log('Spreadsheet ready');
    });
});
```

---

## Constructor Options

| Option | Type | Default | Description |
|---|---|---|---|
| `container` | `HTMLElement` | *(required)* | DOM element to mount into |
| `rows` | `number` | `1000` | Initial row count |
| `cols` | `number` | `26` | Initial column count (26 = A–Z) |
| `defaultColWidth` | `number` | `100` | Default column width in px |
| `defaultRowHeight` | `number` | `24` | Default row height in px |
| `showToolbar` | `boolean` | `true` | Show the formatting toolbar |
| `showFormulaBar` | `boolean` | `true` | Show the formula bar |
| `showSheetTabs` | `boolean` | `true` | Show the sheet tab bar |
| `showGridlines` | `boolean` | `true` | Show cell gridlines |
| `showHeaders` | `boolean` | `true` | Show row/column headers |
| `zoom` | `number` | `1` | Initial zoom level (0.25–4.0) |
| `data` | `Object` | `null` | Preload workbook from JSON (see [Data Model](#data-model-json)) |
| `mode` | `string` | `'editor'` | `'editor'`, `'viewer'`, or `'minimal'` (see [Modes](#modes)) |
| `readOnly` | `boolean` | `false` | Prevent all editing (auto-set by `mode: 'viewer'`) |
| `toolbar` | `Object` | `{...all true}` | Per-group toolbar toggle (see [Toolbar Config](#toolbar-configuration)) |

---

## Modes

Pre-configured presets for common embedding scenarios:

| Mode | Toolbar | Formula Bar | Sheet Tabs | Editing | Best For |
|---|---|---|---|---|---|
| `'editor'` | shown | shown | shown | enabled | Full spreadsheet application |
| `'viewer'` | hidden | hidden | shown | disabled | Displaying data, reports, dashboards |
| `'minimal'` | hidden | shown | hidden | enabled | Compact data-entry forms, embedded grids |

```js
// Full editor (default)
Spreadsheet({ container: el });

// Read-only viewer
Spreadsheet({ container: el, mode: 'viewer', data: savedJSON });

// Compact data-entry form
Spreadsheet({ container: el, mode: 'minimal', rows: 15, cols: 5 });
```

You can override any default set by the mode:

```js
// Viewer with sheet tabs AND a toolbar for zoom/print/export
Spreadsheet({
    container: el,
    mode: 'viewer',
    showToolbar: true,
    toolbar: { view: true, print: true, importExport: true }
});
```

---

## Toolbar Configuration

When `showToolbar: true`, toggle individual toolbar groups:

```js
Spreadsheet({
    container: el,
    toolbar: {
        history:      true,   // Undo, Redo
        clipboard:    true,   // Cut, Copy, Paste, Paste Special
        font:         true,   // Font family/size, bold/italic/underline, colors
        alignment:    true,   // Horizontal/vertical align, wrap, merge
        numberFormat: true,   // Number format dropdown
        cells:        true,   // Insert/delete rows+cols, format
        data:         true,   // Sort, filter, find
        view:         true,   // Zoom slider, freeze, gridlines, fullscreen
        print:        true,   // Print button (Ctrl+P)
        importExport: true    // Import file + Export dropdown (XLSX, CSV, ODS, PDF)
    }
});
```

**Examples:**

```js
// Data-entry form: only undo + basic formatting
Spreadsheet({ container: el, toolbar: { history: true, font: true } });

// Report viewer with export only
Spreadsheet({ container: el, readOnly: true, toolbar: { view: true, print: true, importExport: true } });

// Minimal: just clipboard and search
Spreadsheet({ container: el, toolbar: { clipboard: true, data: true } });
```

> In `readOnly` mode, editing buttons (cut, paste, bold, sort, insert/delete, etc.) are automatically hidden even if their group is enabled.

---

## API Reference

### Core

| Method | Returns | Description |
|---|---|---|
| `node()` | `HTMLElement` | The root DOM element |
| `whenLoaded()` | `Promise` | Resolves when fully initialized |
| `readOnly([val])` | `boolean` | Get or set read-only mode at runtime |
| `isReadOnly()` | `boolean` | Check if currently in read-only mode |
| `mode()` | `string` | Get the current mode (`'editor'`, `'viewer'`, `'minimal'`) |
| `config()` | `Object` | Get a snapshot of the current configuration |
| `engine()` | `SpreadsheetEngine` | Access the data engine directly |
| `renderer()` | `SpreadsheetRenderer` | Access the renderer directly |
| `destroy()` | -- | Clean up DOM and event listeners |

### Cell Data

| Method | Returns | Description |
|---|---|---|
| `value(ref [, val])` | `any` | Get or set a cell's raw value. `ref` can be `'A1'` or `'B2:D5'` |
| `formula(ref [, expr])` | `string` | Get or set a cell's formula (with or without `=` prefix) |
| `style(ref, styles)` | `app` | Apply styles to a cell or range (see [Cell Styles](#cell-styles)) |
| `cell(ref)` | `Object` | Get a cell proxy: `cell('A1').value()`, `cell('A1').formula('=B1+1')` |

### Selection & Navigation

| Method | Returns | Description |
|---|---|---|
| `selection()` | `Object` | Current selection `{start: {row, col}, end: {row, col}, sheet}` |
| `select(ref)` | `app` | Select a cell or range (`'A1'`, `'B2:D5'`) |
| `zoom([level])` | `number` | Get or set zoom level (0.25–4.0) |
| `freeze(rows, cols)` | `app` | Freeze panes. `freeze(1, 0)` freezes the first row |

### Rows, Columns & Sheets

| Method | Returns | Description |
|---|---|---|
| `insertRow(at [, count])` | `app` | Insert `count` rows at row index `at` |
| `insertCol(at [, count])` | `app` | Insert `count` columns at column index `at` |
| `deleteRow(at [, count])` | `app` | Delete `count` rows starting at `at` |
| `deleteCol(at [, count])` | `app` | Delete `count` columns starting at `at` |
| `activeSheet([index])` | `number` | Get or set the active sheet by index |
| `addSheet([name])` | `app` | Add a new sheet (auto-named if no name given) |
| `deleteSheet(index)` | `app` | Delete sheet at index |
| `renameSheet(index, name)` | `app` | Rename a sheet |

### Clipboard

| Method | Returns | Description |
|---|---|---|
| `copy([rangeRef])` | `app` | Copy range to clipboard (uses selection if no ref). Shows dashed border. |
| `cut([rangeRef])` | `app` | Cut range to clipboard. Shows red dashed border. |
| `paste([targetRef, mode])` | `app` | Paste at target (uses active cell if no ref). Shows green flash. |
| `copyRange(sourceRange, targetRef [, sheetIdx])` | `app` | Copy cells from one range to another in one call. |

**Paste modes:** `'all'` (default), `'values'`, `'formats'`, `'formulas'`, `'transpose'`

```js
// Programmatic copy-paste
ss.copy('A1:C3');           // copy A1:C3 to clipboard (shows dashed border)
ss.paste('E1');             // paste at E1 (shows green flash, then fades)
ss.paste('E1', 'values');   // paste values only (no formulas or styles)

// One-step range copy
ss.copyRange('A1:C3', 'E1');             // copy A1:C3 to E1:G3
ss.copyRange('A1:C3', 'E1', 1);          // copy from sheet index 1

// Cut and paste
ss.cut('B2:B10');           // cut B2:B10 (red dashed border)
ss.paste('D2');             // paste at D2, source cells cleared

// Transpose paste
ss.copy('A1:A5');
ss.paste('B1', 'transpose'); // paste as row instead of column
```

### Data Operations

| Method | Returns | Description |
|---|---|---|
| `sort(range, options)` | `app` | Sort a range. `options`: `[{col: 0, ascending: true}, ...]` |
| `filter(col)` | `app` | Toggle auto-filter on a column |
| `find(query, options)` | `Array` | Find matching cells. Returns `[{ref, sheet, value}]` |
| `replace(query, replacement, options)` | `number` | Replace all matches, return count |
| `merge(range)` | `app` | Merge cells in range (e.g., `'A1:C1'`) |
| `unmerge(range)` | `app` | Unmerge previously merged cells |
| `namedRange(name [, ref])` | `string` | Get or define a named range |
| `conditionalFormat(range, rules)` | `app` | Add conditional formatting (see [Conditional Formatting](#conditional-formatting)) |
| `insertChart(range, type, opts)` | `app` | Insert a chart (see [Charts](#charts)) |

### History & Versions

| Method | Returns | Description |
|---|---|---|
| `undo()` | `app` | Undo last action |
| `redo()` | `app` | Redo last undone action |

The engine uses a command-pattern stack with `beginBatch()` / `endBatch()` to group multiple operations into a single undo step.

### Import / Export

| Method | Returns | Description |
|---|---|---|
| `exportXLSX()` | `Promise<Blob>` | Export as XLSX (OpenXML via archiver/packer) |
| `exportCSV([sheet])` | `string` | Export one sheet as CSV |
| `exportODS()` | `Promise<Blob>` | Export as ODS (Open Document) |
| `exportPDF()` | `Promise` | Export via browser print dialog |
| `importFile(file)` | `Promise` | Import XLSX, CSV, TSV, or ODS (File or Blob) |
| `toJSON()` | `Object` | Serialize the entire workbook to JSON |
| `fromJSON(data)` | `app` | Load workbook from JSON, recalculate all formulas |
| `print([options])` | -- | Open the browser print dialog |

#### Native ZIP-based Import

XLSX and ODS imports use a built-in **native ZIP parser** based on the browser's `DecompressionStream('deflate-raw')` — there is no JSZip or other external dependency. The pipeline:

1. Reads the ZIP central directory and decompresses each entry.
2. For XLSX: parses `xl/sharedStrings.xml`, `xl/workbook.xml`, and every `xl/worksheets/sheet*.xml`. Cell values, formulas, merges, column widths, and row heights are restored.
3. For ODS: parses `content.xml` and recreates rows/columns/cells from the OpenDocument table model.
4. CSV/TSV imports use a streaming parser that auto-detects the delimiter and quote character.

To keep async chains working when ApplicationPrototype overrides `window.Promise`, the module saves a reference to the native `Promise` constructor at module load (`_NativePromise = window.Promise`) and uses it for the import pipeline. End-user code calling `importFile().then(...)` continues to work transparently. Errors during import surface through the returned promise — the toolbar Import button wraps it with `.catch()` to show an alert dialog.

---

## Events

| Event | Data | Description |
|---|---|---|
| `event:ready` | -- | Spreadsheet fully initialized and rendered |
| `event:cell-change` | `{ref, oldValue, newValue, sheet}` | A cell value or formula was modified |
| `event:selection-change` | `{start, end, sheet}` | The selected cell/range changed |
| `event:sheet-change` | `{index, name}` | The active sheet was switched |
| `event:before-edit` | `{ref, value}` | Fires before a cell enters edit mode. Return `false` to cancel |
| `event:after-edit` | `{ref, oldValue, newValue}` | Fires after a cell edit is confirmed |
| `event:context-menu` | `{ref, event}` | Right-click on a cell |
| `event:scroll` | `{scrollTop, scrollLeft}` | The grid viewport was scrolled |
| `event:zoom` | `{level}` | Zoom level changed |
| `event:import` | `{fileName, sheets}` | A file was imported |
| `event:export` | `{format}` | A file was exported |

---

## Cell Styles

The `style(ref, styles)` method accepts an object with any combination of these properties:

| Property | Type | Default | Description |
|---|---|---|---|
| `bold` | `boolean` | `false` | Bold text |
| `italic` | `boolean` | `false` | Italic text |
| `underline` | `boolean` | `false` | Underlined text |
| `strikethrough` | `boolean` | `false` | Strikethrough text |
| `fontFamily` | `string` | `'Arial'` | Font family name |
| `fontSize` | `number` | `11` | Font size in points |
| `color` | `string` | `'#000000'` | Text color (hex) |
| `bg` | `string` | `null` | Background/fill color (hex). `null` = no fill |
| `align` | `string` | `'left'` | Horizontal alignment: `'left'`, `'center'`, `'right'` |
| `valign` | `string` | `'bottom'` | Vertical alignment: `'top'`, `'middle'`, `'bottom'` |
| `wrap` | `boolean` | `false` | Wrap text within cell |
| `numFmt` | `string` | `'General'` | Number format string (see [Number Formats](#number-formats)) |
| `borderTop` | `Object` | `null` | `{style, width, color}` — style: `'solid'`, `'dashed'`, `'dotted'` |
| `borderRight` | `Object` | `null` | Same as above |
| `borderBottom` | `Object` | `null` | Same as above |
| `borderLeft` | `Object` | `null` | Same as above |

```js
// Example: style a header row
ss.style('A1:F1', {
    bold: true,
    bg: '#1a73e8',
    color: '#ffffff',
    align: 'center',
    fontSize: 12,
    borderBottom: { style: 'solid', width: 2, color: '#0d47a1' }
});
```

---

## Number Formats

The `numFmt` property controls how values are displayed. Common format strings:

| Format | Example | Description |
|---|---|---|
| `General` | `1234.5` | Default — no special formatting |
| `0` | `1235` | Integer (rounded) |
| `0.00` | `1234.50` | Fixed 2 decimal places |
| `#,##0` | `1,235` | Thousands separator |
| `#,##0.00` | `1,234.50` | Thousands + 2 decimals |
| `$#,##0.00` | `$1,234.50` | US currency |
| `0%` | `12%` | Percentage (value * 100) |
| `0.00%` | `12.35%` | Percentage with decimals |
| `0.00E+0` | `1.23E+3` | Scientific notation |
| `yyyy-mm-dd` | `2026-04-04` | ISO date |
| `mm/dd/yyyy` | `04/04/2026` | US date |
| `hh:MM:ss` | `14:30:00` | Time |
| `@` | `(text)` | Text — no number conversion |

```js
ss.style('B2:B100', { numFmt: '$#,##0.00' }); // Currency
ss.style('C2:C100', { numFmt: '0.0%' });       // Percentage
ss.style('D2:D100', { numFmt: 'yyyy-mm-dd' }); // Date
```

---

## Formula Functions

The formula engine ships 130+ built-in functions organized by category. Enter formulas with `=` prefix.

### Math & Aggregation

`SUM`, `AVERAGE`, `MIN`, `MAX`, `COUNT`, `COUNTA`, `COUNTBLANK`, `ABS`, `ROUND`, `ROUNDUP`, `ROUNDDOWN`, `CEILING`, `FLOOR`, `POWER`, `SQRT`, `MOD`, `RAND`, `RANDBETWEEN`, `PI`, `LOG`, `LOG10`, `LN`, `EXP`, `INT`, `SIGN`, `TRUNC`, `PRODUCT`, `SUMPRODUCT`, `SIN`, `COS`, `TAN`, `ASIN`, `ACOS`, `ATAN`, `ATAN2`, `DEGREES`, `RADIANS`

### Conditional Aggregation

`SUMIF`, `SUMIFS`, `COUNTIF`, `COUNTIFS`, `AVERAGEIF`, `AVERAGEIFS`

### Text

`CONCAT`, `CONCATENATE`, `LEFT`, `RIGHT`, `MID`, `LEN`, `UPPER`, `LOWER`, `PROPER`, `TRIM`, `SUBSTITUTE`, `REPLACE`, `FIND`, `SEARCH`, `TEXT`, `VALUE`, `REPT`, `CHAR`, `CODE`, `EXACT`, `T`, `TEXTJOIN`

### Logical

`IF`, `AND`, `OR`, `NOT`, `XOR`, `IFERROR`, `IFNA`, `IFS`, `SWITCH`, `TRUE`, `FALSE`

### Lookup & Reference

`VLOOKUP`, `HLOOKUP`, `INDEX`, `MATCH`, `OFFSET`, `INDIRECT`, `ROW`, `COLUMN`, `ROWS`, `COLUMNS`, `ADDRESS`, `CHOOSE`

### Date & Time

`NOW`, `TODAY`, `DATE`, `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `DATEVALUE`, `DAYS`, `EDATE`, `EOMONTH`, `WEEKDAY`, `WEEKNUM`

### Statistical

`MEDIAN`, `MODE`, `STDEV`, `STDEV.S`, `STDEV.P`, `VAR`, `VAR.S`, `VAR.P`, `LARGE`, `SMALL`, `RANK`, `PERCENTILE`, `QUARTILE`, `CORREL`, `FORECAST`

### Financial

`PMT`, `FV`, `PV`, `NPV`, `IRR`, `NPER`

### Info

`ISBLANK`, `ISERROR`, `ISNUMBER`, `ISTEXT`, `ISLOGICAL`, `ISNA`, `TYPE`, `NA`, `ERROR.TYPE`

### Formula Examples

```js
ss.formula('D2', '=B2*C2');                          // Arithmetic
ss.formula('D10', '=SUM(D2:D9)');                    // Range sum
ss.formula('E2', '=IF(D2>1000,"High","Low")');       // Conditional
ss.formula('F2', '=VLOOKUP(A2,Sheet2!A:B,2,FALSE)'); // Cross-sheet lookup
ss.formula('G2', '=TEXT(B2,"$#,##0.00")');           // Format as text
ss.formula('H2', '=IFERROR(B2/C2,"N/A")');          // Error handling
ss.formula('I2', '=SUMIFS(D:D,A:A,"Widget",C:C,">50")'); // Multi-criteria
```

### Formula Error Values

| Error | Meaning |
|---|---|
| `#REF!` | Invalid cell reference |
| `#VALUE!` | Wrong value type |
| `#DIV/0!` | Division by zero |
| `#NAME?` | Unrecognized function or name |
| `#N/A` | Value not available |
| `#NULL!` | Invalid range intersection |
| `#NUM!` | Invalid numeric value |
| `#CIRC!` | Circular reference detected |

---

## Conditional Formatting

Apply visual rules to highlight cells based on their values.

```js
// Highlight cells greater than 1000 in red
ss.conditionalFormat('B2:B100', {
    type: 'cellIs',
    operator: 'greaterThan',
    values: [1000],
    style: { bg: '#fce4ec', color: '#c62828' }
});

// 3-color scale (green → yellow → red)
ss.conditionalFormat('C2:C100', {
    type: 'colorScale',
    colors: ['#4caf50', '#ffeb3b', '#f44336']
});

// Data bars
ss.conditionalFormat('D2:D50', {
    type: 'dataBar',
    color: '#2196f3'
});

// Icon sets (arrows, traffic lights, stars, flags)
ss.conditionalFormat('E2:E50', {
    type: 'iconSet',
    icons: 'arrows3'    // 'arrows3', 'traffic3', 'stars3', 'flags3'
});

// Formula-based rule
ss.conditionalFormat('A2:F100', {
    type: 'expression',
    formula: '=MOD(ROW(),2)=0',
    style: { bg: '#f5f5f5' }       // Alternate row shading
});
```

---

## Charts

Insert charts from a data range. Charts render as inline SVG and can be dragged/resized.

```js
// Bar chart
ss.insertChart('A1:B7', 'bar', {
    title: 'Sales by Region',
    legend: 'right',
    width: 400,
    height: 300
});

// Line chart
ss.insertChart('A1:C12', 'line', { title: 'Monthly Trend' });

// Pie chart
ss.insertChart('A1:B5', 'pie', { title: 'Market Share' });

// Scatter plot
ss.insertChart('A1:B50', 'scatter', { title: 'Correlation' });

// Area chart
ss.insertChart('A1:D12', 'area', { title: 'Revenue Breakdown' });
```

**Supported chart types:** `bar`, `line`, `pie`, `area`, `scatter`, `combo`

Charts can be repositioned by dragging the title bar and resized via the handle at the bottom-right corner.

---

## Data Validation

Restrict what values can be entered in cells.

```js
// Dropdown list
ss.cell('B2').validation({
    type: 'list',
    list: ['High', 'Medium', 'Low'],
    message: 'Select a priority level'
});

// Number range
ss.cell('C2:C100').validation({
    type: 'number',
    operator: 'between',
    value1: 0,
    value2: 100,
    message: 'Enter a value between 0 and 100'
});

// Date range
ss.cell('D2:D100').validation({
    type: 'date',
    operator: 'greaterThan',
    value1: '2026-01-01',
    message: 'Date must be in 2026 or later'
});

// Text length
ss.cell('E2:E100').validation({
    type: 'textLength',
    operator: 'lessThanOrEqual',
    value1: 50,
    message: 'Maximum 50 characters'
});

// Custom formula
ss.cell('F2:F100').validation({
    type: 'custom',
    formula: '=AND(F2>=0, MOD(F2,1)=0)',
    message: 'Must be a positive integer'
});
```

**Validation operators:** `between`, `notBetween`, `equal`, `notEqual`, `greaterThan`, `lessThan`, `greaterThanOrEqual`, `lessThanOrEqual`

---

## Named Ranges

Define names for cells or ranges to use in formulas.

```js
// Define
ss.namedRange('tax_rate', 'B1');
ss.namedRange('prices', 'Sheet1!B2:B100');

// Use in formula
ss.formula('C2', '=B2*tax_rate');
ss.formula('D1', '=SUM(prices)');

// Get all named ranges
var ranges = ss.engine().getNamedRanges();
// { tax_rate: 'B1', prices: 'Sheet1!B2:B100' }
```

---

## Find & Replace

```js
// Find all cells containing "widget"
var results = ss.find('widget', { matchCase: false });
// [{ref: 'A2', sheet: 0, value: 'Widget'}, ...]

// Find with regex
var dates = ss.find('\\d{4}-\\d{2}-\\d{2}', { regex: true });

// Replace
var count = ss.replace('USD', '$', { matchCase: true });
console.log(count, 'replacements made');
```

**Find options:**

| Option | Type | Default | Description |
|---|---|---|---|
| `matchCase` | `boolean` | `false` | Case-sensitive search |
| `wholeCell` | `boolean` | `false` | Match entire cell content |
| `regex` | `boolean` | `false` | Treat query as regular expression |
| `sheet` | `number` | all | Search only in specific sheet |

---

## Sort & Filter

### Sorting

```js
// Simple: sort by column A ascending
ss.sort('A1:D100', [{ col: 0, ascending: true }]);

// Multi-level: sort by column C descending, then A ascending
ss.sort('A1:D100', [
    { col: 2, ascending: false },
    { col: 0, ascending: true }
]);
```

### Auto-Filter

```js
// Toggle auto-filter on column headers
ss.filter(0);  // adds dropdown to column A header

// The filter dropdown shows unique values with checkboxes.
// Users can select/deselect values to filter rows.
```

---

## Freeze Panes

```js
ss.freeze(1, 0);  // Freeze first row (headers stay visible while scrolling)
ss.freeze(0, 1);  // Freeze first column
ss.freeze(2, 1);  // Freeze first 2 rows and first column
ss.freeze(0, 0);  // Unfreeze all
```

---

## Zoom & Viewport

The spreadsheet supports zoom from **25% to 400%** via the toolbar slider, the `zoom()` API, or `Ctrl + scroll`. The implementation has a few subtleties worth knowing about:

```js
ss.zoom(0.5);      // Zoom out to 50%
ss.zoom(2);        // Zoom in to 200%
var current = ss.zoom();  // returns current level
```

### How it works

- The grid area always stays at the container's natural size (it never overflows the parent). Zoom is applied to the inner `canvas` element using the CSS `zoom` property — **not** `transform: scale()`. This is important because `zoom` actually changes the layout box, so `viewport.scrollHeight`/`scrollWidth` reflect the scaled size and native browser scrolling works correctly.
- The renderer keeps an unscaled coordinate system internally (`_scrollTop`, `_scrollLeft`, `_totalWidth`, `_totalHeight` are all in unscaled CSS pixels). Conversion happens only at the DOM boundary: `vp.scrollTop = _scrollTop * zoom` for programmatic scroll, `_scrollTop = vp.scrollTop / zoom` in the scroll handler.
- Column and row headers multiply their positions by the zoom factor so they line up with the scaled cells. Header text size stays constant (CSS pixels) so labels remain legible at any zoom.
- Selection rectangles, the autofill handle, the copy/paste highlight, and the inline cell editor (`ss-cell-editor`) are appended **inside** the canvas, so they inherit the canvas's zoom automatically and stay aligned with the underlying cells.

### Scrollbars

- The `ss-scrollbar-v` and `ss-scrollbar-h` elements are **always visible** as a UI affordance — even when the entire content fits in the viewport at the current zoom level. When content fits, the thumb fills the entire bar; when content overflows, the thumb size is proportional to the visible/total ratio.
- Thumb dragging is supported on both axes. The drag handler computes the new scroll position in unscaled coordinates (`_scrollTop = ratio * (_totalHeight - _vpHeight/zoom)`), then converts to visual coordinates when writing back to `viewport.scrollTop`.

### Paste highlight

After a paste, the destination range is highlighted with a green dashed border for **2 seconds**, then fades. If the user pastes again before the timer elapses, the previous timer is cancelled and the new range gets its own 2-second window. This makes it visually obvious where the data landed, even when pasting into a cell far from the cursor.

### Container resize handling

The renderer reacts to two separate signals:

1. **`window.addEventListener('resize', …)`** (debounced 100 ms) — fires when the *browser viewport* itself changes size. Handler calls `self.resize()` → `_updateLayout()` → `_buildLayout()` → `render()`.
2. **A 250 ms `setInterval` polling the root element's `clientWidth`/`clientHeight`** — fires when the spreadsheet's *container* changes size without the window resizing. This catches:
   - Window-manager pane drags (resizing a sibling panel)
   - Flex layout changes when a sibling grows or shrinks
   - A parent toggling visibility from `display: none` back on
   - Any CSS-driven layout change that shifts the container's box

   The interval snapshots `clientWidth/clientHeight` into `this._lastContainerSize` and runs `self.resize()` only when the values actually change, so the cost when nothing happens is two property reads and a comparison.

The interval is cleared in `destroy()` next to the window resize listener removal. There's also a defensive guard in the callback (`if (!self._dom || !self._dom.root) return;`) that handles the race where the interval fires once after `destroy()` has nulled out `_dom`.

This is implemented as a polled interval rather than `ResizeObserver` for predictable cross-browser behavior and to avoid the layout-thrash patterns some `ResizeObserver` callbacks can produce when they themselves trigger reflows.

---

## Print

```js
// Basic print
ss.print();

// The Print dialog allows:
// - Print area: selection, active sheet, or entire workbook
// - Orientation: portrait or landscape
// - Margins: normal, narrow, wide, or custom
// - Scale: fit to page, percentage, or custom
// - Headers/footers toggle
```

A print-specific CSS stylesheet hides the toolbar, formula bar, and sheet tabs, and converts the virtual-scroll grid into a static table layout for clean printing.

---

## Data Model (JSON)

The `toJSON()` / `fromJSON()` methods use this structure:

```json
{
    "name": "Workbook",
    "sheets": [
        {
            "name": "Sheet1",
            "tabColor": null,
            "rows": { "5": { "height": 30, "hidden": false } },
            "cols": { "2": { "width": 150, "hidden": false } },
            "cells": {
                "A1": { "v": "Revenue", "s": { "bold": true, "bg": "#4a6cf7", "color": "#fff" } },
                "B1": { "v": 50000, "f": null, "s": { "numFmt": "$#,##0" } },
                "B2": { "v": null, "f": "B1*1.1", "s": {} }
            },
            "merges": ["A1:C1"],
            "freeze": { "row": 1, "col": 0 },
            "filters": null,
            "charts": [],
            "conditionalFormats": [],
            "namedRanges": { "total": "B10" },
            "validations": []
        }
    ],
    "activeSheet": 0
}
```

**Cell object:** `v` = raw value, `f` = formula (without `=`, or `null`), `s` = style object.

---

## Keyboard Shortcuts

| Shortcut | Action |
|---|---|
| `Enter` | Confirm edit, move down |
| `Tab` / `Shift+Tab` | Confirm edit, move right / left |
| `Escape` | Cancel edit |
| `F2` | Enter edit mode on selected cell |
| `Delete` | Clear cell content |
| `Ctrl+Z` | Undo |
| `Ctrl+Y` / `Ctrl+Shift+Z` | Redo |
| `Ctrl+C` / `Ctrl+X` / `Ctrl+V` | Copy / Cut / Paste |
| `Ctrl+D` | Fill down |
| `Ctrl+R` | Fill right |
| `Ctrl+B` / `Ctrl+I` / `Ctrl+U` | Bold / Italic / Underline |
| `Ctrl+A` | Select all cells |
| `Ctrl+F` | Open Find dialog |
| `Ctrl+H` | Open Find & Replace dialog |
| `Ctrl+G` | Go to cell dialog |
| `Ctrl+Home` | Navigate to cell A1 |
| `Ctrl+End` | Navigate to last used cell |
| `Ctrl+;` | Insert current date |
| `Ctrl+Shift+;` | Insert current time |
| `Ctrl+1` | Open Format Cells dialog |
| `Ctrl+Shift+L` | Toggle auto-filter |
| `Arrow keys` | Move selection |
| `Ctrl+Arrow` | Jump to edge of data region |
| `Shift+Arrow` | Extend selection |
| `Shift+Click` | Extend selection to clicked cell |
| `Ctrl+Click` | Add cell to multi-selection |
| `Page Up` / `Page Down` | Scroll by one page |
| `Ctrl+P` | Print current sheet |
| `Alt+Enter` | Insert new line inside cell |

---

## Toolbar Reference

The toolbar contains 8 groups:

| Group | Controls |
|---|---|
| **History** | Undo, Redo |
| **Clipboard** | Cut, Copy, Paste, Paste Special (dropdown: values, formats, formulas, transpose) |
| **Font** | Font family select, Font size select, **B** (bold), *I* (italic), <u>U</u> (underline), ~~S~~ (strikethrough), Font color picker, Fill color picker |
| **Alignment** | Align left/center/right, Align top/middle/bottom, Wrap text toggle, Merge cells (dropdown: merge, unmerge) |
| **Number Format** | Format dropdown (General, Number, Currency, Percentage, Date, Time, Scientific, Text) |
| **Cells** | Insert (dropdown: row above/below, column left/right), Delete (dropdown: row, column), Format (dropdown: row height, column width, hide, unhide) |
| **Data** | Sort ascending, Sort descending, Filter toggle, Find (opens dialog) |
| **View** | Zoom slider (25%–400%), Freeze panes toggle, Gridlines toggle, Full screen |

---

## Dialogs

The module includes 9 built-in dialogs, all accessible from the toolbar or keyboard shortcuts:

| Dialog | Opens Via | Description |
|---|---|---|
| **Format Cells** | `Ctrl+1` or right-click | 5 tabs: Number, Alignment, Font, Border, Fill |
| **Sort** | Data toolbar | Multi-level sort with column/direction selectors |
| **Find & Replace** | `Ctrl+F` / `Ctrl+H` | Search with match case, whole cell, regex options |
| **Conditional Format** | Format menu | Rule builder: cell value, formula, color scale, data bar, icon set |
| **Data Validation** | Data menu | 3 tabs: Settings (type/operator/values), Input Message, Error Alert |
| **Named Range Manager** | Formulas menu | List, add, edit, delete named ranges |
| **Insert Chart** | Insert menu | Chart type grid with preview, title/legend settings |
| **Function Wizard** | Click `fx` in formula bar | Category filter, function list, argument builder |
| **Print** | `Ctrl+P` | Print area, orientation, margins, scale, headers/footers |

---

## Use Cases

### Create a spreadsheet and populate cells

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    ss.whenLoaded().then(function () {
        ss.value('A1', 'Product');
        ss.value('B1', 'Price');
        ss.value('C1', 'Qty');
        ss.value('D1', 'Total');
        ss.style('A1:D1', { bold: true, bg: '#4a6cf7', color: '#fff' });

        ss.value('A2', 'Widget');   ss.value('B2', 9.99);   ss.value('C2', 100);
        ss.value('A3', 'Gadget');   ss.value('B3', 24.99);  ss.value('C3', 50);
        ss.value('A4', 'Gizmo');    ss.value('B4', 4.50);   ss.value('C4', 200);

        ss.formula('D2', '=B2*C2');
        ss.formula('D3', '=B3*C3');
        ss.formula('D4', '=B4*C4');
        ss.formula('D5', '=SUM(D2:D4)');
        ss.style('D2:D5', { numFmt: '$#,##0.00' });
        ss.style('D5', { bold: true, borderTop: { style: 'solid', width: 2, color: '#000' } });
    });
});
```

### Listen for changes and export

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    ss.on('event:cell-change', function (data) {
        console.log('Cell', data.ref, 'changed to', data.newValue);
    });

    document.getElementById('exportBtn').onclick = function () {
        ss.exportXLSX().then(function (blob) {
            var url = URL.createObjectURL(blob);
            var a = document.createElement('a');
            a.href = url; a.download = 'workbook.xlsx'; a.click();
            URL.revokeObjectURL(url);
        });
    };
});
```

### Save and restore from localStorage

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    // Load from localStorage if available
    var saved = localStorage.getItem('myWorkbook');
    var ss = Spreadsheet({
        container: document.getElementById('app'),
        data: saved ? JSON.parse(saved) : null
    });

    // Auto-save on every change
    ss.on('event:cell-change', function () {
        localStorage.setItem('myWorkbook', JSON.stringify(ss.toJSON()));
    });
});
```

### Import a file via drag-and-drop or file picker

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    // Drag-and-drop is built in automatically.
    // For a file picker:
    document.getElementById('fileInput').addEventListener('change', function (e) {
        ss.importFile(e.target.files[0]).then(function () {
            console.log('Imported. Sheets:', ss.activeSheet());
        });
    });
});
```

### Multi-sheet workbook with cross-sheet formulas

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    ss.whenLoaded().then(function () {
        // Sheet 1: Revenue data
        ss.renameSheet(0, 'Revenue');
        ss.value('A1', 'Q1'); ss.value('B1', 50000);
        ss.value('A2', 'Q2'); ss.value('B2', 62000);
        ss.value('A3', 'Q3'); ss.value('B3', 58000);
        ss.value('A4', 'Q4'); ss.value('B4', 71000);
        ss.style('B1:B4', { numFmt: '$#,##0' });

        // Sheet 2: Summary with cross-sheet references
        ss.addSheet('Summary');
        ss.activeSheet(1);
        ss.value('A1', 'Total Revenue');
        ss.formula('B1', '=SUM(Revenue!B1:B4)');
        ss.value('A2', 'Average');
        ss.formula('B2', '=AVERAGE(Revenue!B1:B4)');
        ss.value('A3', 'Best Quarter');
        ss.formula('B3', '=MAX(Revenue!B1:B4)');
        ss.style('B1:B3', { numFmt: '$#,##0', bold: true });
    });
});
```

### Build a dashboard with charts and conditional formatting

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    ss.whenLoaded().then(function () {
        // Headers
        ss.value('A1', 'Region'); ss.value('B1', 'Sales'); ss.value('C1', 'Target'); ss.value('D1', '% of Target');
        ss.style('A1:D1', { bold: true, bg: '#263238', color: '#fff' });

        // Data
        var data = [
            ['North', 85000, 80000], ['South', 62000, 75000],
            ['East', 91000, 85000], ['West', 74000, 70000]
        ];
        data.forEach(function (row, i) {
            var r = i + 2;
            ss.value('A' + r, row[0]); ss.value('B' + r, row[1]); ss.value('C' + r, row[2]);
            ss.formula('D' + r, '=B' + r + '/C' + r);
        });
        ss.style('B2:C5', { numFmt: '$#,##0' });
        ss.style('D2:D5', { numFmt: '0.0%' });

        // Conditional formatting: green if >= 100%, red if < 90%
        ss.conditionalFormat('D2:D5', { type: 'cellIs', operator: 'greaterThanOrEqual', values: [1], style: { bg: '#e8f5e9', color: '#2e7d32' } });
        ss.conditionalFormat('D2:D5', { type: 'cellIs', operator: 'lessThan', values: [0.9], style: { bg: '#ffebee', color: '#c62828' } });

        // Data bars on Sales column
        ss.conditionalFormat('B2:B5', { type: 'dataBar', color: '#42a5f5' });

        // Bar chart
        ss.insertChart('A1:B5', 'bar', { title: 'Sales by Region', width: 350, height: 250 });
    });
});
```

### Viewer mode (read-only data display)

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({
        container: document.getElementById('viewer'),
        mode: 'viewer',           // Read-only, no toolbar, no formula bar
        showSheetTabs: true,       // Show tabs for multi-sheet navigation
        data: preloadedWorkbookJSON
    });

    // Users can navigate, select cells, switch sheets, zoom — but not edit
    // Print and export still work:
    document.getElementById('printBtn').onclick = function () { ss.print(); };
});
```

### Viewer with export toolbar

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    Spreadsheet({
        container: document.getElementById('report'),
        mode: 'viewer',
        showToolbar: true,          // Override viewer default
        toolbar: {
            view: true,             // Zoom, gridlines, fullscreen
            print: true,            // Print button
            importExport: true,     // Export dropdown (XLSX, CSV, ODS, PDF)
            clipboard: true         // Copy (but cut/paste hidden in readOnly)
        },
        data: reportData
    });
});
```

### Minimal data-entry embed

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    Spreadsheet({
        container: document.getElementById('form'),
        mode: 'minimal',            // No toolbar, no sheet tabs
        rows: 15,
        cols: 5,
        showFormulaBar: true        // Keep formula bar for data entry
    });
});
```

### Custom toolbar: only specific features

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    Spreadsheet({
        container: document.getElementById('editor'),
        showToolbar: true,
        toolbar: {
            history: true,           // Undo/Redo
            font: true,              // Bold, italic, colors
            print: true,             // Print button
            importExport: true,      // Import/Export
            // Everything else defaults to false:
            clipboard: false, alignment: false, numberFormat: false,
            cells: false, data: false, view: false
        }
    });
});
```

### Programmatic copy/paste between ranges

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    ss.whenLoaded().then(function () {
        // Populate some data
        ss.value('A1', 'Name'); ss.value('B1', 'Score');
        ss.value('A2', 'Alice'); ss.value('B2', 95);
        ss.value('A3', 'Bob'); ss.value('B3', 87);
        ss.style('A1:B1', { bold: true });

        // Copy A1:B3 to D1:E3 (one step)
        ss.copyRange('A1:B3', 'D1');

        // Or step by step:
        ss.copy('A1:B3');       // dashed border appears
        ss.paste('G1');         // green flash, then fade

        // Paste values only (no formatting or formulas)
        ss.copy('A1:B3');
        ss.paste('J1', 'values');

        // Transpose: paste rows as columns
        ss.copy('A1:B1');       // copies "Name" and "Score"
        ss.paste('M1', 'transpose');  // pastes as column: M1="Name", M2="Score"
    });
});
```

### Toggle read-only at runtime

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    // Lock editing
    document.getElementById('lockBtn').onclick = function () {
        ss.readOnly(true);
        console.log('Locked:', ss.isReadOnly()); // true
    };

    // Unlock editing
    document.getElementById('unlockBtn').onclick = function () {
        ss.readOnly(false);
    };
});
```

### Print a sheet

```js
Application.require("editors/spreadsheet").then(function (Spreadsheet) {
    var ss = Spreadsheet({ container: document.getElementById('app') });

    // Print via API
    ss.print();

    // Or use Ctrl+P keyboard shortcut (works in all modes)
    // The print function:
    // 1. Adds .ss-printing class to root (hides toolbar, scrollbars, etc.)
    // 2. Renders all cells (not just visible viewport)
    // 3. Triggers window.print()
    // 4. Restores normal view after print dialog closes
});
```

---

## Architecture

```
modules/editors/spreadsheet/
  index.js           -- ApplicationPrototype module (toolbar, dialogs, keyboard, import/export, native ZIP parser)
  engine.js          -- Data model, formulas, undo/redo, sort, filter, validation
  renderer.js        -- Virtual-scroll DOM renderer, selection, cell editor, charts, zoom
  formula-parser.js  -- Tokenizer, recursive-descent parser, 130+ functions
  css/
    spreadsheet.css  -- Complete styling
```

| File | Lines | Purpose |
|---|---|---|
| `index.js` | 4,431 | Module shell, toolbar, dialogs, shortcuts, native ZIP parser, XLSX/ODS import |
| `engine.js` | 3,666 | Data model, recalc, undo, sort, filter, validation |
| `renderer.js` | 3,601 | Virtual grid, editor, selection, charts, CSS-zoom-based scaling, scrollbar drag, window-resize + container-size polling |
| `formula-parser.js` | 1,305 | Formula tokenizer + evaluator (130+ functions) |
| `spreadsheet.css` | 2,863 | All styles, including dialogs, scrollbars, paste highlight |
| **Total** | **~15,866** | |

**Dependencies:** `uri-load` (CSS loading), `extensions/prototype`, `archiver/packer` (XLSX/ODS *export*). Imports use the built-in native ZIP parser based on `DecompressionStream` — no JSZip or other external library is required.

---

## Notes

- All rendering uses virtual scrolling — only visible cells exist in the DOM, enabling smooth performance with 1,000+ rows.
- **Zoom** scales the cell canvas via the CSS `zoom` property (not `transform: scale()`) so the browser correctly reports `scrollHeight`/`scrollWidth` for native scrolling. The renderer keeps internal coordinates in unscaled CSS pixels and converts only at the DOM boundary. See [Zoom & Viewport](#zoom--viewport).
- **Scrollbars** stay visible at all zoom levels (the thumb fills the bar when content fits, and shrinks proportionally when content overflows). Both vertical and horizontal thumbs are draggable.
- **Container resize** is detected via two signals: the standard `window` resize event (debounced 100 ms), and a 250 ms interval that polls the root element's `clientWidth/clientHeight` and re-runs `resize()` when they change. The interval catches resizes the window event misses — pane drags, flex sibling growth, parent visibility toggles, dynamic CSS layout changes. See [Container resize handling](#container-resize-handling).
- **Paste highlight** stays visible for 2 seconds after a paste so the user can see exactly where the data landed; subsequent pastes reset the timer.
- **Selection rectangles, autofill handles, copy highlights, and the cell editor input** are children of the canvas element, so they inherit zoom automatically and stay aligned with the underlying cells.
- Formulas support 200+ functions across Math, Text, Logical, Lookup, Date/Time, Statistical, Financial, and Info categories.
- The recalculation engine builds a dependency graph and uses topological sort (Kahn's algorithm) for efficient recalc. Circular references are detected and display `#CIRC!`.
- Charts are inline SVG with 8 default colors: `#4285f4`, `#ea4335`, `#fbbc04`, `#34a853`, `#ff6d01`, `#46bdc6`, `#7b1fa2`, `#c2185b`.
- **Import** uses a built-in native ZIP parser (`DecompressionStream('deflate-raw')`) — no JSZip dependency. Supports XLSX, CSV, TSV, and ODS via drag-and-drop or file picker.
- **Export** uses the built-in `archiver/packer` module for ZIP-based formats (XLSX, ODS).
- The `toJSON()` / `fromJSON()` methods enable full serialization for saving to localStorage, IndexedDB, or a server.
- The module has zero external hard dependencies beyond the ApplicationPrototype framework.