Vitral 0.2
Data

Spreadsheet

A spreadsheet with its own formula engine and no dependency: A1 references, sixty-three functions and recalculation of only what changed.

WAI-ARIA tested

Import

main.ts
import { Spreadsheet } from '@vitral/vue';

Default

Type into it, drag the handle at the corner of the selection to fill, and watch the totals follow. Start a formula — `=B2*C2` — and each cell it reads is outlined where it sits.

A1
A
B
C
D
E
F
Item
Price
Qty
Total
Standing desk
£320.00
2
£640.00
Office chair
£95.50
4
£382.00
Monitor arm
£48.00
2
£96.00
Subtotal
£1,118.00
VAT
20%
£223.60
Due
£1,341.60
<script setup lang="ts">import { Spreadsheet } from '@vitral/vue';import { ref } from 'vue'; const invoice = ref<Record<string, string | number | boolean | null>>({    A1: 'Item',    B1: 'Price',    C1: 'Qty',    D1: 'Total',    A2: 'Standing desk',    B2: 320,    C2: 2,    D2: '=B2*C2',    A3: 'Office chair',    B3: 95.5,    C3: 4,    D3: '=B3*C3',    A4: 'Monitor arm',    B4: 48,    C4: 2,    D4: '=B4*C4',    A6: 'Subtotal',    D6: '=SUM(D2:D4)',    A7: 'VAT',    B7: '20%',    D7: '=D6*B7',    A8: 'Due',    D8: '=D6+D7'}); const formats = {    'B2:B4': { kind: 'currency', currency: 'GBP' } as const,    'D2:D8': { kind: 'currency', currency: 'GBP' } as const,    B7: { kind: 'percent' } as const};</script> <template>    <Spreadsheet v-model="invoice" :formats="formats" :rows="20" :columns="6" aria-label="Invoice" style="height: 22rem; width: 100%" /></template>

What comes back

`v-model` is what was typed, keyed by A1 — the formulas are formulas, not what they worked out to.

A1
A
B
C
D
E
Item
Price
Qty
Total
Standing desk
£320.00
2
£640.00
Office chair
£95.50
4
£382.00
Monitor arm
£48.00
2
£96.00
Subtotal
£1,118.00
VAT
20%
£223.60
Due
£1,341.60

What is kept for D8 is =D6+D7, and it reads £1,341.60 — edit a price above and both follow. 

<script setup lang="ts">import { Spreadsheet, StackPanel } from '@vitral/vue';import { nextTick, onMounted, ref } from 'vue'; const invoice = ref<Record<string, string | number | boolean | null>>({    A1: 'Item',    B1: 'Price',    C1: 'Qty',    D1: 'Total',    A2: 'Standing desk',    B2: 320,    C2: 2,    D2: '=B2*C2',    A3: 'Office chair',    B3: 95.5,    C3: 4,    D3: '=B3*C3',    A4: 'Monitor arm',    B4: 48,    C4: 2,    D4: '=B4*C4',    A6: 'Subtotal',    D6: '=SUM(D2:D4)',    A7: 'VAT',    B7: '20%',    D7: '=D6*B7',    A8: 'Due',    D8: '=D6+D7'}); const formats = {    'B2:B4': { kind: 'currency', currency: 'GBP' } as const,    'D2:D8': { kind: 'currency', currency: 'GBP' } as const,    B7: { kind: 'percent' } as const}; // `v-model` carries what was typed; what a cell reads as comes from the sheet// the component exposes.const sheet = ref<InstanceType<typeof Spreadsheet> | null>(null);const due = ref('');async function readDue() {    await nextTick();    due.value = sheet.value?.sheet()?.display({ row: 7, col: 3 }) ?? '';}onMounted(readDue);</script> <template>    <StackPanel spacing="1rem" style="width: 100%">        <Spreadsheet ref="sheet" v-model="invoice" :formats="formats" :rows="10" :columns="5" aria-label="Invoice" style="height: 16rem; width: 100%" @change="readDue" />        <p>            What is kept for <code>D8</code> is <code>{{ invoice.D8 }}</code            >, and it reads <strong>{{ due }}</strong> — edit a price above and both follow.        </p>    </StackPanel></template>

Formulas

Sixty-three of them: SUM, AVERAGE, MIN, MAX, COUNT and COUNTIF, IF and IFERROR, the text ones, INDEX, MATCH and VLOOKUP, and dates as the days every spreadsheet counts in.

A1
A
B
C
D
Name
Score
Grade
Ada
92
A
Alan
84
B
Grace
78
C
Average
84.7
Top
92
Above 80
2
<script setup lang="ts">import { Spreadsheet } from '@vitral/vue';import { ref } from 'vue'; const scores = ref<Record<string, string | number | boolean | null>>({    A1: 'Name',    B1: 'Score',    C1: 'Grade',    A2: 'Ada',    B2: 92,    C2: '=IF(B2>=90,"A",IF(B2>=80,"B","C"))',    A3: 'Alan',    B3: 84,    C3: '=IF(B3>=90,"A",IF(B3>=80,"B","C"))',    A4: 'Grace',    B4: 78,    C4: '=IF(B4>=90,"A",IF(B4>=80,"B","C"))',    A6: 'Average',    B6: '=ROUND(AVERAGE(B2:B4),1)',    A7: 'Top',    B7: '=MAX(B2:B4)',    A8: 'Above 80',    B8: '=COUNTIF(B2:B4,">=80")'});</script> <template>    <Spreadsheet v-model="scores" :rows="12" :columns="4" aria-label="Scores" style="height: 16rem; width: 100%" /></template>

When a formula cannot answer

An error is a value: it travels through arithmetic, and `IFERROR` catches it.

