`<bmx-spreadsheet>`

A spreadsheet in the page: sheets of cells with over four hundred functions, formatting, frozen panes, merged cells, conditional formats, data validation, notes, sorting and filtering, find and replace, fill, copy and paste with Excel and other spreadsheets, and Excel (.xlsx) and CSV files opened and saved - all in the browser, with no server and nothing sent anywhere.

14 properties · 6 events · 54 methods · 6 parts

Example

Download as Excel

Download the sheet as CSV

Show the data as JSON

Change a figure: the totals, the growth and the colours follow. Try the Status column's list, the filter buttons, and Ctrl+C into another spreadsheet.

Read-only, for showing figures

Cells can be chosen, copied and searched, and nothing can be changed. No toolbar, no formula bar.

Show markup

<bmx-spreadsheet id="ex-sheet" file-name="sales-plan" label="Sales plan" style="--bmx-spreadsheet-height: 34rem"></bmx-spreadsheet>

<div class="row" style="margin-block-start: 0.75rem; gap: 0.5rem; flex-wrap: wrap">
  <bmx-button variant="outline" tone="neutral" id="ex-sheet-xlsx">Download as Excel</bmx-button>
  <bmx-button variant="outline" tone="neutral" id="ex-sheet-csv">Download the sheet as CSV</bmx-button>
  <bmx-button variant="outline" tone="neutral" id="ex-sheet-json">Show the data as JSON</bmx-button>
</div>
<p class="note" id="ex-sheet-out">Change a figure: the totals, the growth and the colours follow. Try the Status column's list, the filter buttons, and Ctrl+C into another spreadsheet.</p>
<pre class="note" id="ex-sheet-json-out" style="white-space: pre-wrap; max-block-size: 16rem; overflow: auto" hidden></pre>

<h3>Read-only, for showing figures</h3>

<p class="note">Cells can be chosen, copied and searched, and nothing can be changed. No toolbar, no formula bar.</p>
<bmx-spreadsheet id="ex-sheet-ro" readonly toolbar="false" formula-bar="false" label="Price list" style="--bmx-spreadsheet-height: 16rem"></bmx-spreadsheet>

<script type="module">
  await customElements.whenDefined('bmx-spreadsheet');

  const sheet = document.getElementById('ex-sheet');
  const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'];
  const products = [
    ['Starter licence', 4200, 4650, 5100, 5320, 5900, 6400],
    ['Team licence', 8800, 9100, 9900, 11200, 11800, 12900],
    ['Enterprise licence', 15000, 15000, 18500, 18500, 21000, 24500],
    ['Support renewals', 3100, 3300, 3250, 3600, 3900, 4100],
    ['Training days', 1200, 0, 1800, 2400, 600, 3000],
  ];
  const rows = products.map((p, i) => [...p, `=SUM(B${i + 3}:G${i + 3})`, `=IF(B${i + 3}=0,"",G${i + 3}/B${i + 3}-1)`, i % 3 === 0 ? 'On track' : i % 3 === 1 ? 'Ahead' : 'Watch']);
  sheet.data = {
    sheets: [
      {
        name: 'Plan',
        cells: [
          ['Sales plan, first half', null, null, null, null, null, null, null, null, null],
          ['Product', ...months, 'Total', 'Growth', 'Status'],
          ...rows,
          ['Total', ...months.map((_m, i) => `=SUM(${String.fromCharCode(66 + i)}3:${String.fromCharCode(66 + i)}7)`), '=SUM(H3:H7)', '=G8/B8-1', null],
        ],
        formats: { 'B3:H8': '£#,##0', 'I3:I8': '0.0%' },
      },
      { name: 'Notes', cells: [['Figures are made up for this example.']] },
    ],
    looks: {
      Plan: {
        columns: { A: { width: 150 }, H: { width: 100 }, J: { width: 96 } },
        styles: {
          A1: { bold: true, size: 14 },
          'A2:J2': { bold: true, fill: '#1971c2', color: '#ffffff', align: 'center' },
          'A8:J8': { bold: true, borderTop: { style: 'thin' }, borderBottom: { style: 'double' } },
        },
        freeze: { rows: 2, columns: 1 },
        rules: [
          { range: 'B3:G7', type: 'colorScale', colors: ['#fff5f5', '#ffffff', '#ebfbee'] },
          { range: 'H3:H7', type: 'dataBar', color: '#1971c2' },
          { range: 'I3:I7', type: 'cell', operator: 'less', value: 0.3, style: { color: '#c92a2a', bold: true } },
          { range: 'J3:J7', type: 'text', operator: 'contains', value: 'Watch', style: { fill: '#fff6db' } },
        ],
        validations: [{ range: 'J3:J7', type: 'list', list: ['On track', 'Ahead', 'Watch'], prompt: 'Choose a status.', message: 'Choose On track, Ahead or Watch.' }],
        notes: { H8: 'The half-year total for every product.' },
        filter: { range: 'A2:J7' },
      },
    },
    activeSheet: 'Plan',
  };

  const out = document.getElementById('ex-sheet-out');
  sheet.addEventListener('bmxSpreadsheetChange', async event => {
    if (event.detail.kind !== 'cells') return;
    out.textContent = `Half-year total: ${await sheet.getText('Plan!H8')}.`;
  });
  document.getElementById('ex-sheet-xlsx').addEventListener('click', () => sheet.downloadXlsx());
  document.getElementById('ex-sheet-csv').addEventListener('click', () => sheet.downloadCSV());
  document.getElementById('ex-sheet-json').addEventListener('click', async () => {
    const pre = document.getElementById('ex-sheet-json-out');
    pre.hidden = false;
    pre.textContent = JSON.stringify(await sheet.getData(), null, 2);
  });

  const prices = document.getElementById('ex-sheet-ro');
  prices.data = {
    sheets: [
      {
        name: 'Prices',
        cells: [
          ['Edition', 'Per year', 'Seats', 'Per seat'],
          ['Standard', 499, 1, '=B2/C2'],
          ['Team', 1499, 5, '=B3/C3'],
          ['Enterprise', 4999, 25, '=B4/C4'],
        ],
        formats: { 'B2:B4': '£#,##0', 'D2:D4': '£#,##0.00' },
      },
    ],
    looks: { Prices: { styles: { 'A1:D1': { bold: true } }, columns: { A: { width: 140 } } } },
  };
