- Spreadsheet docs
- Developers
PivotTables and slicers from code
Build PivotTables from a range or a table, change their fields and layout, filter them, add slicers and refresh them from code.
A PivotTable summarises a list of records without formulas: sales by region and year, counts by status, averages by team. From code you create one with sheet.addPivotTable, then change its fields, layout and filters through the SheetPivot it returns, and add slicers that filter it with buttons. Each change rewrites the table's cells and is one undo step.
Build a report
JavaScript
import { mountWorkbook } from '@8sheets/core';
const wb = await mountWorkbook(document.querySelector('#sheet'));
const data = wb.activeSheet;
data.name = 'Data';
data.range('A1').values = [
['Region', 'Product', 'Year', 'Sales'],
['East', 'Pen', 2026, 120], ['West', 'Pen', 2026, 80], ['East', 'Ink', 2027, 150], ['West', 'Ink', 2027, 95],
];
const report = wb.addSheet('Report');
const pivot = report.addPivotTable('Data!A1:D5', 'A3', {
rows: ['Region'],
columns: ['Year'],
values: [{ field: 'Sales', summarize: 'sum', name: 'Total sales' }],
layout: 'tabular',
style: 'PivotStyleMedium9',
});
const slicer = pivot.addSlicer('Product', { x: 360, y: 20, width: 160, height: 180 });
slicer.selected = ['Pen']; // filters the PivotTable, as clicking the buttons does
console.log(pivot.range.values);React
import { Sheet } from '@8sheets/react';
function buildReport(wb) {
const report = wb.addSheet('Report');
const pivot = report.addPivotTable('Sales', 'A3', { // a table's name works as the source
rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
});
pivot.addSlicer('Product');
}
export function SalesReport({ onOpen }) {
return (
<div style={{ height: 600 }}>
<Sheet onReady={async (wb) => { await onOpen(wb); buildReport(wb); }} />
</div>
);
}Vue
<script setup>
import { Sheet } from '@8sheets/vue';
function buildReport(wb) {
const report = wb.addSheet('Report');
const pivot = report.addPivotTable('Sales', 'A3', {
rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
});
pivot.addSlicer('Product');
}
</script>
<template>
<Sheet style="height: 600px" @ready="buildReport" />
</template>Angular
import { Component } from '@angular/core';
import { SheetComponent, SheetsWorkbook } from '@8sheets/angular';
@Component({
selector: 'app-sales-report',
imports: [SheetComponent],
template: `<eight-sheet class="grid" (ready)="buildReport($event)" />`,
styles: `.grid { height: 600px; }`,
})
export class SalesReport {
buildReport(wb: SheetsWorkbook) {
const report = wb.addSheet('Report');
const pivot = report.addPivotTable('Sales', 'A3', {
rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
});
pivot.addSlicer('Product');
}
}The React, Vue and Angular samples assume a table named Sales with Region, Product, Year and Sales columns, such as one opened from a file.
Create
sheet.addPivotTable(source, at, options) adds a PivotTable on sheet, with its top-left cell at at.
source: a range with a header row ('Data!A1:D99'for another sheet), a table, or a table's name.options: the layout below, plusnameandforce. Adding throws when the table would cover cells that hold data;force: truewrites over them.
| Layout field | What it does |
|---|---|
rows, columns | Source headers, outermost first |
filters | Report filters, shown above the table |
values | Headers, or { field, summarize, name }. summarize is sum, count, average, max, min, product, countNums, stdDev, stdDevp, var or varp; left out, sum for numbers and count otherwise. name is the caption, Sum of Sales by default |
layout | compact, outline or tabular, as Report Layout offers |
grandTotalRows, grandTotalColumns | The totals row at the bottom and the totals column at the right |
style | A PivotTable style such as PivotStyleMedium9 |
Change
pivot.setLayout({ rows: ['Region', 'Product'], filters: ['Year'] }); // areas left out keep their fields
pivot.filter('Region', ['East', 'West']); // show only these items; null shows every item again
pivot.items('Region'); // [{ value, label, visible }]
pivot.layout; // the layout now, in the form setLayout takes
pivot.fields; // the source's headers
pivot.range.address; // the cells it fills now, report filters included
await pivot.refresh(); // read the source again, after its data changed
pivot.remove(); // delete it and clear its cells
report.pivotTables lists the PivotTables on a sheet, and report.pivotTable(name) finds one by name. refresh() also rewrites every PivotTable that shares the source, and calculates the source's formulas first.
Slicers
pivot.addSlicer(field, options) adds a slicer for a source field; options takes caption, columns (of buttons) and a position and size in points: x, y, width, height. It filters this PivotTable and every one that shares its source; a mounted workbook shows it over the grid.
| Member | What it does |
|---|---|
selected | The values of the selected items. Set an array to select exactly those, null or [] to clear |
items | Every item with whether it is selected and has data |
caption, field | The caption over the buttons, and the field it filters |
clear() | Shows every item again |
remove() | Takes the slicer away; the filter it set stays, as in Excel |
pivot.slicers finds the slicers of a PivotTable again.
In the app
Users build PivotTables with Insert > PivotTable and change them in the field list; see Data tools. From your own button, wb.commands.execute('ui.insertPivotTable') opens the same dialog for the selection, and ui.insertSlicer and ui.insertTimeline add slicers and timelines.
Limits
- Headless workbooks create, change and refresh PivotTables, and their cells are saved. The definition itself lives only while the workbook does: a headless workbook doesn't write PivotTable definitions to
.xlsxor JSON. A mounted workbook saves them in.xlsxand.8sh, as the app does. - Slicers aren't written to
.xlsxyet; the filter they set is. Slicers of tables and timelines aren't in the API yet. - PivotCharts, calculated fields, grouping of dates and numbers, and "show values as" are available in the app, not yet in the API.