- Spreadsheet docs
- Developers
Recipes
Complete solutions for common jobs - fill a template from your API, save to your backend, a read-only report viewer, lock down the UI, input validation, your own toolbar and two synchronised workbooks.
Each recipe solves one job from start to finish, with code you can adapt. They use only the public API; the pages linked from each recipe explain the calls in depth.
Fill a template from your API
Keep the layout, formulas, charts and print setup in an .xlsx template that someone designs in a spreadsheet app, and let your code fill only the data. Named cells make the template robust: your code writes to CustomerName, not to B4, so the designer can move things around.
JavaScript
import { mountWorkbook } from '@8sheets/core';
const wb = await mountWorkbook(document.querySelector('#sheet'), { fileName: 'Invoice' });
async function openTemplate(url) {
const blob = await (await fetch(url)).blob();
await wb.openFile(blob, { name: 'invoice-template.xlsx' });
}
// Writes to a defined name such as "CustomerName" (refers to Invoice!$B$4).
function setByName(name, value) {
const ref = wb.names.get(name);
if (!ref) throw new Error(`The template has no name ${name}`);
const [sheetName, address] = ref.replace(/'/g, '').split('!');
wb.sheet(sheetName).range(address.replace(/\$/g, '')).values = value;
}
await openTemplate('/templates/invoice.xlsx');
const invoice = await (await fetch('/api/invoices/2027-014')).json();
wb.history.transaction('Fill invoice', () => {
setByName('CustomerName', invoice.customer.name);
setByName('InvoiceNumber', invoice.number);
const lines = wb.sheet('Invoice').range('A12'); // the first row of the item table
lines.values = invoice.lines.map((l) => [l.description, l.quantity, l.unitPrice]);
});
wb.history.clear();React
import { Sheet } from '@8sheets/react';
function setByName(wb, name, value) {
const [sheetName, address] = wb.names.get(name).replace(/'/g, '').split('!');
wb.sheet(sheetName).range(address.replace(/\$/g, '')).values = value;
}
export function InvoiceEditor({ invoiceId }) {
async function fill(wb) {
const blob = await (await fetch('/templates/invoice.xlsx')).blob();
await wb.openFile(blob, { name: 'invoice-template.xlsx' });
const invoice = await (await fetch(`/api/invoices/${invoiceId}`)).json();
wb.history.transaction('Fill invoice', () => {
setByName(wb, 'CustomerName', invoice.customer.name);
setByName(wb, 'InvoiceNumber', invoice.number);
wb.sheet('Invoice').range('A12').values = invoice.lines.map((l) => [l.description, l.quantity, l.unitPrice]);
});
wb.history.clear();
}
return (
<div style={{ height: 640 }}>
<Sheet key={invoiceId} options={{ fileName: 'Invoice' }} onReady={fill} />
</div>
);
}Vue
<script setup>
import { Sheet } from '@8sheets/vue';
const props = defineProps({ invoiceId: String });
function setByName(wb, name, value) {
const [sheetName, address] = wb.names.get(name).replace(/'/g, '').split('!');
wb.sheet(sheetName).range(address.replace(/\$/g, '')).values = value;
}
async function fill(wb) {
const blob = await (await fetch('/templates/invoice.xlsx')).blob();
await wb.openFile(blob, { name: 'invoice-template.xlsx' });
const invoice = await (await fetch(`/api/invoices/${props.invoiceId}`)).json();
wb.history.transaction('Fill invoice', () => {
setByName(wb, 'CustomerName', invoice.customer.name);
setByName(wb, 'InvoiceNumber', invoice.number);
wb.sheet('Invoice').range('A12').values = invoice.lines.map((l) => [l.description, l.quantity, l.unitPrice]);
});
wb.history.clear();
}
</script>
<template>
<Sheet :key="invoiceId" :options="{ fileName: 'Invoice' }" style="height: 640px" @ready="fill" />
</template>Angular
import { Component, Input } from '@angular/core';
import { SheetComponent, SheetsWorkbook } from '@8sheets/angular';
interface Invoice { number: string; customer: { name: string }; lines: { description: string; quantity: number; unitPrice: number }[] }
@Component({
selector: 'app-invoice-editor',
imports: [SheetComponent],
template: `<eight-sheet class="grid" [options]="{ fileName: 'Invoice' }" (ready)="fill($event)" />`,
styles: `.grid { height: 640px; }`,
})
export class InvoiceEditor {
@Input() invoiceId = '2027-014';
async fill(wb: SheetsWorkbook) {
const blob = await (await fetch('/templates/invoice.xlsx')).blob();
await wb.openFile(blob, { name: 'invoice-template.xlsx' });
const invoice: Invoice = await (await fetch(`/api/invoices/${this.invoiceId}`)).json();
wb.history.transaction('Fill invoice', () => {
this.setByName(wb, 'CustomerName', invoice.customer.name);
this.setByName(wb, 'InvoiceNumber', invoice.number);
wb.sheet('Invoice').range('A12').values = invoice.lines.map((l) => [l.description, l.quantity, l.unitPrice]);
});
wb.history.clear();
}
private setByName(wb: SheetsWorkbook, name: string, value: string | number) {
const [sheetName, address] = (wb.names.get(name, undefined) ?? '').replace(/'/g, '').split('!');
wb.sheet(sheetName).range(address.replace(/\$/g, '')).values = value;
}
}The template's formulas recalculate as the data arrives. Opening a file replaces the workbook's contents, so open the template first and fill it after. To hand the finished file on, call saveXlsx or sheet.print.toPdf after filling it; see Loading and saving files.
Save to your backend
Track whether the workbook has unsaved changes, and save the file to your server on a button or after a pause in editing:
JavaScript
let dirty = false;
let timer = 0;
wb.events.on('step-done', () => { // every undo step, including formats and undo itself
dirty = true;
clearTimeout(timer);
timer = setTimeout(save, 5000);
});
async function save() {
if (!dirty) return;
dirty = false;
const parts = [];
await wb.saveXlsx({ write: async (bytes) => { parts.push(bytes.slice()); } });
const res = await fetch('/api/documents/42', { method: 'PUT', body: new Blob(parts) });
if (!res.ok) dirty = true; // try again with the next change
wb.statusBar.setMessage(res.ok ? 'Saved' : 'Saving failed');
}
addEventListener('beforeunload', (e) => { if (dirty) e.preventDefault(); });React
import { useRef, useState } from 'react';
import { Sheet } from '@8sheets/react';
export function Document({ id }) {
const sheet = useRef(null);
const [dirty, setDirty] = useState(false);
async function save() {
const parts = [];
await sheet.current.saveXlsx({ write: async (bytes) => { parts.push(bytes.slice()); } });
const res = await fetch(`/api/documents/${id}`, { method: 'PUT', body: new Blob(parts) });
if (res.ok) setDirty(false);
}
return (
<>
<button onClick={save} disabled={!dirty}>{dirty ? 'Save' : 'Saved'}</button>
<div style={{ height: 600 }}>
<Sheet ref={sheet} onStepDone={() => setDirty(true)} />
</div>
</>
);
}Vue
<script setup>
import { ref } from 'vue';
import { Sheet, useSheet } from '@8sheets/vue';
const props = defineProps({ id: String });
const { sheet } = useSheet();
const dirty = ref(false);
async function save() {
const parts = [];
await sheet.value.saveXlsx({ write: async (bytes) => { parts.push(bytes.slice()); } });
const res = await fetch(`/api/documents/${props.id}`, { method: 'PUT', body: new Blob(parts) });
if (res.ok) dirty.value = false;
}
</script>
<template>
<button :disabled="!dirty" @click="save">{{ dirty ? 'Save' : 'Saved' }}</button>
<Sheet ref="sheet" style="height: 600px" @step-done="dirty = true" />
</template>Angular
import { Component, Input, ViewChild, signal } from '@angular/core';
import { SheetComponent } from '@8sheets/angular';
@Component({
selector: 'app-document',
imports: [SheetComponent],
template: `
<button (click)="save()" [disabled]="!dirty()">{{ dirty() ? 'Save' : 'Saved' }}</button>
<eight-sheet class="grid" (stepDone)="dirty.set(true)" />
`,
styles: `.grid { height: 600px; }`,
})
export class DocumentEditor {
@Input() id = '42';
@ViewChild(SheetComponent) sheet!: SheetComponent;
dirty = signal(false);
async save() {
const blob = (await this.sheet.saveXlsx()) as Blob;
const res = await fetch(`/api/documents/${this.id}`, { method: 'PUT', body: blob });
if (res.ok) this.dirty.set(false);
}
}Choose the format by what happens next: .xlsx for a file people download and open elsewhere, wb.saveDocument() for a working copy that keeps everything, or wb.toJSON() to store the model in a database column.
A read-only report viewer
Show a report that readers can scroll, sort and filter but not change. The complete setup, in all four forms, is on Embedding and options: leave out the editing parts, turn off the context menu, drag and sheet management, and cancel before-edit and before-change. Check access to the report on your server; the browser view is a convenience, not a lock.
Lock down the UI
Offer a spreadsheet with exactly the tools your users need: hide ribbon tabs and controls you don't support, stop the commands that remain reachable by keys, and keep the structure fixed. This combines Control everything from code, Protection and, for a view with no editing at all, Read-only workbooks.
JavaScript
import { mountWorkbook } from '@8sheets/core';
const wb = await mountWorkbook(document.querySelector('#sheet'), {
parts: { titleBar: false },
scripts: false, // no Automate tab
onlineFeatures: false, // nothing reaches other sites
newSheetButton: false,
});
lockDown(wb);
async function lockDown(wb) {
for (const tab of ['Draw', 'Page Layout', 'View']) wb.ribbon.hideTab(tab);
wb.ribbon.hide('ribbon.insert.pivotTable');
wb.ribbon.hideGroup('ribbon.data.removeDuplicates');
const blocked = new Set(['grid.insertSheet', 'grid.macros', 'grid.getData']);
wb.events.on('before-command', (e) => {
if (e.source !== 'api' && (blocked.has(e.name) || e.name.startsWith('ribbon.pageLayout.'))) e.cancel();
});
await wb.protectStructure(); // no adding, renaming or deleting sheets
}React
import { Sheet } from '@8sheets/react';
const options = { parts: { titleBar: false }, scripts: false, onlineFeatures: false, newSheetButton: false };
const blocked = new Set(['grid.insertSheet', 'grid.macros', 'grid.getData']);
async function lockDown(wb) {
for (const tab of ['Draw', 'Page Layout', 'View']) wb.ribbon.hideTab(tab);
wb.ribbon.hide('ribbon.insert.pivotTable');
await wb.protectStructure();
}
export function LockedWorkbook() {
return (
<div style={{ height: 600 }}>
<Sheet
options={options}
onReady={lockDown}
onBeforeCommand={(e) => { if (e.source !== 'api' && blocked.has(e.name)) e.cancel(); }}
/>
</div>
);
}Vue
<script setup>
import { Sheet } from '@8sheets/vue';
const options = { parts: { titleBar: false }, scripts: false, onlineFeatures: false, newSheetButton: false };
const blocked = new Set(['grid.insertSheet', 'grid.macros', 'grid.getData']);
async function lockDown(wb) {
for (const tab of ['Draw', 'Page Layout', 'View']) wb.ribbon.hideTab(tab);
wb.ribbon.hide('ribbon.insert.pivotTable');
await wb.protectStructure();
}
function guard(e) {
if (e.source !== 'api' && blocked.has(e.name)) e.cancel();
}
</script>
<template>
<Sheet :options="options" style="height: 600px" @ready="lockDown" @before-command="guard" />
</template>Angular
import { Component } from '@angular/core';
import { SheetComponent, SheetEventPayload, SheetOptions, SheetsWorkbook } from '@8sheets/angular';
@Component({
selector: 'app-locked-workbook',
imports: [SheetComponent],
template: `<eight-sheet class="grid" [options]="options" (ready)="lockDown($event)" (beforeCommand)="guard($event)" />`,
styles: `.grid { height: 600px; }`,
})
export class LockedWorkbook {
options: SheetOptions = { parts: { titleBar: false }, scripts: false, onlineFeatures: false, newSheetButton: false };
private blocked = new Set(['grid.insertSheet', 'grid.macros', 'grid.getData']);
async lockDown(wb: SheetsWorkbook) {
for (const tab of ['Draw', 'Page Layout', 'View']) wb.ribbon.hideTab(tab);
wb.ribbon.hide('ribbon.insert.pivotTable');
await wb.protectStructure();
}
guard(e: SheetEventPayload) {
if (e['source'] !== 'api' && this.blocked.has(e['name'])) e.cancel();
}
}- Hidden controls can't be clicked, but their keyboard shortcuts still work, which is why the listener cancels
before-commandby name as well. Find a control's id withwb.ribbon.controls(tab), and watchafter-commandwhile you try the app to learn the ids of the keys and menu rows your users press. - What
hide,hideGroupandhideTabhide isn't saved with the workbook: runlockDownafter every mount. - Edits that aren't commands, such as typing in cells, still work; protect sheets or follow the read-only setup to stop those too.
Validate input
Two layers work together. Data validation travels with the file and shows Excel's own input messages and error alerts. Events run your own rules, which can call your code or your server.
const sheet = wb.sheet('Orders');
// 1. Rules in the file: a list and a number range.
sheet.setValidation('B2:B500', { type: 'list', formula1: '"North,South,East,West"', errorTitle: 'Region', error: 'Pick a region from the list.' });
sheet.setValidation('D2:D500', { type: 'whole', operator: 'between', formula1: '1', formula2: '1000', errorTitle: 'Units', error: 'Units are a whole number from 1 to 1000.' });
wb.setOptions({ flagInvalidData: true }); // circle cells that break their rule
// 2. Your own rules, before the text is stored.
const skuPattern = /^[A-Z]{3}-\d{4}$/;
wb.events.on('before-commit', (e) => {
if (e.sheet.name !== 'Orders' || e.col !== 0 || e.row === 0) return;
e.text = e.text.trim().toUpperCase();
if (!skuPattern.test(e.text)) {
e.cancel(); // the editor stays open
wb.statusBar.setMessage('A SKU looks like ABC-1234.');
}
});
// 3. Hear about entries that break a file rule.
wb.events.on('validation-failed', (e) => console.warn('invalid', e.row, e.col, e.text));
before-commit covers typing; pasted and filled cells arrive through cells-changed, where you can check them after the fact.
Your own toolbar
Leave out the ribbon and give users the few actions your product needs, in your own design:
JavaScript
import { mountWorkbook } from '@8sheets/core';
const wb = await mountWorkbook(document.querySelector('#sheet'), { parts: { titleBar: false, ribbon: false } });
const actions = {
bold: () => wb.commands.execute('format.bold'),
money: () => { for (const r of wb.selectedRanges) r.style = { numFmt: '#,##0.00' }; },
sum: () => wb.commands.execute('edit.autoSum'),
undo: () => wb.history.undo(),
redo: () => wb.history.redo(),
};
for (const [name, run] of Object.entries(actions)) {
document.querySelector(`[data-action="${name}"]`).addEventListener('click', run);
}
const sync = () => {
document.querySelector('[data-action="undo"]').disabled = !wb.history.canUndo;
document.querySelector('[data-action="redo"]').disabled = !wb.history.canRedo;
};
wb.events.on('step-done', sync);
sync();React
import { useRef, useState } from 'react';
import { Sheet } from '@8sheets/react';
export function SimpleEditor() {
const sheet = useRef(null);
const [history, setHistory] = useState({ canUndo: false, canRedo: false });
const wb = () => sheet.current.workbook;
const refresh = () => setHistory({ canUndo: wb().history.canUndo, canRedo: wb().history.canRedo });
return (
<>
<div role="toolbar" aria-label="Formatting">
<button onClick={() => wb().commands.execute('format.bold')}>Bold</button>
<button onClick={() => { for (const r of wb().selectedRanges) r.style = { numFmt: '#,##0.00' }; }}>Money</button>
<button onClick={() => wb().commands.execute('edit.autoSum')}>Sum</button>
<button onClick={() => wb().history.undo()} disabled={!history.canUndo}>Undo</button>
<button onClick={() => wb().history.redo()} disabled={!history.canRedo}>Redo</button>
</div>
<div style={{ height: 560 }}>
<Sheet ref={sheet} options={{ parts: { titleBar: false, ribbon: false } }} onStepDone={refresh} />
</div>
</>
);
}Vue
<script setup>
import { reactive } from 'vue';
import { Sheet, useSheet } from '@8sheets/vue';
const { sheet, workbook } = useSheet(); // <Sheet ref="sheet"> below
const history = reactive({ canUndo: false, canRedo: false });
function refresh() {
history.canUndo = workbook.value.history.canUndo;
history.canRedo = workbook.value.history.canRedo;
}
function money() {
for (const r of workbook.value.selectedRanges) r.style = { numFmt: '#,##0.00' };
}
</script>
<template>
<div role="toolbar" aria-label="Formatting">
<button @click="workbook.commands.execute('format.bold')">Bold</button>
<button @click="money">Money</button>
<button @click="workbook.commands.execute('edit.autoSum')">Sum</button>
<button :disabled="!history.canUndo" @click="workbook.history.undo()">Undo</button>
<button :disabled="!history.canRedo" @click="workbook.history.redo()">Redo</button>
</div>
<Sheet ref="sheet" :options="{ parts: { titleBar: false, ribbon: false } }" style="height: 560px" @step-done="refresh" />
</template>Angular
import { Component, signal } from '@angular/core';
import { SheetComponent, SheetsWorkbook } from '@8sheets/angular';
@Component({
selector: 'app-simple-editor',
imports: [SheetComponent],
template: `
<div role="toolbar" aria-label="Formatting">
<button (click)="wb?.commands?.execute('format.bold')">Bold</button>
<button (click)="money()">Money</button>
<button (click)="wb?.commands?.execute('edit.autoSum')">Sum</button>
<button (click)="wb?.history?.undo()" [disabled]="!canUndo()">Undo</button>
<button (click)="wb?.history?.redo()" [disabled]="!canRedo()">Redo</button>
</div>
<eight-sheet class="grid" [options]="options" (ready)="wb = $event" (stepDone)="refresh()" />
`,
styles: `.grid { height: 560px; }`,
})
export class SimpleEditor {
options = { parts: { titleBar: false, ribbon: false } };
wb?: SheetsWorkbook;
canUndo = signal(false);
canRedo = signal(false);
refresh() {
this.canUndo.set(!!this.wb?.history.canUndo);
this.canRedo.set(!!this.wb?.history.canRedo);
}
money() {
for (const r of this.wb?.selectedRanges ?? []) r.style = { numFmt: '#,##0.00' };
}
}Commands act on the workbook's current selection. Ribbon, menus and commands has the full command list and how to add buttons to the built-in ribbon instead.
Synchronise two workbooks
Two workbooks on one page, such as an input sheet and a preview, can follow each other through events. This recipe mirrors every edit made in the first into the second, and keeps the second scrolled to the same cell:
JavaScript
import { mountWorkbook } from '@8sheets/core';
const input = await mountWorkbook(document.querySelector('#input'));
const preview = await mountWorkbook(document.querySelector('#preview'), {
parts: { titleBar: false, ribbon: false, formulaBar: false, sidePanels: false },
});
function mirror(from, to) {
return (e) => {
const { r0, c0, r1, c1 } = e.bounds;
const source = e.sheet.range(r0, c0, r1, c1);
const target = to.sheet(e.sheet.index).range(r0, c0, r1, c1);
const formulas = source.formulas;
const values = source.values;
to.history.transaction('Mirror', () => {
target.values = values;
formulas.forEach((row, i) => row.forEach((f, j) => {
if (f) target.offset(i, j, 1, 1).formulas = f;
}));
});
};
}
input.events.on('cells-changed', mirror(input, preview));
input.events.on('selection-changed', (e) => preview.showCell(e.to.row, e.to.col));React
import { useRef } from 'react';
import { Sheet } from '@8sheets/react';
function copyChange(e, to) {
const { r0, c0, r1, c1 } = e.bounds;
const source = e.sheet.range(r0, c0, r1, c1);
const target = to.sheet(e.sheet.index).range(r0, c0, r1, c1);
const formulas = source.formulas;
to.history.transaction('Mirror', () => {
target.values = source.values;
formulas.forEach((row, i) => row.forEach((f, j) => { if (f) target.offset(i, j, 1, 1).formulas = f; }));
});
}
const previewOptions = { parts: { titleBar: false, ribbon: false, formulaBar: false, sidePanels: false } };
export function InputAndPreview() {
const preview = useRef(null);
const target = () => preview.current?.workbook;
return (
<div style={{ display: 'grid', gridTemplateColumns: '1fr 1fr', gap: 12, height: 520 }}>
<Sheet
onCellsChanged={(e) => target() && copyChange(e, target())}
onSelectionChanged={(e) => target()?.showCell(e.to.row, e.to.col)}
/>
<Sheet ref={preview} options={previewOptions} />
</div>
);
}Vue
<script setup>
import { Sheet, useSheet } from '@8sheets/vue';
const { sheet, workbook: preview } = useSheet(); // <Sheet ref="sheet"> below is the preview
const previewOptions = { parts: { titleBar: false, ribbon: false, formulaBar: false, sidePanels: false } };
function copyChange(e) {
const to = preview.value;
if (!to) return;
const { r0, c0, r1, c1 } = e.bounds;
const source = e.sheet.range(r0, c0, r1, c1);
const target = to.sheet(e.sheet.index).range(r0, c0, r1, c1);
const formulas = source.formulas;
to.history.transaction('Mirror', () => {
target.values = source.values;
formulas.forEach((row, i) => row.forEach((f, j) => { if (f) target.offset(i, j, 1, 1).formulas = f; }));
});
}
</script>
<template>
<div style="display: grid; grid-template-columns: 1fr 1fr; gap: 12px; height: 520px">
<Sheet @cells-changed="copyChange" @selection-changed="(e) => preview?.showCell(e.to.row, e.to.col)" />
<Sheet ref="sheet" :options="previewOptions" />
</div>
</template>Angular
import { Component } from '@angular/core';
import { SheetComponent, SheetEventPayload, SheetsWorkbook } from '@8sheets/angular';
@Component({
selector: 'app-input-and-preview',
imports: [SheetComponent],
template: `
<div class="pair">
<eight-sheet (cellsChanged)="copyChange($event)" (selectionChanged)="follow($event)" />
<eight-sheet [options]="previewOptions" (ready)="preview = $event" />
</div>
`,
styles: `.pair { display: grid; grid-template-columns: 1fr 1fr; gap: 12px; height: 520px; }`,
})
export class InputAndPreview {
preview?: SheetsWorkbook;
previewOptions = { parts: { titleBar: false, ribbon: false, formulaBar: false, sidePanels: false } };
copyChange(e: SheetEventPayload) {
const to = this.preview;
if (!to) return;
const { r0, c0, r1, c1 } = e['bounds'];
const source = e['sheet'].range(r0, c0, r1, c1);
const target = to.sheet(e['sheet'].index).range(r0, c0, r1, c1);
const formulas: (string | null)[][] = source.formulas;
to.history.transaction('Mirror', () => {
target.values = source.values;
formulas.forEach((row, i) => row.forEach((f, j) => { if (f) target.offset(i, j, 1, 1).formulas = f; }));
});
}
follow(e: SheetEventPayload) {
this.preview?.showCell(e['to'].row, e['to'].col);
}
}This mirrors cell contents, which is what cells-changed reports. Formatting, new sheets and structure changes aren't mirrored; for a full copy at a moment of your choosing, use preview.fromJSON(input.toJSON()). For people editing the same workbook on different machines, use real-time collaboration instead.
Email a range as HTML
const html = await wb.rangeToHtml('A1:F20');
await fetch('/api/mail', { method: 'POST', headers: { 'content-type': 'application/json' }, body: JSON.stringify({ to, subject: 'Weekly figures', html }) });
Keep the model in your database
toJSON gives plain data you can store in a JSON column and load into a new workbook later, in the browser or in Node:
const data = wb.toJSON();
await fetch('/api/models/7', { method: 'PUT', headers: { 'content-type': 'application/json' }, body: JSON.stringify(data) });
// Later, in another session:
const saved = await (await fetch('/api/models/7')).json();
const again = await mountWorkbook(element, { data: saved });
JSON leaves out PivotTable definitions, data sheet sources and picture bytes; use saveDocument() for workbooks that have them.