SpreadsheetFormulaBar

Lets the user edit one cell's value or formula in a text field above the grid.

import { SpreadsheetFormulaBar } from '@tumblerjs/svelte';

Example

Preview

Loading project-budget.xlsx…

With Cell set to D2, type =B2*C2*2 in the bar. Press Enter to apply it or Escape to cancel.

FormulaPreview.svelte
<script lang="ts">
  import { untrack } from 'svelte';
  import { openSpreadsheetArtifact } from '@tumblerjs/sheets';
  import {
    SpreadsheetFormulaBar,
    type SpreadsheetFormulaBarEdit,
  } from '@tumblerjs/svelte';

  let { bytes }: { bytes: Uint8Array } = $props();
  let artifact = $state(
    untrack(() => openSpreadsheetArtifact(bytes)),
  );
  let reference = $state('D2');
  let error = $state('');
  let result = $derived(
    artifact.calculation.displayText(reference),
  );

  // Return false to keep the bar in edit mode.
  function edit(change: SpreadsheetFormulaBarEdit) {
    try {
      artifact = change.kind === 'formula'
        ? artifact.editFormula(change.reference, change.formula)
        : artifact.editCell(change.reference, change.value);
      error = '';
      return true;
    } catch {
      error = 'Could not apply this formula.';
      return false;
    }
  }
</script>

<label>
  Cell
  <select bind:value={reference}>
    <option>B2</option>
    <option>C2</option>
    <option>D2</option>
  </select>
</label>
<SpreadsheetFormulaBar
  worksheet={artifact.worksheet}
  {reference}
  onedit={edit}
/>
<p>
  Calculated value
  <output aria-label="Calculated value">{result}</output>
</p>
{#if error}<p role="alert">{error}</p>{/if}

<style>
  label {
    display: flex;
    align-items: center;
    gap: 10px;
    margin-bottom: 16px;
  }
  output {
    margin-left: 12px;
    font-variant-numeric: tabular-nums;
  }
</style>

Usage

Pass the worksheet and the address of the cell to edit, such as D2. When the user presses Enter, onedit receives either a formula or a value: apply a formula with artifact.editFormula and a value with artifact.editCell, then assign the returned artifact. Input that starts with = becomes a formula, unless the cell is formatted as text. artifact.editFormula throws if the formula does not parse. Catch the error and return false, and the bar stays open so the user can fix it. Without onedit the bar is read-only.

The example takes the file contents as a bytes prop. To run it in your app, save the example source as FormulaPreview.svelte, put project-budget.xlsx in static/, and load it from +page.svelte:

+page.svelte
<script lang="ts">
  import { onMount } from 'svelte';
  import FormulaPreview from './FormulaPreview.svelte';

  let bytes = $state<Uint8Array>();
  onMount(async () => {
    const response = await fetch('/project-budget.xlsx');
    bytes = new Uint8Array(await response.arrayBuffer());
  });
</script>

{#if bytes}<FormulaPreview {bytes} />{/if}

Guide: Cells and formulas →

Props

PropDescription
worksheetrequired SpreadsheetWorksheetThe sheet containing the cell.
referencerequired stringThe cell to edit, in A1 notation, such as D2.
onedit (edit: SpreadsheetFormulaBarEdit) => boolean | voidCalled when the user commits. edit.kind is 'formula' or 'value'. Return false to keep the bar open.
referencePick SpreadsheetFormulaReferencePickInserts a grid range into the formula being typed, at the cursor. Pass { id, sheet, range }: a new numeric id for each pick, so picking the same range twice still inserts it; the sheet name; and the grid selection's range.
readonly booleanShows the cell's contents without allowing edits.