</script>

or from data:

sheet.data = { sheets: [{ name: 'Budget', cells: [['Item', 'Cost'], ['Rent', 950], ['Total', '=SUM(B2:B2)']] }], looks: { Budget: { styles: { 'A1:B1': { bold: true, fill: '#e7f0fb' } }, freeze: { rows: 1 } } }, };

The values and formulas live in a BmxFormulaWorkbook (getWorkbook()), the same engine bmx-formula-engine runs, and the looks beside it (getModel()), so a page can read and write cells from script while the reader works in the sheet. Every change fires bmxSpreadsheetChange; getData() returns the whole spreadsheet as JSON to store wherever the application keeps its data.

Keyboard, as a spreadsheet: arrows move and Ctrl+arrows jump to the edge of the data; Shift extends; Tab, Enter and their Shift forms move within a selection; type or F2 to edit (point at cells, or use arrows, to put references into a formula); Delete clears; Ctrl+C, Ctrl+X and Ctrl+V copy, cut and paste (to and from other spreadsheets); Ctrl+D and Ctrl+R fill; Ctrl+Z and Ctrl+Y undo and redo; Ctrl+B, Ctrl+I and Ctrl+U format; Ctrl+F finds; Alt+Down opens a list; Shift+F10 the cell menu; Ctrl+PageUp and Ctrl+PageDown change sheet.

Properties

Property	Attribute	Type	Default	Description
`data`	`data`	`BmxSpreadsheetData \| string`	—	The spreadsheet: `{ sheets, names, tables, looks, activeSheet }`, as a property or JSON.
`fileName`	`file-name`	`string`	`'spreadsheet'`	The file name downloads are saved under, without its extension.
`formulaBar`	`formula-bar`	`boolean`	`true`	Show the formula bar.
`functions`	property only	`Record<string, BmxFormulaCustomFunction>`	—	Functions of your own, by name, for formulas. Set from script.
`headings`	`headings`	`boolean`	`true`	Show the row and column headings.
`label`	`label`	`string`	—	The accessible name of the sheet grid.
`locale`	`locale`	`string`	—	A BCP 47 locale: decides formula separators, how typed numbers and dates are read, and month names.
`readonly`	`readonly`	`boolean`	`false`	Nothing can be changed; cells can still be chosen, copied and searched.
`requestInit`	`request-init`	`RequestInit \| string`	—	Options for the `src` request (headers, credentials), as a property or JSON.
`sheetTabs`	`sheet-tabs`	`boolean`	`true`	Show the sheet tabs.
`src`	`src`	`string`	—	A URL to open: an Excel file (.xlsx), a CSV or tab-separated file, or JSON in the shape `getData()` returns. Your server, your file.
`statusBar`	`status-bar`	`boolean`	`true`	Show the status bar (sum, average and count of the selection).
`strings`	`strings`	`Partial<BmxSpreadsheetStrings> \| string`	—	The words it shows, to translate or change them.
`toolbar`	`toolbar`	`boolean`	`true`	Show the formatting toolbar.

