Spreadsheet features

With cell selection and editable columns the grid behaves much like a spreadsheet: fill cells by dragging, undo and redo changes, cut, copy, paste and delete cell ranges.

<template>
  <div class="demo">
    <div class="demo-toolbar">
      <button :disabled="!canUndo" @click="grid?.undo()">↶ Undo</button>
      <button :disabled="!canRedo" @click="grid?.redo()">↷ Redo</button>
      <span>Select cells and drag the square at the corner of the selection.</span>
    </div>
    <Datagrid ref="grid" :columns="columns" :rows="rows" :options="options" @update:rows="onRowsChange" />
  </div>
</template>

<script setup lang="ts">
import { ref } from "vue";
import { Datagrid, formatters, type ColumnConfig, type GridOptions } from "@datagrid/vue-ui";

type BudgetLine = {
  id: number;
  item: string;
  jan: number | null;
  feb: number | null;
  mar: number | null;
  apr: number | null;
  may: number | null;
  jun: number | null;
};

const grid = ref<InstanceType<typeof Datagrid>>();
const canUndo = ref(false);
const canRedo = ref(false);

// January and February are filled, the other months are empty
const rows = ref<Array<BudgetLine>>(
  ["Rent", "Salaries", "Hosting", "Software", "Travel", "Marketing", "Office supplies", "Insurance"].map(
    (item, i) => ({
      id: i + 1,
      item,
      jan: (i + 1) * 1000,
      feb: (i + 1) * 1000 + 250,
      mar: null,
      apr: null,
      may: null,
      jun: null,
    })
  )
);

function onRowsChange(changed: Array<BudgetLine>) {
  rows.value = changed;
  // the undo history changed with every edit, paste, fill, cut, undo and redo
  canUndo.value = grid.value?.api.canUndo() ?? false;
  canRedo.value = grid.value?.api.canRedo() ?? false;
}

const euro = formatters.currency("EUR", { maximumFractionDigits: 0 });
const months = ["jan", "feb", "mar", "apr", "may", "jun"] as const;

const columns: Array<ColumnConfig<BudgetLine>> = [
  { field: "item", text: "Item", minWidth: 130 },
  ...months.map(
    (month): ColumnConfig<BudgetLine> => ({
      field: month,
      text: month.charAt(0).toUpperCase() + month.slice(1),
      type: "number",
      valueFormatter: euro,
    })
  ),
];

const options: GridOptions<BudgetLine> = {
  rowId: (data) => data.id,
  selection: "Cell",
  singleSelect: false, // allows selecting ranges
  fillHandle: true,
  undoRedo: true, // default
  enterMovesDown: true,
  statusBar: true,
  defaultColumn: { editable: true, flex: 1, minWidth: 80 },
};
</script>

import { useRef, useState } from "react";
import {
  Datagrid,
  formatters,
  type ColumnConfig,
  type DatagridHandle,
  type GridOptions,
} from "@datagrid/react-ui";

type BudgetLine = {
  id: number;
  item: string;
  jan: number | null;
  feb: number | null;
  mar: number | null;
  apr: number | null;
  may: number | null;
  jun: number | null;
};

// January and February are filled, the other months are empty
const initialRows: Array<BudgetLine> = [
  "Rent",
  "Salaries",
  "Hosting",
  "Software",
  "Travel",
  "Marketing",
  "Office supplies",
  "Insurance",
].map((item, i) => ({
  id: i + 1,
  item,
  jan: (i + 1) * 1000,
  feb: (i + 1) * 1000 + 250,
  mar: null,
  apr: null,
  may: null,
  jun: null,
}));

const euro = formatters.currency("EUR", { maximumFractionDigits: 0 });
const months = ["jan", "feb", "mar", "apr", "may", "jun"] as const;

const columns: Array<ColumnConfig<BudgetLine>> = [
  { field: "item", text: "Item", minWidth: 130 },
  ...months.map(
    (month): ColumnConfig<BudgetLine> => ({
      field: month,
      text: month.charAt(0).toUpperCase() + month.slice(1),
      type: "number",
      valueFormatter: euro,
    })
  ),
];

const options: GridOptions<BudgetLine> = {
  rowId: (data) => data.id,
  selection: "Cell",
  singleSelect: false, // allows selecting ranges
  fillHandle: true,
  undoRedo: true, // default
  enterMovesDown: true,
  statusBar: true,
  defaultColumn: { editable: true, flex: 1, minWidth: 80 },
};

export default function Spreadsheet() {
  const grid = useRef<DatagridHandle>(null);
  const [rows, setRows] = useState(initialRows);
  const [canUndo, setCanUndo] = useState(false);
  const [canRedo, setCanRedo] = useState(false);

  function onRowsChange(changed: Array<BudgetLine>) {
    setRows(changed);
    // the undo history changed with every edit, paste, fill, cut, undo and redo
    setCanUndo(grid.current?.api.canUndo() ?? false);
    setCanRedo(grid.current?.api.canRedo() ?? false);
  }

  return (
    <div className="demo">
      <div className="demo-toolbar">
        <button disabled={!canUndo} onClick={() => grid.current?.undo()}>
          ↶ Undo
        </button>
        <button disabled={!canRedo} onClick={() => grid.current?.redo()}>
          ↷ Redo
        </button>
        <span>Select cells and drag the square at the corner of the selection.</span>
      </div>
      <Datagrid ref={grid} columns={columns} rows={rows} options={options} onRowsChange={onRowsChange} />
    </div>
  );
}