A1
A
B
C
#DIV/0!
divided by nothing
#NAME?
no such function
#CIRCULAR!
reads itself
#N/A
not in the list
caught
caught instead
<script setup lang="ts">import { Spreadsheet } from '@vitral/vue';import { ref } from 'vue'; const errors = ref<Record<string, string | number | boolean | null>>({    A1: '=1/0',    A2: '=NOPE(1)',    A3: '=A3+1',    A4: '=VLOOKUP("nothing",B1:C2,2)',    A5: '=IFERROR(1/0,"caught")',    B1: 'divided by nothing',    B2: 'no such function',    B3: 'reads itself',    B4: 'not in the list',    B5: 'caught instead'});</script> <template>    <Spreadsheet v-model="errors" :rows="8" :columns="3" :column-widths="{ B: 180 }" aria-label="Errors" style="height: 12rem; width: 100%" /></template>

The toolbar

`toolbar` takes groups of item names, or `false` for none; the `toolbar` slot replaces the bar with parts of your own. Every tool acts on the selected rectangle, and the pressed ones read the cell the caret is in.

A1
A
B
C
D
E
Month
Budget
Spent
Left
Used
January
4000
3120
880
0.78
February
4000
4310
-310
1.0775
March
4500
3980
520
0.884444444444
A
B
C
D
Left
Centre
Right
Pick a cell and press a tool.
<script setup lang="ts">import { Spreadsheet, StackPanel } from '@vitral/vue';import { ref } from 'vue'; const budget = ref<Record<string, string | number | boolean | null>>({    A1: 'Month',    B1: 'Budget',    C1: 'Spent',    D1: 'Left',    E1: 'Used',    A2: 'January',    B2: 4000,    C2: 3120,    D2: '=B2-C2',    E2: '=C2/B2',    A3: 'February',    B3: 4000,    C3: 4310,    D3: '=B3-C3',    E3: '=C3/B3',    A4: 'March',    B4: 4500,    C4: 3980,    D4: '=B4-C4',    E4: '=C4/B4'}); const minimal = ref<Record<string, string | number | boolean | null>>({    A1: 'Left',    B1: 'Centre',    C1: 'Right',    A2: 'Pick a cell and press a tool.'});</script> <template>    <StackPanel spacing="1rem" style="width: 100%">        <Spreadsheet v-model="budget" :rows="10" :columns="5" aria-label="Toolbar example" style="height: 14rem; width: 100%" />        <Spreadsheet            v-model="minimal"            :toolbar="[['bold', 'italic'], ['alignLeft', 'alignCenter', 'alignRight']]"            :formula-bar="false"            :rows="6"            :columns="4"            aria-label="Fewer tools"            style="height: 10rem; width: 100%"        />    </StackPanel></template>

Read only

`readonly` leaves the keyboard and the selection, and takes the typing and the fill handle away.

A
B
C
D
Plan
Seats
Monthly
Team
12
108
Business
40
280
<script setup lang="ts">import { Spreadsheet } from '@vitral/vue';import { ref } from 'vue'; const terms = ref<Record<string, string | number | boolean | null>>({    A1: 'Plan',    B1: 'Seats',    C1: 'Monthly',    A2: 'Team',    B2: 12,    C2: '=B2*9',    A3: 'Business',    B3: 40,    C3: '=B3*7'});</script> <template>    <Spreadsheet v-model="terms" readonly :formula-bar="false" :rows="6" :columns="4" aria-label="Terms" style="height: 10rem; width: 100%" /></template>

Where the keyboard is

`selection-change` says which cell, or which rectangle, every time it moves.

A1
A
B
C
D
Plan
Seats
Monthly
Team
12
108
Business
40
280

Selected: A1

<script setup lang="ts">import { Spreadsheet, StackPanel } from '@vitral/vue';import { ref } from 'vue'; const plans = ref<Record<string, string | number | boolean | null>>({    A1: 'Plan',    B1: 'Seats',    C1: 'Monthly',    A2: 'Team',    B2: 12,    C2: '=B2*9',    A3: 'Business',    B3: 40,    C3: '=B3*7'}); const selection = ref('A1');</script> <template>    <StackPanel spacing="1rem" style="width: 100%">        <Spreadsheet            v-model="plans"            :rows="8"            :columns="4"            aria-label="Selection example"            style="height: 12rem; width: 100%"            @selection-change="selection = $event.address"        />        <p>            Selected: <code>{{ selection }}</code>        </p>    </StackPanel></template>

API

Read from packages/vue/src/components/Spreadsheet/types.ts, so it says what the component actually accepts.

Props

NameTypeDescription
modelValueRecord<string, string | number | boolean | null>The cells, keyed by A1: `{ A1: 'Sales', B2: 42, B3: '=B2*2' }`. What is kept is what was typed, so a formula survives a round trip.
rowsnumberHow many rows and columns the grid offers.
columnsnumber—
formatsRecord<string, CellFormatLike>Formats, by A1 or by a rectangle: `{ 'B2:B9': { kind: 'currency' } }`.
columnWidthsRecord<string, number>Widths in pixels by column letter; the reader's own resizing goes here too.
rowHeightsRecord<number, number>Heights in pixels by row number, as a person counts them.
toolbarboolean | SpreadsheetToolbarItemLike[][]The bar of tools over the grid: `false` for none, or groups of item names. The `toolbar` slot replaces the bar with parts of your own.
formulaBarbooleanThe bar over the grid that shows the address and the formula. On by default.
currencystringThe currency a cell formatted as money is written in. Defaults to the locale's.
readonlybooleanNothing can be typed into it.
ariaLabelstringNames the grid, where no label points at it.

Plus pt, dt and unstyled from BaseProps, see pass-through and unstyled mode.

Slots

NameSlot propsDescription
toolbar—Replaces the bar of tools over the grid.