Events

Event	Detail	Description
`bmxSpreadsheetCellEdit`	`BmxSpreadsheetCellEditDetail`	A typed entry is about to go into a cell; cancel to refuse it.
`bmxSpreadsheetChange`	`BmxSpreadsheetChangeDetail`	Values, looks, rows or sheets changed.
`bmxSpreadsheetError`	`BmxSpreadsheetErrorDetail`	Opening a file or `src` failed.
`bmxSpreadsheetLoad`	`{ readonly sheets: readonly string[]; }`	A file or `src` was opened.
`bmxSpreadsheetSelectionChange`	`BmxSpreadsheetSelectionDetail`	The selection or the active cell changed.
`bmxSpreadsheetSheetChange`	`BmxSpreadsheetSheetDetail`	Another sheet was brought to the front.

Methods

Method	Signature	Description
`activateSheet`	`activateSheet(name: string) => Promise<void>`	Brings a sheet to the front.
`addConditionalFormat`	`addConditionalFormat(rule: BmxSpreadsheetRule, sheet?: string) => Promise<string>`	Adds a conditional format; returns its id.
`addSheet`	`addSheet(name?: string) => Promise<string>`	Adds a sheet (after the active one); returns its name.
`addValidation`	`addValidation(validation: BmxSpreadsheetValidation, sheet?: string) => Promise<string>`	Adds data validation to a range; returns its id.
`autoFitColumns`	`autoFitColumns(columns?: string) => Promise<void>`	Fits columns to their contents (all used columns by default).
`deleteColumns`	`deleteColumns(from: string \| number, count?: number) => Promise<void>`	Deletes columns from a column (a letter, or 1-based).
`deleteRows`	`deleteRows(from: number, count?: number) => Promise<void>`	Deletes rows from a row (1-based).
`downloadCSV`	`downloadCSV(sheet?: string, name?: string) => Promise<void>`	Saves a sheet as a CSV file.
`downloadXlsx`	`downloadXlsx(name?: string) => Promise<void>`	Saves the spreadsheet as an Excel file.
`find`	`find(query: string, options?: BmxSpreadsheetFindOptions) => Promise<BmxSpreadsheetFound[]>`	Finds cells by their text (or formulas).
`freezePanes`	`freezePanes(rows: number, columns?: number) => Promise<void>`	Keeps rows and columns in view while the rest scrolls; 0 and 0 unfreezes.
`getActiveSheet`	`getActiveSheet() => Promise<string>`	The sheet in front.
`getCellAddress`	`getCellAddress() => Promise<string>`	The active cell with its sheet: `Sheet1!B4`.
`getConditionalFormats`	`getConditionalFormats(sheet?: string) => Promise<BmxSpreadsheetRule[]>`
`getData`	`getData() => Promise<BmxSpreadsheetData>`	The whole spreadsheet as JSON: sheets, cells, formulas, formats, names, tables and looks.
`getFormulaWorkbook`	`getFormulaWorkbook() => Promise<BmxFormulaWorkbook>`	The workbook, for a `bmx-formula-bar` bound to this spreadsheet.
`getModel`	`getModel() => Promise<BmxSpreadsheetModel>`	The model: the workbook and every sheet's look.
`getSelection`	`getSelection() => Promise<BmxSpreadsheetSelectionDetail>`	The active cell and the selected ranges.
`getStyle`	`getStyle(address: string) => Promise<BmxSpreadsheetCellStyle>`	A cell's style (its column's, row's and own together).
`getText`	`getText(address: string) => Promise<string>`	A cell's value as shown.
`getValue`	`getValue(address: string) => Promise<BmxFormulaValue>`	A cell's value.
`getWorkbook`	`getWorkbook() => Promise<BmxFormulaWorkbook>`	The workbook behind the cells: the formula engine's full API.
`goToCell`	`goToCell(address: string) => Promise<boolean>`	Makes a cell (or range) the active one and shows it.
`insertColumns`	`insertColumns(before: string \| number, count?: number) => Promise<void>`	Inserts columns before a column (a letter, or 1-based).
`insertRows`	`insertRows(before: number, count?: number) => Promise<void>`	Inserts rows before a row (1-based).
`load`	`load(file: Blob \| File \| string) => Promise<void>`	Opens a file (an Excel, CSV or JSON file, as a File, Blob or URL).
`loadCSV`	`loadCSV(text: string, at?: string) => Promise<void>`	Reads CSV (or tab-separated) text into a sheet from a cell, as typed.
`mergeCells`	`mergeCells(range: string, mode?: "all" \| "across" \| "center") => Promise<void>`	Merges a range: `all`, `across` (each row) or `center` (and centres it).
`recalculate`	`recalculate() => Promise<void>`	Recalculates every formula.
`redoChange`	`redoChange() => Promise<boolean>`
`removeConditionalFormat`	`removeConditionalFormat(id: string, sheet?: string) => Promise<void>`
`removeSheet`	`removeSheet(name: string) => Promise<void>`
`removeValidation`	`removeValidation(idOrRange: string, sheet?: string) => Promise<void>`
`renameSheet`	`renameSheet(from: string, to: string) => Promise<void>`
`replace`	`replace(query: string, replacement: string, options?: BmxSpreadsheetFindOptions) => Promise<number>`	Replaces text in what cells hold; returns how many cells changed.
`select`	`select(range: string) => Promise<void>`	Selects a cell or range (on its sheet), and scrolls it into view.
`setBorders`	`setBorders(range: string, which: BmxSpreadsheetBorderSet, border?: BmxSpreadsheetBorder) => Promise<void>`	Draws borders: `all`, `outer`, `inner`, `top`, `bottom`, `left`, `right`, `horizontal`, `vertical` or `none`.
`setCell`	`setCell(address: string, text: string \| number \| boolean \| null) => Promise<void>`	Sets a cell from text, as typed: `=` starts a formula. Not checked against validation; use `setCellText` for that.
`setCellText`	`setCellText(address: string, text: string) => Promise<boolean>`	Sets a cell from typed text through the spreadsheet's checks: data validation and `bmxSpreadsheetCellEdit`. Resolves false when refused.
`setCells`	`setCells(cells: Record<string, string \| number \| boolean \| null>) => Promise<void>`	Sets several cells at once, as one undo step: `{ "A1": "Total", "B1": "=SUM(B2:B9)" }`.
`setColumnWidth`	`setColumnWidth(columns: string, width: number) => Promise<void>`	Sets column widths in CSS pixels: `setColumnWidth('B:D', 120)`.
`setData`	`setData(data: BmxSpreadsheetData) => Promise<void>`	Replaces the whole spreadsheet.
`setFilter`	`setFilter(range: string \| null, columns?: Record<string, BmxSpreadsheetFilterColumn>) => Promise<void>`	Turns the filter on over a range (its first row the headings), or off with null.
`setFocus`	`setFocus() => Promise<void>`	Moves focus to the sheet.
`setNote`	`setNote(address: string, text: string \| null) => Promise<void>`	Sets a cell's note (null removes it).
`setNumberFormat`	`setNumberFormat(range: string, code: string \| null) => Promise<void>`	Sets a range's number format (`#,##0.00`, `dd/mm/yyyy`, `0%`); null or 'General' removes it.
`setRowHeight`	`setRowHeight(rows: string \| number, height: number) => Promise<void>`	Sets row heights in CSS pixels: `setRowHeight('1:3', 32)`.
`setStyle`	`setStyle(range: string, style: BmxSpreadsheetStyleChange) => Promise<void>`	Lays a style over a range: `{ bold: true, fill: '#fff3bf' }`; `null` removes a property.
`showReferences`	`showReferences(references: readonly { range: string; color?: string; }[]) => Promise<void>`	Outlines ranges in colours, as a formula bar does for the formula being typed.
`sort`	`sort(range: string, by: readonly { column: string; descending?: boolean; }[], header?: boolean) => Promise<void>`	Sorts a range's rows by columns (letters): `sort('A1:D20', [{ column: 'C', descending: true }], true)`.
`toCSV`	`toCSV(sheet?: string, delimiter?: string) => Promise<string>`	A sheet (the active one by default) as CSV, values as shown. Text that another spreadsheet would read as a formula is written with a `'` first.
`toXlsx`	`toXlsx() => Promise<Uint8Array>`	The spreadsheet as an Excel file (.xlsx): values, formulas, formats and looks.
`undoChange`	`undoChange() => Promise<boolean>`
`unmergeCells`	`unmergeCells(range: string) => Promise<void>`

CSS shadow parts

Part	Description
`formula-bar`	the formula bar row.
`frame`	the whole spreadsheet.
`grid`	the scrolling sheet.
`status`	the status bar.
`tabs`	the sheet tabs.
`toolbar`	the formatting toolbar.

CSS custom properties

Property	Description
`--bmx-spreadsheet-cell-font-size`	The text size of cells with no size of their own. Default 13px.
`--bmx-spreadsheet-gridlines`	The colour of the grid lines.
`--bmx-spreadsheet-header-background`	Behind the row and column headings.
`--bmx-spreadsheet-height`	How tall the spreadsheet is. Default 36rem.
`--bmx-spreadsheet-selection`	The selection's outline and the active cell's border.