Try it:

• Select Jan and Feb of a row and drag the fill handle to the right: the series continues (1000, 1250 → 1500, 1750, …).
• Select a text cell and drag it down: the value is repeated.
• Press Ctrl + Z to undo the fill in one step, Ctrl + Y to redo it.
• Select a range and press Delete or Ctrl + X.

Options

Option	Default	Description
fillHandle	false	shows the fill handle at the bottom right corner of the cell selection, requires selection: "Cell"
undoRedo	true	records cell changes for undo and redo
undoLimit	50	maximum number of undo steps, older steps are dropped
clipboard	true	copy, cut and paste with the keyboard
enterMovesDown	false	after finishing an edit with Enter the focus moves to the cell below (Shift + Enter: cell above)

Only editable cells are changed by filling, pasting, cutting and deleting. Other cells in the range are skipped.

Fill handle

With fillHandle: true and selection: "Cell" a small square is shown at the bottom right cell of the selection. Drag it to extend the selection; when the mouse is released the new cells are filled with values of the selected (source) cells. Use singleSelect: false so that the user can select a range as source.

• The fill direction is the dominant direction of the mouse movement: down, up, right or left. The grid scrolls automatically when the mouse reaches the edge.
• Numbers with at least two source values continue as linear series: 1, 2 → 3, 4, 5, 10, 20 → 30, 40. Filling upwards or to the left continues the series backwards (10, 20 → 0, -10).
• All other values (text, dates, booleans, a single number) are repeated: a, b → a, b, a, b.
• Every column (when filling vertically) or row (when filling horizontally) is filled independently.
• Escape while dragging cancels filling and restores the selection.

A fill is one undo step.

Filling can also be done programmatically. Both ranges are row / column indexes of the visible rows and leaf columns, the target range contains the source range:

// fill rows 2–9 of columns 1–2 with the series of rows 0–1
api.fillRange(
  { rowStart: 0, rowEnd: 1, colStart: 1, colEnd: 2 },
  { rowStart: 0, rowEnd: 9, colStart: 1, colEnd: 2 }
);

Undo and redo

Every change of a cell value made by the grid – editing, paste, fill, cut, delete – is recorded (undoRedo: true is the default).

Key	Action
Ctrl / ⌘ + Z	undo
Ctrl / ⌘ + Y or Ctrl / ⌘ + Shift + Z	redo

• Changes of one user action are undone together: a paste, a fill, a cut or deleting a range is one undo step, however many cells it changed.
• A new change clears the redo history.
• After undo / redo the focus moves to the last restored cell.
• Undo and redo change the row objects like editing does, so cell-value-changed is emitted with source: "undo" / "redo" and the rows are updated (v-model:rows / onRowsChange).
• The history is kept in the grid only. Changes of the rows from outside (e.g. reloading data) are not recorded – call api.clearUndoHistory() when the old steps don't make sense anymore.

api.undo();              // false if there was nothing to undo
api.redo();
api.canUndo();           // e.g. to enable buttons
api.canRedo();
api.clearUndoHistory();

The undo stack changes with every change of the rows, so update the state of undo / redo buttons when the rows change, as in the demo above:

<Datagrid ref="grid" :rows="rows" @update:rows="onRowsChange" ... />

function onRowsChange(changed: Array<BudgetLine>) {
  rows.value = changed;
  canUndo.value = grid.value!.api.canUndo();
  canRedo.value = grid.value!.api.canRedo();
}

function onRowsChange(changed: Array<BudgetLine>) {
  setRows(changed);
  setCanUndo(grid.current!.api.canUndo());
  setCanRedo(grid.current!.api.canRedo());
}

<Datagrid ref={grid} rows={rows} onRowsChange={onRowsChange} ... />

Cut, copy, paste and delete

Key	Action
Ctrl + C	copies the selected cells (or the focused cell) as tab separated text
Ctrl + X	copies the selected cells and clears the editable ones
Ctrl + V	pastes starting at the focused cell; a single value is pasted into all selected cells
Delete / Backspace	clears the editable selected cells

Clearing sets a cell to the value its column parses from an empty input (usually null, see valueParser). Copy, cut and paste require clipboard: true (default). The text format is compatible with Excel and Google Sheets, see Clipboard for details.

await api.copySelection();   // returns the copied text
await api.cutSelection();    // copy + clear, one undo step
api.pasteText("1\t2\n3\t4"); // paste at the focused cell
api.clearSelectedCells();    // like the Delete key
api.getSelectionAsText();

With row selection the complete selected rows are copied, cut and cleared.

Related

• Status bar – sum, average, min and max of the selected cells
• Context menu – copy, cut, paste, undo and redo with the right mouse button
• Editing – editors, valueParser, validation